Home » RDBMS Server » Performance Tuning » Why did I not use the index? (Windows XP, Oracle 10.2.0.2 Release)
Why did I not use the index? [message #306360] Thu, 13 March 2008 20:59 Go to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
I have 2 indexes on 2 columns as EMPNO and ENAME, one is NORMAL, one is FUNCTION-BASED NORMAL
SQL> ed
Wrote file afiedt.buf

  1  select index_name,index_type, table_type
  2  from dba_indexes
  3  where owner='SYS'
  4* and table_name='EMP'
SQL> /

INDEX_NAME                     INDEX_TYPE                  TABLE_TYPE
------------------------------ --------------------------- -----------
ENAME_INDX                     FUNCTION-BASED NORMAL       TABLE
EMPNO_INDX                     NORMAL                      TABLE


And the EMPNO_INDX is to be used like Range Scan with INLIST ITERATOR normally.
SQL> explain plan for
  2  select empno,ename
  3  from emp
  4  where empno in (100,105);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 367964842

--------------------------------------------------------------------------------

-----------

| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)|

 Time     |

--------------------------------------------------------------------------------

-----------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |            |     2 |    40 |     1   (0)|

 00:00:01 |

|   1 |  INLIST ITERATOR             |            |       |       |            |

          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP        |     2 |    40 |     1   (0)|

 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | EMPNO_INDX |     1 |       |     1   (0)|

 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------

-----------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMPNO"=100 OR "EMPNO"=105)

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-----
   - dynamic sampling used for this statement

19 rows selected.

SQL>


But with the <, > operator, the Oracle did not use my index, EMP_INDX.
SQL> explain plan for
  2  select empno,ename
  3  from emp
  4  where empno>100;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    19 |   380 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    19 |   380 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


   1 - filter("EMPNO">100)

Note
-----
   - dynamic sampling used for this statement

17 rows selected.


Would you like to explain to me?
Thank you!

[Updated on: Thu, 13 March 2008 21:08]

Report message to a moderator

Re: Why did I not use the index? [message #306362 is a reply to message #306360] Thu, 13 March 2008 21:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle thinks it might return a lot of rows, so it chooses the ideal plan for returning large quantities of data: full table scan.

Indexes are only good when you select a small number of rows. See this article for more information.

Ross Leishman
Re: Why did I not use the index? [message #306365 is a reply to message #306362] Thu, 13 March 2008 22:46 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thanks rleishman!

Yeap, I knew that..
Quote:

Large Amount of Data
If the optimizer thinks that the query will access most of the blocks in the table, then it uses a full table scan, even though indexes might be available.



But can you explain more:
Quote:

Small Table
If a table contains less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the high water mark, which can be read in a single I/O call, then a full table scan might be cheaper than an index range scan, regardless of the fraction of tables being accessed or indexes present



And.. Okie, I will make a test
C:\>sqlplus sys/test@test as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 14 10:34:32 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table X (x varchar2(20), y number(4));

Table created.

SQL> insert into X ('TEST',3);
insert into X ('TEST',3)
               *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into X values ('TEST',3);

1 row created.
SQL> insert into X values ('TEST1',4);

1 row created.
SQL> commit;

Commit complete.

SQL>


Okie, I have 2 rows in X, so on, I will measure somethings
SQL> select bytes from dba_segments
  2  where owner='SYS'
  3  and segment_type='TABLE'
  4  and segment_name='X';

     BYTES
----------
     65536

SQL> show parameter db_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16
db_file_name_convert                 string
db_files                             integer     200
SQL> show parameter db_block

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TRUE
db_block_size                        integer     8192
SQL> select 16*8192 from dual;

   16*8192
----------
    131072


You see, I have table X with only 65536 bytes, but the db_file_multi_block_read_count with 131072 bytes. On the above quote, one FTS occurs with the Oracle thinks the data that table contains bigger than db_file_multi_block_read_count values..

But..
SQL> set autotrace traceonly explain
SQL> select x,y
  2  from x
  3  where x>3;

Execution Plan
----------------------------------------------------------
Plan hash value: 2941724873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |   100 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| X    |     4 |   100 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("X")>3)

SQL>


Just do you ask me about the buffer cached? OKIE,..
Quote:

Once the access path has been decided upon it is stored in the library cache together with the statement itself. We store queries in the library cache based upon a hashed representation of that query. When looking for a statement in the library cache, we first apply a hashing algorithm to the statement and then we look for this hash value in the library cache. This access path will be used until the query is reparsed.



Execute this statement for the second time..

SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 2941724873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |   100 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| X    |     4 |   100 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("X")>3)


Re: Why did I not use the index? [message #306800 is a reply to message #306365] Sun, 16 March 2008 21:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Your calculation sizes the total space allocated to the table for current and future use. When you do a full table scan, Oracle will not real ALL of this, just the blocks up to the High Water Mark. Since you have only inserted a couple of rows, they will certainly fit within one block, so your high water mark will be 1 block (8192 bytes), not 65536 bytes.

Don't try to over-think this; it is NOT an exact science. Just accept that indexes are not always faster than a full scan.

Ross Leishman
Re: Why did I not use the index? [message #306802 is a reply to message #306800] Sun, 16 March 2008 21:09 Go to previous message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Yeap, thank you, resl!

You are rigth, Index is not always good, FTS is not always bad!
You make me add more one experience.

Thanks again!
Previous Topic: sqlplus: query takes too much time
Next Topic: Database parameters for tuning
Goto Forum:
  


Current Time: Wed Dec 07 06:41:31 CST 2016

Total time taken to generate the page: 0.07124 seconds