Today I stumbled about some weird 11gR2 behavior (running on AIX).
Please see script for details (posted as follow up).
When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is
faster when using
outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).
Any explanation for that funny behavior?
To get things straight, i tried the same statement with a standard like (but not fulfilling the same condition).
This time performance was as expected (inner join outperforming outer join).
Thanks in advance for any input.
Edited by: stueckl on Jul 30, 2012 2:22 PM - adding some requested information
Oracle version information
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
-----
Some information about the tables involved:
- both have actual statistics (less then a week old)
- the BundlePart table contains about 19 thsd entries (with a four-column PK)
- the Part table has about 840 thsd entries (with the Teile_Id as PK)
- each entry of BundlePart has a foreign key reference (Part_Id) to the Part table
- the column involved in regexp_like has an alternate key (though it never would be used here, I guess).
Explain Plans ... I hope it doesn't matter that these are "old fashioned" plans ... preferred SQuirreL output over SQL-Developer due to easier export (and more information)
left-regexp
# Operation Options Object Name Mode Cost Bytes Cardinality
0 SELECT STATEMENT ALL_ROWS 2157 25 1
1 SORT AGGREGATE 25 1
2 FILTER
3 HASH JOIN OUTER 2157 387125 15485
4 INDEX FAST FULL SCAN ZCBSPART_BUPA_4X ANALYZED 10 92910 15485
5 TABLE ACCESS FULL ZCBRPART_PART ANALYZED 2142 15896711 836669
inner-regexp
0 SELECT STATEMENT ALL_ROWS 2165 25 1
1 SORT AGGREGATE 25 1
2 HASH JOIN 2165 387125 15485
3 INDEX FAST FULL SCAN ZCBSPART_BUPA_4X ANALYZED 10 92910 15485
4 TABLE ACCESS FULL ZCBRPART_PART ANALYZED 2154 794827 41833
As far as I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...
The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".
I hope sample data are not needed as there would be needed a lot ...
PS: this is the second time I came across the "plan worse but execution time better" phenomenon ... :-O