Home » SQL & PL/SQL » SQL & PL/SQL » Tree View, Connect by Prior on 100m records (Oracle 12c, RED HAT)
Tree View, Connect by Prior on 100m records [message #674688] Sun, 10 February 2019 09:22 Go to next message
sureshonly@hotmail.com
Messages: 9
Registered: December 2018
Junior Member
Hi Oracle Gurus,

My table structure is similar to EMP table with CHILD_NODE, SRC_NAME, PARENT_NODE. just three columns. But the problem is I got over 100M records in the table

I am trying to get the tree View using below query.

select SYS_CONNECT_BY_PATH(SRC_NAME, ' <- ')
from BRANCHES
start with PARENT_NODE is null
connect by prior CHILD_NODE = PARENT_NODE;


CHILD_NODE, SRC_NAME, PARENT NODE
A...........SYS1...........[NULL]
B...........SYS2...........A
C...........SYS1...........B
D...........SYS3...........A
E...........SYS2...........A

EXPECTED RESULTS:
SRC_NAME...........Count
_____________________________________
SYS1...............1...........(A)
SYS1<-SYS2.........2...........(A<-B, A<-E)
SYS1<-SYS2<-SYS1...1...........(A<-B<-C)
SYS1<-SYS3.........1...........(A<-D)

I created individual indexes on CHILD_NODE, PARENT_NODE columns.

But query is running forever. Its been running for more than 24 hours, still no sign of completion. wait time showing weeks in the longops view.
Above query is working fine for another table which has got only 30M records. But 100M records is not responding Sad
Can you please help me how I can tune this sql.

Regards,
Suresh

[Updated on: Sun, 10 February 2019 09:27]

Report message to a moderator

Re: Tree View, Connect by Prior on 100m records [message #674690 is a reply to message #674688] Sun, 10 February 2019 12:39 Go to previous messageGo to next message
Michel Cadot
Messages: 66170
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

John Watson wrote on Wed, 05 December 2018 13:21
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read
...

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

Re: Tree View, Connect by Prior on 100m records [message #674692 is a reply to message #674690] Sun, 10 February 2019 13:19 Go to previous messageGo to next message
sureshonly@hotmail.com
Messages: 9
Registered: December 2018
Junior Member
CREATE TABLE BRANCHES
( CHILD_NODE VARCHAR2(10),
SRC_NAME VARCHAR2(10),
PARENT_NODE VARCHAR2(10)
);
CREATE INDEX "INX_BR_01" ON "BRANCHES" ("CHILD_NODE");
CREATE INDEX "INX_BR_02" ON "BRANCHES" ("PARENT_NODE");


INSERT INTO BRANCHES VALUES('A','SYS1',NULL);
INSERT INTO BRANCHES VALUES('B','SYS2','A');
INSERT INTO BRANCHES VALUES('C','SYS1','B');
INSERT INTO BRANCHES VALUES('D','SYS3','A');
INSERT INTO BRANCHES VALUES('E','SYS2','A');
COMMIT;

SELECT P, COUNT(1) FROM (
select cHILD_NODE, PARENT_NODE, sys_connect_by_path(SRC_NAME,'/') P
from BRANCHES p
start with PARENT_NODE is null
connect by PARENT_NODE= prior cHILD_NODE)
GROUP BY P;

PATH COUNT
/SYS1/SYS2/SYS1 1
/SYS1/SYS3 1
/SYS1/SYS2 2
/SYS1 1

query is working fine, but the problem is only with volume. its very bad on 100m table.
Re: Tree View, Connect by Prior on 100m records [message #674693 is a reply to message #674692] Sun, 10 February 2019 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 66170
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 10 February 2019 19:39

From your previous topic:

John Watson wrote on Wed, 05 December 2018 13:21
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read
...
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

[Updated on: Sun, 10 February 2019 13:30]

Report message to a moderator

Re: Tree View, Connect by Prior on 100m records [message #674696 is a reply to message #674693] Mon, 11 February 2019 05:49 Go to previous messageGo to next message
Bill B
Messages: 1837
Registered: December 2004
Senior Member
Null values are never indexed unless you include the column in a composite index. so when it is looking for null parent nodes it has to do a full table scan. Make your indexes like this

CREATE INDEX Inx_br_01 ON Branches(Child_node, Parent_node,Src_name);

CREATE INDEX Inx_br_02 ON Branches(Parent_node, Child_node,Src_name);

You should see quite a performance increase. If the child_node will never be null then I would make the column not null which will cause the optimizer to always look at the index when it's looking for a null parent_node. Also if all the columns that the query is looking for are in the index, oracle will never go to the data blocks. Since this is such a small table with 3 10 byte columns it's worth doing.

As always, make sure your statistics are up to date on the table.

WHen testing on a small table it will always choose a full table scan, but on your 100 million rows table it will use the indexes

[Updated on: Mon, 11 February 2019 06:03]

Report message to a moderator

Re: Tree View, Connect by Prior on 100m records [message #674699 is a reply to message #674696] Mon, 11 February 2019 08:34 Go to previous messageGo to next message
sureshonly@hotmail.com
Messages: 9
Registered: December 2018
Junior Member
Thanks Bill.

After I tweaked the Connect by clause and converted the null to 0 and rebuilt the index. Query working fine, returned the results after running for an hour.

Old "connect by prior CHILD_NODE = PARENT_NODE"

New "connect by PARENT_NODE= prior CHILD_NODE"

I will also try with your solution later. Thanks a lot.

Regards,
Suresh
Re: Tree View, Connect by Prior on 100m records [message #674700 is a reply to message #674699] Mon, 11 February 2019 14:51 Go to previous messageGo to next message
Bill B
Messages: 1837
Registered: December 2004
Senior Member
your fix worked because you were looking for an indexed value. Using the select below it will do an index scan and NOT a full table scan.

SELECT P, COUNT(1) FROM (
select cHILD_NODE, PARENT_NODE, sys_connect_by_path(SRC_NAME,'/') P
from BRANCHES p
start with PARENT_NODE = 0
connect by PARENT_NODE= prior cHILD_NODE)
GROUP BY P;
Re: Tree View, Connect by Prior on 100m records [message #674701 is a reply to message #674700] Mon, 11 February 2019 16:05 Go to previous message
sureshonly@hotmail.com
Messages: 9
Registered: December 2018
Junior Member
Thanks Bill,
But somehow I dont see the explain plan using index. But somehow query is running.
But I will still try your index solution to see how it works.
Regards,
Suresh
Previous Topic: Pull out string between two words in string column
Next Topic: How to collate rows
Goto Forum:
  


Current Time: Fri Feb 15 19:11:16 CST 2019