7.Oracle里的常见的执行计划
1.与索有关的执行计划
索引唯一扫描：index unique scan
scott@ORCLPDB01 2023-04-02 22:44:32> create table employee(gender varchar2(1),employee_id number);
Table created.
Elapsed: 00:00:00.05
scott@ORCLPDB01 2023-04-02 22:45:05> insert into employee values('F','99');
1 row created.
Elapsed: 00:00:00.03
scott@ORCLPDB01 2023-04-02 22:46:56> insert into employee values('F','100');
1 row created.
Elapsed: 00:00:00.10
scott@ORCLPDB01 2023-04-02 22:47:02> insert into employee values('M','101');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:09> insert into employee values('M','102');
1 row created.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 22:47:12> insert into employee values('M','103');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:15> insert into employee values('M','104');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:17> insert into employee values('M','105');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:22> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:25> create unique index idx_uni_emp on employee(employee_id);
Index created.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-02 22:50:13> select * from employee where employee_id = 100;
G EMPLOYEE_ID
- -----------
F 100
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:50:40> set autot trace;
scott@ORCLPDB01 2023-04-02 22:50:52> select * from employee where employee_id = 100;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1887894887
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 15 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_UNI_EMP | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
491 bytes sent via SQL*Net to client
397 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed索引范围扫描：index range scan
scott@ORCLPDB01 2023-04-02 22:53:11> select * from employee where employee_id = 100;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3410127368
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEE | 1 | 15 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_UNI_EMP | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
635 bytes sent via SQL*Net to client
412 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed索引全扫描：index full scan
scott@ORCLPDB01 2023-04-02 22:53:13> truncate table employee;
Table truncated.
Elapsed: 00:00:00.04
scott@ORCLPDB01 2023-04-02 22:54:00> begin
2 for i in 1..5000 loop
3 insert into employee values('F',i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
scott@ORCLPDB01 2023-04-02 22:54:42> begin
2 for i in 5001..10000 loop
3 insert into employee values('M',i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
scott@ORCLPDB01 2023-04-02 22:55:45> select gender,count(*) from employee group by gender;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1624656943
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 20000 | 8 (13)| 00:00:01 |
| 1 | HASH GROUP BY | | 10000 | 20000 | 8 (13)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEE | 10000 | 20000 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
684 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
scott@ORCLPDB01 2023-04-02 22:56:20> set autot off;
scott@ORCLPDB01 2023-04-02 22:56:30> select gender,count(*) from employee group by gender;
G COUNT(*)
- ----------
M 5000
F 5000
Elapsed: 00:00:00.00
22:58:37> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE',estimate_percent=>100,cascade=>true,no_invalidate=>false,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.31
scott@ORCLPDB01 2023-04-02 22:58:48> set autot trace
scott@ORCLPDB01 2023-04-02 22:59:02> select employee_id from employee;
10000 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2119105728
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEE | 10000 | 40000 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
689 consistent gets
0 physical reads
0 redo size
179660 bytes sent via SQL*Net to client
7724 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
scott@ORCLPDB01 2023-04-02 22:59:19> select /*+ index(employee idx_uni_emp) */ employee_id from employee;
10000 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2119105728
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEE | 10000 | 40000 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / EMPLOYEE@SEL$1
U - index(employee idx_uni_emp)
Statistics
----------------------------------------------------------
43 recursive calls
0 db block gets
745 consistent gets
0 physical reads
0 redo size
179660 bytes sent via SQL*Net to client
7976 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
10000 rows processed索引快速全扫描：index fast full scan
scott@ORCLPDB01 2023-04-02 23:01:08> alter table employee modify(employee_id not null);
Table altered.
Elapsed: 00:00:00.05
scott@ORCLPDB01 2023-04-02 23:03:53> select employee_id from employee;
10000 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 114952492
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IDX_UNI_EMP | 10000 | 40000 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
709 consistent gets
0 physical reads
0 redo size
179660 bytes sent via SQL*Net to client
7724 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10000 rows processed
scott@ORCLPDB01 2023-04-02 23:04:07> select /*+ index(employee idx_uni_emp) */ employee_id from employee;
10000 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3257444309
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | 20 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | IDX_UNI_EMP | 10000 | 40000 | 20 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
685 consistent gets
0 physical reads
0 redo size
179660 bytes sent via SQL*Net to client
7755 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed索引跳跃式扫描：index skip scan
scott@ORCLPDB01 2023-04-02 23:05:32> create index idx_emp on employee(gender,employee_id);
Index created.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-02 23:06:15> select * from employee where employee_id = 101;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1432429342
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_EMP | 1 | 6 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPLOYEE_ID"=101)
filter("EMPLOYEE_ID"=101)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
13 physical reads
0 redo size
632 bytes sent via SQL*Net to client
412 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed2.与位图索引有关的执行计划
1.如果被索引的列的distinct值较少，那么位图索引与相同列上的B树索引比起来，会显著节省存储空间；
2.如果需要在多个列上创建索引，那么位图索引与同等条件下得B树木索引比起来，往往会显著节省存储空间；
3.位图索引能够快速处理一些包含了各种AND或OR查询条件的SQL，这主要是因为位图索引能够实现快捷的按位运算的缘故；
scott@ORCLPDB01 2023-04-03 16:32:12> r
1 create table customer(
2 customer# number,
3 marital_status varchar2(10),
4 region varchar2(10),
5 gender varchar2(10),
6 income_level varchar2(10)
7* )
Table created.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 16:32:15> insert into customer values(101,'single','east','male','bracket_1');
1 row created.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-03 16:33:09> insert into customer values(102,'married','central','female','bracket_4');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 16:34:08> insert into customer values(103,'married','west','female','bracket_2');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 16:34:28> insert into customer values(104,'divorced','west','male','bracket_4');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 16:34:53> insert into customer values(105,'single','central','female','bracket_2');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 16:35:25> insert into customer values(106,'married','central','female','bracket_3');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 16:35:48> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 16:35:56> select * from customer;
CUSTOMER# MARITAL_ST REGION GENDER INCOME_LEV
---------- ---------- ---------- ---------- ----------
101 single east male bracket_1
102 married central female bracket_4
103 married west female bracket_2
104 divorced west male bracket_4
105 single central female bracket_2
106 married central female bracket_3
6 rows selected.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 16:36:12> create bitmap index idx_b_region on customer(region);
Index created.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 16:36:57> create bitmap index idx_b_maritalstatus on customer(marital_status);
Index created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 16:39:45> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'CUSTOMER',estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.36位图索引单键值：BITMAP INDEX SINGLE VALUE
scott@ORCLPDB01 2023-04-03 16:42:33> select /*+ index(customer idx_b_region) */ customer# from customer where region='east';
CUSTOMER#
----------
101
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 16:44:34> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 33qp58c8zapsk, child number 0
-------------------------------------
select /*+ index(customer idx_b_region) */ customer# from customer
where region='east'
Plan hash value: 1493077514
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER | 2 | 22 | 5 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | IDX_B_REGION | | | | |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / CUSTOMER@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("REGION"='east')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "CUSTOMER#"[NUMBER,22]
2 - "CUSTOMER".ROWID[ROWID,10], "REGION"[VARCHAR2,10]
3 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "REGION"[VARCHAR2,10]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1 / CUSTOMER@SEL$1
- index(customer idx_b_region)
40 rows selected.
Elapsed: 00:00:00.12位图索引范围：BITMAP INDEX RANG SCAN
scott@ORCLPDB01 2023-04-03 16:44:45> select /*+ index(customer idx_b_region) */ customer# from customer where region between 'east' and 'west';
CUSTOMER#
----------
101
103
104
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 16:49:01> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1hp3f8t98u4j1, child number 0
-------------------------------------
select /*+ index(customer idx_b_region) */ customer# from customer
where region between 'east' and 'west'
Plan hash value: 3049997815
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER | 6 | 66 | 12 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
|* 3 | BITMAP INDEX RANGE SCAN | IDX_B_REGION | | | | |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / CUSTOMER@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("REGION">='east' AND "REGION"<='west')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "CUSTOMER#"[NUMBER,22]
2 - "CUSTOMER".ROWID[ROWID,10], "REGION"[VARCHAR2,10]
3 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "REGION"[VARCHAR2,10]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1 / CUSTOMER@SEL$1
- index(customer idx_b_region)
40 rows selected.
Elapsed: 00:00:00.02位图索引快速全扫描：BITMAP INDEX FAST FULL SCAN
scott@ORCLPDB01 2023-04-03 16:49:04> select /*+ index(customer idx_b_region) */ customer# from customer;
CUSTOMER#
----------
102
105
106
101
103
104
6 rows selected.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 16:50:02> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gttahfj7f3bh7, child number 0
-------------------------------------
select /*+ index(customer idx_b_region) */ customer# from customer
Plan hash value: 1913192162
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER | 6 | 24 | 12 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP INDEX FULL SCAN | IDX_B_REGION | | | | |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / CUSTOMER@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "CUSTOMER#"[NUMBER,22]
2 - "CUSTOMER".ROWID[ROWID,10]
3 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1 / CUSTOMER@SEL$1
- index(customer idx_b_region)
34 rows selected.
Elapsed: 00:00:00.03scott@ORCLPDB01 2023-04-03 16:50:04> select count(*) from customer where region = 'east';
COUNT(*)
----------
1
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 16:51:34> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 88pbvtdhbx7sf, child number 0
-------------------------------------
select count(*) from customer where region = 'east'
Plan hash value: 1155146465
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | BITMAP CONVERSION COUNT | | 2 | 14 | 1 (0)| 00:00:01 |
|* 3 | BITMAP INDEX FAST FULL SCAN| IDX_B_REGION | | | | |
----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / CUSTOMER@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("REGION"='east')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2 - COUNT(*)[22]
3 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920]
33 rows selected.
Elapsed: 00:00:00.01位图按位与：BITMAP AND，位图按位或：BITMAP OR
scott@ORCLPDB01 2023-04-03 16:53:33> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cjf8t8xn3040z, child number 0
-------------------------------------
select count(*) from customer where marital_status = 'married' and
region in ('central','west')
Plan hash value: 2352868374
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
| 2 | BITMAP CONVERSION COUNT | | 1 | 15 | 3 (0)| 00:00:01 |
| 3 | BITMAP AND | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | IDX_B_MARITALSTATUS | | | | |
| 5 | BITMAP OR | | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE| IDX_B_REGION | | | | |
|* 7 | BITMAP INDEX SINGLE VALUE| IDX_B_REGION | | | | |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / CUSTOMER@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("MARITAL_STATUS"='married')
6 - access("REGION"='central')
7 - access("REGION"='west')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2 - COUNT(*)[22]
3 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
4 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920]
5 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
6 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920]
7 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920]
44 rows selected.
Elapsed: 00:00:00.01位图按位减：BITMAP MINUS
scott@ORCLPDB01 2023-04-03 16:53:36> select /*+ index(customer idx_b_maritalstatus) index(customer idx_b_region) */ customer# from customer where marital_status = 'married' and region != 'central';
CUSTOMER#
----------
103
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 16:58:37> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4bdw1f4ytc60a, child number 0
-------------------------------------
select /*+ index(customer idx_b_maritalstatus) index(customer
idx_b_region) */ customer# from customer where marital_status =
'married' and region != 'central'
Plan hash value: 3228995600
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER | 1 | 19 | 6 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP MINUS | | | | | |
| 4 | BITMAP MINUS | | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | IDX_B_MARITALSTATUS | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE | IDX_B_REGION | | | | |
|* 7 | BITMAP INDEX SINGLE VALUE | IDX_B_REGION | | | | |
-----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / CUSTOMER@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("MARITAL_STATUS"='married')
6 - access("REGION"='central')
7 - access("REGION" IS NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "CUSTOMER#"[NUMBER,22]
2 - "CUSTOMER".ROWID[ROWID,10]
3 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
4 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
5 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920]
6 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920]
7 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
1 - SEL$1 / CUSTOMER@SEL$1
- index(customer idx_b_maritalstatus)
- index(customer idx_b_region)
52 rows selected.
Elapsed: 00:00:00.03与表相连接的执行计划
排合并连接：SORT JOIN 和MERGE JOIN
scott@ORCLPDB01 2023-04-03 21:35:29> select t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2 = t2.col2;
COL1 COL2 COL3
---------- ----- -----
1 A A2
2 B B2
4 D D2
5 E E2
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 21:36:10> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6sjnpqa2vcth8, child number 0
-------------------------------------
select t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2 = t2.col2
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN | | 3 | 30 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 3 | 15 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
3 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL2"="T2"."COL2")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "T1"."COL2"[VARCHAR2,5],
"T1"."COL1"[NUMBER,22], "T2"."COL3"[VARCHAR2,5]
2 - (rowset=256) "T1"."COL1"[NUMBER,22], "T1"."COL2"[VARCHAR2,5]
3 - (rowset=256) "T2"."COL2"[VARCHAR2,5], "T2"."COL3"[VARCHAR2,5]
35 rows selected.
Elapsed: 00:00:00.29
scott@ORCLPDB01 2023-04-03 21:39:40> @xplan.display_cursor.sql gvqhyfdvq39ja 0
old 5: and sql_id = '&v_xc_sql_id'
new 5: and sql_id = 'gvqhyfdvq39ja'
old 6: and child_number = to_number('&v_xc_child_no')
new 6: and child_number = to_number(' 0')
old 34: from table(dbms_xplan.display_cursor('&v_xc_sql_id',to_number('&v_xc_child_no'),'&v_xc_format')) x
new 34: from table(dbms_xplan.display_cursor('gvqhyfdvq39ja',to_number(' 0'),'typical')) x
old 95: ' - XPlan v&v_xc_version by Adrian Billington (http://www.oracle-developer.net)'
new 95: ' - XPlan v1.3 by Adrian Billington (http://www.oracle-developer.net)'
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gvqhyfdvq39ja, child number 0
-------------------------------------
select /*+ use_merge(t1,t2) */ t1.col1,t1.col2,t2.col3 from t1,t2 where
t1.col2 = t2.col2
Plan hash value: 412793182
----------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | | 6 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | 0 | 5 | MERGE JOIN | | 3 | 30 | 8 (25)| 00:00:01 |
| 2 | 1 | 2 | SORT JOIN | | 3 | 15 | 4 (25)| 00:00:01 |
| 3 | 2 | 1 | TABLE ACCESS FULL| SCOTT.T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 4 | 1 | 4 | SORT JOIN | | 3 | 15 | 4 (25)| 00:00:01 |
| 5 | 4 | 3 | TABLE ACCESS FULL| SCOTT.T2 | 3 | 15 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."COL2"="T2"."COL2")
filter("T1"."COL2"="T2"."COL2")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
3 - SEL$1 / T1@SEL$1
U - use_merge(t1,t2)
About
------
- XPlan v1.3 by Adrian Billington (http://www.oracle-developer.net)
31 rows selected.
Elapsed: 00:00:00.08嵌套循环连接对应的关键字：NESTED LOOPS
scott@ORCLPDB01 2023-04-03 21:40:03> select /*+ use_nl(t1,t2) */ t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2 = t2.col2;
COL1 COL2 COL3
---------- ----- -----
1 A A2
2 B B2
4 D D2
5 E E2
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 21:42:10> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9yujz38z258xq, child number 0
-------------------------------------
select /*+ use_nl(t1,t2) */ t1.col1,t1.col2,t2.col3 from t1,t2 where
t1.col2 = t2.col2
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | NESTED LOOPS | | 3 | 30 | 9 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 5 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
3 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."COL2"="T2"."COL2")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T1"."COL1"[NUMBER,22], "T1"."COL2"[VARCHAR2,5],
"T2"."COL3"[VARCHAR2,5]
2 - "T1"."COL1"[NUMBER,22], "T1"."COL2"[VARCHAR2,5]
3 - "T2"."COL3"[VARCHAR2,5]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$1 / T1@SEL$1
U - use_nl(t1,t2)
3 - SEL$1 / T2@SEL$1
- use_nl(t1,t2)
46 rows selected.
Elapsed: 00:00:00.04
scott@ORCLPDB01 2023-04-03 21:42:15> @xplan.display_cursor.sql 9yujz38z258xq 0
old 5: and sql_id = '&v_xc_sql_id'
new 5: and sql_id = '9yujz38z258xq'
old 6: and child_number = to_number('&v_xc_child_no')
new 6: and child_number = to_number(' 0')
old 34: from table(dbms_xplan.display_cursor('&v_xc_sql_id',to_number('&v_xc_child_no'),'&v_xc_format')) x
new 34: from table(dbms_xplan.display_cursor('9yujz38z258xq',to_number(' 0'),'typical')) x
old 95: ' - XPlan v&v_xc_version by Adrian Billington (http://www.oracle-developer.net)'
new 95: ' - XPlan v1.3 by Adrian Billington (http://www.oracle-developer.net)'
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9yujz38z258xq, child number 0
-------------------------------------
select /*+ use_nl(t1,t2) */ t1.col1,t1.col2,t2.col3 from t1,t2 where
t1.col2 = t2.col2
Plan hash value: 1967407726
---------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | | 4 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | 0 | 3 | NESTED LOOPS | | 3 | 30 | 9 (0)| 00:00:01 |
| 2 | 1 | 1 | TABLE ACCESS FULL| SCOTT.T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 3 | 1 | 2 | TABLE ACCESS FULL| SCOTT.T2 | 1 | 5 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."COL2"="T2"."COL2")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$1 / T1@SEL$1
U - use_nl(t1,t2)
About
------
- XPlan v1.3 by Adrian Billington (http://www.oracle-developer.net)
28 rows selected.
Elapsed: 00:00:00.07哈希连接对应的关键字：HASH JOIN
scott@ORCLPDB01 2023-04-03 21:42:31> alter table t1 modify (col2 not null);
Table altered.
Elapsed: 00:00:00.13
scott@ORCLPDB01 2023-04-03 21:44:21> alter table t2 modify (col2 not null);
Table altered.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 21:44:28> select * from t1 where col2 not in (select col2 from t2 where col3 = 'A');
COL1 COL2
---------- -----
2 B
5 E
3 C
1 A
4 D
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 21:45:28> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2yyyvb8h5hwmh, child number 0
-------------------------------------
select * from t1 where col2 not in (select col2 from t2 where col3 =
'A')
Plan hash value: 2706079091
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN ANTI | | 2 | 20 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 5 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("COL2"="COL2")
3 - filter("COL3"='A')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "COL2"[VARCHAR2,5], "T1"."COL1"[NUMBER,22]
2 - (rowset=256) "T1"."COL1"[NUMBER,22], "COL2"[VARCHAR2,5]
3 - (rowset=256) "COL2"[VARCHAR2,5]
36 rows selected.
Elapsed: 00:00:00.02反链接对应的关键字：ANTI，可能是HASH JOIN ANTI，MERGE JOIN ANTI 或者NESTED LOOPS ANTI
scott@ORCLPDB01 2023-04-03 21:45:47> select * from t1 where col2 not in (select /*+ MERGE_AJ */ col2 from t2 where col3 = 'A');
COL1 COL2
---------- -----
1 A
2 B
3 C
4 D
5 E
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 21:47:18> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b960p1x6s8ftu, child number 0
-------------------------------------
select * from t1 where col2 not in (select /*+ MERGE_AJ */ col2 from t2
where col3 = 'A')
Plan hash value: 4048964092
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | MERGE JOIN ANTI | | 2 | 20 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 3 | 15 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 1 | 5 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| T2 | 1 | 5 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / T1@SEL$1
5 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("COL2"="COL2")
filter("COL2"="COL2")
5 - filter("COL3"='A')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "COL2"[VARCHAR2,5], "T1"."COL1"[NUMBER,22]
2 - (#keys=1) "COL2"[VARCHAR2,5], "T1"."COL1"[NUMBER,22]
3 - "T1"."COL1"[NUMBER,22], "COL2"[VARCHAR2,5]
4 - (#keys=1) "COL2"[VARCHAR2,5]
5 - "COL2"[VARCHAR2,5]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - SEL$2
- MERGE_AJ
48 rows selected.
Elapsed: 00:00:00.04
scott@ORCLPDB01 2023-04-03 21:47:21> @xplan.display_cursor.sql b960p1x6s8ftu 0
old 5: and sql_id = '&v_xc_sql_id'
new 5: and sql_id = 'b960p1x6s8ftu'
old 6: and child_number = to_number('&v_xc_child_no')
new 6: and child_number = to_number(' 0')
old 34: from table(dbms_xplan.display_cursor('&v_xc_sql_id',to_number('&v_xc_child_no'),'&v_xc_format')) x
new 34: from table(dbms_xplan.display_cursor('b960p1x6s8ftu',to_number(' 0'),'typical')) x
old 95: ' - XPlan v&v_xc_version by Adrian Billington (http://www.oracle-developer.net)'
new 95: ' - XPlan v1.3 by Adrian Billington (http://www.oracle-developer.net)'
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b960p1x6s8ftu, child number 0
-------------------------------------
select * from t1 where col2 not in (select /*+ MERGE_AJ */ col2 from t2
where col3 = 'A')
Plan hash value: 4048964092
----------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | | 6 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | 0 | 5 | MERGE JOIN ANTI | | 2 | 20 | 8 (25)| 00:00:01 |
| 2 | 1 | 2 | SORT JOIN | | 3 | 15 | 4 (25)| 00:00:01 |
| 3 | 2 | 1 | TABLE ACCESS FULL| SCOTT.T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 4 | 1 | 4 | SORT UNIQUE | | 1 | 5 | 4 (25)| 00:00:01 |
|* 5 | 4 | 3 | TABLE ACCESS FULL| SCOTT.T2 | 1 | 5 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("COL2"="COL2")
filter("COL2"="COL2")
5 - filter("COL3"='A')
About
------
- XPlan v1.3 by Adrian Billington (http://www.oracle-developer.net)
25 rows selected.
Elapsed: 00:00:00.08
scott@ORCLPDB01 2023-04-03 21:49:00> select * from t1 where col2 not in (select /*+ NL_AJ */ col2 from t2 where col3 = 'A');
COL1 COL2
---------- -----
1 A
2 B
3 C
4 D
5 E
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 21:49:03> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 06kyvbg1sd25t, child number 0
-------------------------------------
select * from t1 where col2 not in (select /*+ NL_AJ */ col2 from t2
where col3 = 'A')
Plan hash value: 3519331023
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | NESTED LOOPS ANTI | | 2 | 20 | 9 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 5 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("COL3"='A' AND "COL2"="COL2"))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T1"."COL1"[NUMBER,22], "COL2"[VARCHAR2,5]
2 - "T1"."COL1"[NUMBER,22], "COL2"[VARCHAR2,5]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - SEL$2
- NL_AJ
41 rows selected.
Elapsed: 00:00:00.04半连接的关键字：SEMI，可能是：HASH JOIN SEMI，MERGE JOIN SEMI 或者 NESTED LOOPS SEMI
scott@ORCLPDB01 2023-04-03 21:49:12> insert into t2 values('E','E3');
1 row created.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 21:50:14> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 21:50:19>
scott@ORCLPDB01 2023-04-03 21:50:37>
scott@ORCLPDB01 2023-04-03 21:50:37> select * from t1;
COL1 COL2
---------- -----
1 A
2 B
3 C
4 D
5 E
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 21:50:42> select * from t2;
COL2 COL3
----- -----
A A2
B B2
D D2
E E3
E E2
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 21:50:47> select * from t1 where exists (select * from t2 where t1.col2 = t2.col2 and col3 > 'D2');
COL1 COL2
---------- -----
5 E
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 21:52:00> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dm1f35241gnwr, child number 0
-------------------------------------
select * from t1 where exists (select * from t2 where t1.col2 = t2.col2
and col3 > 'D2')
Plan hash value: 1713220790
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN SEMI | | 1 | 10 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 5 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL2"="T2"."COL2")
3 - filter("COL3">'D2')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "T1"."COL2"[VARCHAR2,5],
"T1"."COL1"[NUMBER,22]
2 - (rowset=256) "T1"."COL1"[NUMBER,22], "T1"."COL2"[VARCHAR2,5]
3 - (rowset=256) "T2"."COL2"[VARCHAR2,5]
37 rows selected.
Elapsed: 00:00:00.03
scott@ORCLPDB01 2023-04-03 21:52:15> select * from t1 where exists (select /*+ MERGE_SJ */ * from t2 where t1.col2 = t2.col2 and col3 > 'D2');
COL1 COL2
---------- -----
5 E
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 21:55:32> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID aayhrqf19xy77, child number 0
-------------------------------------
select * from t1 where exists (select /*+ MERGE_SJ */ * from t2 where
t1.col2 = t2.col2 and col3 > 'D2')
Plan hash value: 1300388549
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | MERGE JOIN SEMI | | 1 | 10 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 3 | 15 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 1 | 5 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| T2 | 1 | 5 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / T1@SEL$1
5 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."COL2"="T2"."COL2")
filter("T1"."COL2"="T2"."COL2")
5 - filter("COL3">'D2')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T1"."COL2"[VARCHAR2,5], "T1"."COL1"[NUMBER,22]
2 - (#keys=1) "T1"."COL2"[VARCHAR2,5], "T1"."COL1"[NUMBER,22]
3 - "T1"."COL1"[NUMBER,22], "T1"."COL2"[VARCHAR2,5]
4 - (#keys=1) "T2"."COL2"[VARCHAR2,5]
5 - "T2"."COL2"[VARCHAR2,5]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - SEL$2
- MERGE_SJ
48 rows selected.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-03 21:55:35> select * from t1 where exists (select /*+ NL_SJ */ * from t2 where t1.col2 = t2.col2 and col3 > 'D2');
COL1 COL2
---------- -----
5 E
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 21:56:57> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID af952j11ay8sp, child number 0
-------------------------------------
select * from t1 where exists (select /*+ NL_SJ */ * from t2 where
t1.col2 = t2.col2 and col3 > 'D2')
Plan hash value: 1856726224
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | NESTED LOOPS SEMI | | 1 | 10 | 9 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 5 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("COL3">'D2' AND "T1"."COL2"="T2"."COL2"))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T1"."COL1"[NUMBER,22], "T1"."COL2"[VARCHAR2,5]
2 - "T1"."COL1"[NUMBER,22], "T1"."COL2"[VARCHAR2,5]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - SEL$2
- NL_SJ
41 rows selected.
Elapsed: 00:00:00.03其他执行计划
scott@ORCLPDB01 2023-04-03 21:58:16> create table emp_temp as select * from emp;
Table created.
Elapsed: 00:00:00.21
scott@ORCLPDB01 2023-04-03 21:58:23>
scott@ORCLPDB01 2023-04-03 21:58:23>
scott@ORCLPDB01 2023-04-03 21:58:24> create index idx_mgr on emp_temp(mgr);
Index created.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-03 21:58:44> create index idx_deptno on emp_temp(deptno);
Index created.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 21:58:58> select /*+ and_equal(emp_temp idx_mgr idx_deptno) */ empno, job from emp_temp where mgr = 7902 and deptno = 20;
EMPNO JOB
---------- ---------
7369 CLERK
Elapsed: 00:00:00.03
scott@ORCLPDB01 2023-04-03 22:00:15> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 97m7s6jjjnfma, child number 0
-------------------------------------
select /*+ and_equal(emp_temp idx_mgr idx_deptno) */ empno, job from
emp_temp where mgr = 7902 and deptno = 20
Plan hash value: 2056553872
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEMP | 1 | 19 | 3 (0)| 00:00:01 |
| 2 | AND-EQUAL | | | | | |
|* 3 | INDEX RANGE SCAN | IDX_MGR | 2 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_DEPTNO | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP_TEMP@SEL$1
3 - SEL$1 / EMP_TEMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("MGR"=7902 AND "DEPTNO"=20))
3 - access("MGR"=7902)
4 - access("DEPTNO"=20)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "JOB"[VARCHAR2,9]
2 - "EMP_TEMP".ROWID[ROWID,10], "MGR"[NUMBER,22], "DEPTNO"[NUMBER,22]
3 - ROWID[ROWID,10], "MGR"[NUMBER,22]
4 - ROWID[ROWID,10], "DEPTNO"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
1 - SEL$1 / EMP_TEMP@SEL$1
- and_equal(emp_temp idx_mgr idx_deptno)
- and_equal(emp_temp idx_mgr idx_deptno)
46 rows selected.
Elapsed: 00:00:00.23index_join
scott@ORCLPDB01 2023-04-03 22:00:21> delete from emp_temp where mgr is null;
1 row deleted.
Elapsed: 00:00:00.03
scott@ORCLPDB01 2023-04-03 22:04:12> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 22:04:16> alter table emp_temp modify(mgr not null);
Table altered.
Elapsed: 00:00:00.12
scott@ORCLPDB01 2023-04-03 22:04:33> alter table emp_temp modify(deptno not null);
Table altered.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 22:04:48> select mgr,deptno from emp_temp;
MGR DEPTNO
---------- ----------
7839 10
7782 10
7902 20
7839 20
7566 20
7788 20
7566 20
7698 30
7698 30
7698 30
7839 30
7698 30
7698 30
13 rows selected.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 22:05:03> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 49yuau068u9dv, child number 0
-------------------------------------
select mgr,deptno from emp_temp
Plan hash value: 1700960094
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | VIEW | index$_join$_001 | 14 | 98 | 2 (0)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
| 3 | INDEX FAST FULL SCAN| IDX_MGR | 14 | 98 | 1 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX_DEPTNO | 14 | 98 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2AEE34FF / EMP_TEMP@SEL$1
2 - SEL$2AEE34FF
3 - SEL$2AEE34FF / indexjoin$_alias$_001@SEL$2AEE34FF
4 - SEL$2AEE34FF / indexjoin$_alias$_002@SEL$2AEE34FF
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID=ROWID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "DEPTNO"[NUMBER,22], "MGR"[NUMBER,22]
2 - (#keys=1; rowset=256) "MGR"[NUMBER,22], "DEPTNO"[NUMBER,22]
3 - ROWID[ROWID,10], "MGR"[NUMBER,22]
4 - ROWID[ROWID,10], "DEPTNO"[NUMBER,22]
37 rows selected.
Elapsed: 00:00:00.21
scott@ORCLPDB01 2023-04-03 22:05:36> select mgr,deptno from emp_temp;
MGR DEPTNO
---------- ----------
7839 10
7782 10
7902 20
7839 20
7566 20
7788 20
7566 20
7698 30
7698 30
7698 30
7839 30
7698 30
7698 30
13 rows selected.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 22:06:01> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 49yuau068u9dv, child number 0
-------------------------------------
select mgr,deptno from emp_temp
Plan hash value: 1700960094
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | VIEW | index$_join$_001 | 14 | 98 | 2 (0)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
| 3 | INDEX FAST FULL SCAN| IDX_MGR | 14 | 98 | 1 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX_DEPTNO | 14 | 98 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2AEE34FF / EMP_TEMP@SEL$1
2 - SEL$2AEE34FF
3 - SEL$2AEE34FF / indexjoin$_alias$_001@SEL$2AEE34FF
4 - SEL$2AEE34FF / indexjoin$_alias$_002@SEL$2AEE34FF
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2AEE34FF")
OUTLINE_LEAF(@"SEL$1")
INDEX_JOIN(@"SEL$1" "EMP_TEMP"@"SEL$1" ("EMP_TEMP"."MGR") ("EMP_TEMP"."DEPTNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID=ROWID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "DEPTNO"[NUMBER,22], "MGR"[NUMBER,22]
2 - (#keys=1; rowset=256) "MGR"[NUMBER,22], "DEPTNO"[NUMBER,22]
3 - ROWID[ROWID,10], "MGR"[NUMBER,22]
4 - ROWID[ROWID,10], "DEPTNO"[NUMBER,22]
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMP_TEMP]]></t><s><![CDATA
[SEL$1]]></s></h></f></q>
59 rows selected.
Elapsed: 00:00:00.04scott@ORCLPDB01 2023-04-03 22:06:04> create view emp_mgr_view as select * from emp_temp where job = 'MANAGER';
create view emp_mgr_view as select * from emp_temp where job = 'MANAGER'
*
ERROR at line 1:
ORA-01031: insufficient privileges
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 22:10:03> create view emp_mgr_view as select * from emp_temp where job = 'MANAGER';
View created.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-03 22:10:38> select empno,sal from emp_mgr_view where ename = 'CLARK';
EMPNO SAL
---------- ----------
7782 2450
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-03 22:11:19> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8wjqrcsngv4sf, child number 0
-------------------------------------
select empno,sal from emp_mgr_view where ename = 'CLARK'
Plan hash value: 2473744504
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP_TEMP | 1 | 22 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1 / EMP_TEMP@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2" >"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$F5BB74E1" "EMP_TEMP"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("ENAME"='CLARK' AND "JOB"='MANAGER'))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "SAL"[NUMBER,22]
Query Block Registry:
---------------------
<q o="18" f="y" h="y"><n><![CDATA[SEL$F5BB74E1]]></n><p><![CDATA[SEL$1]]
></p><i><o><t>VW</t><v><![CDATA[SEL$2]]></v></o></i><f><h><t><![CDATA[EM
P_TEMP]]></t><s><![CDATA[SEL$2]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMP_MGR_VIEW]]></t><s
><![CDATA[SEL$1]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[EMP_TEMP]]></t><s><![
CDATA[SEL$2]]></s></h></f></q>
57 rows selected.
Elapsed: 00:00:00.03
scott@ORCLPDB01 2023-04-03 22:11:22> create or replace view emp_mgr_view as select * from emp_temp where job = 'MANAGER' and rownum < 10;
View created.
Elapsed: 00:00:00.05
scott@ORCLPDB01 2023-04-03 22:12:59> select empno,sal from emp_mgr_view where ename = 'CLARK';
EMPNO SAL
---------- ----------
7782 2450
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 22:13:12> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8wjqrcsngv4sf, child number 0
-------------------------------------
select empno,sal from emp_mgr_view where ename = 'CLARK'
Plan hash value: 187861155
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | VIEW | EMP_MGR_VIEW | 3 | 99 | 3 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS FULL| EMP_TEMP | 3 | 66 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / EMP_MGR_VIEW@SEL$1
2 - SEL$2
3 - SEL$2 / EMP_TEMP@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "EMP_MGR_VIEW"@"SEL$1")
FULL(@"SEL$2" "EMP_TEMP"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='CLARK')
2 - filter(ROWNUM<10)
3 - filter("JOB"='MANAGER')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=9) "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22]
2 - (rowset=9) "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22]
3 - (rowset=9) "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22]
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMP_MGR_VIEW]]></t>
<s><![CDATA[SEL$1]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[EMP_TEMP]]></t><s><
![CDATA[SEL$2]]></s></h></f></q>
61 rows selected.
Elapsed: 00:00:00.03scott@ORCLPDB01 2023-04-03 22:14:58> select empno,ename from emp where empno in (select empno from emp_mgr_view);
EMPNO ENAME
---------- ----------
7566 JONES
7698 BLAKE
7782 CLARK
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 22:15:31> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5za5g24spz6y5, child number 0
-------------------------------------
select empno,ename from emp where empno in (select empno from
emp_mgr_view)
Plan hash value: 486331278
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | MERGE JOIN SEMI | | 3 | 69 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 140 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 3 | 39 | 4 (25)| 00:00:01 |
| 5 | VIEW | VW_NSO_1 | 3 | 39 | 3 (0)| 00:00:01 |
| 6 | VIEW | EMP_MGR_VIEW | 3 | 39 | 3 (0)| 00:00:01 |
|* 7 | COUNT STOPKEY | | | | | |
|* 8 | TABLE ACCESS FULL | EMP_TEMP | 3 | 36 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / EMP@SEL$1
3 - SEL$5DA710D3 / EMP@SEL$1
5 - SEL$683B0107 / VW_NSO_1@SEL$5DA710D3
6 - SEL$3 / EMP_MGR_VIEW@SEL$2
7 - SEL$3
8 - SEL$3 / EMP_TEMP@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$683B0107")
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2" UNNEST_SEMIJ_VIEW)
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
INDEX(@"SEL$5DA710D3" "EMP"@"SEL$1" ("EMP"."EMPNO"))
NO_ACCESS(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3")
LEADING(@"SEL$5DA710D3" "EMP"@"SEL$1" "VW_NSO_1"@"SEL$5DA710D3")
USE_MERGE(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3")
NO_ACCESS(@"SEL$683B0107" "EMP_MGR_VIEW"@"SEL$2")
FULL(@"SEL$3" "EMP_TEMP"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPNO"="EMPNO")
filter("EMPNO"="EMPNO")
7 - filter(ROWNUM<10)
8 - filter("JOB"='MANAGER')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
2 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
3 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
4 - (#keys=1) "EMPNO"[NUMBER,22]
5 - "EMPNO"[NUMBER,22]
6 - "EMPNO"[NUMBER,22]
7 - "EMPNO"[NUMBER,22]
8 - "EMPNO"[NUMBER,22]
Query Block Registry:
---------------------
<q o="12" f="y"><n><![CDATA[SEL$683B0107]]></n><p><![CDATA[SEL$2]]></p><f><h><t><![CD
ATA[EMP_MGR_VIEW]]></t><s><![CDATA[SEL$2]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMP]]></t><s><![CDATA[SEL$1]]></s>
</h></f></q>
<q o="2"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[EMP_MGR_VIEW]]></t><s><![CDATA[SEL
$2]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$3]]></n><f><h><t><![CDATA[EMP_TEMP]]></t><s><![CDATA[S
EL$3]]></s></h></f></q>
<q o="19" f="y" h="y"><n><![CDATA[SEL$5DA710D3]]></n><p><![CDATA[SEL$1]]></p><i><o><t
>SQ</t><v><![CDATA[SEL$2]]></v></o></i><f><h><t><![CDATA[EMP]]></t><s><![CDATA[SEL$1]
]></s></h><h><t><![CDATA[VW_NSO_1]]></t><s><![CDATA[SEL$5DA710D3]]></s></h></f></q>
92 rows selected.
Elapsed: 00:00:00.03
scott@ORCLPDB01 2023-04-03 22:15:33> @xplan.display_cursor.sql 5za5g24spz6y5 0
old 5: and sql_id = '&v_xc_sql_id'
new 5: and sql_id = '5za5g24spz6y5'
old 6: and child_number = to_number('&v_xc_child_no')
new 6: and child_number = to_number(' 0')
old 34: from table(dbms_xplan.display_cursor('&v_xc_sql_id',to_number('&v_xc_child_no'),'&v_xc_format')) x
new 34: from table(dbms_xplan.display_cursor('5za5g24spz6y5',to_number(' 0'),'typical')) x
old 95: ' - XPlan v&v_xc_version by Adrian Billington (http://www.oracle-developer.net)'
new 95: ' - XPlan v1.3 by Adrian Billington (http://www.oracle-developer.net)'
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5za5g24spz6y5, child number 0
-------------------------------------
select empno,ename from emp where empno in (select empno from
emp_mgr_view)
Plan hash value: 486331278
---------------------------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | | 9 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | 0 | 8 | MERGE JOIN SEMI | | 3 | 69 | 6 (17)| 00:00:01 |
| 2 | 1 | 2 | TABLE ACCESS BY INDEX ROWID| SCOTT.EMP | 14 | 140 | 2 (0)| 00:00:01 |
| 3 | 2 | 1 | INDEX FULL SCAN | SCOTT.PK_EMP | 14 | | 1 (0)| 00:00:01 |
|* 4 | 1 | 7 | SORT UNIQUE | | 3 | 39 | 4 (25)| 00:00:01 |
| 5 | 4 | 6 | VIEW | VW_NSO_1 | 3 | 39 | 3 (0)| 00:00:01 |
| 6 | 5 | 5 | VIEW | SCOTT.EMP_MGR_VIEW | 3 | 39 | 3 (0)| 00:00:01 |
|* 7 | 6 | 4 | COUNT STOPKEY | | | | | |
|* 8 | 7 | 3 | TABLE ACCESS FULL | SCOTT.EMP_TEMP | 3 | 36 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPNO"="EMPNO")
filter("EMPNO"="EMPNO")
7 - filter(ROWNUM<10)
8 - filter("JOB"='MANAGER')
About
------
- XPlan v1.3 by Adrian Billington (http://www.oracle-developer.net)
29 rows selected.
Elapsed: 00:00:00.07scott@ORCLPDB01 2023-04-03 22:18:05> select empno,ename from emp where empno in (select /*+ NO_UNNEST */ empno from emp_mgr_view);
EMPNO ENAME
---------- ----------
7566 JONES
7698 BLAKE
7782 CLARK
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 22:21:00> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2ysj8vygg6g13, child number 0
-------------------------------------
select empno,ename from emp where empno in (select /*+ NO_UNNEST */
empno from emp_mgr_view)
Plan hash value: 4147161479
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 24 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMP | 14 | 140 | 3 (0)| 00:00:01 |
|* 3 | VIEW | EMP_MGR_VIEW | 3 | 39 | 3 (0)| 00:00:01 |
|* 4 | COUNT STOPKEY | | | | | |
|* 5 | TABLE ACCESS FULL| EMP_TEMP | 3 | 36 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$3 / EMP_MGR_VIEW@SEL$2
4 - SEL$3
5 - SEL$3 / EMP_TEMP@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
PQ_FILTER(@"SEL$1" SERIAL)
NO_ACCESS(@"SEL$2" "EMP_MGR_VIEW"@"SEL$2")
FULL(@"SEL$3" "EMP_TEMP"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("EMPNO"=:B1)
4 - filter(ROWNUM<10)
5 - filter("JOB"='MANAGER')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
2 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
3 - "EMPNO"[NUMBER,22]
4 - "EMPNO"[NUMBER,22]
5 - "EMPNO"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - SEL$2
- NO_UNNEST
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMP]]></t><s><![CDAT
A[SEL$1]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[EMP_MGR_VIEW]]></t><
s><![CDATA[SEL$2]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$3]]></n><f><h><t><![CDATA[EMP_TEMP]]></t><s><!
[CDATA[SEL$3]]></s></h></f></q>
81 rows selected.
Elapsed: 00:00:00.11scott@ORCLPDB01 2023-04-03 22:21:03> select * from t1;
COL1 COL2
---------- -----
1 A
2 B
3 C
4 D
5 E
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 22:23:22> select * from t2;
COL2 COL3
----- -----
A A2
B B2
D D2
E E3
E E2
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 22:23:27> select /*+ gather_plan_statistics */ * from t1 where col2 in (select /*+ no_unnest */ col2 from t2);
COL1 COL2
---------- -----
1 A
2 B
4 D
5 E
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 22:24:31> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0f1xbq8tbyx8u, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t1 where col2 in (select
/*+ no_unnest */ col2 from t2)
Plan hash value: 895956251
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 36 |
|* 1 | FILTER | | 1 | | 4 |00:00:00.01 | 36 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 3 | 5 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T2 | 5 | 1 | 4 |00:00:00.01 | 28 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("COL2"=:B1)
22 rows selected.
Elapsed: 00:00:00.09SORT AGGREGATE
scott@ORCLPDB01 2023-04-03 22:29:43> set autot trace
scott@ORCLPDB01 2023-04-03 22:29:55> select sum(sal) from emp_temp where job = 'MANAGER';
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2836287311
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
|* 2 | TABLE ACCESS FULL| EMP_TEMP | 3 | 36 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"='MANAGER')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
551 bytes sent via SQL*Net to client
413 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSORT UNIQUE
scott@ORCLPDB01 2023-04-03 22:31:05> select distinct ename from emp_temp where job = 'MANAGER' order by ename;
ENAME
----------
BLAKE
CLARK
JONES
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 22:31:37> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 79zqs2wdq0wwj, child number 0
-------------------------------------
select distinct ename from emp_temp where job = 'MANAGER' order by ename
Plan hash value: 1323787459
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | SORT UNIQUE | | 3 | 42 | 4 (25)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP_TEMP | 3 | 42 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP_TEMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP_TEMP"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"='MANAGER')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "ENAME"[VARCHAR2,10]
2 - (rowset=256) "ENAME"[VARCHAR2,10]
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMP_TEMP]]></t>
<s><![CDATA[SEL$1]]></s></h></f></q>
52 rows selected.
Elapsed: 00:00:00.03SORT JOIN
scott@ORCLPDB01 2023-04-03 22:31:45> select /*+ use_merge(t1,t2) */ t1.empno,t2.ename,t2.sal from emp t1,emp_temp t2 where t1.empno = t2.empno;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
13 rows selected.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 22:34:04> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 76wqdcn85gzwf, child number 0
-------------------------------------
select /*+ use_merge(t1,t2) */ t1.empno,t2.ename,t2.sal from emp
t1,emp_temp t2 where t1.empno = t2.empno
Plan hash value: 2960112232
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | MERGE JOIN | | 14 | 252 | 5 (20)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
|* 3 | SORT JOIN | | 14 | 196 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP_TEMP | 14 | 196 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
4 - SEL$1 / T2@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("EMP"."EMPNO"))
FULL(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_MERGE(@"SEL$1" "T2"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."EMPNO"="T2"."EMPNO")
filter("T1"."EMPNO"="T2"."EMPNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T1"."EMPNO"[NUMBER,22], "T2"."SAL"[NUMBER,22],
"T2"."ENAME"[VARCHAR2,10]
2 - "T1"."EMPNO"[NUMBER,22]
3 - (#keys=1) "T2"."EMPNO"[NUMBER,22], "T2"."SAL"[NUMBER,22],
"T2"."ENAME"[VARCHAR2,10]
4 - "T2"."EMPNO"[NUMBER,22], "T2"."ENAME"[VARCHAR2,10],
"T2"."SAL"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$1 / T1@SEL$1
U - use_merge(t1,t2)
4 - SEL$1 / T2@SEL$1
- use_merge(t1,t2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T1]]></t><s><![
CDATA[SEL$1]]></s></h><h><t><![CDATA[T2]]></t><s><![CDATA[SEL$1]]></s></
h></f></q>
76 rows selected.
Elapsed: 00:00:00.03SORT ORDER BY
scott@ORCLPDB01 2023-04-03 22:34:13> select ename from emp_temp where job = 'MANAGER' order by ename;
ENAME
----------
BLAKE
CLARK
JONES
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 22:35:36> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4kkjv90a71kzq, child number 0
-------------------------------------
select ename from emp_temp where job = 'MANAGER' order by ename
Plan hash value: 1609363188
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT ORDER BY | | 3 | 42 | 4 (25)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP_TEMP | 3 | 42 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP_TEMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP_TEMP"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"='MANAGER')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "ENAME"[VARCHAR2,10]
2 - (rowset=256) "ENAME"[VARCHAR2,10]
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMP_TEMP]]></t>
<s><![CDATA[SEL$1]]></s></h></f></q>
52 rows selected.
Elapsed: 00:00:00.02SORT GROUP BY
scott@ORCLPDB01 2023-04-03 22:35:44> select ename from emp_temp where job = 'MANAGER' group by ename order by ename;
ENAME
----------
BLAKE
CLARK
JONES
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 22:36:59> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5s62s96fbb6st, child number 0
-------------------------------------
select ename from emp_temp where job = 'MANAGER' group by ename order
by ename
Plan hash value: 2247958178
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT GROUP BY | | 3 | 42 | 4 (25)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP_TEMP | 3 | 42 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP_TEMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP_TEMP"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"='MANAGER')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "ENAME"[VARCHAR2,10]
2 - (rowset=256) "ENAME"[VARCHAR2,10]
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMP_TEMP]]></t>
<s><![CDATA[SEL$1]]></s></h></f></q>
53 rows selected.
Elapsed: 00:00:00.02BUFFER SORT
scott@ORCLPDB01 2023-04-03 22:37:01> select t1.empno,t2.ename from emp t1,emp_temp t2;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 SMITH
7521 SMITH
7566 SMITH
7654 SMITH
7698 SMITH
7782 SMITH
7788 SMITH
7839 SMITH
7844 SMITH
7876 SMITH
7900 SMITH
7902 SMITH
7934 SMITH
7369 ALLEN
7499 ALLEN
7521 ALLEN
7566 ALLEN
7654 ALLEN
7698 ALLEN
7782 ALLEN
7788 ALLEN
7839 ALLEN
7844 ALLEN
7876 ALLEN
7900 ALLEN
7902 ALLEN
7934 ALLEN
7369 WARD
7499 WARD
7521 WARD
7566 WARD
7654 WARD
7698 WARD
7782 WARD
7788 WARD
7839 WARD
7844 WARD
7876 WARD
7900 WARD
7902 WARD
7934 WARD
7369 JONES
7499 JONES
7521 JONES
7566 JONES
7654 JONES
7698 JONES
7782 JONES
7788 JONES
7839 JONES
7844 JONES
7876 JONES
7900 JONES
7902 JONES
7934 JONES
7369 MARTIN
7499 MARTIN
7521 MARTIN
7566 MARTIN
7654 MARTIN
7698 MARTIN
7782 MARTIN
7788 MARTIN
7839 MARTIN
7844 MARTIN
7876 MARTIN
7900 MARTIN
7902 MARTIN
7934 MARTIN
7369 BLAKE
7499 BLAKE
7521 BLAKE
7566 BLAKE
7654 BLAKE
7698 BLAKE
7782 BLAKE
7788 BLAKE
7839 BLAKE
7844 BLAKE
7876 BLAKE
7900 BLAKE
7902 BLAKE
7934 BLAKE
7369 CLARK
7499 CLARK
7521 CLARK
7566 CLARK
7654 CLARK
7698 CLARK
7782 CLARK
7788 CLARK
7839 CLARK
7844 CLARK
7876 CLARK
7900 CLARK
7902 CLARK
7934 CLARK
7369 SCOTT
7499 SCOTT
7521 SCOTT
7566 SCOTT
7654 SCOTT
7698 SCOTT
7782 SCOTT
7788 SCOTT
7839 SCOTT
7844 SCOTT
7876 SCOTT
7900 SCOTT
7902 SCOTT
7934 SCOTT
7369 TURNER
7499 TURNER
7521 TURNER
7566 TURNER
7654 TURNER
7698 TURNER
7782 TURNER
7788 TURNER
7839 TURNER
7844 TURNER
7876 TURNER
7900 TURNER
7902 TURNER
7934 TURNER
7369 ADAMS
7499 ADAMS
7521 ADAMS
7566 ADAMS
7654 ADAMS
7698 ADAMS
7782 ADAMS
7788 ADAMS
7839 ADAMS
7844 ADAMS
7876 ADAMS
7900 ADAMS
7902 ADAMS
7934 ADAMS
7369 JAMES
7499 JAMES
7521 JAMES
7566 JAMES
7654 JAMES
7698 JAMES
7782 JAMES
7788 JAMES
7839 JAMES
7844 JAMES
7876 JAMES
7900 JAMES
7902 JAMES
7934 JAMES
7369 FORD
7499 FORD
7521 FORD
7566 FORD
7654 FORD
7698 FORD
7782 FORD
7788 FORD
7839 FORD
7844 FORD
7876 FORD
7900 FORD
7902 FORD
7934 FORD
7369 MILLER
7499 MILLER
7521 MILLER
7566 MILLER
7654 MILLER
7698 MILLER
7782 MILLER
7788 MILLER
7839 MILLER
7844 MILLER
7876 MILLER
7900 MILLER
7902 MILLER
7934 MILLER
182 rows selected.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 22:39:13> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dbsn779zu4x9u, child number 0
-------------------------------------
select t1.empno,t2.ename from emp t1,emp_temp t2
Plan hash value: 2676554926
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | MERGE JOIN CARTESIAN | | 196 | 1960 | 9 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP_TEMP | 14 | 84 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 14 | 56 | 6 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| PK_EMP | 14 | 56 | 0 (0)| |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T2@SEL$1
4 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("EMP"."EMPNO"))
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
USE_MERGE_CARTESIAN(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T2"."ENAME"[VARCHAR2,10], "T1"."EMPNO"[NUMBER,22]
2 - "T2"."ENAME"[VARCHAR2,10]
3 - (#keys=0) "T1"."EMPNO"[NUMBER,22]
4 - "T1"."EMPNO"[NUMBER,22]
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T1]]></t><s><![CDA
TA[SEL$1]]></s></h><h><t><![CDATA[T2]]></t><s><![CDATA[SEL$1]]></s></h></f>
</q>
56 rows selected.
Elapsed: 00:00:00.03uniona all
scott@ORCLPDB01 2023-04-03 22:44:19> select empno,ename from emp union all select empno,ename from emp_temp;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
27 rows selected.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 22:59:11> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 16d3d07tqwyd9, child number 0
-------------------------------------
select empno,ename from emp union all select empno,ename from emp_temp
Plan hash value: 1940079451
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP_TEMP | 14 | 140 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$2 / EMP_TEMP@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SET$1")
FULL(@"SEL$2" "EMP_TEMP"@"SEL$2")
FULL(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - STRDEF[22], STRDEF[10]
2 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
3 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SET$1]]></n><f><h><t><![CDATA[NULL_HALIAS]]><
/t><s><![CDATA[SET$1]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMP]]></t><s><!
[CDATA[SEL$1]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[EMP_TEMP]]></t>
<s><![CDATA[SEL$2]]></s></h></f></q>
57 rows selected.
Elapsed: 00:00:00.03scott@ORCLPDB01 2023-04-03 22:59:35> select empno,ename from emp union select empno,ename from emp_temp;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-03 23:01:01> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7jpac3hgu2t8s, child number 0
-------------------------------------
select empno,ename from emp union select empno,ename from emp_temp
Plan hash value: 1715351447
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | SORT UNIQUE | | 28 | 280 | 8 (25)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP_TEMP | 14 | 140 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1
3 - SEL$1 / EMP@SEL$1
4 - SEL$2 / EMP_TEMP@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SET$1")
FULL(@"SEL$2" "EMP_TEMP"@"SEL$2")
FULL(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=2) STRDEF[22], STRDEF[10]
2 - STRDEF[22], STRDEF[10]
3 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
4 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SET$1]]></n><f><h><t><![CDATA[NULL_HALIAS]]><
/t><s><![CDATA[SET$1]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMP]]></t><s><!
[CDATA[SEL$1]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[EMP_TEMP]]></t>
<s><![CDATA[SEL$2]]></s></h></f></q>
59 rows selected.
Elapsed: 00:00:00.02scott@ORCLPDB01 2023-04-03 23:02:20> select empno,ename from emp where empno in (7654,7698,7782);
EMPNO ENAME
---------- ----------
7654 MARTIN
7698 BLAKE
7782 CLARK
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 23:02:49> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 38qd7s0n23t44, child number 0
-------------------------------------
select empno,ename from emp where empno in (7654,7698,7782)
Plan hash value: 2355049923
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 3 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("EMPNO"=7654 OR "EMPNO"=7698 OR "EMPNO"=7782))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
2 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
3 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMP]]></t><s><![CDATA[
SEL$1]]></s></h></f></q>
55 rows selected.
Elapsed: 00:00:00.03
scott@ORCLPDB01 2023-04-03 23:02:52> scott@ORCLPDB01 2023-04-03 23:02:52> select /*+ use_concat */ empno,ename from emp where empno in (7654,7698,7782);
EMPNO ENAME
---------- ----------
7654 MARTIN
7698 BLAKE
7782 CLARK
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-03 23:03:50> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 288az1bw6ufz6, child number 0
-------------------------------------
select /*+ use_concat */ empno,ename from emp where empno in
(7654,7698,7782)
Plan hash value: 2355049923
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 3 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("EMPNO"=7654 OR "EMPNO"=7698 OR "EMPNO"=7782))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
2 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
3 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1
U - use_concat
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMP]]></t><s><![CDATA[
SEL$1]]></s></h></f></q>
63 rows selected.
Elapsed: 00:00:00.03scott@ORCLPDB01 2023-04-03 23:07:00> select empno,ename,mgr from emp start with empno = 7839 connect by prior empno = mgr;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7698 BLAKE 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
7782 CLARK 7839
7934 MILLER 7782
14 rows selected.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-03 23:07:55> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fh9r5fz2n629g, child number 0
-------------------------------------
select empno,ename,mgr from emp start with empno = 7839 connect by
prior empno = mgr
Plan hash value: 763482334
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 2 | TABLE ACCESS FULL | EMP | 14 | 196 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / EMP@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "connect$_by$_work$_set$_006"@"SEL$1")
NO_CONNECT_BY_FILTERING(@"SEL$1")
CONNECT_BY_COMBINE_SW(@"SEL$1")
INDEX_RS_ASC(@"SEL$4" "EMP"@"SEL$4" ("EMP"."EMPNO"))
FULL(@"SEL$3" "connect$_by$_pump$_002"@"SEL$3")
FULL(@"SEL$3" "EMP"@"SEL$3")
LEADING(@"SEL$3" "connect$_by$_pump$_002"@"SEL$3" "EMP"@"SEL$3")
USE_HASH(@"SEL$3" "EMP"@"SEL$3")
FULL(@"SEL$2" "EMP"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MGR"=PRIOR NULL)
filter("EMPNO"=7839)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "MGR"[NUMBER,22], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], PRIOR NULL[22],
LEVEL[4]
2 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "MGR"[NUMBER,22]
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SET$1]]></n><f><h><t><![CDATA[NULL_HALIAS]]></t><s><![CDATA[S
ET$1]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$4]]></n><f><h><t><![CDATA[EMP]]></t><s><![CDATA[SEL$4]]><
/s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[connect$_by$_work$_set$_006]]><
/t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[EMP]]></t><s><![CDATA[SEL$2]]><
/s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$3]]></n><f><h><t><![CDATA[EMP]]></t><s><![CDATA[SEL$3]]><
/s></h><h><t><![CDATA[connect$_by$_pump$_002]]></t><s><![CDATA[SEL$3]]></s></h></f></q>
75 rows selected.
Elapsed: 00:00:00.03
