Home » RDBMS Server » Performance Tuning » Problem Index with IN Statement (merged) (Oracle 10g Enterprise Edition Release 10.2.0.1.0, Solaris 10)
Problem Index with IN Statement (merged) [message #539742] Wed, 18 January 2012 06:07 Go to next message
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

Re: Problem Index with IN Statement (merged 2) [message #539749 is a reply to message #539742] Wed, 18 January 2012 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any peformances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

If it chooses FFS then it is because it thinks FFS is better...

Regards
Michel
Re: Problem Index with IN Statement [message #539771 is a reply to message #539742] Wed, 18 January 2012 07:57 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
MarkusW wrote on Wed, 18 January 2012 07:07
                   WHERE datum BETWEEN '20120117' AND '20120117'



What is the purpose of that or more precisely, why use so much verbiage when you can use so much less?

[Updated on: Wed, 18 January 2012 07:58]

Report message to a moderator

Problem Index with IN Statement [message #539968 is a reply to message #539742] Thu, 19 January 2012 10:09 Go to previous messageGo to next message
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 #539970 is a reply to message #539771] Thu, 19 January 2012 10:10 Go to previous messageGo to next message
MarkusW
Messages: 20
Registered: June 2009
Location: Austria
Junior Member
Closed
Re: Problem Index with IN Statement [message #539975 is a reply to message #539970] Thu, 19 January 2012 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which means?
If you have a solution pleas post it with the reason of the problem.

Regards
Michel
Re: Problem Index with IN Statement [message #539977 is a reply to message #539968] Thu, 19 January 2012 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And? The 2 statements are NOT the same ones, why do you want Oracle takes the same plan?

Regards
Michel
Re: Problem Index with IN Statement [message #539981 is a reply to message #539977] Thu, 19 January 2012 10:27 Go to previous messageGo to next message
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
Re: Problem Index with IN Statement [message #539983 is a reply to message #539981] Thu, 19 January 2012 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot compare the cost of 2 different (I mean not equivalent query).
The only thing you can deduce is that if you know the list then do not search it in the database hard_code it in the query (but this was obvious, wasn't it?).

Regards
Michel
Re: Problem Index with IN Statement [message #540005 is a reply to message #539975] Thu, 19 January 2012 12:38 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Thread reposted in Perf. tuning was I think what he meant Smile

[Updated on: Thu, 19 January 2012 12:38]

Report message to a moderator

Re: Problem Index with IN Statement [message #540007 is a reply to message #540005] Thu, 19 January 2012 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Topics merged. Wink
Thanks
Michel
Re: Problem Index with IN Statement [message #540057 is a reply to message #539983] Fri, 20 January 2012 01:12 Go to previous messageGo to next message
MarkusW
Messages: 20
Registered: June 2009
Location: Austria
Junior Member
The 2 statements are NOT the same ones, i know!

My question is, why be caused by the sub-select themselves as higher costs due to fixed values?


Regards,
Markus

Re: Problem Index with IN Statement [message #540058 is a reply to message #540057] Fri, 20 January 2012 01:17 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because doing something is more expensive than doing nothing.

Regards
Michel
Previous Topic: Undo space
Next Topic: Please help me tune this sql
Goto Forum:
  


Current Time: Fri Mar 29 00:21:54 CDT 2024