One of the key thing addressed in Oracle 12.2 optimizer is the ability
to perform table elimination (aka join elimination) for relationships based on
composite keys.
demo@PDB1>
create table t1 as
2
select rownum as t1_id ,
3
a.*
4 from
all_objects a
5
where rownum <=5;
Table created.
demo@PDB1>
demo@PDB1>
create table t2 as
2
select mod(rownum,5)+1 as t1_id,
3
rownum as t2_id ,
4
a.*
5 from
all_objects a
6
where rownum <=10000;
Table created.
demo@PDB1>
create table t3 as
2
select t1_id, t2_id ,
3
rownum as t3_id ,
4
rpad('*',80,'*') as string_pad
5 from
t2 ;
Table created.
demo@PDB1>
alter table t1 add constraint t1_pk primary key(t1_id);
Table altered.
demo@PDB1>
alter table t2 add constraint t2_pk primary key(t1_id,t2_id);
Table altered.
demo@PDB1>
alter table t3 add constraint t3_pk primary key(t1_id,t2_id,t3_id);
Table altered.
demo@PDB1>
alter table t2 add constraint t2_fk
2
foreign key(t1_id)
3
references t1;
Table altered.
demo@PDB1>
alter table t3 add constraint t3_fk
2
foreign key(t1_id,t2_id)
3
references t2;
Table altered.
demo@PDB1>
We got three tables T1, T2 and T3, where T1 and T2 connected using
single-column primary/foreign keys. Where T2 and T3 connected using composite
primary/foreign keys.
Now we will take a simple query involving three table joins and test it
against 12.1 and 12.2 optimizers.
demo@PDB1>
alter session set optimizer_features_enable='12.1.0.2';
Session
altered.
demo@PDB1>
set autotrace traceonly explain
demo@PDB1>
select count(*)
2 from
t3, t2, t1
3
where t1.t1_id = t2.t1_id
4 and
t2.t1_id = t3.t1_id
5 and
t2.t2_id = t3.t2_id;
Execution Plan
----------------------------------------------------------
Plan hash
value: 402256924
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 14 |
11 (10)| 00:00:01 |
| 1 |
SORT AGGREGATE | |
1 | 14 | | |
| 2 |
NESTED LOOPS | | 10000 | 136K|
11 (10)| 00:00:01 |
| 3 |
INDEX FAST FULL SCAN| T3_PK | 10000 | 70000 | 10
(0)| 00:00:01 |
|* 4 |
INDEX UNIQUE SCAN | T2_PK | 1 |
7 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
4 -
access("T2"."T1_ID"="T3"."T1_ID" AND
"T2"."T2_ID"="T3"."T2_ID")
The need for the table T1 is eliminated, since prior to 12.2 optimizer
can do table elimination for only single column primary/foreign keys.
However running the same query against the 12.2 optimizer produced the
results like this.
demo@PDB1>
alter session set optimizer_features_enable='12.2.0.1';
Session
altered.
demo@PDB1>
select count(*)
2 from
t3, t2, t1
3
where t1.t1_id = t2.t1_id
4 and
t2.t1_id = t3.t1_id
5 and
t2.t2_id = t3.t2_id;
Execution Plan
----------------------------------------------------------
Plan hash
value: 2110918630
-----------------------------------------------------------------------
| Id | Operation | Name | Rows
| Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 10
(0)| 00:00:01 |
| 1 |
SORT AGGREGATE | |
1 | | |
| 2 |
INDEX FAST FULL SCAN| T3_PK | 10000 |
10 (0)| 00:00:01 |
-----------------------------------------------------------------------
demo@PDB1>
This confirms that 12.2 optimizer can do table elimination for
relationships involving composite primary/foreign key columns.
However, one important thing to note is if we change the order of the
tables in from clause like this t1 -> t2 -> t3 then plan changes like this.
demo@PDB1>
set autotrace traceonly explain
demo@PDB1>
select count(*)
2 from
t1, t2, t3
3
where t1.t1_id = t2.t1_id
4 and
t2.t1_id = t3.t1_id
5 and
t2.t2_id = t3.t2_id;
Execution Plan
----------------------------------------------------------
Plan hash
value: 611653035
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 6 |
10 (0)| 00:00:01 |
| 1 |
SORT AGGREGATE | |
1 | 6 | | |
| 2 |
NESTED LOOPS | | 10000 | 60000 | 10
(0)| 00:00:01 |
| 3 |
INDEX FAST FULL SCAN| T3_PK | 10000 | 30000 | 10
(0)| 00:00:01 |
|* 4 |
INDEX UNIQUE SCAN | T1_PK | 1 |
3 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
4 -
access("T1"."T1_ID"="T3"."T1_ID")
demo@PDB1>
The optimizer has eliminate T2 but not T1.
Looking into the 10053 trace it shows this
SQL:*******
UNPARSED QUERY IS *******
SELECT COUNT(*)
"COUNT(*)" FROM "DEMO"."T1"
"T1","DEMO"."T2"
"T2","DEMO"."T3" "T3" WHERE
"T1"."T1_ID"="T2"."T1_ID" AND
"T2"."T1_ID"="T3"."T1_ID" AND "T2"."T2_ID"="T3"."T2_ID"
JE:[V2] Query
block (00007FFD15BD4108) before join elimination:
SQL:*******
UNPARSED QUERY IS *******
SELECT COUNT(*)
"COUNT(*)" FROM "DEMO"."T1"
"T1","DEMO"."T2"
"T2","DEMO"."T3" "T3" WHERE
"T1"."T1_ID"="T2"."T1_ID" AND
"T2"."T1_ID"="T3"."T1_ID" AND
"T2"."T2_ID"="T3"."T2_ID"
JE:[V2]: Try to
eliminate T3 by ref. join elim using NTSID
JE:[V2]: Cannot
eliminate T3 by ref. join elim - no constr. based join pred(s)
JE:[V2]: Try to
eliminate T3 by ref. join elim using NTSID
JE:[V2]: Cannot
eliminate T3 by ref. join elim - no constr. based join pred(s)
JE:[V2]:
Try to eliminate T2 by ref. join elim using PRIMARY(T1_ID, T2_ID) <-
FOREIGN(T1_ID, T2_ID)
JE:[V2]:
Can eliminate T2 by ref. join elim using PRIMARY(T1_ID, T2_ID) <-
FOREIGN(T1_ID, T2_ID)
JE:[V2]
Eliminate table: T2 (T2)
JE:[V2]
Replaced column: T2.T1_ID with column: T3.T1_ID
JE:[V2] Query
block (00007FFD15BD4108) after join elimination:
SQL:*******
UNPARSED QUERY IS *******
SELECT COUNT(*)
"COUNT(*)" FROM "DEMO"."T1"
"T1","DEMO"."T3" "T3" WHERE
"T1"."T1_ID"="T3"."T1_ID"
The above trace confirms that optimizer started with evaluation of join
between T2 and T3 and eliminated the need for T2, and left off with T1 and T3,
finally no relationship between T1 and T3 so no further table elimination is
possible.
However, this behavior is termed as a bug 22228669 at Oracle support.
On the final note. Table elimination on composite keys to work, you
might have to get a bit lucky in the order you list the tables in from clause.
No comments:
Post a Comment