Connect by clause taking lot of time [message #674880] |
Wed, 20 February 2019 12:13 |
rajivn786
Messages: 161 Registered: January 2010
|
Senior Member |
|
|
Hi,
I have this connect by query which takes lot of time, It keeps on executing and never ends.
Create bitmap index got_next_r_num_idx on got_next_r_num(mm);
Create bitmap index got_next_r_nnum_idx on got_next_r_num(next_r_num);
Create bitmap index got_next_rn_num_idx on got_next_r_num(r_num);
Drop table aki_resolution_dates1 purge;
Create table aki_resolution_dates1 as
SELECT --+ parallel(g 128) index(g)
mm, date1, date2, sysdate dt
FROM got_next_r_num g
START WITH r_num = 1
CONNECT BY mm= PRIOR mm
AND r_num = PRIOR next_r_num;
Table "got_next_r_num" has around 287753 rows.
Attaching only 13k due to size limitations.
-
Attachment: ff.csv
(Size: 890.21KB, Downloaded 1762 times)
|
|
|
|
|
Re: Connect by clause taking lot of time [message #674883 is a reply to message #674882] |
Wed, 20 February 2019 12:39 |
rajivn786
Messages: 161 Registered: January 2010
|
Senior Member |
|
|
create table GOT_NEXT_R_NUM
(
medical_record_number VARCHAR2(20) not null,
date1 DATE,
date2 DATE,
r_num NUMBER,
next_r_num NUMBER
)
Create bitmap index got_next_r_num_idx on got_next_r_num(mm);
Create bitmap index got_next_r_nnum_idx on got_next_r_num(next_r_num);
Create bitmap index got_next_rn_num_idx on got_next_r_num(r_num);
Plan hash value: 109065721
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1434 | 80304 | 2080 (1)| 00:00:25 |
|* 1 | CONNECT BY WITH FILTERING | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | GOT_NEXT_R_NUM | 717 | 22227 | 149 (1)| 00:00:02 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | GOT_NEXT_RN_NUM_IDX | | | | |
| 5 | NESTED LOOPS | | 717 | 40152 | 1930 (1)| 00:00:24 |
| 6 | CONNECT BY PUMP | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | GOT_NEXT_R_NUM | 1 | 31 | 1930 (1)| 00:00:24 |
| 8 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 9 | BITMAP AND | | | | | |
|* 10 | BITMAP INDEX SINGLE VALUE| GOT_NEXT_RN_NUM_IDX | | | | |
|* 11 | BITMAP INDEX SINGLE VALUE| GOT_NEXT_R_NUM_IDX | | | | |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MEDICAL_RECORD_NUMBER"=PRIOR "MEDICAL_RECORD_NUMBER" AND "R_NUM"=PRIOR
"NEXT_R_NUM")
4 - access("R_NUM"=1)
10 - access("R_NUM"="connect$_by$_pump$_002"."PRIOR next_r_num ")
11 - access("MEDICAL_RECORD_NUMBER"="connect$_by$_pump$_002"."PRIOR
medical_record_nu$POS190")
I dont think I have permissions enabled to see tkprof/trace.
Let me know if this helps.
|
|
|
|
Re: Connect by clause taking lot of time [message #674885 is a reply to message #674884] |
Wed, 20 February 2019 13:06 |
rajivn786
Messages: 161 Registered: January 2010
|
Senior Member |
|
|
Looks like nothing changed
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
Plan hash value: 109065721
| 0 | SELECT STATEMENT | | 1434 | 80304 | 2080 (1)| 00:00:25 |
| 2 | TABLE ACCESS BY INDEX ROWID | GOT_NEXT_R_NUM | 717 | 22227 | 149 (1)| 00:00:02 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 5 | NESTED LOOPS | | 717 | 40152 | 1930 (1)| 00:00:24 |
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
|* 1 | CONNECT BY WITH FILTERING | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | GOT_NEXT_RN_NUM_IDX | | | | |
| 6 | CONNECT BY PUMP | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | GOT_NEXT_R_NUM | 1 | 31 | 1930 (1)| 00:00:24 |
| 8 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 9 | BITMAP AND | | | | | |
|* 10 | BITMAP INDEX SINGLE VALUE| GOT_NEXT_RN_NUM_IDX | | | | |
|* 11 | BITMAP INDEX SINGLE VALUE| GOT_NEXT_R_NUM_IDX | | | | |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MEDICAL_RECORD_NUMBER"=PRIOR "MEDICAL_RECORD_NUMBER" AND "R_NUM"=PRIOR
"NEXT_R_NUM")
4 - access("R_NUM"=1)
10 - access("R_NUM"="connect$_by$_pump$_002"."PRIOR next_r_num ")
11 - access("MEDICAL_RECORD_NUMBER"="connect$_by$_pump$_002"."PRIOR
medical_record_nu$POS159")
|
|
|
|
|
|