I imagine there are many reasons why Oracle might (or might not) use an index. I guess there are also many reasons why you might (or might not) WANT Oracle to use an index.
I got the idea for this example from a book written by Mark Gurry and ran it on Oracle 11.2.
I dedicate it to Oliver, who thinks that database administrators spend all day creating indexes.
First I created a table:
SQL> create table t1
2 as select * from dba_segments
3 /
Table created.
SQL>
…then I made sure it contained enough data:
SQL> begin
2 for a in 1..8 loop
3 insert into t1 select * from t1;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
I added an index:
SQL> create index i1 on t1(owner, extents)
2 /
Index created.
SQL> exec dbms_stats.gather_table_stats -
> (ownname=>'ORACLE',tabname=>'T1');
PL/SQL procedure successfully completed.
SQL>
…then I ran a query against the table:
SQL> set autotrace on
SQL> set timing on
SQL> select sum(bytes) from t1
2 where owner = 'SYS'
3 and extents = 1
4 /
SUM(BYTES)
----------
3.0098E+10
Elapsed: 00:00:27.29
Execution Plan
----------------------------------------------------------
Plan hash value: 3693069535
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 2438 (29)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| T1 | 10388 | 162K| 2438 (29)| 00:00:03 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EXTENTS"=1 AND "OWNER"='SYS')
Statistics
----------------------------------------------------------
365 recursive calls
0 db block gets
37109 consistent gets
37046 physical reads
0 redo size
533 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL> set timing off
SQL>
The query had to look at 23% of the rows in the table:
SQL> l
1 select round
2 ((select count(*) from t1
3 where owner = 'SYS' and extents = 1)
4 /
5 (select count(*) from t1) * 100)
6* as percentage from dual
SQL> /
PERCENTAGE
----------
23
SQL>
…so it did a full table scan instead of using the index and the elapsed time was 27 seconds. I wanted to improve on this so I added the bytes column to the index:
SQL> drop index i1
2 /
Index dropped.
SQL> create index i2 on t1(owner, extents, bytes)
2 /
Index created.
SQL> exec dbms_stats.gather_table_stats -
> (ownname=>'ORACLE',tabname=>'T1');
PL/SQL procedure successfully completed.
SQL>
I ran the query again. This time, Oracle could get all the information it needed from the index so the elapsed time went down to 3 seconds:
SQL> set autotrace on
SQL> set timing on
SQL> select sum(bytes) from t1
2 where owner = 'SYS'
3 and extents = 1
4 /
SUM(BYTES)
----------
3.0098E+10
Elapsed: 00:00:03.34
Execution Plan
----------------------------------------------------------
Plan hash value: 494139663
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 581 (47)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | INDEX FAST FULL SCAN| I2 | 518K| 8099K| 581 (47)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS' AND "EXTENTS"=1)
Statistics
----------------------------------------------------------
432 recursive calls
0 db block gets
6843 consistent gets
569 physical reads
0 redo size
533 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL> set timing off
SQL>
No comments:
Post a Comment