OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Not using Index (10.2.0.1, Windows 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Not using Index [message #383413]

Wed, 28 January 2009 16:29

sant_new
Messages: 165
Registered: June 2008

Senior Member

Hi all,

I'm running a sql query(part of a query from the application code) to see if the index is being used on our production database. I set autotrace and here is the execution plan:

SQL> select  * from  bog where natur = 1  and   nat_code = '1' and   currn = 'Y'  and   tetd <> 'Y';

...
...
...


Execution Plan
----------------------------------------------------------

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2845 |   502K| 10540 |
|   1 |  TABLE ACCESS FULL| BOG  |  2845 |   502K| 10540 |
----------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
         99  recursive calls
          0  db block gets
      48143  consistent gets
          0  physical reads
        116  redo size
       3420  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

There is an foreign key index FK_BOG_IDX ON BOG table for (natur,nat_code). Apparently, this index is not being used in the query.

But, for the exact number of rows in BOG table in our test database, for the same sql query the index is being used.

SQL> select  * from  bog where natur = 1  and   nat_code = '1' and   currn = 'Y'  and   tetd <> 'Y';

...
...
...


Execution Plan
----------------------------------------------------------

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

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

| Id  | Operation                   | Name                           | Rows  | B

ytes | Cost  |

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

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

|   0 | SELECT STATEMENT            |                                |    22 |
3300 |    40 |

|   1 |  TABLE ACCESS BY INDEX ROWID| COI                            |    22 |
3300 |    40 |

|   2 |   INDEX RANGE SCAN          | FK_ARCST__COMPANY__CUST_C__COI |    86 |
     |     3 |

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

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


Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
        554  recursive calls
          0  db block gets
       2565  consistent gets
          0  physical reads
        116  redo size
       3891  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          9  rows processed

I tried to force the index FK_BOG_IDX ON BOG in the production database by using Hint. THen the query uses this index. But at this point, I need to find out why the index is not being used in the production database and its been used in the test database with same number of rows and the tables are analyzed pretty recently on both the databases.

Please give me your thoughts.

Thank you.