MySQL Forums
Forum List  »  Optimizer & Parser

How to force the optimizer to use an index in a sub-query?
Posted by: Natalia Kuznesov
Date: October 27, 2005 10:43AM

I need to tune a query:

SELECT RS.SESSION_ID FROM RC_STAT RS
WHERE RS.SESSION_ID in
(SELECT SESSION_ID
FROM RC_ERROR RE
WHERE RE.CREATION_DATE_TIME > 'S'
AND RE.ERROR_DESC ||'S'= 'S'
)

There are 2 indexes on RC_ERROR:

IDX_RC_ERR_SESSION_ID ( SESSION_ID )
IDX_RC_ERROR_CREATION_DATE_TIME (CREATION_DATE_TIME)

I want to use the index on creation_date_time in the sub-query. However, explain plan shows that index on session_id is used (second line in the plan):

+----+--------------------+-------+----------------+-----------------------+------------------------+
| id | select_type | table | type | possible_keys | key |
+----+--------------------+-------+----------------+-----------------------+------------------------+
| 1 | PRIMARY | RS | index | [NULL] | IDX_RC_STAT_SESSION_ID |
| 2 | DEPENDENT SUBQUERY | RE | index_subquery | IDX_RC_ERR_SESSION_ID | IDX_RC_ERR_SESSION_ID |
+----+--------------------+-------+----------------+-----------------------+------------------------+

Is it any way to force the correct index? Something like hint in Oracle?

Options: ReplyQuote


Subject
Views
Written By
Posted
How to force the optimizer to use an index in a sub-query?
6499
October 27, 2005 10:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.