Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to force query to use an Index

RPGJul 8 2013 — edited Jul 8 2013

Database :  10.2.0.1 (on Linux)

I have a query (see below) which is doing table scan and not use the index. If I give " Explain plan for select ...." it uses the index. But when I check execution plan for query coming from application using sql_id, it is not using the index. I tried using Index hint, didn't work through application.


SELECT

/* + Index(PK_BATCH_ID, INDX_T_TIME_BONUS) */

tb.time_bonus_id, b.* FROM t_time_bonus tb, t_batch b

WHERE tb.site_look_id=:"SYS_B_0" AND tb.batch_id=:"SYS_B_1"

AND tb.offered=:"SYS_B_2" AND tb.offer_time<sysdate AND tb.pool_batch_id=b.batch_id

AND tb.pool_promotion_id=b.promotion_id AND rownum=:"SYS_B_3" FOR UPDATE nowait

Plan hash value: 1671755995                                                                                                                                                                                                                                                                       

<format>                                                                                                                                                                                                                                                                                                      ----------------------------------------------------------------------------------------------                                                                                                                                                                                                    

| Id  | Operation             | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                                    

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1084608392

-------------------------------------------------------------
| Id  | Operation                       | Name              |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |
|   1 |  FOR UPDATE                     |                   |
|   2 |   COUNT                         |                   |
|   3 |    FILTER                       |                   |
|   4 |     HASH JOIN                   |                   |
|   5 |      TABLE ACCESS BY INDEX ROWID| T_TIME_BONUS      |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   6 |       INDEX RANGE SCAN          | INDX_T_TIME_BONUS |
|   7 |      TABLE ACCESS FULL          | T_BATCH           |
-------------------------------------------------------------

<\format>

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

|   0 | SELECT STATEMENT      |              |       |       |       |  7446 (100)|          |                                                                                                                                                                                                    

|   1 |  FOR UPDATE           |              |       |       |       |            |          |                                                                                                                                                                                                    

|   2 |   COUNT               |              |       |       |       |            |          |                                                                                                                                                                                                    

|   3 |    FILTER             |              |       |       |       |            |          |                                                                                                                                                                                                    

|   4 |     HASH JOIN         |              | 78486 |  6898K|  3296K|  7446   (3)| 00:01:30 |                                                                                                                                                                                                    

|   5 |      TABLE ACCESS FULL| T_TIME_BONUS | 78486 |  2376K|       |  6965   (3)| 00:01:24 |                                                                                                                                                                                                    

|   6 |      TABLE ACCESS FULL| T_BATCH      | 56289 |  3243K|       |   128   (3)| 00:00:02 |                                                                                                                                                                                                    

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

\

SQL>  show parameter optimizer

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_dynamic_sampling           integer                          2
optimizer_features_enable            string                           10.2.0.1
optimizer_index_caching              integer                          0
optimizer_index_cost_adj             integer                          50
optimizer_mode                       string                           ALL_ROWS
optimizer_secure_view_merging        boolean                          TRUE

\

Thanks                                                                                                                                                                                                     

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 5 2013
Added on Jul 8 2013
6 comments
8,176 views