3 Replies Latest reply: Jan 17, 2013 2:59 AM by Billy~Verreynne RSS

    Slow select using primary key

    825199
      I have a table ( DISPATCH ) that has about 7M rows.It has a primary key on an integer column (DIS_ID) with index DIS_PK.

      It takes 20-30~ seconds to run this query : select dispatch_date from DISPATCH where dis_id = 10443. On the execution plan, by default, Oracle uses FULL TABLE SCAN. When I force it to use index with hint, the execution plan change to FULL INDEX SCAN, and it takes about 20 seconds to execute. That is too slow. We have another table, with similar structure, similar query which only takes < 0.1 second to execute. I have gathered statitics for the schema but no better result was found.

      Does anybody else know what I should try next.

      Thanks,

      Nguyen.

      Edited by: user1152556 on 00:09 17-01-2013

      Edited by: user1152556 on 00:10 17-01-2013
        • 1. Re: Slow select using primary key
          Paul  Horth
          user1152556 wrote:
          I have a table ( DISPATCH ) that has about 7M rows.It has a primary key on an integer column (DIS_ID) with index DIS_PK.

          It takes 20-30~ seconds to run this query : select dispatch_date from DISPATCH where dis_id = 10443. On the execution plan, by default, Oracle uses FULL TABLE SCAN. When I force it to use index with hint, the execution plan change to FULL INDEX SCAN, and it takes about 20 seconds to execute. That is too slow. We have another table, with similar structure, similar query which only takes < 0.1 second to execute. I have gathered statitics for the schema but no better result was found.

          Does anybody else know what I should try next.

          Thanks,

          Nguyen.
          Please read {message:id=9360003} and follow the advice there.

          In particular post your Oracle version, create table and index statements, the SQL you are running and its explain plan.
          • 2. Re: Slow select using primary key
            Purvesh K
            user1152556 wrote:
            I have a table ( DISPATCH ) that has about 7M rows.It has a primary key on an integer column (DIS_ID) with index DIS_PK.

            It takes 20-30~ seconds to run this query : select dispatch_date from DISPATCH where dis_id = 10443. On the execution plan, by default, Oracle uses FULL TABLE SCAN. When I force it to use index with hint, the execution plan change to FULL INDEX SCAN, and it takes about 20 seconds to execute. That is too slow. We have another table, with similar structure, similar query which only takes < 0.1 second to execute. I have gathered statitics for the schema but no better result was found.

            Does anybody else know what I should try next.
            Probably the Statistics are not gathered recently and depending on Stats, CBO chooses a FULL Table Scan rather than an Index Scan, which should be faster.

            It would be helpful, if you can post details as mentioned in post {message:id=3292438}.

            Please do post the Explain Plans, for both SQL's, with and without Hint of Index.

            Also, below results might be helpful:
            select * from v$version;
            
            select num_rows, last_analyzed
              from user_tables
            where table_name = 'DISPATCH';
            
            select count(*)
              from dispatch;
            • 3. Re: Slow select using primary key
              Billy~Verreynne
              There something funky with index DIS_PK - as why is there an index range scan when hitting it with a fully unique indexed value, and not with a unique index scan?

              basic example:
              SQL> create table hashtab( id number constraint pk_hashtab primary key using index );
              
              Table created.
              
              SQL> 
              SQL> set autotrace on explain
              SQL> select * from hashtab where id = 1;
              
              no rows selected
              
              
              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 2881930540
              
              --------------------------------------------------------------------------------
              | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
              --------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT  |            |     1 |    13 |     1   (0)| 00:00:01 |
              |*  1 |  INDEX UNIQUE SCAN| PK_HASHTAB |     1 |    13 |     1   (0)| 00:00:01 |
              --------------------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                 1 - access("ID"=1)
              
              SQL> set autotrace off
              SQL> 
              SQL> alter table hashtab drop constraint pk_hashtab;
              
              Table altered.
              
              SQL> 
              SQL> create index unq_hashtab on hashtab( id+0 );
              
              Index created.
              
              SQL> set autotrace on explain
              SQL> select * from hashtab where id = 1;
              
              no rows selected
              
              
              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 3152300751
              
              -----------------------------------------------------------------------------
              | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
              -----------------------------------------------------------------------------
              |   0 | SELECT STATEMENT  |         |     1 |    13 |     2   (0)| 00:00:01 |
              |*  1 |  TABLE ACCESS FULL| HASHTAB |     1 |    13 |     2   (0)| 00:00:01 |
              -----------------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                 1 - filter("ID"=1)
              
              Note
              -----
                 - dynamic sampling used for this statement (level=2)
              
              SQL> select /*+index(h unq_hashtab) */ * from hashtab h where id = 1;
              
              no rows selected
              
              
              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 560589908
              
              -------------------------------------------------------------------------------------------
              | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
              -------------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT            |             |     1 |    13 |     0   (0)| 00:00:01 |
              |*  1 |  TABLE ACCESS BY INDEX ROWID| HASHTAB     |     1 |    13 |     0   (0)| 00:00:01 |
              |   2 |   INDEX FULL SCAN           | UNQ_HASHTAB |     1 |       |     0   (0)| 00:00:01 |
              -------------------------------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                 1 - filter("ID"=1)
              
              Note
              -----
                 - dynamic sampling used for this statement (level=2)
              
              SQL> 
              Verify the objects (table, indexes, etc) definitions - and not assume they are defined as you think they are.