Here’s a thought that falls somewhere between philosophical and pragmatic. It came up while I was playing around with a problem from the Oracle database forum that was asking about options for rewriting a query with a certain type of predicate. This note isn’t really about that question but the OP supplied a convenient script to demonstrate their requirement and I’ve hi-jacked most of the code for my own purposes so that I can ask the question:
Should the presence of an intermediate view name generated by the optimizer in the course of cost-based query transformation cause two plans, which are otherwise identical and do exactly the same thing, to have different plan hash values ?
To demonstrate the issue let’s start with a simple script to create some data and generate an execution plan.
rem
rem Script: or_expand_plans.sql
rem Author: Jonathan Lewis
rem Dated Oct 2019
rem
rem Last tested
rem 19.3.0.0
rem 12.2.0.1
rem
rem Notes:
rem Data creation copied from ODC
rem https://community.oracle.com/thread/4297365
rem
create table t as
select 1 as id, to_date('2019-10-11', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
union all
select 2 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
union all
select 3 as id, to_date('2019-10-15', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
union all
select 4 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
;
create table t_others as
select 1 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'Blue' as color, 'Zone 7' as zoneid from dual
union all
select 2 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'Red' as color, 'Zone 7' as zoneid from dual
union all
select 3 as id, to_date('2019-10-16', 'YYYY-MM-DD') as lastupdated, 'White' as color, 'Zone 7' as zoneid from dual
union all
select 4 as id, to_date('2019-10-17', 'YYYY-MM-DD') as lastupdated, 'Green' as color, 'Zone 7' as zoneid from dual
;
create table t_further_info as
select 1 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'A' as typeinfo from dual
union all
select 2 as id, to_date('2019-10-14', 'YYYY-MM-DD') as lastupdated, 'C' as typeinfo from dual
union all
select 3 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'D' as typeinfo from dual
union all
select 4 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'E' as typeinfo from dual
;
prompt ====================
prompt "Traditional" syntax
prompt ====================
explain plan for
select
/*+ or_expand */
*
from
t,
t_others pt,
t_further_info fi
/*
where (
t.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
or pt.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
or fi.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
)
*/
where to_date('2019-10-21', 'YYYY-MM-DD') <= any(t.lastupdated, pt.lastupdated, fi.lastupdated)
and pt.id = t.id
and fi.id = t.id
;
select * from table(dbms_xplan.display(null,null,'outline'));
You’ll see that I have a simple three-table join with the nasty little detail that I have a “non-join” predicates that may require Oracle to check across all three tables before it can decide whether or not a row should be discarded. I’ve shown two variants on a theme – they both have exactly the same effect but the ANY() presentation is just a little bit neater and more compact.
Essentially Oracle can use one of two strategies for this type of query/predicate; the first is to join all three tables and wait until the final join rowsource appears and then apply the check, or it can split the query into a union all of three separate queries where each query drives off a different table selecting only the rows from that table that match “its” part of the predicate.
In the latter case the second and third branches of the union all have to be modified to ensure that they discard any rows already returned by preceding parts of the union all; this can mean lots of new predicates appearing that use the lnnvl() function. (Of course there are variants between these two extremes, but the general principle is unchanged.)
The presence of the (incomplete) /*+ or_expand */ hint in my query is there to tell the optimizer that it should attempt to transform the query into the three-part union all. This, by the way, is a 12c feature, though older versions of Oracle could get similar effects in some cases from the /*+ use_concat */ hint. Here’s the plan, with outline, I got from 12.2.0.1:
Plan hash value: 3181357500
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 270 | 17 (6)| 00:00:01 |
| 1 | VIEW | VW_ORE_67EF6547 | 3 | 270 | 17 (6)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN | | 1 | 55 | 6 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN| | 4 | 144 | 4 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T_FURTHER_INFO | 1 | 13 | 2 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 3 | 69 | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | T_OTHERS | 3 | 69 | 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | T | 3 | 57 | 2 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 2 | 182 | 11 (10)| 00:00:01 |
| 10 | VIEW | VW_JF_SET$FB5125FC | 2 | 156 | 9 (12)| 00:00:01 |
| 11 | UNION-ALL | | | | | |
|* 12 | HASH JOIN | | 1 | 42 | 4 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL| T_OTHERS | 1 | 23 | 2 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL| T | 3 | 57 | 2 (0)| 00:00:01 |
|* 15 | HASH JOIN | | 1 | 42 | 4 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL| T | 1 | 19 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL| T_OTHERS | 4 | 92 | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | T_FURTHER_INFO | 4 | 52 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$7C4216F7" "PT"@"SEL$1")
LEADING(@"SEL$7C4216F7" "T"@"SEL$1" "PT"@"SEL$1")
FULL(@"SEL$7C4216F7" "PT"@"SEL$1")
FULL(@"SEL$7C4216F7" "T"@"SEL$1")
USE_HASH(@"SEL$A4A33BE0" "T"@"SEL$1")
LEADING(@"SEL$A4A33BE0" "PT"@"SEL$1" "T"@"SEL$1")
FULL(@"SEL$A4A33BE0" "T"@"SEL$1")
FULL(@"SEL$A4A33BE0" "PT"@"SEL$1")
USE_HASH(@"SET$49E1C21B_3" "T"@"SEL$1")
USE_MERGE_CARTESIAN(@"SET$49E1C21B_3" "PT"@"SEL$1")
LEADING(@"SET$49E1C21B_3" "FI"@"SEL$1" "PT"@"SEL$1" "T"@"SEL$1")
FULL(@"SET$49E1C21B_3" "T"@"SEL$1")
FULL(@"SET$49E1C21B_3" "PT"@"SEL$1")
FULL(@"SET$49E1C21B_3" "FI"@"SEL$1")
USE_HASH(@"SEL$5FCD2D3C" "FI"@"SEL$1")
LEADING(@"SEL$5FCD2D3C" "VW_JF_SET$FB5125FC"@"SEL$81DF0931" "FI"@"SEL$1")
FULL(@"SEL$5FCD2D3C" "FI"@"SEL$1")
NO_ACCESS(@"SEL$5FCD2D3C" "VW_JF_SET$FB5125FC"@"SEL$81DF0931")
NO_ACCESS(@"SEL$67EF6547" "VW_ORE_67EF6547"@"SEL$67EF6547")
OUTLINE(@"SET$49E1C21B_2")
OUTLINE(@"SET$49E1C21B_1")
OUTLINE(@"SEL$1")
FACTORIZE_JOIN(@"SET$49E1C21B"("FI"@"SET$49E1C21B_2" "FI"@"SET$49E1C21B_1"))
OUTLINE(@"SET$0E101D56")
OUTLINE(@"SEL$81DF0931")
OUTLINE(@"SEL$5AB42CD1")
OR_EXPAND(@"SEL$1" (1) (2) (3))
OUTLINE(@"SET$49E1C21B")
OUTLINE_LEAF(@"SEL$67EF6547")
FACTORIZE_JOIN(@"SET$49E1C21B"("FI"@"SET$49E1C21B_2" "FI"@"SET$49E1C21B_1"))
OUTLINE_LEAF(@"SET$0E101D56")
OUTLINE_LEAF(@"SEL$5FCD2D3C")
OUTLINE_LEAF(@"SET$FB5125FC")
OUTLINE_LEAF(@"SEL$A4A33BE0")
OUTLINE_LEAF(@"SEL$7C4216F7")
OUTLINE_LEAF(@"SET$49E1C21B_3")
ALL_ROWS
DB_VERSION('12.2.0.1')
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PT"."ID"="T"."ID" AND "FI"."ID"="T"."ID")
5 - filter("FI"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
7 - filter(LNNVL("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
8 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
9 - access("FI"."ID"="ITEM_1")
12 - access("PT"."ID"="T"."ID")
13 - filter("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
14 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
15 - access("PT"."ID"="T"."ID")
16 - filter("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
This is a wonderful demonstration of how brilliant the optimizer can be. The query has gone through several transformations and two of them have very high visibility. First, you can see the name VW_ORE_67EF6547 at operation 1. This is a view name that Oracle generates to express (cost-based) “OR-Expansion” so the optimizer has clearly obeyed my hint. As a consequence of OR-expansion we can also see several examples of the lnnvl() function appearing in the Predicate Information section of the output.; we can also see the hint re-appearing in the completed form of OR_EXPAND(@”SEL$1″ (1) (2) (3)) in the Outline Data.
However, we don’t have the union all of three pieces that we might have expected; we have a union all of two pieces and the second piece is a hash join between the table t_further_info and a view called VW_JF_SET$FB5125FC. This view is the result of “join factorization”. The optimizer has taken the 2nd and 3rd sections of our union all view and decided that it would be cost-effective to “factor out” a common table, so this:
select from t_others, t, t_further_info ... where t_others.date_predicate ...
union all
select from t, t_others, t_further_info ... where t.date_predicate ...
changes to this:
select from
(
select from t_others, t ... where t_others.date_predicate ...
union all
select from t, t_others ... where t.date_predicate ...
),
t_further_info
where ...
Having said all that, I now have to change the code because the original query was written using “ANSI” style joins – like this:
explain plan for
select
/*+ or_expand */
*
from
t
inner join
t_others pt
on pt.id = t.id
inner join
t_further_info fi
on fi.id = t.id
where
(
t.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
or pt.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
or fi.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
)
;
select * from table(dbms_xplan.display(null,null,'outline'));
In the ANSI example I happen to have used the explicit “OR” list for the date predicates but that’s not really signficant . Here’s the plan produced by this query – and the first thing I’d like you to note is the Plan hash value:
Plan hash value: 3309788271
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 270 | 17 (6)| 00:00:01 |
| 1 | VIEW | VW_ORE_31069B60 | 3 | 270 | 17 (6)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN | | 1 | 55 | 6 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN| | 4 | 144 | 4 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T_FURTHER_INFO | 1 | 13 | 2 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 3 | 69 | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | T_OTHERS | 3 | 69 | 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | T | 3 | 57 | 2 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 2 | 182 | 11 (10)| 00:00:01 |
| 10 | VIEW | VW_JF_SET$997549B1 | 2 | 156 | 9 (12)| 00:00:01 |
| 11 | UNION-ALL | | | | | |
|* 12 | HASH JOIN | | 1 | 42 | 4 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL| T_OTHERS | 1 | 23 | 2 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL| T | 3 | 57 | 2 (0)| 00:00:01 |
|* 15 | HASH JOIN | | 1 | 42 | 4 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL| T | 1 | 19 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL| T_OTHERS | 4 | 92 | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | T_FURTHER_INFO | 4 | 52 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$D12FC97A" "PT"@"SEL$1")
LEADING(@"SEL$D12FC97A" "T"@"SEL$1" "PT"@"SEL$1")
FULL(@"SEL$D12FC97A" "PT"@"SEL$1")
FULL(@"SEL$D12FC97A" "T"@"SEL$1")
USE_HASH(@"SEL$09C9729D" "T"@"SEL$1")
LEADING(@"SEL$09C9729D" "PT"@"SEL$1" "T"@"SEL$1")
FULL(@"SEL$09C9729D" "T"@"SEL$1")
FULL(@"SEL$09C9729D" "PT"@"SEL$1")
USE_HASH(@"SET$E8D85587_3" "T"@"SEL$1")
USE_MERGE_CARTESIAN(@"SET$E8D85587_3" "PT"@"SEL$1")
LEADING(@"SET$E8D85587_3" "FI"@"SEL$2" "PT"@"SEL$1" "T"@"SEL$1")
FULL(@"SET$E8D85587_3" "T"@"SEL$1")
FULL(@"SET$E8D85587_3" "PT"@"SEL$1")
FULL(@"SET$E8D85587_3" "FI"@"SEL$2")
USE_HASH(@"SEL$95B99BAF" "FI"@"SEL$2")
LEADING(@"SEL$95B99BAF" "VW_JF_SET$997549B1"@"SEL$BB7F1ECF" "FI"@"SEL$2")
FULL(@"SEL$95B99BAF" "FI"@"SEL$2")
NO_ACCESS(@"SEL$95B99BAF" "VW_JF_SET$997549B1"@"SEL$BB7F1ECF")
NO_ACCESS(@"SEL$31069B60" "VW_ORE_31069B60"@"SEL$31069B60")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
MERGE(@"SEL$1" >"SEL$2")
OUTLINE(@"SEL$58A6D7F6")
OUTLINE(@"SEL$3")
OUTLINE(@"SET$E8D85587_2")
OUTLINE(@"SET$E8D85587_1")
MERGE(@"SEL$58A6D7F6" >"SEL$3")
OUTLINE(@"SEL$9E43CB6E")
FACTORIZE_JOIN(@"SET$E8D85587"("FI"@"SET$E8D85587_2" "FI"@"SET$E8D85587_1"))
OUTLINE(@"SET$6117B24C")
OUTLINE(@"SEL$BB7F1ECF")
OUTLINE(@"SEL$344003E3")
OR_EXPAND(@"SEL$9E43CB6E" (1) (2) (3))
OUTLINE(@"SET$E8D85587")
OUTLINE_LEAF(@"SEL$31069B60")
FACTORIZE_JOIN(@"SET$E8D85587"("FI"@"SET$E8D85587_2" "FI"@"SET$E8D85587_1"))
OUTLINE_LEAF(@"SET$6117B24C")
OUTLINE_LEAF(@"SEL$95B99BAF")
OUTLINE_LEAF(@"SET$997549B1")
OUTLINE_LEAF(@"SEL$09C9729D")
OUTLINE_LEAF(@"SEL$D12FC97A")
OUTLINE_LEAF(@"SET$E8D85587_3")
ALL_ROWS
DB_VERSION('12.2.0.1')
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FI"."ID"="T"."ID" AND "PT"."ID"="T"."ID")
5 - filter("FI"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
7 - filter(LNNVL("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
8 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
9 - access("FI"."ID"="ITEM_1")
12 - access("PT"."ID"="T"."ID")
13 - filter("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
14 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
15 - access("PT"."ID"="T"."ID")
16 - filter("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Is the plan for the “ANSI” version of the query the same as the plan for the “traditional” version? How carefully have you checked – after simply noting that the two Plan hash values were different.
The plans are the same – in that the mechanics are exactly the same and that ought to be the criterion on which we should judge them. But the hash values are different because of the change from traditional to ANSI syntax. The traditional form of the query starts life with a single query block while the ANSI form is considered to be two query blocks, so the initial ANSI query goes through three stages:
1) select from t1 join t2 join t3
2) select /*+ qb_name(sel$2) */ from (select /* qb_name(sel$1) */ from t1, t2), t3
3) select /*+ qb_name(sel$9E43CB6E) */ from t1, t2, t3
So the query is rapidly transformed to the equivalent traditional syntax but we now have a query block name of SEL$9E43CB6E instead of SEL$1 that the traditional query (in the absence of a /*+ qb_name() */ hint would have had. This is why you see the difference in the two or_expand() hints in the Outline Data section. One reads: OR_EXPAND(@SEL$1 (1) (2) (3)), the other reads OR_EXPAND(@”SEL$9E43CB6E” (1) (2) (3)), and all the subseqent query block name differences follow on from this initial transformation. (Note: the value “sel$9e43cb6e” is derived from the input query block names of sel$1 and sel$2 that the new query block is derived from)
You may decide that this mismatch isn’t really terribly important. If you’ve modified the code to switch to ANSI style joins then you may be prepared to put in a little extra effort to check the plan in detail to see that it hasn’t changed; but it would be a lot nicer if the hash value wasn’t dependent on generated view names. You may recall that at one time Oracle had problems with plans that used materialized CTEs (“with” subqueries) because the plan hash value was affected by object names like sys_temp_0fd9d6791_dfc12da. The same principle ought, I think, to apply here.
If you don’t mind the ANSI/tradiational switch though, you might find that you’re less happy when you upgrade to 19c, because the same effect appears there too, only it’s worse. Not only do “identical” traditional and ANSI plans have different hash values, they don’t match the values from 12c because the generated name for the join factorization views (VW_JF) change in the upgrade. So if you’re depending on SQL Plan Baselines to reproduce 12c plans on 19c when you upgrade you may find cases where you know the stored baseline is giving you the same plan but Oracle thinks it isn’t and refuses to use it.
tl;dr
Plans which are functionally identical can have different plan hash values because the plans were reached through a different series of tranformations. In particular if you rewrite queries from “traditional” Oracle syntax to “ANSI” syntax you will find cases where the plan doesn’t change but the plan hash value does thanks to a change in the names of views generated by some transformations.
More significantly, if you upgrade from 12c to 19c there are case where the names of views generated by transformations may change, which could cause the optimizer to discard some of your carefully constructed SQL Plan Baselines as “not reproducible”.
Footnote
For reference, if I add the hint /*+ opt_param(‘_optimizer_join_factorization’ ‘false’) */ to the “traditional query then I get the following plan which shows more clearly the three branches that the original query has been split into – each section starting with a different choice for the driving table:
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 270 | 19 (6)| 00:00:01 |
| 1 | VIEW | VW_ORE_67EF6547 | 3 | 270 | 19 (6)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN | | 1 | 55 | 7 (15)| 00:00:01 |
|* 4 | HASH JOIN | | 1 | 32 | 4 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T | 1 | 19 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T_FURTHER_INFO | 4 | 52 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T_OTHERS | 4 | 92 | 2 (0)| 00:00:01 |
|* 8 | HASH JOIN | | 1 | 55 | 6 (0)| 00:00:01 |
| 9 | MERGE JOIN CARTESIAN| | 4 | 144 | 4 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | T_OTHERS | 1 | 23 | 2 (0)| 00:00:01 |
| 11 | BUFFER SORT | | 4 | 52 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | T_FURTHER_INFO | 4 | 52 | 2 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | T | 3 | 57 | 2 (0)| 00:00:01 |
|* 14 | HASH JOIN | | 1 | 55 | 6 (0)| 00:00:01 |
| 15 | MERGE JOIN CARTESIAN| | 4 | 144 | 4 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL | T_FURTHER_INFO | 1 | 13 | 2 (0)| 00:00:01 |
| 17 | BUFFER SORT | | 3 | 69 | 2 (0)| 00:00:01 |
|* 18 | TABLE ACCESS FULL | T_OTHERS | 3 | 69 | 2 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | T | 3 | 57 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SET$BB614FD2_3" "T"@"SET$BB614FD2_3")
USE_MERGE_CARTESIAN(@"SET$BB614FD2_3" "PT"@"SET$BB614FD2_3")
LEADING(@"SET$BB614FD2_3" "FI"@"SET$BB614FD2_3" "PT"@"SET$BB614FD2_3"
"T"@"SET$BB614FD2_3")
FULL(@"SET$BB614FD2_3" "T"@"SET$BB614FD2_3")
FULL(@"SET$BB614FD2_3" "PT"@"SET$BB614FD2_3")
FULL(@"SET$BB614FD2_3" "FI"@"SET$BB614FD2_3")
USE_HASH(@"SET$BB614FD2_2" "T"@"SET$BB614FD2_2")
USE_MERGE_CARTESIAN(@"SET$BB614FD2_2" "FI"@"SET$BB614FD2_2")
LEADING(@"SET$BB614FD2_2" "PT"@"SET$BB614FD2_2" "FI"@"SET$BB614FD2_2"
"T"@"SET$BB614FD2_2")
FULL(@"SET$BB614FD2_2" "T"@"SET$BB614FD2_2")
FULL(@"SET$BB614FD2_2" "FI"@"SET$BB614FD2_2")
FULL(@"SET$BB614FD2_2" "PT"@"SET$BB614FD2_2")
USE_HASH(@"SET$BB614FD2_1" "PT"@"SET$BB614FD2_1")
USE_HASH(@"SET$BB614FD2_1" "FI"@"SET$BB614FD2_1")
LEADING(@"SET$BB614FD2_1" "T"@"SET$BB614FD2_1" "FI"@"SET$BB614FD2_1"
"PT"@"SET$BB614FD2_1")
FULL(@"SET$BB614FD2_1" "PT"@"SET$BB614FD2_1")
FULL(@"SET$BB614FD2_1" "FI"@"SET$BB614FD2_1")
FULL(@"SET$BB614FD2_1" "T"@"SET$BB614FD2_1")
NO_ACCESS(@"SEL$49E1C21B" "VW_ORE_67EF6547"@"SEL$67EF6547")
OUTLINE(@"SEL$1")
OR_EXPAND(@"SEL$1" (1) (2) (3))
OUTLINE_LEAF(@"SEL$49E1C21B")
OUTLINE_LEAF(@"SET$BB614FD2")
OUTLINE_LEAF(@"SET$BB614FD2_1")
OUTLINE_LEAF(@"SET$BB614FD2_2")
OUTLINE_LEAF(@"SET$BB614FD2_3")
ALL_ROWS
OPT_PARAM('_optimizer_join_factorization' 'false')
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PT"."ID"="T"."ID")
4 - access("FI"."ID"="T"."ID")
5 - filter("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
8 - access("PT"."ID"="T"."ID" AND "FI"."ID"="T"."ID")
10 - filter("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
13 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
14 - access("PT"."ID"="T"."ID" AND "FI"."ID"="T"."ID")
16 - filter("FI"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
18 - filter(LNNVL("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
19 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
88 rows selected.
Although the “traditional” and “ANSI” plans still show a difference in their Plan hash values when join factorization is blocked, the absence of the join factorization view means that the plan hash values are now consistent between 12c to 19c (the output above came from 19.3.0.0 as you can see in the Outline information).
