View subqueries containing unionall cannot expand table join Simulation

Ainemo has written some articles about subqueries that cannot be expanded. The articles mainly involve the following: 1. The correlated columns do not have the notnull constraint, use notin in the subquery (oracle10g cannot be expanded as a subquery, and oracle11g can be expanded as a new hashjoin algorithm), as shown in the following SQLselecta. * fromta01awherea. obj

Ainemo has written some articles about subqueries that cannot be expanded. The articles mainly involve the following: 1. The correlated columns do not have the not null constraint, use not in to search for subqueries (oracle 10 Gb cannot be expanded as subqueries, and oracle 11g can be expanded as new hash join algorithms). The following SQL> select. * from ta01 a where. obj

In the past, ainemo wrote some articles about subqueries that cannot be expanded. The articles mainly involve the following:
1. The correlated columns do not have the not null constraint and use not in to search for them in subqueries (oracle 10 Gb cannot be expanded as subqueries, and oracle 11 GB has a new hash join algorithm .)

SQL> select a. * from ta01 a where a. object_id not in (select B. object_id from ta02 B)
The object_id columns of tables Ta01 and ta02 do not have the not null constraint.

2. predicate filtering of correlated columns in a subquery that contains the primary table (oracle 10 Gb cannot be expanded as subquery, and oracle 11 GB can be expanded as subquery ).

SQL> select a.* from ta01 a where a.object_id in （select b.object_id from ta02 b where a.object_type=’TABLE’）；

3. subqueries include the following or queries (oracle 10 Gb cannot be expanded as subqueries, and oracle 11 GB can be expanded)

SQL> select a.* from ta01 a where a.object_id in (select b.object_id from ta02 b
where object_type='TABLE') or a.object_id in (select c.object_id from ta02 c wh
ere c.object_type='INDEX');

4. The subquery contains like associations between the primary table and the subtable, such as the following SQL statements (oracle 10 GB and 11 GB cannot be expanded as subqueries)

SQL> select a.* from ta01 a where a.object_id in (select b.object_id from ta02 b
where a.data_object_id like b.data_object_id);

http://www.eygle.com/archives/2013/04/2013_oracle_dtcc_dbsnake.html

In this article, eygle mentions a common case where subqueries cannot be expanded. It uses the author's case for a simple simulation to verify the case.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create table ta01 as select * from dba_objects;

Table created.

SQL> create table ta02 as select * from dba_objects;

Table created.

SQL> create table tb01 as select * from dba_objects;

Table created.

SQL> create table tb02 as select * from dba_objects;

Table created.

SQL> create view ta_view as
2 select object_id,data_object_id,object_type from ta01 union all
3 select object_id,data_object_id,object_type from ta02;

View created.

SQL> create view tb_view as
2 select object_id,data_object_id,object_type from tb01 union all
3 select object_id,data_object_id,object_type from tb02;

View created.

SQL> select a.* from ta_view a where a.object_id in (select b.object_id from tb_
view b where b.object_type='TABLE');

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------

Plan hash value: 3623909176

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3738K| 131M| 617 (1)| 00:00:08 |

|* 1 | FILTER | | | | | |

| 2 | VIEW | TA_VIEW | 88401 | 3194K| 308 (1)| 00:00:04 |

| 3 | UNION-ALL | | | | | |

| 4 | TABLE ACCESS FULL| TA01 | 42509 | 1535K| 154 (1)| 00:00:02 |

| 5 | TABLE ACCESS FULL| TA02 | 45892 | 1658K| 154 (1)| 00:00:02 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
| 6 | VIEW | TB_VIEW | 42 | 1008 | 309 (1)| 00:00:04 |

| 7 | UNION-ALL | | | | | |

|* 8 | TABLE ACCESS FULL| TB01 | 17 | 408 | 154 (1)| 00:00:02 |

|* 9 | TABLE ACCESS FULL| TB02 | 25 | 600 | 154 (1)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT /*+ */ 0 FROM ( (SELECT /*+ */
"OBJECT_ID" "OBJECT_ID","DATA_OBJECT_ID" "DATA_OBJECT_ID","OBJECT_
TYPE"

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
"OBJECT_TYPE" FROM SYS."TB01" "TB01" WHERE "OBJECT_ID"=:B1 AND
"OBJECT_TYPE"='TABLE') UNION ALL (SELECT /*+ */ "OBJECT_ID"
"OBJECT_ID","DATA_OBJECT_ID" "DATA_OBJECT_ID","OBJECT_TYPE"
"OBJECT_TYPE" FROM SYS."TB02" "TB02" WHERE "OBJECT_ID"=:B2 AND
"OBJECT_TYPE"='TABLE')) "B"))
8 - filter("OBJECT_ID"=:B1 AND "OBJECT_TYPE"='TABLE')
9 - filter("OBJECT_ID"=:B1 AND "OBJECT_TYPE"='TABLE')

Note
-----
- dynamic sampling used for this statement

33 rows selected.

Statistics
----------------------------------------------------------
146 recursive calls
0 db block gets
136371691 consistent gets
0 physical reads
0 redo size
83259 bytes sent via SQL*Net to client
2868 bytes received via SQL*Net from client
218 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3247 rows processed

Because the tables in the primary query and subquery are both associated views of the two tables, the subquery in the parentheses after the in clause is not a set of constants, therefore, oracle does not expand the subquery as a table connection.

We can rewrite the statement to the following SQL statement, but it seems that the result set is different here because the table we selected is not 1 to 1, which is not within our consideration.
SQL> SELECT .*
2 FROM ta_view,
3 (SELECT object_id
4 FROM tb_view
5 WHERE object_type = 'table') B
6 WHERE a. object_id = B. object_id;

6494 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 48097912

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3328K| 158M| 640 (5)| 00:00:08 |

|* 1 | HASH JOIN | | 3328K| 158M| 640 (5)| 00:00:08 |

| 2 | VIEW | TB_VIEW | 3306 | 42978 | 309 (1)| 00:00:04 |

| 3 | UNION-ALL | | | | | |

|* 4 | TABLE ACCESS FULL| TB01 | 1756 | 24584 | 154 (1)| 00:00:02 |

|* 5 | TABLE ACCESS FULL| TB02 | 1550 | 21700 | 154 (1)| 00:00:02 |

| 6 | VIEW | TA_VIEW | 100K| 3637K| 308 (1)| 00:00:04 |

| 7 | UNION-ALL | | | | | |

| 8 | TABLE ACCESS FULL| TA01 | 50333 | 786K| 154 (1)| 00:00:02 |

| 9 | TABLE ACCESS FULL| TA02 | 50334 | 786K| 154 (1)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."OBJECT_ID"="OBJECT_ID")
4 - filter("OBJECT_TYPE"='TABLE')
5 - filter("OBJECT_TYPE"='TABLE')

Statistics
----------------------------------------------------------
1083 recursive calls
0 db block gets
3412 consistent gets
2782 physical reads
196 redo size
139022 bytes sent via SQL*Net to client
5244 bytes received via SQL*Net from client
434 SQL*Net roundtrips to/from client
31 sorts (memory)
0 sorts (disk)
6494 rows processed

Of course, if the business is also one-to-many, then this rewrite is not in line with the business logic, so whether it cannot be rewritten is actually not like this, we only need to add a distinct in the constructed table to remove the duplicate values of Table B. Isn't it like semi-join.
SQL> SELECT .*
2 FROM ta_view,
3 (SELECT distinct object_id
4 FROM tb_view
5 WHERE object_type = 'table') B
6 WHERE a. object_id = B. object_id;

3247 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1607119383

--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 3328K| 158M| 642 (5)| 00:00:08
|
|* 1 | HASH JOIN | | 3328K| 158M| 642 (5)| 00:00:08
|
| 2 | VIEW | | 3306 | 42978 | 310 (2)| 00:00:04
|
| 3 | HASH UNIQUE | | 3306 | 42978 | 310 (2)| 00:00:04
|
| 4 | VIEW | TB_VIEW | 3306 | 42978 | 309 (1)| 00:00:04
|
| 5 | UNION-ALL | | | | |
|
|* 6 | TABLE ACCESS FULL| TB01 | 1756 | 24584 | 154 (1)| 00:00:02
|
|* 7 | TABLE ACCESS FULL| TB02 | 1550 | 21700 | 154 (1)| 00:00:02
|
| 8 | VIEW | TA_VIEW | 100K| 3637K| 308 (1)| 00:00:04
|
| 9 | UNION-ALL | | | | |
|
| 10 | TABLE ACCESS FULL | TA01 | 50333 | 786K| 154 (1)| 00:00:02
|
| 11 | TABLE ACCESS FULL | TA02 | 50334 | 786K| 154 (1)| 00:00:02
|
--------------------------------------------------------------------------------
--

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
6 - filter("OBJECT_TYPE"='TABLE')
7 - filter("OBJECT_TYPE"='TABLE')

Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
3007 consistent gets
0 physical reads
0 redo size
83259 bytes sent via SQL*Net to client
2868 bytes received via SQL*Net from client
218 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3247 rows processed

About our common select. * from a, B where. id = B. id and select. * from a where. id in (select B. id from B) the two queries have different business logic if Table B does not have duplicate IDs, if table B already exists, the subquery is a semi-join method of hash join, which is applicable to table B. id, so if B. multiple duplicate IDs may cause inconsistent query results.

This does not mean that veiw containing union all will cause the subquery to fail to expand into a table connection. The preceding view of a single union all will not affect the expansion of the subquery into a table connection.
SQL> select a. * from ta01 a where a. object_id in (select B. object_id from tb_vie
W B where B. object_type = 'table ');

1623 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1785931674

--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 1 | 190 | 464 (2)| 00:00:06
|
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 190 | 464 (2)| 00:00:06
|
| 2 | VIEW | VW_NSO_1 | 4229 | 54977 | 309 (1)| 00:00:04
|
| 3 | VIEW | TB_VIEW | 4229 | 99K| 309 (1)| 00:00:04
|
| 4 | UNION-ALL | | | | |
|
|* 5 | TABLE ACCESS FULL| TB01 | 1692 | 40608 | 154 (1)| 00:00:02
|
|* 6 | TABLE ACCESS FULL| TB02 | 2537 | 60888 | 154 (1)| 00:00:02
|
| 7 | TABLE ACCESS FULL | TA01 | 42509 | 7347K| 154 (1)| 00:00:02
|
--------------------------------------------------------------------------------
--

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."OBJECT_ID"="$nso_col_1")
5 - filter("OBJECT_TYPE"='TABLE')
6 - filter("OBJECT_TYPE"='TABLE')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
53 recursive calls
0 db block gets
3166 consistent gets
0 physical reads
0 redo size
85130 bytes sent via SQL*Net to client
1680 bytes received via SQL*Net from client
110 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1623 rows processed

If we switch to oracle 11.2.0.1, let's see if the subquery that contains two views of union all can be expanded.
SQL> select * from v $ version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production

SQL> select a.* from ta_view a where a.object_id in (select b.object_id from tb_view b where b.object_type='TABLE');

7629 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1656093151

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6779K| 323M| 1206 (4)| 00:00:15 |
|* 1 | HASH JOIN | | 6779K| 323M| 1206 (4)| 00:00:15 |
| 2 | VIEW | VW_NSO_1 | 4191 | 54483 | 586 (1)| 00:00:08 |
| 3 | HASH UNIQUE | | 4191 | 54483 | 586 (1)| 00:00:08 |
| 4 | VIEW | TB_VIEW | 4191 | 54483 | 585 (1)| 00:00:08 |
| 5 | UNION-ALL | | | | | |
|* 6 | TABLE ACCESS FULL| TB01 | 2036 | 48864 | 293 (1)| 00:00:04 |
|* 7 | TABLE ACCESS FULL| TB02 | 2155 | 51720 | 292 (1)| 00:00:04 |
| 8 | VIEW | TA_VIEW | 161K| 5845K| 585 (1)| 00:00:08 |
| 9 | UNION-ALL | | | | | |
| 10 | TABLE ACCESS FULL | TA01 | 82194 | 2969K| 292 (1)| 00:00:04 |
| 11 | TABLE ACCESS FULL | TA02 | 79581 | 2875K| 292 (1)| 00:00:04 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."OBJECT_ID"="OBJECT_ID")
6 - filter("OBJECT_TYPE"='TABLE')
7 - filter("OBJECT_TYPE"='TABLE')

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
63 recursive calls
0 db block gets
5917 consistent gets
3072 physical reads
0 redo size
200965 bytes sent via SQL*Net to client
6108 bytes received via SQL*Net from client
510 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7629 rows processed

Here, oracle chooses to expand the subquery as a table connection, and the optimizer is indeed making many changes. These changes need to be verified through the document test cases and actual cases, it can be said that the performance optimization SQL tuning is not rigorous due to the oracle version.

Recently registered a friend introduced a forum http://www.bi168.cn/forum.php, before also registered a lot of forums such as itpub, otn, ask maclean, etc., are not too much to pay attention to the Forum, I remember maclean liu said that there are only a limited number of cases that can be met every day, but there are indeed a lot of cases that can be met by peers on the network. If you can solve the problems encountered by others, then this is your own.

Original article address: view subqueries containing union all cannot expand table join simulation. Thanks to the original author for sharing.