| 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.
|
|
|
|
|
|
| Re: Not using Index [message #383415 is a reply to message #383413] |
Wed, 28 January 2009 16:41  |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
|
Sorry about the wrong information. The BOG database in production database has 1423987 records and test database has 1411390 records in it. That could be the difference in the number of rows processed..
|
|
|
|
| Re: Not using Index [message #383417 is a reply to message #383413] |
Wed, 28 January 2009 16:42 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Since it might not be feasible to force the indexes for the sql's in the application code, I'm thinking if it will make a difference if we rebuild the index in the BOG table in the production database? Please give me your suggestions
Thanks for your help
[Updated on: Wed, 28 January 2009 16:47] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Not using Index [message #383420 is a reply to message #383413] |
Wed, 28 January 2009 16:53 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
|
Black swan, That is the total number of records in the tables. The select query is not fetching the records for the where condition.
|
|
|
|
| Re: Not using Index [message #383422 is a reply to message #383413] |
Wed, 28 January 2009 17:26 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Mahesh,
I collected statistics and it is still not using the index..
EXECUTE DBMS_STATS.GATHER_INDEX_STATS(ownname => 'bint', indname => 'FK_BOG_IDX', ESTIMATE_PERCENT=>10)
Is there a way to find out why the index is not being used??
Thanks
|
|
|
|
|
|
|
|
| Re: Not using Index [message #383622 is a reply to message #383422] |
Thu, 29 January 2009 05:57 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Gather stats on the whole table, rather than for just one index. I'd do it with METHOD_OPT = 'FOR ALL INDEXED COLUMNS'
|
|
|
|
| Re: Not using Index [message #386316 is a reply to message #383413] |
Fri, 13 February 2009 10:10 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Thanks for all your replies. I gathered statistics for that index and I rebuild the index. The program was considerably faster. But today we came across the problem again that now the rebuild index is not being used in the queries. Could it be because of the rebuild??
Please give me your suggestions. Thanks again
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
