"IN" clause making query to run infinite loop
941089Jun 1 2012 — edited Jun 4 2012Hello Masters,
I am using Oracle 9i and using one query with "IN" clause. This query takes arnd 5-6 hours of time to get that completed, but when i removed the "IN" clause with "Inner Join" then query starts completing in seconds.
More over looking at the execution plan for the queries i don't see any differences and the costing is also very low.
Query is:
SELECT contact_id FROM scc_case_contact_t WHERE case_id IN (SELECT id FROM temp_data_manager_t WHERE id_info = 'mDependentCaseIds' AND user_id = 1 AND sess_id = '1769F72A8F7A7B1FB98AAAE6676845F6') AND contact_id IN (SELECT id FROM temp_data_manager_t WHERE id_info = 'arrCntIds' AND user_id = 1 AND sess_id = '1769F72A8F7A7B1FB98AAAE6676845F6')
Execution Plan:
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 272 | 5 |
| 1 | NESTED LOOPS SEMI | | 1 | 272 | 5 |
| 2 | NESTED LOOPS SEMI | | 1 | 142 | 5 |
| 3 | INDEX FAST FULL SCAN | CASE_CONTACT_PK | 94729 | 1110K| 5 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEMP_DATA_MANAGER_T | 1 | 130 | |
| 5 | INDEX RANGE SCAN | TEMP_DATA_MANAGER_IX_ID_INFO | 1 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | TEMP_DATA_MANAGER_T | 1 | 130 | |
| 7 | INDEX RANGE SCAN | TEMP_DATA_MANAGER_IX_ID_INFO | 1 | | |
-----------------------------------------------------------------------------------------------
Almost all the queries with "IN" clause are taking astonishingly high time.
The same set of queries are working fine at Oracle 10G.
Any help on this will be highly appreciated.