SQL conditions - use of NOT - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: SQL conditions - use of NOT

  1. #11
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It seems intuitively correct that if you have a large table having a highly-skewed, indexed, histogram-analyzed column of values "A" and "B", like ...
    Code:
    select my_col,count(*)
    from my_table
    group by my_col;
    
    MY_COL COUNT(*)
    ------ --------
    A      99999999
    B      1
    .. then Oracle would use the index for predicates like ...

    MY_COL='B'
    ... or ...
    MY_COL!='A'
    ... or ...
    MY_COL='C'

    ... and would use FTS for predicates like ...

    MY_COL='A'
    ... or ...
    MY_COL!='B'

    Unfortunately, I haven't been able to put together a test case to demonstrate it, so maybe theory don't apply here.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  2. #12
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Actually, rethinking it i bet that the != would be much more likely to use an index in a star transformation, involving bitamp index merges etc.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #13
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    Code:
    select my_col,count(*)
    from my_table
    group by my_col;
    
    MY_COL COUNT(*)
    ------ --------
    A      99999999
    B      1
    .. then Oracle would use the index for predicates like ...

    MY_COL='B'
    ... or ...
    MY_COL!='A'
    ... or ...
    MY_COL='C'

    ... and would use FTS for predicates like ...

    MY_COL='A'
    ... or ...
    MY_COL!='B'
    I doubt only for that highlighted part...

    PS

    Code:
    TEST:ABHAY> Set trimspool on
    TEST:ABHAY> analyze table test compute statistics;
    
    Table analyzed.
    
    TEST:ABHAY> ed
    Wrote file afiedt.buf
    
      1  select
      2    INDEX_OWNER     ,
      3    INDEX_NAME      ,
      4    TABLE_OWNER     ,
      5    TABLE_NAME      ,
      6    COLUMN_NAME     ,
      7    COLUMN_POSITION
      8* from dba_ind_columns where index_name='AK_TEST'
    TEST:ABHAY> /
    
    INDEX_OWNER                    INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
    ------------------------------ ------------------------------ ------------------------------ ------------------------------
    COLUMN_NAME
    -----------------------------------------------
         COLUMN_POSITION
    --------------------
    ABHAY                          AK_TEST                        ABHAY                          TEST
    ID4
                       1
    
    ABHAY                          AK_TEST                        ABHAY                          TEST
    ID2
                       2
    
    TEST:ABHAY> set autotrace on explain
    TEST:ABHAY> alter session set optimizer_mode=ALL_ROWS;
    
    Session altered.
    
    TEST:ABHAY> select ID4, Count(*) from TEST group by ID4;
    
                     ID4             COUNT(*)
    -------------------- --------------------
                       1              1099799
                       2                   13
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1673 Card=2 Bytes=
              4)
    
       1    0   SORT (GROUP BY) (Cost=1673 Card=2 Bytes=4)
       2    1     TABLE ACCESS (FULL) OF 'TEST' (Cost=359 Card=1099812 Byt
              es=2199624)
    
    
    TEST:ABHAY> select * from test where id4=2 and id2=1999991;
    
                      ID                  ID1                  ID2                  ID4                  ID5
    -------------------- -------------------- -------------------- -------------------- --------------------
                 1999989              1999990              1999991                    2                    1
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=18)
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3 Card=1 Byt
              es=18)
    
       2    1     BITMAP CONVERSION (TO ROWIDS)
       3    2       BITMAP INDEX (SINGLE VALUE) OF 'AK_TEST'
    
    
    TEST:ABHAY> select * from test where id4!=1 and id2=1999991;
    
                      ID                  ID1                  ID2                  ID4                  ID5
    -------------------- -------------------- -------------------- -------------------- --------------------
                 1999989              1999990              1999991                    2                    1
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=359 Card=1 Bytes=1
              8)
    
       1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=359 Card=1 Bytes=18)
    
    You can see its going for FTS with ID4!=1
    
    TEST:ABHAY> select /*+ INDEX(TEST AK_TEST) */ * from test where id4!=1 and id2=1999991;
    
                      ID                  ID1                  ID2                  ID4                  ID5
    -------------------- -------------------- -------------------- -------------------- --------------------
                 1999989              1999990              1999991                    2                    1
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9734 Card=1 Bytes=
              18)
    
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=9734 Card=1
              Bytes=18)
    
       2    1     BITMAP CONVERSION (TO ROWIDS)
       3    2       BITMAP INDEX (FULL SCAN) OF 'AK_TEST'
    compare the costs with index scan and without for != condition
    != condition will always call for FULL scans (Table/Index), and oracle feels FULL Table scan is wiser than FULL Index scan, as you know FTS can fetch DFMC blocks in one I/O which FULL index scan dosent..

    Ok, only exception will be if Index blocks is much less than Table blocks consumed. I would guess a Table with high number COLS present and indexed on only few COLS, than Index Scan would be wiser.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width