Queries against the data dictionary views
are especially problematic. Here is a query that accepts
two parameters and displays a range of days from the
all_objects view:
select
TO_DATE ('2009-06-10' /*+:1*/, 'YYYY-MM-DD')-1 + ROWNUM
AS MY_DAY from
all_objects
where
TO_DATE ('2009-05-26'/*+:2*/, 'YYYY-MM-DD')-1 + ROWNUM <=
TO_DATE('2009-06-08'
/*+:3*/, 'YYYY-MM-DD')
MY_DAY
06/10/2009 00:00:00
06/11/2009 00:00:00
06/12/2009 00:00:00
06/13/2009 00:00:00
06/14/2009
00:00:00
06/15/2009 00:00:00
06/16/2009 00:00:00
06/17/2009 00:00:00
06/18/2009 00:00:00
06/19/2009
00:00:00
06/20/2009 00:00:00
06/21/2009 00:00:00
06/22/2009 00:00:00
06/23/2009 00:00:00
Here is the original SQL execution plan, quite convoluted
and expensive:
ID PID Operation Name Rows Bytes Cost CPU
Cost IO Cost
0 SELECT STATEMENT 31576
3361K 255 201M 243
1 0 COUNT
2 1 FILTER
3 2 HASH JOIN 71259 7585K
255 201M 243
4 3 INDEX FULL SCAN
SYS.I_USER2 96 384 1 7321 1
5 3
HASH JOIN 71259 7306K 253 185M 242
6 5 INDEX FULL SCAN SYS.I_USER2 96
2208 1 7321 1
7 5 TABLE ACCESS
FULL SYS.OBJ$ 71259 5706K 251 170M 241
8 2 TABLE ACCESS BY INDEX ROWID SYS.IND$ 1
8 2 15801 2
9 8 INDEX UNIQUE
SCAN SYS.I_IND1 1 1 8171 1
10 2
HASH JOIN 1 24 3 8741280 2
11 10 INDEX RANGE SCAN SYS.I_OBJAUTH1 1
11 2 14730 2
12 10 FIXED TABLE
FULL SYS.X$KZSRO 100 1300 0 350000 0
13 2 FIXED TABLE FULL SYS.X$KZSPR 1 26
0 380297 0
14 2 HASH JOIN 1
24 3 8741280 2
15 14 INDEX RANGE
SCAN SYS.I_OBJAUTH1 1 11 2 14730 2
16 14 FIXED TABLE FULL SYS.X$KZSRO 100
1300 0 350000 0
17 2 FIXED TABLE
FULL SYS.X$KZSPR 1 26 0 380297 0
18
2 FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380490 0
19 2 NESTED LOOPS 2 48
2 151413 2
20 19 INDEX RANGE
SCAN SYS.I_OBJAUTH1 1 11 2 14913 2
21 19 FIXED TABLE FULL SYS.X$KZSRO 2 26
0 136500 0
22 2 NESTED LOOPS
23 22 NESTED LOOPS
1 77 8 411212 8
24 23
NESTED LOOPS 1 67 5 387358 5
25
24 NESTED LOOPS 1 56 4 378937
4
26 25 MERGE JOIN CARTESIAN
1 52 3 371615 3
27 26
INDEX RANGE SCAN SYS.I_OBJ5 1 39 3 21615 3
28 26 BUFFER SORT 100 1300
0 350000 0
29 28 FIXED
TABLE FULL SYS.X$KZSRO 100 1300 0 350000 0
30 25 INDEX RANGE SCAN SYS.I_USER2
1 4 1 7321 1
31 24 INDEX
RANGE SCAN SYS.I_OBJAUTH1 1 11 1 8421 1
32 23 INDEX RANGE SCAN SYS.I_DEPENDENCY1
3 2 15893 2
33 22 TABLE
ACCESS BY INDEX ROWID SYS.DEPENDENCY$ 1 10 3
23854 3
34 2 FIXED TABLE FULL SYS.X$KZSPR
1 26 0 370497 0
35 2 NESTED
LOOPS 2 76 2 153803 2
36 35
NESTED LOOPS 1 25 2 17303 2
37 36 TABLE ACCESS BY INDEX ROWID SYS.TRIGGER$
1 14 1 8881 1
38 37 INDEX
UNIQUE SCAN SYS.I_TRIGGER2 1 0 1050 0
39 36 INDEX RANGE SCAN SYS.I_OBJAUTH1 1
11 1 8421 1
40 35 FIXED TABLE
FULL SYS.X$KZSRO 2 26 0 136500 0
41
2 FIXED TABLE FULL SYS.X$KZSPR 1 26 0
370497 0
42 2 NESTED LOOPS
43 42 NESTED LOOPS 1 77
8 411213 8
44 43 NESTED LOOPS
1 67 5 387359 5
45 44
NESTED LOOPS 1 56 4 378938 4
46
45 MERGE JOIN CARTESIAN 1 52 3
371616 3
47 46 INDEX RANGE
SCAN SYS.I_OBJ5 1 39 3 21616 3
48
46 BUFFER SORT 100 1300 0
350000 0
49 48 FIXED TABLE
FULL SYS.X$KZSRO 100 1300 0 350000 0
50 45 INDEX RANGE SCAN SYS.I_USER2 1
4 1 7321 1
51 44 INDEX
RANGE SCAN SYS.I_OBJAUTH1 1 11 1 8421 1
52 43 INDEX RANGE SCAN SYS.I_DEPENDENCY1
3 2 15893 2
53 42 TABLE
ACCESS BY INDEX ROWID SYS.DEPENDENCY$ 1 10 3
23854 3
54 2 FIXED TABLE FULL SYS.X$KZSPR
1 26 0 370497 0
55 2 FIXED TABLE
FULL SYS.X$KZSPR 1 26 0 360300 0
56
2 FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380394 0
57 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 380490 0
58 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
370497 0
59 2 VIEW 1 13 2
7271 2
60 59 FAST DUAL 1 2
7271 2
61 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 370497 0
62 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380394 0
63 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 370497 0
64 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380490 0
65 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 380394 0
66 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
370497 0
67 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 360300 0
68 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380394 0
69 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 380394 0
70 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
360300 0
71 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 370497 0
72 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380297 0
73 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 380394 0
74 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380585 0
75 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 380394 0
76 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380297 0
77 2 NESTED LOOPS 2 42
2 151143 2
78 77 INDEX RANGE
SCAN SYS.I_OBJAUTH1 1 8 2 14643 2
79 77 FIXED TABLE FULL SYS.X$KZSRO 2 26
0 136500 0
80 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 380490 0
81 2
NESTED LOOPS 2 42 2 151143 2
82 81 INDEX RANGE SCAN SYS.I_OBJAUTH1 1
8 2 14643 2
83 81 FIXED TABLE
FULL SYS.X$KZSRO 2 26 0 136500 0
84
2 FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380585 0
85 2 VIEW 1 16 1
9621 1
86 85 SORT GROUP BY 1 86
1 9621 1
87 86 NESTED LOOPS
1 86 1 9621 1
88 87
MERGE JOIN CARTESIAN 1 78 0 1450 0
89 88 NESTED LOOPS 1 65 0
1250 0
90 89 INDEX UNIQUE SCAN
SYS.I_OLAP_CUBES$ 1 13 0 1050 0
91
89 TABLE ACCESS BY INDEX ROWID
SYS.OLAP_DIMENSIONALITY$ 1 52 0 200 0
92 91 INDEX RANGE SCAN
SYS.I_OLAP_DIMENSIONALITY$ 1 0 200 0
93 88 BUFFER SORT 1 13 0
1250 0
94 93 INDEX FULL SCAN
SYS.I_OLAP_CUBE_DIMENSIONS$ 1 13 0 200 0
95 87 INDEX RANGE SCAN SYS.I_OBJ1 1
8 1 8171 1
96 2 NESTED LOOPS
1 28 2 20493 2
97 96 INDEX
FULL SCAN SYS.I_USER2 1 20 1 12321 1
98 96 INDEX RANGE SCAN SYS.I_OBJ4 1 8
1 8171 1
Re-writing a dictionary query
Here is how it was re-written, using the connect by operator.
SELECT
TO_DATE ('2009-06-10' /*+:1*/, 'YYYY-MM-DD')
- 1 + ROWNUM AS MEDIA_DAY
FROM
dual
connect by
TO_DATE ('2009-05-26'/*+:2*/, 'YYYY-MM-DD') - 1
+ ROWNUM <= TO_DATE ('2009-06-08' /*+:3*/, 'YYYY-MM-DD')
The connect by in the
statement forces the return of the number of rows that this
calculation returns:
TO_DATE ('2009-05-26', 'YYYY-MM-DD') - 1 + ROWNUM
<= TO_DATE ('2009-06-08' , 'YYYY-MM-DD')
It does a recursive join until the
rownum value is hit. Its a fairly useful way of returning
a known number of rows. This re-written plan
is much faster and simpler:
0 SELECT STATEMENT 1 2 7271 2
1
0 COUNT
2 1 CONNECT BY
WITHOUT FILTERING
3 2 FAST
DUAL 1 2 7271 2
