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 |
|
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
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 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
From your previous topic:
John Watson wrote on Wed, 05 December 2018 13:21Welcome 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 |
|
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 |
|
Michel Cadot
Messages: 68665 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:21Welcome 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 |
Bill B
Messages: 1971 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 #674700 is a reply to message #674699] |
Mon, 11 February 2019 14:51 |
Bill B
Messages: 1971 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;
|
|
|
|
Goto Forum:
Current Time: Tue Jul 02 21:14:27 CDT 2024
|