FAQ
I often puzzle over the best way to deal with coding for NOT EQUAL conditions ( where x != y) in SQL code when I'm looking at performance tuning.
In some cases, it's just a matter of education and getting a developer to look at what values are actually being stored in the table and have them look for the values they actually want versus the one they don't want.

But sometimes, these values are changing and the developer knows he/she wants ALL BUT THIS ONE value but he/she cannot know what possible values *might* exist.

Obviously the use of != causes indexes to be not available (except for a trick or two) to the optimizer, so I'm curious what are some strategies to turn a != into an = condition when the possible values aren't known or are too many to code for?

Could you use an EXISTS or IN statement and a subquery, or is there an alternative I haven't considered?


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.

Search Discussions

  • Stephens, Chris at Nov 16, 2011 at 3:09 pm
    "Obviously the use of != causes indexes to be not available"


    Really?



    db_admin@REDDB> create table t1(col1 number not null);



    Table created.



    db_admin@REDDB> create index t1_idx1 on t1(col1);



    Index created.



    db_admin@REDDB> insert into t1(col1) values(1);



    1 row created.



    db_admin@REDDB> insert into t1(col1) values(2);



    1 row created.



    db_admin@REDDB> select /*+ gather_plan_statistics */ col1 from t1 where col1 != 1;



    COL1

    ----------

    2



    db_admin@REDDB> select * from table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));



    --------------------------------------------------------------------------------------
    Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
    --------------------------------------------------------------------------------------
    0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
    * 1 | INDEX FULL SCAN | T1_IDX1 | 1 | 1 | 1 |00:00:00.01 | 1 |
    --------------------------------------------------------------------------------------



    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Taylor, Chris David
    Sent: Tuesday, November 15, 2011 1:10 PM
    To: 'oracle-l@freelists.org'
    Subject: Strategies for dealing with (NOT EQUAL) conditions and indexes



    I often puzzle over the best way to deal with coding for NOT EQUAL conditions ( where x != y) in SQL code when I'm looking at performance tuning.

    In some cases, it's just a matter of education and getting a developer to look at what values are actually being stored in the table and have them look for the values they actually want versus the one they don't want.



    But sometimes, these values are changing and the developer knows he/she wants ALL BUT THIS ONE value but he/she cannot know what possible values *might* exist.



    Obviously the use of != causes indexes to be not available (except for a trick or two) to the optimizer, so I'm curious what are some strategies to turn a != into an = condition when the possible values aren't known or are too many to code for?



    Could you use an EXISTS or IN statement and a subquery, or is there an alternative I haven't considered?





    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.





    --

    http://www.freelists.org/webpage/oracle-l





    CONFIDENTIALITY NOTICE:
    This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.



    --
    http://www.freelists.org/webpage/oracle-l
  • Taylor, Chris David at Nov 16, 2011 at 5:55 pm
    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
    Sent: Wednesday, November 16, 2011 8:52 AM
    To: Taylor, Chris David; 'oracle-l@freelists.org'
    Subject: RE: Strategies for dealing with (NOT EQUAL) conditions and indexes

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

    Really?
  • Taral Desai at Nov 17, 2011 at 12:16 am
    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

    --------------------------------------------------------------------------------------------------
    Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |
    A-Time | Buffers |
    --------------------------------------------------------------------------------------------------
    0 | SELECT STATEMENT | | 1 | | 15 (100)| 100
    00:00:00.01 | 53 |
    * 1 | TABLE ACCESS FULL| TEST1 | 1 | 100 | 15 (0)| 100
    00:00:00.01 | 53 |
    --------------------------------------------------------------------------------------------------

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

    Plan hash value: 4122059633

    --------------------------------------------------------------------------------------------------
    Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |
    A-Time | Buffers |
    --------------------------------------------------------------------------------------------------
    0 | SELECT STATEMENT | | 1 | | 15 (100)| 0
    00:00:00.01 | 52 |
    * 1 | TABLE ACCESS FULL| TEST1 | 1 | 99 | 15 (0)| 0
    00:00:00.01 | 52 |
    --------------------------------------------------------------------------------------------------

    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
    On Wed, Nov 16, 2011 at 11:53 AM, Taylor, Chris David wrote:

    SQL> select val2, val3, val4, val5
    2 from test1
    3 where val1 = 12
    4 and val2 != 'A12E'


    --
    Thanks & Regards,
    Taral Desai


    --
    http://www.freelists.org/webpage/oracle-l
  • Sidney Chen at Nov 17, 2011 at 12:54 pm
    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@CS10G> select count(*)
    2 from test1
    3 where val1 
    4 and val2 = 'A12E'
    5 /

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

    1 row selected.

    oe@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@CS10G> @x typical
    oe@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@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@CS10G> @x typical
    oe@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;
    /
  • Taylor, Chris David at Nov 17, 2011 at 1:59 pm
    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: taral.desai@gmail.com
    Cc: Taylor, Chris David; Stephens, Chris; Mark W. Farnham; oracle-l@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@CS10G> select count(*)
    2 from test1
    3 where val1 
    4 and val2 = 'A12E'
    5 /

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

    1 row selected.

    oe@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@CS10G> @x typical
    oe@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@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@CS10G> @x typical
    oe@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;
    /
  • Taylor, Chris David at Nov 17, 2011 at 2:30 pm
    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.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Taylor, Chris David
    Sent: Thursday, November 17, 2011 7:58 AM
    To: 'Sidney Chen'; 'taral.desai@gmail.com'
    Cc: 'Stephens, Chris'; 'Mark W. Farnham'; 'oracle-l@freelists.org'
    Subject: RE: Strategies for dealing with (NOT EQUAL) conditions and indexes

    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: taral.desai@gmail.com
    Cc: Taylor, Chris David; Stephens, Chris; Mark W. Farnham; oracle-l@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.


    --
    http://www.freelists.org/webpage/oracle-l
  • Mark W. Farnham at Nov 17, 2011 at 3:16 pm
    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

    -----Original Message-----
    From: Taylor, Chris David
    Sent: Thursday, November 17, 2011 9:29 AM
    To: Taylor, Chris David; 'Sidney Chen'; 'taral.desai@gmail.com'
    Cc: 'Stephens, Chris'; 'Mark W. Farnham'; 'oracle-l@freelists.org'
    Subject: RE: Strategies for dealing with (NOT EQUAL) conditions and indexes

    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".

    <snip>


    --
    http://www.freelists.org/webpage/oracle-l
  • Dom Brooks at Nov 17, 2011 at 5:23 pm
    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
  • Taylor, Chris David at Nov 17, 2011 at 5:53 pm
    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.


    -----Original Message-----
    From: Dom Brooks
    Sent: Thursday, November 17, 2011 11:21 AM
    To: mwf@rsiz.com
    Cc: Taylor, Chris David; Sidney Chen; <taral.desai@gmail.com>; Stephens, Chris; <oracle-l@freelists.org>; Jonathan Lewis
    Subject: Re: Strategies for dealing with (NOT EQUAL) conditions and indexes

    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



    --
    http://www.freelists.org/webpage/oracle-l
  • Jorgensen, Finn at Nov 17, 2011 at 7:14 pm
    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@freelists.org On Behalf Of Sidney Chen
    Sent: Thursday, November 17, 2011 7:53 AM
    To: taral.desai@gmail.com
    Cc: ChrisDavid.Taylor@ingrambarge.com; Stephens, Chris; Mark W. Farnham; oracle-l@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@CS10G> select count(*)
    2 from test1
    3 where val1 
    4 and val2 = 'A12E'
    5 /

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

    1 row selected.

    oe@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@CS10G> @x typical
    oe@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@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@CS10G> @x typical
    oe@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
  • Mark W. Farnham at Nov 17, 2011 at 1:56 pm
    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@freelists.org
    On Behalf Of Taylor, Chris David
    Sent: Wednesday, November 16, 2011 12:54 PM
    To: 'Stephens, Chris'; 'Mark W. Farnham'
    Cc: 'oracle-l@freelists.org'
    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


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

    To: Taylor, Chris David; 'oracle-l@freelists.org'

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



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



    Really?
  • Mark W. Farnham at Nov 17, 2011 at 2:01 pm
    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.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Mark W. Farnham
    Sent: Thursday, November 17, 2011 8:54 AM
    To: ChrisDavid.Taylor@ingrambarge.com; 'Stephens, Chris'
    Cc: oracle-l@freelists.org
    Subject: RE: Strategies for dealing with (NOT EQUAL) conditions and indexes

    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.
    <snip>


    --
    http://www.freelists.org/webpage/oracle-l
  • Mark W. Farnham at Nov 16, 2011 at 5:00 pm
    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

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Taylor, Chris David
    Sent: Tuesday, November 15, 2011 2:10 PM
    To: 'oracle-l@freelists.org'
    Subject: Strategies for dealing with (NOT EQUAL) conditions and indexes

    I often puzzle over the best way to deal with coding for NOT EQUAL
    conditions ( where x != y) in SQL code when I'm looking at performance
    tuning.
    In some cases, it's just a matter of education and getting a developer to
    look at what values are actually being stored in the table and have them
    look for the values they actually want versus the one they don't want.

    But sometimes, these values are changing and the developer knows he/she
    wants ALL BUT THIS ONE value but he/she cannot know what possible values
    *might* exist.

    Obviously the use of != causes indexes to be not available (except for a
    trick or two) to the optimizer, so I'm curious what are some strategies to
    turn a != into an = condition when the possible values aren't known or are
    too many to code for?

    Could you use an EXISTS or IN statement and a subquery, or is there an
    alternative I haven't considered?


    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.


    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 15, '11 at 7:11p
activeNov 17, '11 at 7:14p
posts14
users7
websiteoracle.com

People

Translate

site design / logo © 2017 Grokbase