Bizarreness / Oddity of START WITH … CONNECT BY in Oracle SQL

Usually, the “start with .. connect by” clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts). For example, see this data hierarchical :

000
    001
        100
            110
                111
                112
    002
        200
        250
    004
999

then the sql scripts like this :
create table tmp_test_connectby (
ORG_ID varchar2(3 byte),
org_parent varchar2(3 byte),
constraint c01 unique (org_id)
);

insert into tmp_test_connectby values (‘000’, null);
insert into tmp_test_connectby values (‘999’, null);

insert into tmp_test_connectby values (‘001’, ‘000’);
insert into tmp_test_connectby values (‘002’, ‘000’);
insert into tmp_test_connectby values (‘004’, ‘000’);

insert into tmp_test_connectby values (‘100’, ‘001’);
insert into tmp_test_connectby values (‘110’, ‘001’);

insert into tmp_test_connectby values (‘111’, ‘110’);
insert into tmp_test_connectby values (‘112’, ‘110’);

insert into tmp_test_connectby values (‘200’, ‘002’);
insert into tmp_test_connectby values (‘250’, ‘002’);

commit;

select lpad(‘ ‘,2*(level-1)) || to_char(org_id) s
from tmp_test_connectby
start with org_parent is null
connect by prior org_id = org_parent;
———————————————
From here, if i want to get result

002
    200
    250

i run this sql,
SELECT org_id
FROM tmp_test_connectby
WHERE org_id IN (
SELECT org_id
FROM tmp_test_connectby
WHERE org_id = :b1
UNION ALL
SELECT org_id
FROM tmp_test_connectby
CONNECT BY org_id = org_parent
START WITH org_parent IN (
SELECT org_id
FROM tmp_test_connectby
WHERE org_id = :b1
UNION ALL
SELECT org_id
FROM tmp_test_connectby
CONNECT BY org_id = org_parent
START WITH org_parent = :b1))
ORDER BY org_id;

but when i make an update to record like this
update tmp_test_connectby
set org_parent = ‘999’
where org_id = ‘999’;

and run this again,
SELECT org_id
FROM tmp_test_connectby
WHERE org_id IN (
SELECT org_id
FROM tmp_test_connectby
WHERE org_id = :b1
UNION ALL
SELECT org_id
FROM tmp_test_connectby
CONNECT BY org_id = org_parent
START WITH org_parent IN (
SELECT org_id
FROM tmp_test_connectby
WHERE org_id = :b1
UNION ALL
SELECT org_id
FROM tmp_test_connectby
CONNECT BY org_id = org_parent
START WITH org_parent = :b1))
ORDER BY org_id;

then you may have your database memory consume to nothing,
you may got this error too :

ORA-04030: out of process memory when trying to allocate 80 bytes (kxs-heap-w,cursor work heap)
ORA-04031: unable to allocate 8224 bytes of shared memory…

so be carefull about using “START WITH … CONNECT BY”, if something wrong, check your data

Oracle/PLSQL Check-Unique Constraint / Index

Oracle constraints are used to enforce database integrity (enforce table rules and prevent data dependent. It can be used to enforce business rules.

Generally, there are 5 types of constraints (PUNCR) in the Oracle, ie:
* Primary Key (Type P)
* Unique (Type U)
* Not Null (Type N)
* Check (Type C)
* Foreign Key (Referential – Type R)

But I want 1 more type that use check and then unique, so i can be called Oracle Check-Unique Constraint. So I googling around and I did not find even one article that discusses it.

And then with the advice of my partner, Hendra Ong, by using the unique index this can be done.

So here, my problem
I want table TRD_COLLECTION have validation
if status = ‘3’ and pay_type =’C’ then must be use unique on these columns (ORG_ID, AGREEMENT_ID, PAY_TYPE)
and if status ‘3’ then no unique.

and the solution
CREATE UNIQUE INDEX TRD_COLLECTION_T01 ON TRD_COLLECTION
(CASE WHEN STATUS = ‘3’ AND PAY_TYPE = ‘C’ THEN
ORG_ID || AGREEMENT_ID || PAY_TYPE
ELSE
NULL
END)
TABLESPACE TMP_IDX

Any suggestions on that would be acceptable.

The Mystery of Number 9

Let’s play with number 9 to find some mystery

1. Numbers in each multiplication of nine, when added together the end result is always nine
example:
1 x 9 = 9 —> 9
2 x 9 = 18 —> 1 + 8 = 9
3 x 9 = 27 —> 2 + 7 = 9
4 x 9 = 36 —> 3 + 6 = 9
5 x 9 = 45 —> 4 + 5 = 9
10 x 9 = 90 —> 9 + 0 = 9
etc. …

This also applies even to a complex multiplication.
sample (random):
638 x 9 = 5742 —> 5 + 7 + 4 + 2 = 18 —> 1 + 8 = 9
23477 x 9 = 211293 —> 2 + 1 + 1 + 2 + 9 + 3 = 18 —> 1 + 8 = 9
etc. …

2. Each number is not divisible by nine, then the numbers behind the comma the results will be repeated
example:
8 / 9 = 0.88888888888888………
23 / 9 = 2.55555555555555………
56768231 / 9 = 6307581.22222222………

3. Multiplication with the number that composed of the same digit
example:
33 x 9 = 297 —> 3 x 9 = 27 put 9 (1 times) in the middle of 2 and 7, so the result 297
444 x 9 = 3996 —> 4 x 9 = 36 put 9 (2 times) in the middle of 3 and 6, so the result 3996
5555 x 9 = 49995 —> 4 x 9 = 36 put 9 (3 times) in the middle of 4 and 5, so the result 49995
etc. …

4. If 12345679 (remember without the number 8) multiplied by 1-9 multiples of 9 then the result must contain the numbers over and over
example:
12345679 x 9 = 111111111
12345679 x 18 = 222222222 —> 12345679 x 9 x 2
12345679 x 27 = 333333333 —> 12345679 x 9 x 3
12345679 x 36 = 444444444 —> 12345679 x 9 x 4
.
.
.
12345679 x 81 = 999999999 —> 12345679 x 9 x 9

That’s all, maybe next time i will add some more fun with 9. Cheers.

Six of Nines

teng… teng… teng… 09/09/09 09:09:09
let’s play with this time

9*9*9*9*9*9=531441 => 5+3+1+4+4+1=18 => 1+8=9
9+9+9+9+9+9=54     => 5+4=9
9-9-9-9-9-9=-36    => -3-6=-9
9/9/9/9/9/9        => 0.000152415(9 digit decimal)    => 0+0+0+1+5+2+4+1+5=18 => 1+8=9

what will happen?