I tested this on Oracle 11.2. First I created a table with two columns. The first column, OWNER, had low cardinality i.e. it had only two possible values, PUBLIC and SYS. The second column, OBJECT_NAME, had the names of all objects owned by PUBLIC and/or SYS so it had high cardinality i.e. it had many different values:
SQL> conn system/manager
Connected.
SQL> create table t1 as
2 select owner, object_name
3 from dba_objects
4 where owner in ('SYS', 'PUBLIC')
5 /
Table created.
SQL> select owner, count(*)
2 from t1
3 group by owner
4 /
OWNER COUNT(*)
------------------------------ ----------
PUBLIC 29041
SYS 31907
SQL>
I created an index on the table and gathered statistics:
SQL> create index i1 on t1(owner, object_name)
2 /
Index created.
SQL> exec sys.dbms_stats.gather_table_stats(-
> ownname=>'SYSTEM',tabname=>'T1');
PL/SQL procedure successfully completed.
SQL>
Then I counted the number of rows with an OBJECT_NAME of DUAL. There were two, one owned by SYS and the other owned by PUBLIC. You might think that Oracle would not have been able to use the index, as OBJECT_NAME was not the leading column. However, as the leading column in the index, OWNER, had low cardinality, Oracle could SCAN the entries in the index for PUBLIC and see if any had an OBJECT_NAME of DUAL. It could then SKIP to the index entries for SYS and look for an OBJECT_NAME of DUAL there. This is why it is called an INDEX SKIP SCAN:
SQL> set autotrace on
SQL> select count(*) from t1
2 where object_name = 'DUAL'
3 /
COUNT(*)
----------
2
Execution Plan
----------------------------------------------------------
---------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 |
| 1 | SORT AGGREGATE | | 1 | 25 | |
| 2 | INDEX SKIP SCAN| I1 | 2 | 50 | 3 |
---------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
I changed the test to include all the rows from DBA_OBJECTS, not just those owned by PUBLIC and/or SYS then reran it. Table T1 had 51 different values in the OWNER column so Oracle did a full table scan instead:
SQL> conn system/manager
Connected.
SQL> create table t1 as
2 select owner, object_name
3 from dba_objects
4 /
Table created.
SQL> select count(distinct owner) from t1
2 /
COUNT(DISTINCTOWNER)
--------------------
51
SQL> create index i1 on t1(owner, object_name)
2 /
Index created.
SQL> exec sys.dbms_stats.gather_table_stats(-
> ownname=>'SYSTEM',tabname=>'T1');
PL/SQL procedure successfully completed.
SQL> set autotrace on
SQL> select count(*) from t1
2 where object_name = 'DUAL'
3 /
COUNT(*)
----------
2
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 31 |
| 1 | SORT AGGREGATE | | 1 | 24 | |
| 2 | TABLE ACCESS FULL| T1 | 2 | 48 | 31 |
-----------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
362 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
No comments:
Post a Comment