Hi All,
i think i faced a big issue for me nowadays. Following query running long time and couldnt finish.
SELECT
ip_id,level lvl,cc_nbr,trns_to_cc_nbr, trns_to_date,
CONNECT_BY_ROOT trns_to_cc_nbr final_cc_id,
SYS_CONNECT_BY_PATH(trns_to_cc_nbr,'/') final_path
FROM pg.tdwccar a
CONNECT BY NOCYCLE PRIOR cc_nbr = trns_to_cc_nbr
PLAN_TABLE_OUTPUT
Plan hash value: 3739935319
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 38M| 1971M| 11270 (4)| 00:03:12 | | | |
|* 1 | CONNECT BY WITHOUT FILTERING | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 38M| 1971M| 11270 (4)| 00:03:12 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 38M| 1971M| 11270 (4)| 00:03:12 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS STORAGE FULL| TDWCCAR | 38M| 1971M| 11270 (4)| 00:03:12 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TRNS_TO_CC_NBR"=PRIOR "CC_NBR")
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Please advise me,
Thanks,
Sinan,