Home » RDBMS Server » Performance Tuning » Connect By Cause Full tablescan:pl help
| Connect By Cause Full tablescan:pl help [message #65824] |
Tue, 04 January 2005 23:56  |
BhavinShah
Messages: 105
Registered: February 2004
|
Senior Member |
|
|
My prob is when I am using Connect by clause my tables main_trn1 and main_trn2 scaned full.But when I have removed connect by clause my plan has been changed dramatically.
--With connect by clause
select
c.trans_id,c.cop_id, c.parent_id, c.entry_date, c.user_id,c.l1e1_id,c.l1e1_name,c.l2e1_id,
c.l2e1_name, c.l1e2_id,c.l1e2_name,c.l2e2_id,c.l2e2_name,c.l1e3_id,c.l1e3_name, c.l2e3_id,c.l2e3_name,c.l1e4_id,
c.l1e4_name,c.l2e4_id,c.l2e4_name, c.l1e5_id,c.l1e5_name,c.l2e5_id,c.l2e5_name,c.l1e7_id,c.l1e6_id, c.l1e6_name,
c.l1e8_id,c.l1e8_name,c.logentry,c.user_id as assignedTo
from (
select
rownum r, main_trn1.trans_id,main_trn1.cop_id,
main_trn1.parent_id,to_char(main_trn1.entry_date,'MM/DD/YYYY HH:MM:SS') as entry_date, main_trn1.user_id, main_trn1.l1e1_id,
main_trn1.l1e1_name,main_trn1.l2e1_id,main_trn1.l2e1_name, main_trn1.l1e2_id,main_trn1.l1e2_name,main_trn1.l2e2_id,
main_trn1.l2e2_name, main_trn1.l1e3_id,main_trn1.l1e3_name,main_trn1.l2e3_id,main_trn1.l2e3_name, main_trn1.l1e4_id,
main_trn1.l1e4_name,main_trn1.l2e4_id,main_trn1.l2e4_name, main_trn1.l1e5_id,main_trn1.l1e5_name,main_trn1.l2e5_id,main_trn1.l2e5_name,
main_trn1.l1e7_id, main_trn1.l1e6_id,main_trn1.l1e6_name, main_trn1.l1e8_id,main_trn1.l1e8_name, main_trn2.logentry,
main_trn2.user_id as assignedTo
from
main_trn1,main_trn2
where
main_trn2.trans_id = main_trn1.trans_id and main_trn1.cop_id =3
and l1e1_id=14
connect by prior main_trn1.trans_id = main_trn1.parent_id start with main_trn1.parent_id is null )
c where c.r between 1 and 15
Exexution Plan :
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=568 Card=3632 Bytes=
8676848)
1 0 VIEW (Cost=568 Card=3632 Bytes=8676848)
2 1 COUNT
3 2 FILTER
4 3 CONNECT BY (WITH FILTERING)
5 4 FILTER
6 5 COUNT
7 6 MERGE JOIN (Cost=568 Card=3632 Bytes=1569024)
8 7 SORT (JOIN) (Cost=473 Card=3632 Bytes=133294
4)
9 8 TABLE ACCESS (FULL) OF 'MAIN_TRN2' (Cost=6
6 Card=3632 Bytes=1332944)
10 7 SORT (JOIN) (Cost=95 Card=3634 Bytes=236210)
11 10 TABLE ACCESS (FULL) OF 'MAIN_TRN1' (Cost=1
3 Card=3634 Bytes=236210)
12 4 HASH JOIN
13 12 CONNECT BY PUMP
14 12 COUNT
15 14 MERGE JOIN (Cost=568 Card=3632 Bytes=1569024)
16 15 SORT (JOIN) (Cost=473 Card=3632 Bytes=133294
4)
17 16 TABLE ACCESS (FULL) OF 'MAIN_TRN2' (Cost=6
6 Card=3632 Bytes=1332944)
18 15 SORT (JOIN) (Cost=95 Card=3634 Bytes=236210)
19 18 TABLE ACCESS (FULL) OF 'MAIN_TRN1' (Cost=1
3 Card=3634 Bytes=236210)
Statistics
----------------------------------------------------------
0 recursive calls
9160 db block gets
3216 consistent gets
6045 physical reads
0 redo size
7977 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
7 sorts (disk)
15 rows processed
--without connect by clause..
select
c.trans_id,c.cop_id, c.parent_id, c.entry_date, c.user_id,c.l1e1_id,c.l1e1_name,c.l2e1_id,
c.l2e1_name, c.l1e2_id,c.l1e2_name,c.l2e2_id,c.l2e2_name,c.l1e3_id,c.l1e3_name, c.l2e3_id,c.l2e3_name,c.l1e4_id,
c.l1e4_name,c.l2e4_id,c.l2e4_name, c.l1e5_id,c.l1e5_name,c.l2e5_id,c.l2e5_name,c.l1e7_id,c.l1e6_id, c.l1e6_name,
c.l1e8_id,c.l1e8_name,c.logentry,c.user_id as assignedTo
from (
select
rownum r, main_trn1.trans_id,main_trn1.cop_id,
main_trn1.parent_id,to_char(main_trn1.entry_date,'MM/DD/YYYY HH:MM:SS') as entry_date, main_trn1.user_id, main_trn1.l1e1_id,
main_trn1.l1e1_name,main_trn1.l2e1_id,main_trn1.l2e1_name, main_trn1.l1e2_id,main_trn1.l1e2_name,main_trn1.l2e2_id,
main_trn1.l2e2_name, main_trn1.l1e3_id,main_trn1.l1e3_name,main_trn1.l2e3_id,main_trn1.l2e3_name, main_trn1.l1e4_id,
main_trn1.l1e4_name,main_trn1.l2e4_id,main_trn1.l2e4_name, main_trn1.l1e5_id,main_trn1.l1e5_name,main_trn1.l2e5_id,main_trn1.l2e5_name,
main_trn1.l1e7_id, main_trn1.l1e6_id,main_trn1.l1e6_name, main_trn1.l1e8_id,main_trn1.l1e8_name, main_trn2.logentry,
main_trn2.user_id as assignedTo
from
main_trn1,main_trn2
where
main_trn2.trans_id = main_trn1.trans_id and main_trn1.cop_id =3
and l1e1_id=14
)
c
where c.r between 1 and 15
Execution Plan :
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=68 Card=1 Bytes=2402
)
1 0 VIEW (Cost=68 Card=1 Bytes=2402)
2 1 COUNT
3 2 NESTED LOOPS (Cost=68 Card=1 Bytes=432)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'MAIN_TRN1' (Cost=1
Card=1 Bytes=65)
5 4 BITMAP CONVERSION (TO ROWIDS)
6 5 BITMAP INDEX (SINGLE VALUE) OF 'MAIN_TRN1_COPID'
7 3 TABLE ACCESS (FULL) OF 'MAIN_TRN2' (Cost=66 Card=1 B
ytes=367)
--------------------
So My prob is when I am using Connect by clause my tables main_trn1 and main_trn2 scaned full.But when I have removed connect by clause my plan has been changed dramatically.
Now I don't want to do full table scan with connect by clause .. Is there any way..I have to use connect by clause but i don't want full table scans..
main_trn1 :
SQL> desc main_trn1
Name Null? Type
----------------------- -------- ---------------
TRANS_ID NOT NULL NUMBER(10)
COP_ID NOT NULL NUMBER(9)
USER_ID NOT NULL VARCHAR2(20)
ENTRY_DATE NOT NULL DATE
L1E1_ID NUMBER(9)
L1E2_ID NUMBER(9)
L1E3_ID NUMBER(9)
L1E4_ID NUMBER(9)
L1E5_ID NUMBER(9)
L2E1_ID NUMBER(9)
L2E2_ID NUMBER(9)
L2E3_ID NUMBER(9)
L2E4_ID NUMBER(9)
L2E5_ID NUMBER(9)
L1E1_NAME NUMBER(9)
L1E2_NAME NUMBER(9)
L1E3_NAME NUMBER(9)
L1E4_NAME NUMBER(9)
L1E5_NAME NUMBER(9)
L2E1_NAME NUMBER(9)
L2E2_NAME NUMBER(9)
L2E3_NAME NUMBER(9)
L2E4_NAME NUMBER(9)
L2E5_NAME NUMBER(9)
L1E7_ID NUMBER(9)
L1E8_ID NUMBER(9)
L1E6_ID NUMBER(9)
L1E7_NAME NUMBER(9)
L1E8_NAME NUMBER(9)
L1E6_NAME NUMBER(9)
PARENT_ID NUMBER(10)
L2E6_ID NUMBER(9)
L2E7_ID NUMBER(9)
L2E8_ID NUMBER(9)
L2E6_NAME NUMBER(9)
L2E7_NAME NUMBER(9)
L2E8_NAME NUMBER(9)
main_trn2 :
Name Null? Type
----------------------- -------- ----------------
TRANS_ID NOT NULL NUMBER(10)
LOGENTRY VARCHAR2(4000)
USER_ID VARCHAR2(20)
Pl. help ..Thax..
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jan 03 15:27:03 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
