RE: Strategies for dealing with (NOT EQUAL) conditions and indexes
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>, "'Stephens, Chris'" <Chris.Stephens@xxxxxxx>
- Date: Thu, 17 Nov 2011 08:54:18 -0500
Okay, I did not realize the not equals was on the indexed column rather than
the non-indexed column.
In the following, there is an index on columns (a,b,c), and none on d. But a
is highly selective (one row in fact, since I didn't want to fool around and
find the break point).
The value on D is then just filtered out from the one row the index returns.
So if the costs are right, you can turn a single not equals into the
concatenation of a pair of index range scans with a table probe
instead of a fast full index scan or a full table scan.
As I tested variations on 11.2.0.1.0 (64bit windows 7) my laptop, I was not
impressed by reaching more than 2x the cost in buffers than even a fast full
index scan without it switching to an index oriented plan.
There is possibly some way to get the CBO to transform a not equals on a
single indexed value into a pair of range scan as you see below, but I'm not
aware of it. That would be a useful permutation for the CBO to examine.
In the meantime re-writing your code as an OR seems to do the trick when
the costs are right.
Regards,
mwf
SQL> @q_xplan_allstats_cost
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
------------------------------------
SQL_ID 47v1xmpbwnh93, child number 0
-------------------------------------
select --+ gather_plan_statistics a,d from junk13 where (a < '1' or a >
'1') and d != 'not aA'
Plan hash value: 1908861750
----------------------------------------------------------------------------
-------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost
(%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------
-------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8
(100)| 0 |00:00:00.01 | 7 |
| 1 | CONCATENATION | | 1 | |
| 0 |00:00:00.01 | 7 |
|* 2 | TABLE ACCESS BY INDEX ROWID| JUNK13 | 1 | 1 | 4
(0)| 0 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | JUNK13ABC | 1 | 1 | 3
(0)| 0 |00:00:00.01 | 3 |
|* 4 | TABLE ACCESS BY INDEX ROWID| JUNK13 | 1 | 1 | 4
(0)| 0 |00:00:00.01 | 4 |
|* 5 | INDEX RANGE SCAN | JUNK13ABC | 1 | 1 | 3
(0)| 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------------
-------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("D"<>'not aA')
3 - access("A"<'1')
4 - filter("D"<>'not aA')
5 - access("A">'1')
filter(LNNVL("A"<'1'))
Much better than:
SQL> @q_xplan_allstats_cost
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
---------------------------------------------
SQL_ID aw7zp0n0b99pj, child number 0
-------------------------------------
select --+ gather_plan_statistics t1.a,t1.d from junk13 t1,
(select --+ no_merge rowid from junk13 where a!= '1') t2 where
t1.rowid = t2.rowid and t1.d != 'not aA'
Plan hash value: 2423682382
----------------------------------------------------------------------------
---------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost
(%CPU)| A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------
---------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2705
(100)| 0 |00:00:00.28 | 10119 | 5 |
| 1 | NESTED LOOPS | | 1 | 1 | 2705
(2)| 0 |00:00:00.28 | 10119 | 5 |
| 2 | VIEW | | 1 | 1 | 2704
(2)| 1 |00:00:00.28 | 10118 | 0 |
|* 3 | INDEX FAST FULL SCAN | JUNK13ABC | 1 | 1 | 2704
(2)| 1 |00:00:00.28 | 10118 | 0 |
|* 4 | TABLE ACCESS BY USER ROWID| JUNK13 | 1 | 1 | 1
(0)| 0 |00:00:00.01 | 1 | 5 |
----------------------------------------------------------------------------
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"<>'1')
4 - filter("T1"."D"<>'not aA')
or the naturally occurring:
SQL> @q_xplan_allstats_cost
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
---------------------------------
SQL_ID 1squ2m80qsqm6, child number 0
-------------------------------------
select --+ gather_plan_statistics a,d from junk13 where a != '1' and d
!= 'not aA'
Plan hash value: 230125383
----------------------------------------------------------------------------
--------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |
A-Time | Buffers | Reads |
----------------------------------------------------------------------------
--------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7222 (100)| 0
|00:00:01.03 | 25679 | 25669 |
|* 1 | TABLE ACCESS FULL| JUNK13 | 1 | 1 | 7222 (1)| 0
|00:00:01.03 | 25679 | 25669 |
----------------------------------------------------------------------------
--------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("A"<>'1' AND "D"<>'not aA'))
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Taylor, Chris David
Sent: Wednesday, November 16, 2011 12:54 PM
To: 'Stephens, Chris'; 'Mark W. Farnham'
Cc: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Strategies for dealing with (NOT EQUAL) conditions and indexes
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:[mailto:Chris.Stephens@xxxxxxx]>
[mailto:Chris.Stephens@xxxxxxx]
Sent: Wednesday, November 16, 2011 8:52 AM
To: Taylor, Chris David; 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Strategies for dealing with (NOT EQUAL) conditions and indexes
"Obviously the use of != causes indexes to be not available"
Really?
--
<//www.freelists.org/webpage/oracle-l>
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
