Why Is My Index Not Being Used Quiz (Brain Damage) September 28, 2011
Posted by Richard Foote in CBO, Oracle Indexes, Quiz.9 comments
This one is a little different as it comes in the form of a demo (and about 1 minute to read) so you have to work a little 🙂
I create table, index and sequence:
SQL> create table bowie (id number, name varchar2(30)) tablespace user_data; Table created. SQL> create index bowie_id_i on bowie(id); Index created. SQL> create sequence bowie_id order; Sequence created.
I then create a little procedure that simply adds 100,000 rows to the table:
SQL> create or replace procedure add_bowie_rows as 2 begin 3 for i in 1..100000 loop 4 insert into bowie values (bowie_id.nextval, 'DAVID BOWIE'); 5 commit; 6 end loop; 7 end; 8 / Procedure created.
I then have 3 different sessions that run the procedure simultaneously (eg. exec add_bowie_rows).
I collect 100% accurate stats:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', estimate_percent=>null, cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed.
I run a query that selects about 10,000 rows (out of the 300,000 the table now has):
SQL> select * from bowie where id between 42000 and 52000; 10001 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 131801496 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10002 | 166K| 125 (1)|00:00:02 | | 1 | TABLE ACCESS BY INDEX ROWID| BOWIE | 10002 | 166K| 125 (1)|00:00:02 | |* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 10002 | | 51 (0)|00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID">=42000 AND "ID"<=52000) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 129 consistent gets 44 physical reads 0 redo size 100270 bytes sent via SQL*Net to client 264 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10001 rows processed
Oracle uses the index and all is well.
OK, in exactly the same database, I repeat the demo again with the same 3 sessions populating the data in exactly the same way using exactly the same procedure with 100% accurate statistics, but there’s just one tiny little difference in the setup script:
SQL> drop table bowie; Table dropped. SQL> drop sequence bowie_id; Sequence dropped. SQL> create table bowie (id number, name varchar2(30)) tablespace user_data1; Table created. SQL> create index bowie_id_i on bowie(id); Index created. SQL> create sequence bowie_id order; Sequence created.
I next populate the table in 3 different sessions concurrently and collect stats exactly as before…
However, now when I run my query:
SQL> select * from bowie where id between 42000 and 52000; 10001 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4157583811 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10002 | 166K| 285 (4)| 00:00:04 | |* 1 | TABLE ACCESS FULL| BOWIE | 10002 | 166K| 285 (4)| 00:00:04 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"<=52000 AND "ID">=42000) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 998 consistent gets 693 physical reads 0 redo size 100270 bytes sent via SQL*Net to client 264 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10001 rows processed
It performs a FTS ??? Note, the cardinality estimate of 1002 is practically spot on and identical to previously when the index was used by the CBO.
Instead of selecting 10,000 rows, if I now select say just 500 rows:
SQL> select * from bowie where id between 42000 and 42499; 500 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4157583811 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 501 | 8517 | 284 (4)| 00:00:04 | |* 1 | TABLE ACCESS FULL| BOWIE | 501 | 8517 | 284 (4)| 00:00:04 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"<=42499 AND "ID">=42000) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 997 consistent gets 0 physical reads 0 redo size 5263 bytes sent via SQL*Net to client 248 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 500 rows processed
It still performs a FTS !!
Only when I get down to a really low number of rows, for example 100 rows:
SQL> select * from bowie where id between 42000 and 42099; 100 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 131801496 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 101 | 1717 | 95 (0)|00:00:02 | | 1 | TABLE ACCESS BY INDEX ROWID| BOWIE | 101 | 1717 | 95 (0)|00:00:02 | |* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 101 | | 3 (0)|00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID">=42000 AND "ID"<=42099) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 103 consistent gets 0 physical reads 0 redo size 1266 bytes sent via SQL*Net to client 248 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 100 rows processed
Will Oracle use the index.
QUESTION: Why, what is the tiny little difference that has made such a huge difference in behaviour ???
Now there are a couple of possible answers (at least) that come to mind …
Enjoy !!