| Problem Index with IN Statement (merged) [message #539742] |
Wed, 18 January 2012 06:07  |
MarkusW
Messages: 20
Registered: June 2009
Location: Austria
|
Junior Member |
|
|
Hello,
I have the following problem. When I used in the IN-Statement fixed values e.q. 197321,197322,197323 ..., the index i_tab2_index works fine (index range scan).
But when I used in the IN-Statement an Sub-Select, the index i_tab2_index doesn't work (fast full scan)!
What's the problem?
Thanks for all the advice and help!
Mark
My scale indices and used Selects:
CREATE INDEX i_tab1_index ON tab1 ( datum, flag_inst );
CREATE INDEX i_tab2_index ON tab2 ( tab2Idx, kontro );
SELECT count(epidx) as rowAnz
FROM tab2
WHERE tab2Idx IN ( SELECT tab1IDX FROM tab1
WHERE datum BETWEEN '20120117' AND '20120117'
AND flag_inst = '1' )
AND kontro = '9876521'
SELECT count(epidx) as rowAnz
FROM tab2
WHERE tab2Idx IN (197321,197322,197323,197324,197325)
AND kontro = '9876521'
[Updated on: Wed, 18 January 2012 06:25] Report message to a moderator |
|
|
|
|
|
|
|
| Problem Index with IN Statement [message #539968 is a reply to message #539742] |
Thu, 19 January 2012 10:09  |
MarkusW
Messages: 20
Registered: June 2009
Location: Austria
|
Junior Member |
|
|
Hello,
I have the following problem. When I used in the IN-Statement fixed values e.q. 197321,197322,197323 ..., the index i_tab2_index works fine (index range scan).
But when I used in the IN-Statement an Sub-Select, the index i_tab2_index doesn't work (fast full scan)!
What's the problem?
Thanks for all the advice and help!
Mark
CREATE INDEX i_tab1_index ON tab1 ( datum, flag_inst );
CREATE INDEX i_tab2_index ON tab2 ( tab2Idx, kontro );
Select with Sub-Select in IN-Statement
SELECT count(epidx) as rowAnz
FROM tab2
WHERE tab2Idx IN ( SELECT tab1IDX FROM tab1
WHERE datum BETWEEN '20120101' AND '20120117'
AND flag_inst = '1' )
AND kontro = '9876521'
Explain Plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 1100884518
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 364 (5)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | HASH JOIN | | 6418 | 131K| 364 (5)| 00:00:05 |
|* 3 | INDEX FAST FULL SCAN | I_TAB2_INDEX | 6417 | 32085 | 342 (5)| 00:00:05 |
| 4 | TABLE ACCESS BY INDEX ROWID| TAB1 | 6853 | 107K| 20 (0)| 00:00:01 |
|* 5 | INDEX SKIP SCAN | I_TAB1_INDEX | 259 | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EPIDX"="TAB1IDX")
3 - filter("KONTRO"='9876521')
5 - access("DATUM">='20120101' AND "FLAG_INST"='1' AND "DATUM"<='20120117')
filter("FLAG_INST"='1')
Select with fixed values in IN-Statement
SELECT count(epidx) as rowAnz
FROM tab2
WHERE tab2Idx IN ( 197221, 197243, 197246, 197248 )
AND kontro = '9876521'
Explain Plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2580069467
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | INDEX RANGE SCAN| I_TAB2_INDEX | 1 | 5 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("EPIDX"=197221 OR "EPIDX"=197243 OR "EPIDX"=197246 OR
"EPIDX"=197248) AND "KONTRO"='9876521')
|
|
|
|
|
|
|
|
|
|
| Re: Problem Index with IN Statement [message #539981 is a reply to message #539977] |
Thu, 19 January 2012 10:27 |
MarkusW
Messages: 20
Registered: June 2009
Location: Austria
|
Junior Member |
|
|
In the Select with Sub-Select in IN-Statement the costs are 364, when I replaced the sub-select with fixed values, the cost is 6!
What's the problem with the Sub-Select in the IN-Statement, although the sub-select has a valid index?
Thanks for any help.
Markus
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
