RE: Strategies for dealing with (NOT EQUAL) conditions and indexes
Date: Thu, 17 Nov 2011 14:13:08 -0500
Message-ID: <9CE162BC5ED2C643956B526A7EDE46FF02A0A560E6B5_at_EXM-OMF-04.Ceg.Corp.Net>
Your example is not valid. The index used in your "inequality" example is on val2 which is an equality "and val2 = 'A12E'". The inequality is then handled by the filter operation in step 1.
In my experience you can't get an index access path with inequality other than what's explained in this thread with a FFIS or a rewrite to use < OR >.
Thanks,
Finn
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sidney Chen
Sent: Thursday, November 17, 2011 7:53 AM
To: taral.desai_at_gmail.com
Cc: ChrisDavid.Taylor_at_ingrambarge.com; Stephens, Chris; Mark W. Farnham; oracle-l_at_freelists.org
Subject: Re: Strategies for dealing with (NOT EQUAL) conditions and indexes
it's all about cost, the index is not used because the Optimizer think it's
cheaper(lower cost) to do a full table scan, not because it can't do a
index scan for an not equal predicate.
just as Mark said, if you make val1 popular enough, the index will be
used. I remove the primary key to make sure around 90% var1 = 12. this tick
to show how index scan can happen for not equal predicate.
oe_at_CS10G> select count(*)
2 from test1
3 where val1
4 and val2 = 'A12E'
5 /
COUNT(*)
90094
1 row selected.
oe_at_CS10G> explain plan for
2 select /*+ dynamic_sampling(4)*/ val2, val3, val4, val5
3 from test1
4 where val1 = 12
5 and val2 = 'A12E'
6 /
Explained.
oe_at_CS10G> @x typical
oe_at_CS10G> select * from table(dbms_xplan.display('plan_table',null,'&1'));
PLAN_TABLE_OUTPUT
Plan hash value: 1966964964
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 90087 | 1847K| 99 (0)| 00:00:02 | |* 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 90087 | 1847K| 99 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | TEST1_IDX01 | 1000 | | 3 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("VAL2"='A12E')
2 - access("VAL1")
Note
- dynamic sampling used for this statement
19 rows selected.
oe_at_CS10G> explain plan for
2 select /*+ dynamic_sampling(4)*/val2, val3, val4, val5
3 from test1
4 where val1 != 12
5 and val2 = 'A12E'
6 /
Explained.
oe_at_CS10G> @x typical
oe_at_CS10G> select * from table(dbms_xplan.display('plan_table',null,'&1'));
PLAN_TABLE_OUTPUT
Plan hash value: 2016337987
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 10 | 210 | 100 (0)| 00:00:02 | |* 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 10 | 210 | 100 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | TEST1_IDX02 | 1000 | | 4 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("VAL1"<>12)
2 - access("VAL2"='A12E')
Note
- dynamic sampling used for this statement
19 rows selected.
--update created table scripts
---create table and load with data----
drop table test1
/
/* Let us create a wider table */
create table test1
(val1 number(3) not null,
val2 varchar2(8) not null,
val3 number(3) not null,
val4 varchar2(5) not null, val5 varchar2(5) not null, val6 varchar2(5) not null, val7 varchar2(5), val8 varchar2(5)
)
/
/* Now let us add a primary key like might exist */
--alter table test1 add constraint test1_pk primary key (val1, val2, val3) --using index
/* Now let us add some additional indexes like might exist */
create index test1_idx01 on test1 (val1)
/
create index test1_idx02 on test1 (val2)
/
create index test1_idx03 on test1 (val3)
/
create index test1_idx04 on test1 (val1, val3)
/
create index test1_idx05 on test1 (val2, val3)
/
create index test1_idx06 on test1 (val1, val2, val4)
/
create index test1_idx07 on test1 (val1, val3, val4)
/
create index test1_idx08 on test1 (val1, val2, val3, val4)
/
create index test1_idx09 on test1 (val1, val2, val3, val5)
/
create index test1_idx10 on test1 (val1, val2, val3, val6)
/
/* Now let us add some data */
insert into test1
with generator as
(select
*case when mod(rownum,10) < 9 then 12 else round(dbms_random.value(1,100))
end n1,*
round(dbms_random.value(1,100)) n2
from dual connect by level < 100000
)
select
n1,
'A'||n1||'E',
n2,
'B'||n1||'D', 'C'||n2||'C', 'D'||n2||'B', 'E'||n2||'A',
null
from generator;
begin
dbms_Stats.gather_table_stats(ownname=>'',tabname=>'TEST1',estimate_percent=>100,cascade=>TRUE,
method_opt=>'FOR ALL COLUMNS SIZE AUTO', granularity=>'ALL',
block_sample=>TRUE);
end;
/
-- Regards Sidney Chen -- http://www.freelists.org/webpage/oracle-l >>> This e-mail and any attachments are confidential, may contain legal, professional or other privileged information, and are intended solely for the addressee. If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2 -- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 17 2011 - 13:13:08 CST