Home » SQL & PL/SQL » SQL & PL/SQL » Inconsistent execution plans (Oracle 11g)
Inconsistent execution plans [message #589608] Tue, 09 July 2013 02:52 Go to next message
Andrey_K
Messages: 1
Registered: July 2013
Junior Member
I have two very similar queries with very different execution plans and don`t know what is the reason for that.

1) The execution order of this query is at initially retrieves records from T1 and then join it with T2. T1 has 42302700 records, T2 has 58040615 records and there are the following indexes on IND1 = 1T1.ID, IND2 = T2.ID, IND3 = T2.VALUE. The query returns a few records.

SELECT *
FROM TABLE1 T1
LEFT OUTER JOIN TABLE2 T2 ON T1.ID = T2.ID
WHERE T2.VALUE BETWEEN 'AAAAAAA' AND 'BBBBBBBB'
OR T2.VALUE BETWEEN 'CCCCCCC' AND 'DDDDDDD'

2) An additional filter is added on column with cardinality one. However the execution order is changed and initially the records from T2 are retrieved. There is no index on T2.TYPE.

SELECT *
FROM TABLE1 T1
LEFT OUTER JOIN TABLE2 T2 ON T1.ID = T2.ID
WHERE T2.TYPE = 'ONLY_ONE_VALUE'
AND (T2.VALUE BETWEEN 'AAAAAAA' AND 'BBBBBBBB'
OR T2.VALUE BETWEEN 'CCCCCCC' AND 'DDDDDDD')


Thanks in advance.

Re: Inconsistent execution plans [message #589610 is a reply to message #589608] Tue, 09 July 2013 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Inconsistent execution plans [message #589645 is a reply to message #589608] Tue, 09 July 2013 06:46 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2444
Registered: May 2013
Location: World Wide on the Web
Senior Member
Andrey_K wrote on Tue, 09 July 2013 13:22
I have two very similar queries with very different execution plans and don`t know what is the reason for that.

No, there are not at all two SIMILAR queries. Look at the predicate, you have used an extra condition to filter the data. So, the CBO decided what it is supposed to do depending on the conditions you mentioned. If you want to verify then do this for both your sql and see:-
select sql_id, plan_hash_value from v$sql where sql_text like '%<your query>%'


Check the output, you would definitely have different SQL_IDs for sure. I am not advocating that two different SQL_IDs cannot have same execution plan, but in your case it ought to be two different SQL_IDs with different execution plan.

Also, I would suggest you to go through the document to understand how optimizer works:-
http://docs.oracle.com/cd/B10500_01/server.920/a96533/optimops.htm

[Updated on: Tue, 09 July 2013 06:48]

Report message to a moderator

Re: Inconsistent execution plans [message #589646 is a reply to message #589645] Tue, 09 July 2013 06:51 Go to previous message
cookiemonster
Messages: 11073
Registered: September 2008
Location: Rainy Manchester
Senior Member
You do realise that those left outer joins are acting like inner joins?
The where clauses are excluding all t1 rows that don't have a match in t2.
Previous Topic: PlSql Procedure Parameter
Next Topic: Inner and outer loop in cursor
Goto Forum:
  


Current Time: Wed Oct 22 18:36:58 CDT 2014

Total time taken to generate the page: 0.05993 seconds