I was trying to find a workaround for a bug in 11.2.0.2
SELECT * FROM
(SELECT 2 B FROM DUAL WHERE DUMMY = 'Y'),
(SELECT 3 C FROM DUAL WHERE DUMMY LIKE '%')
WHERE C = B(+);
B C
---------- ----------
2 3
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER| | 1 | 4 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
As dummy is not Y, B could not be 2.
Ok, I tried :
alter session set "_optimizer_random_plan"=1;
SELECT * FROM
(SELECT 2 B FROM DUAL WHERE DUMMY = 'Y'),
(SELECT 3 C FROM DUAL WHERE DUMMY LIKE '%')
WHERE C = B(+);
B C
---------- ----------
3
Execution Plan
----------------------------------------------------------
Plan hash value: 837538736
-------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1146 | 5730 | 27G|
| 1 | MERGE JOIN OUTER | | 603K| 2946K| 27G|
|* 2 | TABLE ACCESS FULL | DUAL | 392K| 767K| 136K|
| 3 | VIEW | | 2 | 6 | 69180 |
|* 4 | FILTER | | | | |
|* 5 | TABLE ACCESS FULL| DUAL | 123K| 240K| 69180 |
-------------------------------------------------------------
Cool, I got correct results! the fact that the cost jumped from 4 to 27 Billions is just a minor annoyance I suppose 😈
I also tried
alter session set "_optimizer_random_plan"=0; -- default
alter session set "_complex_view_merging"=false;
SELECT * FROM
(SELECT 2 B FROM DUAL WHERE DUMMY = 'Y'),
(SELECT 3 C FROM DUAL WHERE DUMMY LIKE '%')
WHERE C = B(+);
B C
---------- ----------
3
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 5 | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 3 | | |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
The cost is now 5 and instead of 4 and the results are correct
The first thing I did is opening a SR, now I am impatiently waiting for Oracle Support guidance…
By default, the original query generates an interesting predicate in the execution plan:
3 – filter((CASE WHEN ROWID IS NOT NULL THEN 2 ELSE NULL END =3 AND “DUMMY”=’Y’))
You can always use the ultimate cop out – /*+ no_query_transformation */
Nice, thank you!
Actually I am looking for a system wide solution as I am in the process of migrating a db with 3000 views to 11g, maybe I will wait for 11.2.0.3 which is announced for October for AIX
Pingback: 11.2.0.2 Bug – Outer Joins and Literals « Random Neurons
I was going to tell you that the Oracle support advice will be an upgrade to 11.2.0.3 where that bug seems to be solved (with the same plan as your very first one). A bit of patience, we’re in October already.
Nicolas.
Indeed Nicolas :
Metalink: Solution offered Apply patch 11.2.0.3 when it is released on your platform
Me: I cannot base my business planning on a patchset which will be released in future
I do not trust Oracle Support on their schedule, time will say 🙂
Metalink: apply patch 10269193
NICE!!! thank you Oracle Support 🙂
Pingback: State of Data #72 « Dr Data's Blog
next time