January 28, 2015 at 11:01Oracle join elimination
The optimizer in Oracle can use various ways to transform queries to improve their performance. One such way is join elimination . The official documentation of the Oracle Database SQL Tuning Guide says quite a bit about this method, unlike others.
I invite readers to cat to talk about this method in more detail.
Content:
This method of query transformation first appeared in Oracle 10.2, but in a rather limited form - it only supported inner join. In versions 11.1 and 11.2, join elimination capabilities have been significantly expanded.
In the documentation, join elimination is defined as: Removing redundant tables from a query. A table is considered superfluous if its columns are used only in the join condition, and such a join is guaranteed not to filter data or add new rows.
At first glance, this may seem strange - why would someone write such a meaningless request? But this can happen if we use the generated request or refer to the views.
Let's look at a small example (scripts were run on Oracle 11.2).
Now let's try a simple query and look at its plan:
Despite the fact that we only query the column from the child table , Oracle, however, does an honest inner join and makes a call to the parent table in vain .
It turns out that the optimizer does not understand that in this query, joining these two tables does not lead to any filtering or row propagation. So, you need to help him understand this.
We bind these tables using the foreign key from child to parent and look at how the query plan changes:
As you can see from the query plan, this turned out to be enough.
For Oracle to be able to remove extra tables from the query connected via inner join, it is necessary that there exists a foreign key - primary key (or unique constraint) relationship between them.
In order for Oracle to remove unnecessary tables from the query in the case of outer join, it was enough on the column of the external table participating in the join to have a primary key or unique constraint.
And try to execute the following query:
As you can see from the query plan, in this case, Oracle also guessed that the parent_3 table is redundant and can be deleted.
The number of tables that can be removed from a query is not limited. Join elimination is convenient to use if there is a child table, several parent tables, and the result of their join is set as a view.
Let's create a view that will unite all our tables and try to use it in the query:
As you can see from the plan, Oracle did an excellent job with such a request too.
In order for such transformations to be possible: between tables there should be a foreign key - primary key relationship, as in the case of inner join.
First, consider the semi join example:
And now an example of anti join:
As you can see, Oracle also learned to work with these types of queries.
Much less often, but there are queries with a join of the same table. Fortunately, join elimination extends to them, but with a small condition - it is necessary that a column with a primary key or unique constraint be used in the join condition.
Such a request is also successfully transformed:
There is another interesting feature of join elimination - it continues to work even when the restrictions (foreign key and primary key) are disabled (disable), but marked as trust (rely).
To get started, just try to disable the restrictions and look at the query plan:
It is expected that join elimination stopped working. Now let's try to specify rely disable for both restrictions:
As you can see, join elimination is working again.
In fact, rely is for a slightly different query transformation . In such cases, it is required that the query_rewrite_integrity parameter be set to “trusted” instead of the standard “enforced”, but, in our case, it does not affect anything and everything works fine even with the value “enforced”.
Unfortunately, rely disable constraints only cause join elimination with inner join. It is also worth noting that despite the fact that we can specify rely disable primary key or rely disable foreign key for representations - unfortunately, this will not work for join elimination.
Along with such a wonderful way of transforming the request, the hidden parameter _optimizer_join_elimination_enabled was added, which is enabled by default (true) and is responsible for using this transformation.
If you get tired of it, you can always turn it off:
Added after xtender comment .
Also, to control this transformation, you can apply the hints of the optimizer.
To enable the transformation, use the ELIMINATE_JOIN hint:
To turn off the transformation, use the NO_ELIMINATE_JOIN hint:
In the comments below, xtender provided a link to his interesting example, which shows that join elimination can degrade the query execution plan. And also gave some clarification in further comments.
There is another transformation option - removing the same connections from the request:
This transformation also works just fine with subqueries that turn into subquery unnesting :
But, this transformation option has some differences.
1) It is not necessary for him to have a foreign key - primary key (or unique constraint) relationship:
2) It is not affected by disabling the _optimizer_join_elimination_enabled parameter :
But at least the prompts apply:
Summing up a brief result, I want to say that this method of transformation can be really useful in a number of cases. But one must also rely on it wisely. If something changes within your view and Oracle can no longer be guaranteed to determine that the connection to such a view does not filter or multiply strings, you will receive an unexpected loss of query execution speed.
I invite readers to cat to talk about this method in more detail.
Content:
- Inner join transformation
- Transformation outer join
- Transformation semi join and anti join
- Self join transformation
- Rely disable and join elimination
- _Optimizer_join_elimination_enabled parameter
- Tips ELIMINATE_JOIN and NO_ELIMINATE_JOIN
- When join elimination is bad
- Transformation of identical compounds
- Total
This method of query transformation first appeared in Oracle 10.2, but in a rather limited form - it only supported inner join. In versions 11.1 and 11.2, join elimination capabilities have been significantly expanded.
In the documentation, join elimination is defined as: Removing redundant tables from a query. A table is considered superfluous if its columns are used only in the join condition, and such a join is guaranteed not to filter data or add new rows.
At first glance, this may seem strange - why would someone write such a meaningless request? But this can happen if we use the generated request or refer to the views.
Inner join transformation
Let's look at a small example (scripts were run on Oracle 11.2).
First, create some tables, one parent and one child (master-detail):
create table parent (
id number not null,
description varchar2(20) not null,
constraint parent_pk primary key (id)
);
insert into parent values (1, 'первый');
insert into parent values (2, 'второй');
commit;
create table child (
id number not null,
parent_id number,
description varchar2(20) not null
);
insert into child values (1, 1, 'первый');
insert into child values (2, 1, 'второй');
insert into child values (3, 2, 'третий');
insert into child values (4, 2, 'четвертый');
commit;
Now let's try a simple query and look at its plan:
explain plan for
select c.id
from child c
join parent p on c.parent_id = p.id;
select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 36 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 36 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| CHILD | 4 | 24 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PARENT_PK | 1 | 3 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
3 - access("C"."PARENT_ID"="P"."ID")
Despite the fact that we only query the column from the child table , Oracle, however, does an honest inner join and makes a call to the parent table in vain .
It turns out that the optimizer does not understand that in this query, joining these two tables does not lead to any filtering or row propagation. So, you need to help him understand this.
We bind these tables using the foreign key from child to parent and look at how the query plan changes:
alter table child
add constraint child_parent_fk foreign key (parent_id) references parent(id);
explain plan for
select c.id
from child c
join parent p on c.parent_id = p.id;
select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 104 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CHILD | 4 | 104 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
1 - filter("C"."PARENT_ID" IS NOT NULL)
As you can see from the query plan, this turned out to be enough.
For Oracle to be able to remove extra tables from the query connected via inner join, it is necessary that there exists a foreign key - primary key (or unique constraint) relationship between them.
Transformation outer join
In order for Oracle to remove unnecessary tables from the query in the case of outer join, it was enough on the column of the external table participating in the join to have a primary key or unique constraint.
Add some more parent tables
create table parent2 (
id number not null,
description varchar2(20) not null,
constraint parent2_pk primary key (id)
);
insert into parent2 values (3, 'третий');
insert into parent2 values (4, 'четвертый');
commit;
create table parent3 (
id number not null,
description varchar2(20) not null,
constraint parent3_pk primary key (id)
);
insert into parent3 values (5, 'пятый');
insert into parent3 values (6, 'шестой');
commit;
alter table child add (parent2_id number, parent3_id number);
alter table child add constraint child_parent2_fk foreign key (parent2_id) references parent2(id);
merge into child c
using (
select 1 id, 3 parent2_id, null parent3_id from dual union all
select 2 id, 4 parent2_id, 5 from dual union all
select 3 id, 3 parent2_id, 6 from dual union all
select 4 id, 4 parent2_id, null from dual
) s on (c.id = s.id)
when matched then update set c.parent2_id = s.parent2_id, c.parent3_id = s.parent3_id;
commit;
And try to execute the following query:
explain plan for
select c.id, c.description
from child c
left join parent3 p on c.parent3_id = p.id;
select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 100 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| CHILD | 4 | 100 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
As you can see from the query plan, in this case, Oracle also guessed that the parent_3 table is redundant and can be deleted.
The number of tables that can be removed from a query is not limited. Join elimination is convenient to use if there is a child table, several parent tables, and the result of their join is set as a view.
Let's create a view that will unite all our tables and try to use it in the query:
create or replace view child_parents_v
as
select c.id, c.parent_id, c.parent2_id, c.parent3_id, c.description, p1.description p1_desc, p2.description p2_desc, p3.description p3_desc
from child c
join parent p1 on c.parent_id = p1.id
join parent2 p2 on c.parent2_id = p2.id
left join parent3 p3 on c.parent3_id = p3.id;
explain plan for
select id
from child_parents_v;
select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 156 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CHILD | 4 | 156 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
1 - filter("C"."PARENT2_ID" IS NOT NULL AND "C"."PARENT_ID" IS NOT NULL)
As you can see from the plan, Oracle did an excellent job with such a request too.
Transformation semi join and anti join
In order for such transformations to be possible: between tables there should be a foreign key - primary key relationship, as in the case of inner join.
First, consider the semi join example:
explain plan for
select * from child c
where exists
(select * from parent2 p where c.parent2_id = p.id);
select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 256 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CHILD | 4 | 256 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
1 - filter("C"."PARENT2_ID" IS NOT NULL)
And now an example of anti join:
explain plan for
select * from child c
where c.parent_id not in (select p.id from parent p);
select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 308 | 5 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI SNA | | 4 | 308 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | CHILD | 4 | 256 | 3 (0)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| PARENT_PK | 2 | 26 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
1 - access("C"."PARENT_ID"="P"."ID")
As you can see, Oracle also learned to work with these types of queries.
Self join transformation
Much less often, but there are queries with a join of the same table. Fortunately, join elimination extends to them, but with a small condition - it is necessary that a column with a primary key or unique constraint be used in the join condition.
create or replace view child_child_v
as
select c.id, c.description c_desc, c2.description c2_desc
from child c
join child c2 on c.id = c2.id;
alter table child add primary key(id);
explain plan for
select id, c2_desc
from child_child_v;
select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 100 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| CHILD | 4 | 100 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Such a request is also successfully transformed:
explain plan for
select c.id, c.description
from child c
where
c.parent3_id is null and
c.id in (select c2.id from child c2 where c2.id > 1);
select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| CHILD | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_C0013028957 | 3 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
1 - filter("PARENT3_ID" IS NULL)
2 - access("C2"."ID">1)
Rely disable and join elimination
There is another interesting feature of join elimination - it continues to work even when the restrictions (foreign key and primary key) are disabled (disable), but marked as trust (rely).
To get started, just try to disable the restrictions and look at the query plan:
alter table child modify constraint child_parent_fk disable;
alter table parent modify constraint parent_pk disable;
explain plan for
select c.id, c.description
from child c
join parent p on c.parent_id = p.id;
select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 204 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 204 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| PARENT | 2 | 26 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CHILD | 4 | 152 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
1 - access("C"."PARENT_ID"="P"."ID")
It is expected that join elimination stopped working. Now let's try to specify rely disable for both restrictions:
alter table child modify constraint child_parent_fk rely disable;
alter table parent modify constraint parent_pk rely disable;
explain plan for
select c.id, c.description
from child c
join parent p on c.parent_id = p.id;
select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 152 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CHILD | 4 | 152 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
1 - filter("C"."PARENT_ID" IS NOT NULL)
As you can see, join elimination is working again.
In fact, rely is for a slightly different query transformation . In such cases, it is required that the query_rewrite_integrity parameter be set to “trusted” instead of the standard “enforced”, but, in our case, it does not affect anything and everything works fine even with the value “enforced”.
Unfortunately, rely disable constraints only cause join elimination with inner join. It is also worth noting that despite the fact that we can specify rely disable primary key or rely disable foreign key for representations - unfortunately, this will not work for join elimination.
_Optimizer_join_elimination_enabled parameter
Along with such a wonderful way of transforming the request, the hidden parameter _optimizer_join_elimination_enabled was added, which is enabled by default (true) and is responsible for using this transformation.
If you get tired of it, you can always turn it off:
alter session set "_optimizer_join_elimination_enabled" = false;
explain plan for
select c.id, c.description
from child c
join parent p on c.parent_id = p.id;
select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 204 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 204 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| PARENT | 2 | 26 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CHILD | 4 | 152 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
1 - access("C"."PARENT_ID"="P"."ID")
Tips ELIMINATE_JOIN and NO_ELIMINATE_JOIN
Added after xtender comment .
Also, to control this transformation, you can apply the hints of the optimizer.
To enable the transformation, use the ELIMINATE_JOIN hint:
alter session set "_optimizer_join_elimination_enabled" = false;
explain plan for
select /*+ ELIMINATE_JOIN(p) */ c.id, c.description
from child c
join parent p on c.parent_id = p.id;
select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 84 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CHILD | 4 | 84 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
1 - filter("C"."PARENT_ID" IS NOT NULL)
To turn off the transformation, use the NO_ELIMINATE_JOIN hint:
alter session set "_optimizer_join_elimination_enabled" = true;
explain plan for
select /*+ NO_ELIMINATE_JOIN(p) */ c.id, c.description
from child c
join parent p on c.parent_id = p.id;
select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 96 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 96 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| CHILD | 4 | 84 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PARENT_PK | 1 | 3 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
3 - access("C"."PARENT_ID"="P"."ID")
When join elimination is bad
In the comments below, xtender provided a link to his interesting example, which shows that join elimination can degrade the query execution plan. And also gave some clarification in further comments.
Transformation of identical compounds
There is another transformation option - removing the same connections from the request:
select c.id
from child c
join parent p on p.id = c.parent_id
join parent p2 on p2.id = c.parent_id
join parent p3 on p3.id = c.parent_id
where
p.description = 'первый' and
p2.description = 'первый' and
p3.description = 'первый'
/
select * from table(dbms_xplan.display_cursor(null, null, 'outline'))
/
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN | | 2 | 102 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| PARENT | 1 | 25 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CHILD | 4 | 104 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
1 - access("P3"."ID"="C"."PARENT_ID")
2 - filter("P3"."DESCRIPTION"='первый')
Outline Data
-------------
...
ELIMINATE_JOIN(@"SEL$EE94F965" "P"@"SEL$1")
ELIMINATE_JOIN(@"SEL$EE94F965" "P2"@"SEL$2")
...
This transformation also works just fine with subqueries that turn into subquery unnesting :
select c.id
from child c
where
parent_id in (select /*+ qb_name(query_1) */ id from parent where description = 'первый') and
parent_id in (select /*+ qb_name(query_2) */id from parent where description = 'первый') and
parent_id in (select /*+ qb_name(query_3) */id from parent where description = 'первый')
/
select * from table(dbms_xplan.display_cursor(null, null, 'outline'))
/
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN | | 2 | 102 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| PARENT | 1 | 25 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CHILD | 4 | 104 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
1 - access("PARENT_ID"="ID")
2 - filter("DESCRIPTION"='первый')
Outline Data
-------------
...
ELIMINATE_JOIN(@"SEL$45781D08" "PARENT"@"QUERY_3")
ELIMINATE_JOIN(@"SEL$45781D08" "PARENT"@"QUERY_2")
...
UNNEST(@"QUERY_3")
UNNEST(@"QUERY_2")
UNNEST(@"QUERY_1")
...
But, this transformation option has some differences.
1) It is not necessary for him to have a foreign key - primary key (or unique constraint) relationship:
alter table child drop constraint child_parent_fk
/
select c.id
from child c
join parent p on p.id = c.parent_id
join parent p2 on p2.id = c.parent_id
join parent p3 on p3.id = c.parent_id
where
p.description = 'первый' and
p2.description = 'первый' and
p3.description = 'первый'
/
select * from table(dbms_xplan.display_cursor(null, null, 'LAST OUTLINE'))
/
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN | | 2 | 102 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| PARENT | 1 | 25 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CHILD | 4 | 104 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
1 - access("P3"."ID"="C"."PARENT_ID")
2 - filter("P3"."DESCRIPTION"='первый')
Outline Data
-------------
...
ELIMINATE_JOIN(@"SEL$EE94F965" "P"@"SEL$1")
ELIMINATE_JOIN(@"SEL$EE94F965" "P2"@"SEL$2")
...
2) It is not affected by disabling the _optimizer_join_elimination_enabled parameter :
alter session set "_optimizer_join_elimination_enabled" = false
/
select c.id
from child c
join parent p on p.id = c.parent_id
join parent p2 on p2.id = c.parent_id
join parent p3 on p3.id = c.parent_id
where
p.description = 'первый' and
p2.description = 'первый' and
p3.description = 'первый'
/
select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE'))
/
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN | | 2 | 102 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| PARENT | 1 | 25 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CHILD | 4 | 104 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
1 - access("P3"."ID"="C"."PARENT_ID")
2 - filter("P3"."DESCRIPTION"='первый')
Outline Data
-------------
...
ELIMINATE_JOIN(@"SEL$EE94F965" "P"@"SEL$1")
ELIMINATE_JOIN(@"SEL$EE94F965" "P2"@"SEL$2")
...
But at least the prompts apply:
select /*+ no_eliminate_join(p) no_eliminate_join(p2) no_eliminate_join(p3) */ c.id
from child c
join parent p on p.id = c.parent_id
join parent p2 on p2.id = c.parent_id
join parent p3 on p3.id = c.parent_id
where
p.description = 'первый' and
p2.description = 'первый' and
p3.description = 'первый'
/
select * from table(dbms_xplan.display_cursor())
/
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | NESTED LOOPS | | 1 | 101 | 8 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 101 | 8 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 76 | 8 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 2 | 102 | 6 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | PARENT | 1 | 25 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | CHILD | 4 | 104 | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| PARENT | 1 | 25 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PARENT_PK | 1 | | 0 (0)| |
|* 9 | INDEX UNIQUE SCAN | PARENT_PK | 1 | | 0 (0)| |
|* 10 | TABLE ACCESS BY INDEX ROWID | PARENT | 1 | 25 | 0 (0)| |
--------------------------------------------------------------------------------------------
4 - access("P"."ID"="C"."PARENT_ID")
5 - filter("P"."DESCRIPTION"='первый')
7 - filter("P2"."DESCRIPTION"='первый')
8 - access("P2"."ID"="C"."PARENT_ID")
9 - access("P3"."ID"="C"."PARENT_ID")
10 - filter("P3"."DESCRIPTION"='первый')
Total
Summing up a brief result, I want to say that this method of transformation can be really useful in a number of cases. But one must also rely on it wisely. If something changes within your view and Oracle can no longer be guaranteed to determine that the connection to such a view does not filter or multiply strings, you will receive an unexpected loss of query execution speed.
And finally, the script to delete all created objects
drop view child_parents_v;
drop view child_child_v;
drop table child;
drop table parent;
drop table parent2;
drop table parent3;