1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query not using index but doing a full table scan

Discussion in 'SQL PL/SQL' started by rajenb, May 13, 2014.

  1. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi All,

    I have a batch PL/SQL which needs to be optimized and there is a specific query which is taking considerable time because of a full table scan where I was expecting it to use the indexes. Here's the SQL to simulate the problem:

    Code (SQL):
    CREATE TABLE T1
      (
        lower_lim  VARCHAR2(20),
        upper_lim VARCHAR2(20),
        t1_val1    NUMBER(2),
        t1_val2    VARCHAR2(10)
      );

    CREATE INDEX T1_I1 ON T1(LOWER_LIM);
    CREATE INDEX T1_I2 ON T1(UPPER_LIM);

    INSERT INTO T1 VALUES ('54601000', '54602000', 30, 'SPLIT');
    INSERT INTO T1 VALUES ('54601000', '54601500', 30, 'NEW');
    INSERT INTO T1 VALUES ('54601501', '54602000', 30, 'NEW');
    INSERT INTO T1 VALUES ('54603000', '54609000', 30, 'NEW');

    COMMIT;

    SELECT * FROM T1 WHERE '54601300' BETWEEN LOWER_LIM AND UPPER_LIM;
    When I do an "Explain Plan" of the SELECT statement, I get a "TABLE ACCESS - FULL" (i.e., full table scan).

    Why are the indexes T1_I1 and T1_I2 not used and how can I correct the situation ?

    Note: I'm using around 1000 rows to test (DEV instance) and on PROD, we're expecting around 500K-700K rows.

    Thanks & Regards,
    Rajen.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.


    1) lower_lim and upper_lim - storing only number ?
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    No, they may non-numeric, for ex., 'A045360B' and that's why it has been created as a VARCHAR.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    As you are going to execute BETWEEN for the text value?
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Yes.
    Do you foresee any problem with BETWEEN ?
    Is there any other way to query a range ?
    I suppose we can use conditions like ( 'x' >= LOWER_LIM AND 'x' <= UPPER_LIM) - but isn't this equivalent to BETWEEN ?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    11.2.0.3 -- I see no problems using the index:


    Code (SQL):

    SQL> CREATE TABLE T1
      2    (
      3      lower_lim  VARCHAR2(20),
      4      upper_lim VARCHAR2(20),
      5      t1_val1    NUMBER(2),
      6      t1_val2    VARCHAR2(10)
      7    );


    TABLE created.


    SQL>
    SQL> CREATE INDEX T1_I1 ON T1(LOWER_LIM);


    INDEX created.


    SQL> CREATE INDEX T1_I2 ON T1(UPPER_LIM);


    INDEX created.


    SQL>
    SQL> INSERT INTO T1 VALUES ('54601000', '54602000', 30, 'SPLIT');


    1 ROW created.


    SQL> INSERT INTO T1 VALUES ('54601000', '54601500', 30, 'NEW');


    1 ROW created.


    SQL> INSERT INTO T1 VALUES ('54601501', '54602000', 30, 'NEW');


    1 ROW created.


    SQL> INSERT INTO T1 VALUES ('54603000', '54609000', 30, 'NEW');


    1 ROW created.


    SQL>
    SQL> COMMIT;


    Commit complete.


    SQL>
    SQL> SET autotrace ON
    SQL>
    SQL> SELECT * FROM T1 WHERE '54601300' BETWEEN LOWER_LIM AND UPPER_LIM;


    LOWER_LIM            UPPER_LIM               T1_VAL1 T1_VAL2
    -------------------- -------------------- ---------- ----------
    54601000             54602000                     30 SPLIT
    54601000             54601500                     30 NEW

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1429545322
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |     2 |    88 |     1   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     2 |    88 |     1   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | T1_I1 |     2 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------


    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER("UPPER_LIM">='54601300')
       2 - access("LOWER_LIM"<='54601300')


    Note
    -----
       - dynamic sampling used FOR this statement (level=2)
       - SQL plan baseline "SQL_PLAN_0a213h7y59tnpac124365" used FOR this statement

    Statistics
    ----------------------------------------------------------
              7  recursive calls
              0  db block gets
             18  consistent gets
              0  physical reads
              0  redo SIZE
            836  bytes sent via SQL*Net TO client
            519  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              2  ROWS processed


    SQL>
    SQL> EXEC dbms_stats.gather_schema_stats('GRIBNAUT')


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT * FROM T1 WHERE '54601300' BETWEEN LOWER_LIM AND UPPER_LIM;


    LOWER_LIM            UPPER_LIM               T1_VAL1 T1_VAL2
    -------------------- -------------------- ---------- ----------
    54601000             54602000                     30 SPLIT
    54601000             54601500                     30 NEW

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1429545322
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |     2 |    52 |     2   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     2 |    52 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | T1_I1 |     2 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------


    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER("UPPER_LIM">='54601300')
       2 - access("LOWER_LIM"<='54601300')


    Note
    -----
       - SQL plan baseline "SQL_PLAN_0a213h7y59tnpac124365" used FOR this statement

    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              4  consistent gets
              0  physical reads
              0  redo SIZE
            836  bytes sent via SQL*Net TO client
            519  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              2  ROWS processed


    SQL>
     

    Which release of Oracle are you using?
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi David,

    Thanks for info.
    I'm using Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 (LINUX box).

    Is there any configuration to check whether Oracle is "costing" the query in some specific way ?

    I'll try to re-run with "dbms_stats.gather_schema_stats" once the server is up and will revert back asap.

    Regards,
    Rajen.
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    The cost of sql-query on your of system will be another.
    The plan structure shall match.


    Note:
    Yes, of course, the index can be used.
    Technically it will work too...
    But...
    I think that the request of such look isn't correctly for varchar2 data
     
  9. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi All,

    Thanks for the tips.

    Sergey: I can't avoid the VARCHAR2 as the source data (and lookup table reference also) are VARCHAR2 (in fact in the application, they are "Serial Numbers"). The plan structure should match.

    David: I ran the DBMS_STATS.GATHER_TABLE_STATS (for the single table T1) and this time it worked - it used the index T1_I1.

    However, when I asked the Production Support Team to repeat same for the PROD table, it didn't use the index and again a full table scan.

    I then requested Prod. DBA to export the table and import it on TEST environment.

    I did the same operation as for table T1, and it worked !!

    How come same query on same table (identical DDLs + Data) react differently from 1 instance to another ?

    Is there any other oracle configurations or optimization options to check ?

    Thanks,
    Rajen.
    P.S: Both Oracle instances are on same version (11g).
     
  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    what as value of parameter "METHOD" for executed DBMS_STATS.GATHER_TABLE_STATS for table 'T1' ?
     
  11. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    Here's what I used for T1 (and for the PROD Serial No. table also):

    Code (SQL):
    BEGIN
     DBMS_STATS.GATHER_TABLE_STATS (
       ownname => 'APPS',
       tabname => 'T1',
       degree => 2,
       cascade => TRUE,
       METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
       estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
         END;
         /
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    A 10053 trace will report on what the optimizer is seeing and using in its decision mechanism:


    alter session set tracefile_identifier='index';
    alter session set events='10053 trace name context forever, level 1';


    Run your query; when it's done do this:


    alter session set events='10053 trace name context off';


    Then go look for a trace file with 'index' in the name in the user_dump_dest. It will be readable as-is. It will report all of the various paths the optimizer considered as well as the final path it chose. There will also be statistics reported that the optimizer read for the table and the indexes.


    If you need help post the trace file here and someone should be able to assist you.