rajesh@ORA10GR2> create table t
2 as
3 select * from all_objects;
rajesh@ORA10GR2> create index t_ind on t(object_id) nologging;
Index created.
Elapsed: 00:00:00.32
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.42
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select *
2 from t
3 where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );
Elapsed: 00:00:00.07
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"=VALUE(KOKBF$))
Statistics
----------------------------------------------------------
274 recursive calls
0 db block gets
797 consistent gets
1 physical reads
0 redo size
1394 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
5 rows processed
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
27 consistent gets
0 physical reads
0 redo size
1388 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
The predicate rownum > 0 is added to have the subquery "materialized". Now we have the correct cardinality in plan & the optimizer picked up the index to answer our queries.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.02 0 0 0 0
Fetch 1 0.04 0.03 0 694 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.06 0.05 0 694 0 5
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
5 TABLE ACCESS FULL T (cr=694 pr=0 pw=0 time=140 us)
rajesh@ORA10GR2>
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 1 0.03 0.03 0 113 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.04 0.04 0 113 0 5
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
5 TABLE ACCESS BY INDEX ROWID T (cr=113 pr=0 pw=0 time=97 us)
5 INDEX FULL SCAN T_IND (cr=112 pr=0 pw=0 time=60 us)(object id 55213)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 9 0 0
Fetch 1 0.00 0.00 0 9 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 18 0 5
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
5 TABLE ACCESS BY INDEX ROWID T (cr=9 pr=0 pw=0 time=278 us)
12 NESTED LOOPS (cr=8 pr=0 pw=0 time=889 us)
6 SORT UNIQUE (cr=0 pr=0 pw=0 time=92 us)
6 COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=28 us)
5 INDEX RANGE SCAN T_IND (cr=8 pr=0 pw=0 time=93 us)(object id 55213)
When working with Oracle 10g database, we need to Instruct cardinality to optimizer using Cardinality Hint. But when repeating the same test in Oracle 11g database
select object_id,data_object_id,owner,object_type,object_name
from t
where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.02 0 1026 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.02 0 1026 0 5
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
5 HASH JOIN RIGHT SEMI (cr=1026 pr=0 pw=0 time=0 us cost=316 size=49 card=1)
6 COLLECTION ITERATOR CONSTRUCTOR FETCH (cr=0 pr=0 pw=0 time=5 us cost=29 size=16336 card=8168)
71598 TABLE ACCESS FULL T (cr=1026 pr=0 pw=0 time=99596 us cost=286 size=3365106 card=71598)
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
5 NESTED LOOPS (cr=12 pr=0 pw=0 time=0 us)
5 NESTED LOOPS (cr=10 pr=0 pw=0 time=136 us cost=36 size=294 card=6)
6 SORT UNIQUE (cr=0 pr=0 pw=0 time=5 us cost=29 size=12 card=6)
6 COLLECTION ITERATOR CONSTRUCTOR FETCH (cr=0 pr=0 pw=0 time=5 us cost=29 size=12 card=6)
5 INDEX RANGE SCAN T_IND (cr=10 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 79427)
5 TABLE ACCESS BY INDEX ROWID T (cr=2 pr=0 pw=0 time=0 us cost=2 size=47 card=1)
You see the optimizer hard parsed the query again & came up with right explain plan to answer this query. And further execution optimizer has reused the plan again.
2 as
3 select * from all_objects;
Table created.
Elapsed: 00:00:04.21
rajesh@ORA10GR2>rajesh@ORA10GR2> create index t_ind on t(object_id) nologging;
Index created.
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.42
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select *
2 from t
3 where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 1584247153
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 187 (4)|
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 187 (4)|
| 2 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | | |
| 3 | TABLE ACCESS FULL | T | 50096 | 161 (3)|
----------------------------------------------------------------------------
----------------------------------------------------------
Plan hash value: 1584247153
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 187 (4)|
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 187 (4)|
| 2 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | | |
| 3 | TABLE ACCESS FULL | T | 50096 | 161 (3)|
----------------------------------------------------------------------------
---------------------------------------------------
1 - access("OBJECT_ID"=VALUE(KOKBF$))
Statistics
----------------------------------------------------------
274 recursive calls
0 db block gets
797 consistent gets
1 physical reads
0 redo size
1394 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
5 rows processed
rajesh@ORA10GR2>
Optimizer is not using the index, since cardinality is not attached to plan at id=2, So how can we instruct the optimizer about cardinality? using undocumented hint cardinality
rajesh@ORA10GR2> select *
2 from t
3 where object_id in (select /*+ cardinality(t,5) */ * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) t where rownum > 0);
2 from t
3 where object_id in (select /*+ cardinality(t,5) */ * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) t where rownum > 0);
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 561482654
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
-------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 27 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 2 |
| 2 | NESTED LOOPS | | 1 | 27 |
| 3 | VIEW | VW_NSO_1 | 5 | 24 |
| 4 | HASH UNIQUE | | 1 | |
| 5 | COUNT | | | |
|* 6 | FILTER | | | |
| 7 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | | |
|* 8 | INDEX RANGE SCAN | T_IND | 1 | 1 |
--------------------------------------------------------------------------------
----------------------------------------------------------
Plan hash value: 561482654
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
-------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 27 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 2 |
| 2 | NESTED LOOPS | | 1 | 27 |
| 3 | VIEW | VW_NSO_1 | 5 | 24 |
| 4 | HASH UNIQUE | | 1 | |
| 5 | COUNT | | | |
|* 6 | FILTER | | | |
| 7 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | | |
|* 8 | INDEX RANGE SCAN | T_IND | 1 | 1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
6 - filter(ROWNUM>0)
8 - access("OBJECT_ID"="COLUMN_VALUE")
8 - access("OBJECT_ID"="COLUMN_VALUE")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
27 consistent gets
0 physical reads
0 redo size
1388 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2>
The predicate rownum > 0 is added to have the subquery "materialized". Now we have the correct cardinality in plan & the optimizer picked up the index to answer our queries.
Even the MEMBER OF is not looking index at this point -- even when hinted to use index, it does Fast Full Scan and not Range scan.
rajesh@ORA10GR2> create or replace type numarray is table of number;
2 /
2 /
Type created.
Elapsed: 00:00:00.06
rajesh@ORA10GR2>
rajesh@ORA10GR2> variable x refcursor;
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> variable x refcursor;
rajesh@ORA10GR2>
rajesh@ORA10GR2> declare
2 arr numarray := numarray(1,2,3,4,5,6);
3 begin
4 open :x for
5 select object_id,data_object_id,owner,object_type,object_name
6 from t
7 where object_id member of ( arr );
8 end;
9 /
2 arr numarray := numarray(1,2,3,4,5,6);
3 begin
4 open :x for
5 select object_id,data_object_id,owner,object_type,object_name
6 from t
7 where object_id member of ( arr );
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SELECT OBJECT_ID,DATA_OBJECT_ID,OWNER,OBJECT_TYPE,OBJECT_NAME
FROM
T WHERE OBJECT_ID MEMBER OF ( :B1 )
FROM
T WHERE OBJECT_ID MEMBER OF ( :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.02 0 0 0 0
Fetch 1 0.04 0.03 0 694 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.06 0.05 0 694 0 5
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
5 TABLE ACCESS FULL T (cr=694 pr=0 pw=0 time=140 us)
rajesh@ORA10GR2>
rajesh@ORA10GR2> declare
2 arr numarray := numarray(1,2,3,4,5,6);
3 begin
4 open :x for
5 select /*+ index(t,t_ind) */ object_id,data_object_id,owner,object_type,object_name
6 from t
7 where object_id member of ( arr );
8 end;
9 /
2 arr numarray := numarray(1,2,3,4,5,6);
3 begin
4 open :x for
5 select /*+ index(t,t_ind) */ object_id,data_object_id,owner,object_type,object_name
6 from t
7 where object_id member of ( arr );
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
rajesh@ORA10GR2>
rajesh@ORA10GR2>
SELECT /*+ index(t,t_ind) */ OBJECT_ID,DATA_OBJECT_ID,OWNER,OBJECT_TYPE,
OBJECT_NAME
FROM
T WHERE OBJECT_ID MEMBER OF ( :B1 )
OBJECT_NAME
FROM
T WHERE OBJECT_ID MEMBER OF ( :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 1 0.03 0.03 0 113 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.04 0.04 0 113 0 5
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
5 TABLE ACCESS BY INDEX ROWID T (cr=113 pr=0 pw=0 time=97 us)
5 INDEX FULL SCAN T_IND (cr=112 pr=0 pw=0 time=60 us)(object id 55213)
rajesh@ORA10GR2> declare
2 arr numarray := numarray(1,2,3,4,5,6);
3 begin
4 open :x for
5 select object_id,data_object_id,owner,object_type,object_name
6 from t
7 where object_id in ( select /*+ cardinality(t1,5) */ * from table ( arr ) t1 ) ;
8 end;
9 /
2 arr numarray := numarray(1,2,3,4,5,6);
3 begin
4 open :x for
5 select object_id,data_object_id,owner,object_type,object_name
6 from t
7 where object_id in ( select /*+ cardinality(t1,5) */ * from table ( arr ) t1 ) ;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
rajesh@ORA10GR2>
rajesh@ORA10GR2>
SELECT OBJECT_ID,DATA_OBJECT_ID,OWNER,OBJECT_TYPE,OBJECT_NAME
FROM
T WHERE OBJECT_ID IN ( SELECT /*+ cardinality(t1,5) */ * FROM TABLE ( :B1 )
T1 )
FROM
T WHERE OBJECT_ID IN ( SELECT /*+ cardinality(t1,5) */ * FROM TABLE ( :B1 )
T1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 9 0 0
Fetch 1 0.00 0.00 0 9 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 18 0 5
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
5 TABLE ACCESS BY INDEX ROWID T (cr=9 pr=0 pw=0 time=278 us)
12 NESTED LOOPS (cr=8 pr=0 pw=0 time=889 us)
6 SORT UNIQUE (cr=0 pr=0 pw=0 time=92 us)
6 COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=28 us)
5 INDEX RANGE SCAN T_IND (cr=8 pr=0 pw=0 time=93 us)(object id 55213)
When working with Oracle 10g database, we need to Instruct cardinality to optimizer using Cardinality Hint. But when repeating the same test in Oracle 11g database
rajesh@ORA11GR2>
rajesh@ORA11GR2> select object_id,data_object_id,owner,object_type,object_name
2 from t
3 where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );
rajesh@ORA11GR2> select object_id,data_object_id,owner,object_type,object_name
2 from t
3 where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );
OBJECT_ID DATA_OBJECT_ID OWNER OBJECT_TYPE OBJECT_NAME
---------- -------------- ---------- --------------- --------------------
3 3 SYS INDEX I_OBJ#
6 6 SYS CLUSTER C_TS#
5 2 SYS TABLE CLU$
2 2 SYS CLUSTER C_OBJ#
4 2 SYS TABLE TAB$
---------- -------------- ---------- --------------- --------------------
3 3 SYS INDEX I_OBJ#
6 6 SYS CLUSTER C_TS#
5 2 SYS TABLE CLU$
2 2 SYS CLUSTER C_OBJ#
4 2 SYS TABLE TAB$
Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2>
select object_id,data_object_id,owner,object_type,object_name
from t
where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.02 0 1026 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.02 0 1026 0 5
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
5 HASH JOIN RIGHT SEMI (cr=1026 pr=0 pw=0 time=0 us cost=316 size=49 card=1)
6 COLLECTION ITERATOR CONSTRUCTOR FETCH (cr=0 pr=0 pw=0 time=5 us cost=29 size=16336 card=8168)
71598 TABLE ACCESS FULL T (cr=1026 pr=0 pw=0 time=99596 us cost=286 size=3365106 card=71598)
rajesh@ORA11GR2> select object_id,data_object_id,owner,object_type,object_name
2 from t
3 where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );
2 from t
3 where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );
OBJECT_ID DATA_OBJECT_ID OWNER OBJECT_TYPE OBJECT_NAME
---------- -------------- ---------- --------------- --------------------
2 2 SYS CLUSTER C_OBJ#
3 3 SYS INDEX I_OBJ#
4 2 SYS TABLE TAB$
5 2 SYS TABLE CLU$
6 6 SYS CLUSTER C_TS#
---------- -------------- ---------- --------------- --------------------
2 2 SYS CLUSTER C_OBJ#
3 3 SYS INDEX I_OBJ#
4 2 SYS TABLE TAB$
5 2 SYS TABLE CLU$
6 6 SYS CLUSTER C_TS#
Elapsed: 00:00:00.04
rajesh@ORA11GR2>
select object_id,data_object_id,owner,object_type,object_name
from t
where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) )
from t
where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 12 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 12 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 12 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 12 0 5
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------5 NESTED LOOPS (cr=12 pr=0 pw=0 time=0 us)
5 NESTED LOOPS (cr=10 pr=0 pw=0 time=136 us cost=36 size=294 card=6)
6 SORT UNIQUE (cr=0 pr=0 pw=0 time=5 us cost=29 size=12 card=6)
6 COLLECTION ITERATOR CONSTRUCTOR FETCH (cr=0 pr=0 pw=0 time=5 us cost=29 size=12 card=6)
5 INDEX RANGE SCAN T_IND (cr=10 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 79427)
5 TABLE ACCESS BY INDEX ROWID T (cr=2 pr=0 pw=0 time=0 us cost=2 size=47 card=1)
You see the optimizer hard parsed the query again & came up with right explain plan to answer this query. And further execution optimizer has reused the plan again.
rajesh@ORA11GR2> select object_id,data_object_id,owner,object_type,object_name
2 from t
3 where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );
2 from t
3 where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );
OBJECT_ID DATA_OBJECT_ID OWNER OBJECT_TYPE OBJECT_NAME
---------- -------------- ---------- --------------- --------------------
2 2 SYS CLUSTER C_OBJ#
3 3 SYS INDEX I_OBJ#
4 2 SYS TABLE TAB$
5 2 SYS TABLE CLU$
6 6 SYS CLUSTER C_TS#
---------- -------------- ---------- --------------- --------------------
2 2 SYS CLUSTER C_OBJ#
3 3 SYS INDEX I_OBJ#
4 2 SYS TABLE TAB$
5 2 SYS TABLE CLU$
6 6 SYS CLUSTER C_TS#
Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2>
select object_id,data_object_id,owner,object_type,object_name
from t
where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) )
from t
where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 12 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 12 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 12 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 12 0 5
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
5 NESTED LOOPS (cr=12 pr=0 pw=0 time=0 us)
5 NESTED LOOPS (cr=10 pr=0 pw=0 time=120 us cost=36 size=294 card=6)
6 SORT UNIQUE (cr=0 pr=0 pw=0 time=5 us cost=29 size=12 card=6)
6 COLLECTION ITERATOR CONSTRUCTOR FETCH (cr=0 pr=0 pw=0 time=10 us cost=29 size=12 card=6)
5 INDEX RANGE SCAN T_IND (cr=10 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 79427)
5 TABLE ACCESS BY INDEX ROWID T (cr=2 pr=0 pw=0 time=0 us cost=2 size=47 card=1)
------- ---------------------------------------------------
5 NESTED LOOPS (cr=12 pr=0 pw=0 time=0 us)
5 NESTED LOOPS (cr=10 pr=0 pw=0 time=120 us cost=36 size=294 card=6)
6 SORT UNIQUE (cr=0 pr=0 pw=0 time=5 us cost=29 size=12 card=6)
6 COLLECTION ITERATOR CONSTRUCTOR FETCH (cr=0 pr=0 pw=0 time=10 us cost=29 size=12 card=6)
5 INDEX RANGE SCAN T_IND (cr=10 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 79427)
5 TABLE ACCESS BY INDEX ROWID T (cr=2 pr=0 pw=0 time=0 us cost=2 size=47 card=1)
This proper cardinality is supplied to 11g optimizer by a new feature "cardinality feedback"
Thanks Rajesh - very interesting post!
ReplyDeleteAnother good one:
Cardinality in SQL