How to use queries as a condition for SQL in Oracle

When solving a customer performance problem, come across an interesting SQL statement.

First create a test environment:

Sql> CREATE TABLE T as SELECT rownum ID, a.* from Dba_objects A, dba_queues B;

Table created.

sql> ALTER TABLE T ADD CONSTRAINT pk_t PRIMARY KEY (ID);

Table altered.

Sql> CREATE INDEX ind_t_type on T (object_type);

Index created.

Sql> CREATE TABLE T_type (TYPE VARCHAR2 () PRIMARY KEY, supertype number);

Table created.

Sql> INSERT into T_type SELECT object_type, MOD (rownum, 3)

2 from (SELECT DISTINCT object_type from T);

Created rows.

Sql> CREATE INDEX ind_type on T_type (supertype);

Index created.

Sql> EXEC dbms_stats. Gather_table_stats (USER, ' T ')

Pl/sql procedure successfully completed.

Sql> EXEC dbms_stats. Gather_table_stats (USER, ' T_type ')

Pl/sql procedure successfully completed.

Sql> SET TIMING on

sql> SET Autot TRACE

Sql> SELECT *

2 from T T1, T T2

3 WHERE t1.id = 500

4 and T2.id!= 500

5 and (SELECT supertype from t_type WHERE TYPE = T1. object_type)

6 = (SELECT supertype from t_type WHERE TYPE = T2. object_type)

7 and T1. Object_type in (SELECT TYPE from t_type WHERE supertype = 2);

1167560 rows selected.

elapsed:00:00:38.32

Execution Plan

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

Plan Hash value:2153988938

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

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

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

| 0 |              SELECT STATEMENT |  |  2054k|  411m| 5006 (1) | 00:01:11 |

|* 1 |              FILTER |       |       |            |          | |

|   2 |              NESTED LOOPS |  |  2054k|  411m| 5005 (1) | 00:01:11 |

|    3 |              NESTED LOOPS |     |   1 |     111 | 4 (0) | 00:00:01 |

|     4 | TABLE ACCESS by INDEX rowid|     T |    1 |     99 | 3 (0) | 00:00:01 |

|* 5 | INDEX UNIQUE SCAN |     pk_t |       1 |     | 2 (0) | 00:00:01 |

|* 6 | TABLE ACCESS by INDEX rowid|    T_type |   14 |     168 | 1 (0) | 00:00:01 |

|* 7 | INDEX UNIQUE SCAN |     sys_c0074670 |       1 |     | 0 (0) | 00:00:01 |

|* 8 | TABLE ACCESS Full |  T |  2054k|  193m| 5001 (1) | 00:01:11 |