RE: Strategies for dealing with (NOT EQUAL) conditions and indexes
Date: Wed, 16 Nov 2011 11:53:38 -0600
Message-ID: <C5533BD628A9524496D63801704AE56D6A33D074D4_at_SPOBMEXC14.adprod.directory>
Chris & Mark,
I guess I'm guilty of not being precise enough.
The issue arises when there is a column in the SELECT that is not *also* in the index.
(The create table/indexes and load with data script is at the bottom)
Here's the test case to illustrate what I'm talking about
In my test table there are 100 rows where val1 = 12 and val2 = 'A12E'.
SQL> select count(*)
2 from test1
3 where val1 =12
4 and val2 = 'A12E'
5 /
COUNT(*)
100
HOWEVER (as in a real world example) let's imagine that our test table is really wide and tall, so it is not feasible to index all the columns that we have to SELECT.
For my test, only one of my columns does NOT share an index with the other columns whereas in a real world example there might be several more columns that are also selected which do not share an index.
SELECT COLUMNS: val2, val3, val4, val5 (VAL5 is not in the index) ---imagine that there are even more columns selected WHERE COLUMNS: val1, val2
(VAL1, VAL2, VAL3, and VAL4 exist in TEST1_IDX08)
In the results below notice the behavior I was talking about - the inequality condition prevents the use of the index.
/* Test With Equality Conditions */
SQL> select val2, val3, val4, val5
2 from test1
3 where val1 = 12
4 and val2 = 'A12E'
5 /
Execution Plan
Plan hash value: 994878611
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 21 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TEST1_IDX08 | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
2 - access("VAL1"=12 AND "VAL2"='A12E')
/* Test With INequality Condition on val1 */
SQL> select val2, val3, val4, val5
2 from test1
3 where val1 != 12
4 and val2 = 'A12E'
5 /
Execution Plan
Plan hash value: 4122059633
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 99 | 2079 | 13 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST1 | 99 | 2079 | 13 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("VAL2"='A12E' AND "VAL1"<>12)
/* Test With INequality Condition on val2 *
SQL> select val2, val3, val4, val5
2 from test1
3 where val1 = 12
4 and val2 != 'A12E'
5 /
Execution Plan
Plan hash value: 4122059633
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 99 | 2079 | 13 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST1 | 99 | 2079 | 13 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("VAL1"=12 AND "VAL2"<>'A12E')
Create.sql
---create table and load with data----
set echo on
set define &
set define on
spool create.log
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 */
declare
v_num1 number := 0; v_str1 varchar2(11) := ''; v_num2 number := 0; v_str2 varchar2(5) := ''; v_str3 varchar2(5) := ''; v_str4 varchar2(5) := ''; v_str5 varchar2(5) := ''; v_str6 varchar2(5) := '';
begin
for i in 1..100000
loop
begin
v_num1 := round(dbms_random.value(1,100)); v_num2 := round(dbms_random.value(1,100)); v_str1 := 'A'||v_num1||'E'; v_str2 := 'B'||v_num1||'D'; v_str3 := 'C'||v_num2||'C'; v_str4 := 'D'||v_num1||'B'; v_str5 := 'E'||v_num2||'A';
insert into test1
values
(v_num1, v_str1, v_num2, v_str2, v_str3, v_str4, v_str5, v_str6); exception
when dup_val_on_index
then null;
end;
end loop;
end;
/
commit
/
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;
/
spool off
-----end create script----
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
"Quality is never an accident; it is always the result of intelligent effort." -- John Ruskin (English Writer 1819-1900)
CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.
From: Stephens, Chris [mailto:Chris.Stephens_at_adm.com] Sent: Wednesday, November 16, 2011 8:52 AM To: Taylor, Chris David; 'oracle-l_at_freelists.org' Subject: RE: Strategies for dealing with (NOT EQUAL) conditions and indexes
"Obviously the use of != causes indexes to be not available"
Really?
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 16 2011 - 11:53:38 CST