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  |
trantuananh24hg
Messages: 744
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  |
rleishman
Messages: 3728
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 |
trantuananh24hg
Messages: 744
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 |
rleishman
Messages: 3728
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
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jun 27 21:15:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
