Oracle 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 Oracle Database SQL Tuning Guide documentation says quite a bit about this method, unlike the others.
I invite readers under the 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 supported only the inner join. In version 11.1 and 11.2, join elimination has been greatly enhanced.
In the documentation, join elimination is defined as: Removing unnecessary tables from the request. A table is considered redundant if its columns are used only in the connection condition, and such a connection is guaranteed not to filter data and add new rows.
At first glance, this may seem strange - why would someone write such a meaningless query? But this can happen if we use the generated query or access views.
Let's look at a small example (the scripts were run on Oracle 11.2).
')
Now we will try to execute a simple query and look at its plan:
Despite the fact that we request a column only from the child table, Oracle nevertheless performs an honest inner join and in vain makes a call to the parent table.
It turns out that the optimizer does not understand that in this query the join of these two tables does not lead to any filtering or multiplication of rows. So you need to help him understand it.
Let's link these tables with the help of the foreign key from the child to the parent and see how the query plan changes:
Apparently from the request plan - it appeared enough.
In order for Oracle to be able to remove extra tables from a query connected via an inner join, it is necessary for the foreign key - primary key (or unique constraint) relationship to exist between them.
In order for Oracle to remove unnecessary tables from a query in the case of an outer join - it suffices to have a primary key (primary key) or unique constraint on the column of the outer table involved in the join.
And try the following query:
As can be seen 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 unlimited. Join elimination is convenient to use if there is a child table, several parent tables and the result of their connection is exposed in the form of a view.
Create a view that combines all our tables and try to use it in the query:
As can be seen from the plan, Oracle did an excellent job with such a request too.
In order to have the possibility of such transformations: there must be a foreign key - primary key relationship between the tables, as in the case of inner join.
First, consider an example semi join:
And now an example of anti join:
As you can see, Oracle also learned how to work with these types of queries.
Much less often, but there are queries with the connection of the same table. Fortunately, join elimination extends to them, but with a small condition - it is necessary that the connection condition uses a column with a primary key (primary key) or a unique constraint (unique constraint).
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 trustworthy (rely).
To begin, just try to disable the restrictions and look at the query plan:
It is expected that join elimination has stopped working. And now we will try to specify rely disable for both constraints:
As you can see, join elimination earned again.
Actually, rely is intended 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 with the value “enforced”.
Unfortunately, the rely disable restrictions cause join elimination only with an 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 views, unfortunately, this will not work for join elimination.
Together with such a wonderful way of transforming the query, the hidden parameter _optimizer_join_elimination_enabled was added, which is enabled by default (true) and is responsible for using this transformation.
If she bothers you, you can always turn it off:
Added after xtender comment .
You can also use optimizer hints to control this transformation.
To enable the transformation, use the ELIMINATE_JOIN hint:
In order to turn the transformation off, use the NO_ELIMINATE_JOIN hint:
In the comments below, xtender gave a link to his interesting example, which shows that join elimination can worsen the query execution plan. And also gave some explanations in the further comments.
There is another transformation option - removing identical connections from a query:
This transformation works just as well with subqueries that turn into subquery unnesting :
But, this transformation option has some differences.
1) It is not necessary for it to have a foreign key relationship — the primary key (or unique constraint):
2) It is not affected by disabling the _optimizer_join_elimination_enabled parameter:
But at least there are tips:
To summarize, I would like to say that this method of transformation can be really useful in some cases. But rely on it must also be wisely. If something changes inside your view and Oracle can no longer guarantee that the connection to this view does not filter or multiply the rows, you will get an unexpected loss in query execution speed.
I invite readers under the 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
- Parameter _optimizer_join_elimination_enabled
- ELIMINATE_JOIN and NO_ELIMINATE_JOIN Tips
- When join elimination is bad
- Transformation of the same compounds
- Total
This method of query transformation first appeared in Oracle 10.2, but in a rather limited form — it supported only the inner join. In version 11.1 and 11.2, join elimination has been greatly enhanced.
In the documentation, join elimination is defined as: Removing unnecessary tables from the request. A table is considered redundant if its columns are used only in the connection condition, and such a connection is guaranteed not to filter data and add new rows.
At first glance, this may seem strange - why would someone write such a meaningless query? But this can happen if we use the generated query or access views.
Inner join transformation
Let's look at a small example (the scripts were run on Oracle 11.2).
')
To begin with, we will create several 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 we will try to execute 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 request a column only from the child table, Oracle nevertheless performs an honest inner join and in vain makes a call to the parent table.
It turns out that the optimizer does not understand that in this query the join of these two tables does not lead to any filtering or multiplication of rows. So you need to help him understand it.
Let's link these tables with the help of the foreign key from the child to the parent and see 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) Apparently from the request plan - it appeared enough.
In order for Oracle to be able to remove extra tables from a query connected via an inner join, it is necessary for the foreign key - primary key (or unique constraint) relationship to exist between them.
Transformation outer join
In order for Oracle to remove unnecessary tables from a query in the case of an outer join - it suffices to have a primary key (primary key) or unique constraint on the column of the outer table involved in the join.
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 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 can be seen 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 unlimited. Join elimination is convenient to use if there is a child table, several parent tables and the result of their connection is exposed in the form of a view.
Create a view that combines 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 can be seen from the plan, Oracle did an excellent job with such a request too.
Transformation semi join and anti join
In order to have the possibility of such transformations: there must be a foreign key - primary key relationship between the tables, as in the case of inner join.
First, consider an example semi join:
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 how to work with these types of queries.
Self join transformation
Much less often, but there are queries with the connection of the same table. Fortunately, join elimination extends to them, but with a small condition - it is necessary that the connection condition uses a column with a primary key (primary key) or a unique constraint (unique constraint).
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 trustworthy (rely).
To begin, 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 has stopped working. And now we will try to specify rely disable for both constraints:
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 earned again.
Actually, rely is intended 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 with the value “enforced”.
Unfortunately, the rely disable restrictions cause join elimination only with an 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 views, unfortunately, this will not work for join elimination.
Parameter _optimizer_join_elimination_enabled
Together with such a wonderful way of transforming the query, the hidden parameter _optimizer_join_elimination_enabled was added, which is enabled by default (true) and is responsible for using this transformation.
If she bothers you, 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") ELIMINATE_JOIN and NO_ELIMINATE_JOIN Tips
Added after xtender comment .
You can also use optimizer hints to control this transformation.
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) In order to turn the transformation off, 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 gave a link to his interesting example, which shows that join elimination can worsen the query execution plan. And also gave some explanations in the further comments.
Transformation of the same compounds
There is another transformation option - removing identical connections from a query:
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 works just as well 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 it to have a foreign key relationship — the primary key (or unique constraint):
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 there are tips:
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
To summarize, I would like to say that this method of transformation can be really useful in some cases. But rely on it must also be wisely. If something changes inside your view and Oracle can no longer guarantee that the connection to this view does not filter or multiply the rows, you will get an unexpected loss in query execution speed.
And finally, a 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; Source: https://habr.com/ru/post/248817/
All Articles