[Oracle-l] Strategies for dealing with (NOT EQUAL) conditions and indexes

"Obviously the use of != causes indexes to be not available"


Really?



[email protected]> create table t1(col1 number not null);



Table created.



[email protected]> create index t1_idx1 on t1(col1);



Index created.



[email protected]> insert into t1(col1) values(1);



1 row created.



[email protected]> insert into t1(col1) values(2);



1 row created.



[email protected]> select /*+ gather_plan_statistics */ col1 from t1 where col1 != 1;



COL1

----------

2



[email protected]> select * from table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));



--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------

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
------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
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
---------------------------------------------------------------------------
---------------------------------------------------------------------------
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
---------------------------------------------------------------------------
---------------------------------------------------------------------------
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
Sent: Wednesday, November 16, 2011 8:52 AM
To: Taylor, Chris David; '[email protected]'
Subject: RE: Strategies for dealing with (NOT EQUAL) conditions and indexes

"Obviously the use of != causes indexes to be not available"

Really?

For me on my 32-bit windows DB 11.2.0.3 even i don't get index path
OWNER TABLE_NAME TYPE NUM_ROWS
BLOCKS EMPTY AVGSPC ROWLEN
-------------------- ------------------------------ ---- ------------
---------- --------- ------ ------
TARAL TEST1 TAB 9997
50 0 0 31


SQL_ID 50sw8qujy3d49, child number 0
-------------------------------------
select val2, val3, val4, val5 from test1 where val1 = 12 and val2 =
'A12E'

Plan hash value: 4122059633

--------------------------------------------------------------------------------------------------
A-Time | Buffers |
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------

SQL_ID 8cq7maz5zrzp9, child number 0
-------------------------------------
select val2, val3, val4, val5 from test1 where val1 != 12 and val2 =
'A12E'

Plan hash value: 4122059633

--------------------------------------------------------------------------------------------------
A-Time | Buffers |
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------

IND_NAME IND_STAT I IND_BLEVEL IND_LEAFBLOCKS
IND_NUMROWS IND_DISTINCTKEYS IND_CLUFAC
------------------------------ -------- - ---------- --------------
----------- ---------------- ----------
TEST1_PK VALID Y 1 33
9997 9997 9794
TEST1_IDX01 VALID 1 32
9997 100 4309
TEST1_IDX02 VALID 1 32
9997 100 4309
TEST1_IDX03 VALID 1 31
9997 100 4323
TEST1_IDX04 VALID 1 32
9997 9997 9794
TEST1_IDX05 VALID 1 32
9997 9997 9797
TEST1_IDX06 VALID 1 40
9997 100 4309
TEST1_IDX07 VALID 1 34
9997 9997 9794
TEST1_IDX08 VALID 1 56
9997 9997 9794
TEST1_IDX09 VALID 1 54
9997 9997 9794
TEST1_IDX10 VALID 1 56
9997 9997 9794

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.

[email protected]> select count(*)
2 from test1
3 where val1 
4 and val2 = 'A12E'
5 /

COUNT(*)
----------
90094

1 row selected.

[email protected]> 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.

[email protected]> @x typical
[email protected]> select * from table(dbms_xplan.display('plan_table',null,'&1'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1966964964

-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("VAL2"='A12E')
2 - access("VAL1")

Note
-----
- dynamic sampling used for this statement

19 rows selected.

[email protected]> 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.

[email protected]> @x typical
[email protected]> select * from table(dbms_xplan.display('plan_table',null,'&1'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2016337987

-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------

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;
/

Is there a difference in the way Oracle deals with "!=" versus "<>" inequality /nonequality conditions in the optimizer?


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: Sidney Chen
Sent: Thursday, November 17, 2011 6:53 AM
To: [email protected]
Cc: Taylor, Chris David; Stephens, Chris; Mark W. Farnham; [email protected]
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.

[email protected]> select count(*)
2 from test1
3 where val1 
4 and val2 = 'A12E'
5 /

COUNT(*)
----------
90094

1 row selected.

[email protected]> 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.

[email protected]> @x typical
[email protected]> select * from table(dbms_xplan.display('plan_table',null,'&1'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1966964964

-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("VAL2"='A12E')
2 - access("VAL1")

Note
-----
- dynamic sampling used for this statement

19 rows selected.

[email protected]> 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.

[email protected]> @x typical
[email protected]> select * from table(dbms_xplan.display('plan_table',null,'&1'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2016337987

-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------

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;
/

Well it looks like there is no noticeable difference between <> and != as far as the optimizer is concerned.

Here's what started me down this road and led me to asking the original question:

From DBMS_SQLTUNE.REPORT_TUNING_TASK (see explicit Rationale section at the bottom)


2- Restructure SQL finding (see plan 2 in explain plans section)
----------------------------------------------------------------
Predicate "CHEMREG_SAMPLE"."CONTAINER_STATUS"<>'UNAVAIL' used at line ID 10
of the execution plan is an inequality condition on indexed column
"CONTAINER_STATUS". This inequality condition prevents the optimizer from
selecting indices on table "CHEMREG"."CHEMREG_SAMPLE".

Recommendation
--------------
- Rewrite the predicate into an equivalent form to take advantage of
indices.

Rationale
---------
The optimizer is unable to use an index if the predicate is an inequality
condition or if there is an expression or an implicit data type conversion
on the indexed column.



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.

The inequality does seem to force at best a fast full index scan (ffs). I'm
a bit surprised it does not routinely check the cost of sourcing that as the
relevant pair of index range scans greater than and less than.
Once you're into a fast full index scan at best and have to probe the table
as well, I have not found a cost differential where the optimizer will
choose the ffs from the naturally written query.

This:

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'

will force the issue (and you can cut and paste out the cost profile and
plan from the badly formatted earlier message or test the similar yourself).

Oddly, even

select --+ gather_plan_statistics a,d from junk13 where junk13.rowid in
(select /* + no_merge */ rowid from junk13 where a!= '1') and d!= 'not
aA'

still does the full table scan (fts), even though (in my actual case) the
fts is over 25000 buffers and the ffs is about 10,000 buffers and a single
index probe.

So either I'm missing something or this is indeed an area where the CBO
could be improved (and it seems like a common enough case that it would be
useful).

Of course the greater than or less than rewrite gets this pretty fast with
the concatenation, but it seems that a "not equals range scan" access method
would be a good optimizer team investment, since I have it in mind that
would be cheaper than concatenating the two result sets (and it would be
more convenient for us.)

Jonathan Lewis has a whole new course on "beating" the CBO. Again, there
might be a more natural way around this current problem than I'm seeing, but
there are many cases that the CBO team either has not gotten to yet or which
are judged too special case for them to implement. Jonathan's course, I
believe, delves into several? many? of these cases. I haven't seen the
course materials (yet), but I think they will reflect JL's passion and
intellect for knowing how to get the best possible plan when the CBO cannot
(currently) in a particular case. JL might want to comment on his course if
I have mischaracterized it (or just extend my remarks even if they are
correct).

Good luck,

mwf

For more info on this behaviour see:
http://richardfoote.wordpress.com/2008/08/13/indexes-and-not-equal-not-now-john/

One alternative if absolutely necessary might be an FBI using CASE for example to turn inequality predicate into equality.

Cheers,
Dominic

Interesting. Between that entry and Mark F's information earlier, I now understand more of the "why" an index range scan is not available when using an inequality.

Very interesting stuff indeed.

I do like Mark's and Richard's approach of using x < y OR x > y to get a RANGE scan. I'm going to have keep that in my toolbox.

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.

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: [email protected] On Behalf Of Sidney Chen
Sent: Thursday, November 17, 2011 7:53 AM
To: [email protected]
Cc: [email protected]; Stephens, Chris; Mark W. Farnham; [email protected]
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.

[email protected]> select count(*)
2 from test1
3 where val1 
4 and val2 = 'A12E'
5 /

COUNT(*)
----------
90094

1 row selected.

[email protected]> 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.

[email protected]> @x typical
[email protected]> select * from table(dbms_xplan.display('plan_table',null,'&1'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1966964964

-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("VAL2"='A12E')
2 - access("VAL1")

Note
-----
- dynamic sampling used for this statement

19 rows selected.

[email protected]> 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.

[email protected]> @x typical
[email protected]> select * from table(dbms_xplan.display('plan_table',null,'&1'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2016337987

-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------

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

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

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



----------------------------------------------------------------------------
-------------------------------------
(%CPU)| A-Rows | A-Time | Buffers |

----------------------------------------------------------------------------
-------------------------------------
(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



----------------------------------------------------------------------------
---------------------------------------------
(%CPU)| A-Rows | A-Time | Buffers | Reads |

----------------------------------------------------------------------------
---------------------------------------------
(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



----------------------------------------------------------------------------
--------------------------------
A-Time | Buffers | Reads |

----------------------------------------------------------------------------
--------------------------------
----------------------------------------------------------------------------
--------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



1 - filter(("A"<>'1' AND "D"<>'not aA'))



-----Original Message-----
From: [email protected]
On Behalf Of Taylor, Chris David
Sent: Wednesday, November 16, 2011 12:54 PM
To: 'Stephens, Chris'; 'Mark W. Farnham'
Cc: '[email protected]'
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

----------------------------------------------------------------------------
--------------
(%CPU)| Time |

----------------------------------------------------------------------------
---------------
(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

---------------------------------------------------------------------------
---------------------------------------------------------------------------

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

---------------------------------------------------------------------------
---------------------------------------------------------------------------

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=
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


Sent: Wednesday, November 16, 2011 8:52 AM

To: Taylor, Chris David; '[email protected]'

Subject: RE: Strategies for dealing with (NOT EQUAL) conditions and indexes



"Obviously the use of != causes indexes to be not available"



Really?

Argh. I sent that carefully formatted to not wrap and be proportional, but
apparently the mail chain stripped all that off and wrapped the heck out of
it.

This:

select --+ gather_plan_statistics a,d from junk13 where (a < '1' or a >'1')
and d != 'not aA'

was the key point.

a) what Chris Stephens already demo'd, that you still can get a fast full
index scan quite simply with the != for a not null column.
b) if the column is nullable, then you can still use the index easily as
long as you also don't want NULLs for that column indexed
select * from tab where x < y and x is not null
union all
select * from tab where x > y and x is not null

When y is a sufficiently popular value the pair of range scans will be a
cheaper row source than the full index scan (but in complex plans you may
have to manage this to not screw up the plan generated). Tucking this union
all in the from clause is often an easy way to do this. This also works of
course if the column is not nullable (as in Chris' example) and you don't
then need the "and is not null" clauses. (NOT NULL predicates allow index
usage in all versions you're likely able to get your hands on, though some
texts perpetuate the myth that any NULL reference in the predicate prevents
using the index. Unless you are using an index type that includes null
values IS NULL won't work of course since the null column value rowid
references won't be there. The CBO will use a multicolumn index for when at
least one column in the index is not nullable, if in fact it gets a lower
cost estimate. Standard indexes with all nullable columns of course can't be
guaranteed to return all IS NULL values (and a single column standard index
never will) so the CBO will do the right thing and not consider the indexes
in those cases. I suppose a dictionary stat could be collected that lets the
CBO know there are in fact no NULL values in a particular column, but I
don't believe that is either implemented or considered a useful optimization
to pursue.

When y, or some set of multiple members y are very popular values, you can
also shrink the index on x tremendously (or rather an index on xv, sorry for
the forward reference) by creating a virtual column where the popular values
are mapped to NULL and then putting the predicate on xv instead of x. When
there is one very popular value in a table (often a last status value, but
not always) you can of course convert that value to NULL and include and IS
NOT NULL in your predicate. Then the fast full scan when you don't want that
value less costly by the number of values no longer needed to be stored.

The implementation details of various flavors of this dodge are too long for
an oracle-l post. Unless the value you are avoiding is quite popular the
fast full scan that Chris Stephens demo'd will probably be cheapest and it
certainly is the simplest. Still, I come across cases of a small number of
extremely popular values quite often in transaction systems.

Regards,

mwf