| Why Oracle don't use my predefined index? [message #502525] |
Thu, 07 April 2011 01:07  |
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
|
Senior Member |
|
|
Hi all,
I have been meet interesting issue during the work.
Here, let me explain:
I have create a table with 8 million records and 2 different indexes using 2 different columns (columns name NUM1 & NUM2) on that table.
First indexed column (NUM1) values have many different values (1,2,3... etc).
Second indexed column (NUM2) values have only 2 different values.
7999999 records values is same("A") and remaining one record values is different("B").
Query1:
select * from tbl where num1=val
Query2:
select * from tbl where num2='B'
I have compare explain plan both queries, but Query2 doesn't use predefined index.
Why Oracle don't use my redefined index at column NUM2?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Why Oracle don't use my predefined index? [message #502544 is a reply to message #502542] |
Thu, 07 April 2011 02:43  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Interesting article, although I get different reults in 11gR2, so YMMV
Edit: And in version 9.2
SCOTT@ORA11GMK > create table t1 as
2 select
3 trunc((rownum-1)/15) n1,
4 trunc((rownum-1)/15) n2,
5 rpad('x', 215) v1
6 from all_objects
7 where rownum <= 3000;
Table created.
Elapsed: 00:00:00.53
SCOTT@ORA11GMK > create table t2 as
2 select
3 mod(rownum,200) n1,
4 mod(rownum,200) n2,
5 rpad('x',215) v1
6 from all_objects
7 where rownum <= 3000;
Table created.
Elapsed: 00:00:00.60
SCOTT@ORA11GMK > create index t1_i1 on t1(N1);
Index created.
Elapsed: 00:00:00.06
SCOTT@ORA11GMK > create index t2_i1 on t2(n1);
Index created.
Elapsed: 00:00:00.14
SCOTT@ORA11GMK > analyze table t1 compute
2 statistics;
Table analyzed.
Elapsed: 00:00:00.10
SCOTT@ORA11GMK > analyze table t2 compute
2 statistics;
Table analyzed.
Elapsed: 00:00:00.07
SCOTT@ORA11GMK > explain plan for
2 select * from t1 where n1 = 45;
Explained.
Elapsed: 00:00:00.06
SCOTT@ORA11GMK > select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 1429545322
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 3315 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 15 | 3315 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 15 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=45)
14 rows selected.
Elapsed: 00:00:00.79
SCOTT@ORA11GMK > explain plan for
2 select * from t2 where n1 = 45;
Explained.
Elapsed: 00:00:00.04
SCOTT@ORA11GMK > Select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 365331496
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 3315 | 16 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 15 | 3315 | 16 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T2_I1 | 15 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=45)
14 rows selected.
Elapsed: 00:00:00.07[Updated on: Thu, 07 April 2011 02:47] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
