收获，不止Oracle之表连接 博客分类： Oracle 优化
程序员文章站
2024-03-22 12:47:16
...
DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;
CREATE TABLE T1
(
ID NUMBER NOT NULL,
N NUMBER,
CONTENTS VARCHAR2(4000)
);
CREATE TABLE T2
(
ID NUMBER NOT NULL,
T1_ID NUMBER NOT NULL,
N NUMBER,
CONTENTS VARCHAR2(4000)
);
EXECUTE DBMS_RANDOM.SEED(0);
INSERT INTO T1
SELECT ROWNUM,ROWNUM,DBMS_RANDOM.STRING('A',50)
FROM DUAL
CONNECT BY LEVEL <= 100
ORDER BY DBMS_RANDOM.RANDOM;
INSERT INTO T2
SELECT ROWNUM,ROWNUM,ROWNUM,DBMS_RANDOM.STRING('B',50)
FROM DUAL
CONNECT BY LEVEL <= 100000
ORDER BY DBMS_RANDOM.RANDOM;
admin@ORCL> SELECT COUNT(*) FROM T1;
COUNT(*)
----------
100
admin@ORCL> SELECT COUNT(*) FROM T2;
COUNT(*)
----------
100000
admin@ORCL> SELECT /*+LEADING(T1) USE_NL(T2)*/*
2 FROM T1,T2
3 WHERE T1.ID = T2.T1_ID
--STARTS指的表访问次数,E-ROWS指的每个操作估算返回行数，A-ROWS指每步操作真实返回行数
--Leading 表示强制先访问T1表，也就是把T1作为驱动表
--执行之前需要set serveroutput off
--要显示比较详细的执行计划，两种方式：
--1.alter session set statistics=ALL
--2.执行的语句需要加上/*+ gather_plan_statistics */
admin@ORCL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 100 | 100 |00:00:00.81 | 100K|
| 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 14 |
|* 3 | TABLE ACCESS FULL| T2 | 100 | 1 | 100 |00:00:00.81 | 100K|
-------------------------------------------------------------------------------------
--下面的结果可以看出
--在嵌套循环中，驱动表返回多少条记录，被驱动表就访问多少次
admin@ORCL> SELECT /*+LEADING(T1) USE_NL(T2)*/* FROM T1,T2 WHERE T1.ID = T2.T1_ID AND T1.N = 9999999;
admin@ORCL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 7 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 0 |00:00:00.01 | 7 |
|* 3 | TABLE ACCESS FULL| T2 | 0 | 1 | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------
--来测试下，hash join的执行计划
--得出结论，在Hash连接中，驱动表与被驱动表都只会访问0次或1次
SELECT /*+LEADING(T1) USE_HASH(T2)*/* FROM T1,T2
WHERE T1.ID = T2.T1_ID;
admin@ORCL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 100 | 100 |00:00:00.06 | 1019 | 741K| 741K| 1134K (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 1012 | | | |
----------------------------------------------------------------------------------------------------------------
--查看排序合并的表访问次数
--T1,T2表只会访问0次或1次
--另外一个重要的概念：排序合并连接根本没有驱动表的概念，而嵌套循环和哈希连接要考虑驱动和被驱动的情况
SELECT /*+ ORDERED USE_MERGE(T2) */*
FROM T1,T2
WHERE T1.ID = T2.T1_ID;
admin@ORCL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 1 | MERGE JOIN | | 1 | 100 | 100 |00:00:00.08 | 1012 | | | |
| 2 | SORT JOIN | | 1 | 100 | 100 |00:00:00.01 | 7 | 9216 | 9216 | 8192 (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
|* 4 | SORT JOIN | | 100 | 100K| 100 |00:00:00.08 | 1005 | 9124K| 1177K| 8110K (0)|
| 5 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 1005 | | | |
-----------------------------------------------------------------------------------------------------------------
已选择22行。
--对比下，驱动表选择的性能比较
--T1在前时产生了100K的逻辑读，而t2在前时产生了700K的逻辑读
--证明了嵌套循环需要特别注意驱动表顺序，小的结果集先访问，大的结果集后访问。
SELECT /*+LEADING(T1) USE_NL(T2)*/*
FROM T1,T2
WHERE T1.ID = T2.T1_ID
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 100 | 100 |00:00:00.80 | 100K|
| 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 14 |
|* 3 | TABLE ACCESS FULL| T2 | 100 | 1 | 100 |00:00:00.80 | 100K|
-------------------------------------------------------------------------------------
SELECT /*+LEADING(T2) USE_NL(T1)*/*
FROM T1,T2
WHERE T1.ID = T2.T1_ID
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 100 | 100 |00:00:01.43 | 701K|
| 2 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 1012 |
|* 3 | TABLE ACCESS FULL| T1 | 100K| 1 | 100 |00:00:01.37 | 700K|
-------------------------------------------------------------------------------------
--加上过滤条件后，看下执行计划.
--发现T2的 TABLE ACCESS FULL步骤的逻辑读从700k减少到1006,这也说明了T2的全表访问实际上是依据T1的返回记录数去访问T2表
SELECT /*+LEADING(T1) USE_NL(T2)*/*
FROM T1,T2
WHERE T1.ID = T2.T1_ID AND T1.n = 19;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.02 | 1014 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 1 | 1 |00:00:00.02 | 1006 |
-------------------------------------------------------------------------------------
--对比下hash join的驱动表，执行效率
SELECT /*+LEADING(T1) USE_HASH(T2)*/*
FROM T1,T2
WHERE T1.ID = T2.T1_ID
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 100 | 100 |00:00:00.06 | 1019 | 741K| 741K| 1133K (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 1012 | | | |
----------------------------------------------------------------------------------------------------------------
SELECT /*+LEADING(T2) USE_HASH(T1)*/*
FROM T1,T2
WHERE T1.ID = T2.T1_ID
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 100 | 100 |00:00:00.10 | 1019 | 9471K| 1956K| 10M (0)|
| 2 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 1005 | | | |
| 3 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 14 | | | |
----------------------------------------------------------------------------------------------------------------
--对比上面的没有过滤条件的hash join
--逻辑读几乎一样,但排序使用的内存小了不少,而T1和T2的逻辑读都没有发生变化
--是否意味着T1,T2表时载入内存后，进行过滤后，然后进行排序？
SELECT /*+LEADING(T1) USE_HASH(T2)*/*
FROM T1,T2
WHERE T1.ID = T2.T1_ID AND T1.N = 19;
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.07 | 1013 | 741K| 741K| 289K (0)|
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 1006 | | | |
----------------------------------------------------------------------------------------------------------------
--排序合并的表驱动顺序
SELECT /*+LEADING(T1) USE_MERGE(T2)*/*
FROM T1,T2
WHERE T1.ID = T2.T1_ID AND T1.N = 19;
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 1 | MERGE JOIN | | 1 | 1 | 1 |00:00:00.08 | 1012 | | | |
| 2 | SORT JOIN | | 1 | 1 | 1 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
|* 3 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
|* 4 | SORT JOIN | | 1 | 100K| 1 |00:00:00.08 | 1005 | 9124K| 1177K| 8110K (0)|
| 5 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 1005 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."N"=19)
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
--对比上面的结果，消耗资源一摸一样，说明排序合并表驱动表是哪一个不影响
SELECT /*+LEADING(T2) USE_MERGE(T1)*/*
FROM T1,T2
WHERE T1.ID = T2.T1_ID AND T1.N = 19;
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 1 | MERGE JOIN | | 1 | 1 | 1 |00:00:00.12 | 1012 | | | |
| 2 | SORT JOIN | | 1 | 100K| 20 |00:00:00.11 | 1005 | 9124K| 1177K| 8110K (0)|
| 3 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 1005 | | | |
|* 4 | SORT JOIN | | 20 | 1 | 1 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
|* 5 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
5 - filter("T1"."N"=19)
--去掉过滤条件，发现只是T1表的表访问逻辑读增加了,T1表的cost仍然一样
SELECT /*+LEADING(T1) USE_MERGE(T2)*/*
FROM T1,T2
WHERE T1.ID = T2.T1_ID
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 1 | MERGE JOIN | | 1 | 100 | 100 |00:00:00.10 | 1012 | | | |
| 2 | SORT JOIN | | 1 | 100 | 100 |00:00:00.01 | 7 | 9216 | 9216 | 8192 (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
|* 4 | SORT JOIN | | 100 | 100K| 100 |00:00:00.10 | 1005 | 9124K| 1177K| 8110K (0)|
| 5 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 1005 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
--测试下，合并排序只取部分字段
--会发现表T2的内存排序大小从8110K降到了1621K。应该是由于查询中没有用到T2表的字段，只是连接时用到了T2.T1_ID
--所以只缓存此字段即可
SELECT /*+LEADING(T1) USE_MERGE(T2)*/T1.ID
FROM T1,T2
WHERE T1.ID = T2.T1_ID AND T1.N = 19;
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 1 | MERGE JOIN | | 1 | 1 | 1 |00:00:00.07 | 1012 | | | |
| 2 | SORT JOIN | | 1 | 1 | 1 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
|* 3 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
|* 4 | SORT JOIN | | 1 | 100K| 1 |00:00:00.07 | 1005 | 1824K| 650K| 1621K (0)|
| 5 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 1005 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."N"=19)
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
--来测试下hash表，是不是同样的效果
--结果并不是这样，对于hash join,虽然减少了查询字段，但资源的消耗没有减少。这点有点费解
SELECT /*+LEADING(T1) USE_HASH(T2)*/T1.ID
FROM T1,T2
WHERE T1.ID = T2.T1_ID
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 100 | 100 |00:00:00.07 | 1019 | 1066K| 1066K| 1149K (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 1012 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
--来看下hash join与排序合并连接的限制
--hash连接不支持不等值连接，不支持><连接方式，不支持like
SELECT /*+LEADING(T1) USE_HASH(T2)*/T1.ID
FROM T1,T2
WHERE T1.ID <> T2.T1_ID AND T1.N = 19;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 100K| 99999 |00:00:00.10 | 7613 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 100K| 99999 |00:00:00.01 | 7605 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"<>"T2"."T1_ID")
--排序合并连接不支持<>连接方式，也不支持like,但支持><之类的连接方式
SELECT /*+LEADING(T1) USE_MERGE(T2)*/*
FROM T1,T2
WHERE T1.ID <> T2.T1_ID AND T1.N = 19;
SELECT /*+LEADING(T1) USE_MERGE(T2)*/* FROM T1,T2 WHERE T1.ID <>
T2.T1_ID AND T1.N = 19
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 100K| 99999 |00:00:00.10 | 7613 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 100K| 99999 |00:00:00.01 | 7605 |
-------------------------------------------------------------------------------------
--学习下如何优化表连接
--什么时候适合于NL
/*
两表关联返回少量记录，最佳情况是驱动表仅仅返回1条或者少量几条，而被驱动表也仅仅匹配少量几条数据，这种情况即使T1表和
T2表奇大无比，也是非常迅速的。
调优方式：驱动表的限制条件上有索引
被驱动表的连接条件上所在的列也有索引
*/
SELECT /*+LEADING(T1) USE_NL(T2)*/T1.ID
FROM T1,T2
WHERE T1.ID = T2.T1_ID AND T1.n = 19;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.02 | 1014 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 1 | 1 |00:00:00.02 | 1006 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"="T2"."T1_ID")
--创建索引后，返现访问T1表的效率略有提示，但整体效率仍没有太大提高。这是因为表T1只有100条数据
CREATE INDEX IDX_T1_N ON T1(N);
SELECT /*+LEADING(T1) USE_NL(T2)*/T1.ID
FROM T1,T2
WHERE T1.ID = T2.T1_ID AND T1.n = 19;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 1009 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | IDX_T1_N | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 4 | TABLE ACCESS FULL | T2 | 1 | 1 | 1 |00:00:00.01 | 1006 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."N"=19)
4 - filter("T1"."ID"="T2"."T1_ID")
--在T2表的连接字段上创建索引,执行效率高了很多
CREATE INDEX IDX_T2_ID ON T2(T1_ID);
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.03 | 6 | 4 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 3 | 0 |
|* 3 | INDEX RANGE SCAN | IDX_T1_N | 1 | 1 | 1 |00:00:00.01 | 2 | 0 |
|* 4 | INDEX RANGE SCAN | IDX_T2_ID | 1 | 1 | 1 |00:00:00.03 | 3 | 4 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."N"=19)
4 - access("T1"."ID"="T2"."T1_ID")
--hash连接，排序合并连接
/*
对于HASH连接或者排序合并索引，可以考虑通过PGA参数的调整避免排序尺寸过大在磁盘上排序
HASH连接，需要在HASH_AREA_SIZE中完成
特别注意：连接条件的索引对它们是起不到传递的作用
但若限制条件上如果有合适的索引可以快速检索到少量数据，也是可以提升性能的。
因此hash 连接，可以理解为单表索引的设置技巧
对于排序合并连接与hash连接有点差别：
排序合并连接上的连接条件虽然没有检索作用，但却有消除排序的作用
另外Oracle对于排序合并连接有点缺陷，当两个表都有索引时，Oracle只能消除一张表的排
*/
CREATE INDEX IDX_T1_ID ON T1(ID);
DROP INDEX IDX_T1_N;
DROP INDEX IDX_T1_ID
SELECT /*+LEADING(T1) USE_MERGE(T2)*/*
FROM T1,T2
WHERE T1.ID = T2.T1_ID;
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
| 1 | MERGE JOIN | | 1 | 1 | 1 |00:00:00.07 | 1009 | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 4 | | | |
| 3 | INDEX FULL SCAN | IDX_T1_ID | 1 | 100 | 100 |00:00:00.01 | 2 | | | |
|* 4 | SORT JOIN | | 1 | 100K| 1 |00:00:00.07 | 1005 | 9124K| 1177K| 8110K (0)|
| 5 | TABLE ACCESS FULL | T2 | 1 | 100K| 100K|00:00:00.01 | 1005 | | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")