Filter Analysis in subqueries

At oracle8i, there are many restrictions on the expansion of subqueries into Table connections. Therefore, we often hear that writing in and exist affects SQL Performance. Many articles have mentioned the following arguments: in operations give priority to subqueries, and then drive external queries. exists gives priority to external queries and then drives subqueries. This conclusion is true.

In oracle 8i, there are many restrictions on the expansion of subqueries into Table connections, so we often hear that writing in and exist affects SQL Performance, we can see that many articles mentioned that the in operation takes precedence over subqueries, and then drives external queries. exists takes precedence over external queries and then drives subqueries, this conclusion is true.

In oracle 8i, there are many restrictions on the expansion of subqueries into Table connections, so we often hear that writing in and exist affects SQL Performance, we can see that many articles mentioned that the in operation takes precedence over subqueries, and then drives external queries. exists takes precedence over external queries and then drives subqueries,

Is this conclusion true? Let's take a look at the table join feature if we close the subquery expansion, and see if it is as described above:
SQL> create table xiaoyu03 as select * from dba_objects;
SQL> create table xiaoyu04 as select * from dba_objects where rownum <10;
SQL> explain plan for select/* + optimizer_features_enable ('8. 1.7 ') */* from xiaoyu04 a where. object_id in (select/* + no_unnest */object_id from xiaoyu03 B where B. object_type = 'table ');

Explained.

SQL> select * from table (dbms_xplan.display );

PLAN_TABLE_OUTPUT
Certificate --------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2861815236

---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | select statement | 16 | 3312 | 1 |
| * 1 | FILTER |
| 1 | table access full | XIAOYU04 | 16 | 3312 | 1 |
| * 3 | table access full | XIAOYU03 | 1 | 11 | 24 |
---------------------------------------------------------------

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

1-filter (EXISTS (SELECT/* + NO_UNNEST */0 FROM "XIAOYU03" "B"
WHERE "OBJECT_ID" =: B1 AND "B". "OBJECT_TYPE" = 'table '))
3-filter ("OBJECT_ID" =: B1 AND "B". "OBJECT_TYPE" = 'table ')

Note
-----
-Cpu costing is off (consider enabling it)

Although the subquery is disabled as a table connection, the optimizer returns to the 8I version. Here, the table connection method is changed to filter, then, the External table is used as the driving table, and the sub-query table is used as the driving table for association. This is unreasonable for us to infer that in must first go sub-query as the driving table:
1. Expand the subquery as a table connection. At this time, the subquery is associated with two result sets. The optimizer selects a few result sets as the driver table, the in method cannot guarantee that the optimizer must first select the subquery as the driver table.
2. If the subquery does not show the table connection, the optimizer selects the filter association method. The optimizer selects the table in the primary query as the driving table and uses the filter to associate the result set of the subquery, the in method still does not guarantee that the optimizer must first go to the sub-table at this time.

Some may disagree with argument 2: The optimizer made a query conversion and changed in to the exists method. This seems to meet the driver principle of the optimizer, A small result set is used as the driver table. xiaoyu04 is relatively small here, so it is best to use xiaoyu04 as the driver table, And the in writing optimizer must first go to the sub-Table xiaoyu03, then go to the master table xiaoyu04. After the query is converted to exists, the optimizer can go to xiaoyu04 first and then xiaoyu03. This seems very reasonable. What is the truth?

Here we will change the query relationship. xiaoyu03 is a large table in the primary query, xiaoyu04 is a small table in the subquery, And the optimizer returns to the version of 11g, in addition, we also create an efficient index for object_id of xiaoyu03. Let's take a look at the Association Sequence under no_unnest hint:
SQL> create index ind_objid_xiaoyu03 on xiaoyu03 (object_id );

Index created.
SQL> explain plan for select * from xiaoyu03 a where a. object_id in (select/* + no_unnest */B. object_id from xiaoyu04 B where B. object_type = 'table ');

Explained.

SQL> select * from table (dbms_xplan.display );

PLAN_TABLE_OUTPUT
Certificate --------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2805971548

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------------------------------------
| 0 | select statement | 1 | 88 | 14764 (1) | 00:02:58 |
| * 1 | FILTER |
| 2 | table access full | XIAOYU03 | 9811 | 843K | 43 (0) | 00:00:01 |
| * 3 | table access full | XIAOYU04 | 1 | 10 | 3 (0) | 00:00:01 |
-------------------------------------------------------------------------------

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

1-filter (EXISTS (SELECT/* + NO_UNNEST */0 FROM "XIAOYU04" "B"
WHERE "B". "OBJECT_ID" =: B1 AND "B". "OBJECT_TYPE" = 'table '))
3-filter ("B". "OBJECT_ID" =: B1 AND "B". "OBJECT_TYPE" = 'table ')

17 rows selected.
Or use the table in the primary query as the driving table for filter Association.

Similarly, let's see if exists is the same. After the subquery is closed as the table connection, the optimizer returns to the 11g version. Can the SQL statement of exists use the subquery of the small result set as the driver table, the External table of the large result set is used as the driving table:
SQL> explain plan for select * from xiaoyu03 a where exists (select/* + no_unnest */1 from xiaoyu04 B where B. object_type = 'table' and. object_id = B. object_id );

Explained.

SQL> select * from table (dbms_xplan.display );

PLAN_TABLE_OUTPUT
Certificate --------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2805971548

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------------------------------------
| 0 | select statement | 1 | 88 | 14764 (1) | 00:02:58 |
| * 1 | FILTER |
| 2 | table access full | XIAOYU03 | 9811 | 843K | 43 (0) | 00:00:01 |
| * 3 | table access full | XIAOYU04 | 1 | 10 | 3 (0) | 00:00:01 |
-------------------------------------------------------------------------------

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

1-filter (EXISTS (SELECT/* + NO_UNNEST */0 FROM "XIAOYU04" "B"
WHERE "B". "OBJECT_ID" =: B1 AND "B". "OBJECT_TYPE" = 'table '))
3-filter ("B". "OBJECT_ID" =: B1 AND "B". "OBJECT_TYPE" = 'table ')

17 rows selected.

It seems that after the subquery is disabled as a table connection, exists can only scan the External table as a drive table and then drive the table of the subquery.

Here, we need to overturn our previous understanding: the optimizer selects the driver table here to consider selecting a small driver result set as the driver table, A small result set is satisfied by querying and converting in and exists to form a driver table.

So why does the optimizer select an External table as the driver table after the sub-query is disabled as the table connection feature in the in or exists mode, in fact, the root cause of this phenomenon is the correlation filter.

How filters work:
Table join methods are the same for filter and nested loop, filter only maintains a hash table to record some columns that meet the needs of the row of data associated with the primary table and the sub-table.

It is really hard to explain this filter. Let's give a simple example: for example, the query is select * from xiaoyu03 a where exists (select/* + no_unnest */1 from xiaoyu04 B where B. object_type = 'table' and. object_id = B. object_id );

Oracle uses the fiter association method to retrieve a piece of data from Table xiaoyu03, and then. object_id = B. object_id and B. object_type = 'table' Association. If yes, record. return the value of object_id to the hash table and. the data column corresponding to object_id. Then, all the data in Table xiaoyu03 is retrieved repeatedly. If the next. when object_id has been recorded in the hash table, the optimizer no longer needs to associate with the xiaoyu04 table, but directly returns. the row of Data columns corresponding to object_id. In this filter Association mode, you can only select the primary table as the driver table, so we can see that after the sub-query is closed and converted to the table connection, the filter execution plan corresponding to in and exists is used as the driving table based on the external table.

Therefore, filter is more efficient than the nested loop, saving the I/O consumption of the driven table for duplicate correlated columns.

This article from: http://www.dbaxiaoyu.com, original address: http://www.dbaxiaoyu.com/archives/2360, thanks to the original author to share.