Oracle学习笔记表连接（十六）
三大表连接：
1.Nested Loops Join （偏局部扫描的OLTP应用）
2.Hash Join
3.Merge Sort Join 和 Hash Join 偏OLAP应用
表的访问次数之NL连接研究
/*
结论： NL连接中，驱动表被访问0或者1次，被驱动表被访问0次或者N次，N由驱动表返回的结果集的条数来定）
*/
--环境构造
--研究Nested Loops Join的表访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS 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;
COMMIT;
select count(*) from t1;
select count(*) from t2;
--我们用设置statistics_level=all的方式来观察如下表连接语句的执行计划：
--T2表被访问100次(驱动表访问1次，被驱动表访问100次）
--这个set linesize 1000对dbms_xplan.display_cursor还是有影响的，如果没有设置，默认情况下的输出，将会少了很多列，如BUFFERS等
Set linesize 1000
alter session set statistics_level=all ;
SELECT /*+ leading(t1) use_nl(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id;
--略去记录结果
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.94 | 100K|
| 1 | NESTED LOOPS | | 1 | 100 | 100 |00:00:00.94 | 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.94 | 100K|
-------------------------------------------------------------------------------------
3 - filter("T1"."ID"="T2"."T1_ID")
---换个语句，这次T2表被访问2次(驱动表访问1次，被驱动表访问2次）
Set linesize 1000
alter session set statistics_level=all ;
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(17, 19);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.02 | 2019 |
| 1 | NESTED LOOPS | | 1 | 2 | 2 |00:00:00.02 | 2019 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2 | 2 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T2 | 2 | 1 | 2 |00:00:00.02 | 2011 |
-------------------------------------------------------------------------------------
2 - filter(("T1"."N"=17 OR "T1"."N"=19))
3 - filter("T1"."ID"="T2"."T1_ID")
--继续换个语句，这次T2表被访问1次(驱动表访问1次，被驱动表访问1次）
Set linesize 1000
alter session set statistics_level=all ;
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1014 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 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.01 | 1006 |
-------------------------------------------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"="T2"."T1_ID")
---接下来，T2表居然被访问0次(驱动表访问1次，被驱动表访问0次）
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 999999999;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 7 |
| 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 |
-------------------------------------------------------------------------------------
2 - filter("T1"."N"=999999999)
3 - filter("T1"."ID"="T2"."T1_ID")
---到最后，不只是T2表被访问0次，连T1表也访问0次
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND 1=2;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 |
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 |
| 2 | NESTED LOOPS | | 0 | 100 | 0 |00:00:00.01 |
| 3 | TABLE ACCESS FULL| T1 | 0 | 100 | 0 |00:00:00.01 |
|* 4 | TABLE ACCESS FULL| T2 | 0 | 1 | 0 |00:00:00.01 |
----------------------------------------------------------------------------
1 - filter(NULL IS NOT NULL)
4 - filter("T1"."ID"="T2"."T1_ID")
--分析T2表被访问次数不同的原因
---解释T2表为啥被访问100次
select count(*) from t1;
COUNT(*)
----------
100
---解释T2表为啥被访问2次
select count(*) from t1 where t1.n in (17,19);
COUNT(*)
----------
2
---解释T2表为啥被访问1次
select count(*) from t1 where t1.n = 19;
COUNT(*)
----------
1
---解释T2表为啥被访问0次
select count(*) from t1 where t1.n = 999999999;
COUNT(*)
----------
0
表的访问次数之HASH连接研究
/*
结论： NL连接中，驱动表被访问0或者1次，被驱动表也是被访问0次或者1次，绝大部分场景是驱动表和被驱动表被各访问1次）
*/
--环境构造
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS 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;
COMMIT;
select count(*) from t1;
select count(*) from t2;
--Hash Join中 T2表只会被访问1次或0次(驱动表访问1次，被驱动表访问1次）
set linesize 1000
SELECT /*+ leading(t1) use_hash(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id;
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 |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.07 | 1019 | | | |
|* 1 | HASH JOIN | | 1 | 100 | 100 |00:00:00.07 | 1019 | 742K| 742K| 1178K (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| T2 | 1 | 111K| 100K|00:00:00.02 | 1012 | | | |
----------------------------------------------------------------------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
--Hash Join中T2表被访问0次的情况
SELECT /*+ leading(t1) use_hash(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
and t1.n=999999999;
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 |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 7 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 0 |00:00:00.01 | 7 | 676K| 676K| 205K (0)|
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 0 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| T2 | 0 | 111K| 0 |00:00:00.01 | 0 | | | |
----------------------------------------------------------------------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
2 - filter("T1"."N"=999999999)
--Hash Join中T1和T2表都访问0次的情况
SELECT /*+ leading(t1) use_hash(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
and 1=2;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | | | |
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 | | | |
|* 2 | HASH JOIN | | 0 | 100 | 0 |00:00:00.01 | 732K| 732K| |
| 3 | TABLE ACCESS FULL| T1 | 0 | 100 | 0 |00:00:00.01 | | | |
| 4 | TABLE ACCESS FULL| T2 | 0 | 111K| 0 |00:00:00.01 | | | |
-------------------------------------------------------------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - access("T1"."ID"="T2"."T1_ID")
表的访问次数之排序合并连接研究
/*
结论： 排序合并连接中，两表都是只被访问0次或者1次，和HASH 连接一样）
*/
--环境构造
--研究Nested Loops Join的表访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS 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;
COMMIT;
select count(*) from t1;
select count(*) from t2;
--Merge Sort Join中 T2表只会被访问1次或0次(驱动表访问1次，被驱动表访问1次）
set linesize 1000
SELECT /*+ leading(t1) use_merge(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id;
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 |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.09 | 1012 | | | |
| 1 | MERGE JOIN | | 1 | 100 | 100 |00:00:00.09 | 1012 | | | |
| 2 | SORT JOIN | | 1 | 100 | 100 |00:00:00.01 | 7 | 11264 | 11264 |10240 (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
|* 4 | SORT JOIN | | 100 | 100K| 100 |00:00:00.09 | 1005 | 9266K| 1184K| 8236K (0)|
| 5 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.02 | 1005 | | | |
-----------------------------------------------------------------------------------------------------------------
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
--Merge Sort Join中T2表被访问0次的情况
set linesize 1000
SELECT /*+ leading(t1) use_merge(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
and t1.n=999999999;
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 |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 7 | | | |
| 1 | MERGE JOIN | | 1 | 1 | 0 |00:00:00.01 | 7 | | | |
| 2 | SORT JOIN | | 1 | 1 | 0 |00:00:00.01 | 7 | 1024 | 1024 | |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 1 | 0 |00:00:00.01 | 7 | | | |
|* 4 | SORT JOIN | | 0 | 100K| 0 |00:00:00.01 | 0 | 7250K| 1073K| |
| 5 | TABLE ACCESS FULL| T2 | 0 | 100K| 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------
3 - filter("T1"."N"=999999999)
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
--Merge Sort Join中T1和T2表都访问0次的情况
set linesize 1000
SELECT /*+ leading(t1) use_merge(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
and 1=2;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | | | |
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 | | | |
| 2 | MERGE JOIN | | 0 | 100 | 0 |00:00:00.01 | | | |
| 3 | SORT JOIN | | 0 | 100 | 0 |00:00:00.01 | 73728 | 73728 | |
| 4 | TABLE ACCESS FULL| T1 | 0 | 100 | 0 |00:00:00.01 | | | |
|* 5 | SORT JOIN | | 0 | 100K| 0 |00:00:00.01 | 7250K| 1073K| |
| 6 | TABLE ACCESS FULL| T2 | 0 | 100K| 0 |00:00:00.01 | | | |
--------------------------------------------------------------------------------------------------------
1 - filter(NULL IS NOT NULL)
5 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
表驱动顺序与性能之NL
---嵌套循环连接的t1表先访问的情况
--环境构造
--研究Nested Loops Join访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS 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;
COMMIT;
select count(*) from t1;
select count(*) from t2;
select * from t1
set linesize 1000
alter session set statistics_level=all;
SELECT /*+ leading(t1) use_nl(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1014 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 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.01 | 1006 |
-------------------------------------------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"="T2"."T1_ID")
---Nested Loops Join的t2表先访问的情况
alter session set statistics_level=all;
SELECT /*+ leading(t2) use_nl(t1)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.46 | 701K|
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:01.46 | 701K|
| 2 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.02 | 1006 |
|* 3 | TABLE ACCESS FULL| T1 | 100K| 1 | 1 |00:00:01.40 | 700K|
-------------------------------------------------------------------------------------
3 - filter(("T1"."N"=19 AND "T1"."ID"="T2"."T1_ID"))
--发现性能有巨大差异！
表驱动顺序与性能之HASH连接：
--HASH连接的t1表先访问的情况
--环境构造
--研究Nested Loops Join访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS 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;
COMMIT;
select count(*) from t1;
select count(*) from t2;
set linesize 1000
alter session set statistics_level=all;
SELECT /*+ leading(t1) use_hash(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
and t1.n=19;
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 |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.06 | 1013 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.06 | 1013 | 742K| 742K| 335K (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.02 | 1006 | | | |
----------------------------------------------------------------------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
2 - filter("T1"."N"=19)
---Hash Join的t2表先访问情况
set linesize 1000
SELECT /*+ leading(t2) use_hash(t1)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
and t1.n=19;
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 |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.11 | 1013 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.11 | 1013 | 9472K| 1956K| 10M (0)|
| 2 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.02 | 1005 | | | |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 8 | | | |
----------------------------------------------------------------------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
3 - filter("T1"."N"=19)
表驱动顺序与性能指排序合并连接
--Merge Sort Join的t1表先访问情况
--环境构造
--研究Nested Loops Join访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS 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;
COMMIT;
select count(*) from t1;
select count(*) from t2;
set linesize 1000
alter session set statistics_level=all;
SELECT /*+ leading(t1) use_merge(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
and t1.n=19;
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 |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.08 | 1012 | | | |
| 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 | 9266K| 1184K| 8236K (0)|
| 5 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.02 | 1005 | | | |
-----------------------------------------------------------------------------------------------------------------
3 - filter("T1"."N"=19)
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
--Merge Sort Join的t2表先访问情况
set linesize 1000
SELECT /*+ leading(t2) use_merge(t1)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
and t1.n=19;
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 |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.11 | 1012 | | | |
| 1 | MERGE JOIN | | 1 | 1 | 1 |00:00:00.11 | 1012 | | | |
| 2 | SORT JOIN | | 1 | 100K| 20 |00:00:00.11 | 1005 | 9266K| 1184K| 8236K (0)|
| 3 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.02 | 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 | | | |
-----------------------------------------------------------------------------------------------------------------
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
5 - filter("T1"."N"=19)
表连接是否有排序之NL
/*
结论：通过排序观察统计信息的sorts(memory)和sorts(disk)部分得出，NL连接不会产生排序！
*/
--环境构造
--研究Nested Loops Join访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS 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;
COMMIT;
select count(*) from t1;
select count(*) from t2;
set linesize 1000
set autotrace traceonly
SELECT /*+ leading(t1) use_nl(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 123 | 276 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 1 | 123 | 276 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 57 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 66 | 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"="T2"."T1_ID")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1014 consistent gets
0 physical reads
0 redo size
880 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
表连接是否有排序之HASH连接
/*
结论：通过排序观察统计信息的sorts(memory)和sorts(disk)部分得出，HASH连接不会产生排序！
*/
--环境构造
--研究Nested Loops Join访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS 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;
COMMIT;
select count(*) from t1;
select count(*) from t2;
set linesize 1000
set autotrace traceonly
SELECT /*+ leading(t1) use_hash(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 123 | 277 (1)| 00:00:04 |
|* 1 | HASH JOIN | | 1 | 123 | 277 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 57 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 100K| 6445K| 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
2 - filter("T1"."N"=19)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1013 consistent gets
0 physical reads
0 redo size
880 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
表连接是否有排序之排序合并
/*
结论：通过排序观察统计信息的sorts(memory)和sorts(disk)部分得出，Merge Sort Join会产生排序！
*/
--环境构造
--研究Nested Loops Join访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS 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;
COMMIT;
select count(*) from t1;
select count(*) from t2;
set linesize 1000
set autotrace traceonly
SELECT /*+ leading(t1) use_merge(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19;
执行计划
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 123 | | 1852 (1)| 00:00:23 |
| 1 | MERGE JOIN | | 1 | 123 | | 1852 (1)| 00:00:23 |
| 2 | SORT JOIN | | 1 | 57 | | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 57 | | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 100K| 6445K| 15M| 1848 (1)| 00:00:23 |
| 5 | TABLE ACCESS FULL| T2 | 100K| 6445K| | 273 (1)| 00:00:04 |
------------------------------------------------------------------------------------
3 - filter("T1"."N"=19)
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1012 consistent gets
0 physical reads
0 redo size
880 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
各连接的使用限制之NL：
/*
结论：Nested Loops Join支持大于，小于，不等，LIKE等连接条件,可以说没有受到任何限制！
*/
--环境构造
--研究Nested Loops Join访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS 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;
COMMIT;
select count(*) from t1;
select count(*) from t2;
set linesize 1000
set autotrace traceonly explain
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id > t2.t1_id
AND t1.n = 19;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 6150 | 276 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 50 | 6150 | 276 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 57 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 50 | 3300 | 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID">"T2"."T1_ID")
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id < t2.t1_id
AND t1.n = 19;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99950 | 11M| 276 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 99950 | 11M| 276 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 57 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 99950 | 6442K| 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"<"T2"."T1_ID")
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id <> t2.t1_id
AND t1.n = 19;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99999 | 11M| 276 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 99999 | 11M| 276 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 57 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 99999 | 6445K| 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"<>"T2"."T1_ID")
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id like t2.t1_id
AND t1.n = 19;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 600K| 276 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 5000 | 600K| 276 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 57 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 5000 | 322K| 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter(TO_CHAR("T1"."ID") LIKE TO_CHAR("T2"."T1_ID"))
各连接的使用限制之HASH连接：
/*
结论：Hash Join不支持大于，小于，不等，LIKE等连接条件！
*/
--环境构造
--研究Nested Loops Join访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS 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;
COMMIT;
select count(*) from t1;
select count(*) from t2;
set linesize 1000
set autotrace traceonly explain
SELECT /*+ leading(t1) use_hash(t2)*/ *
FROM t1, t2
WHERE t1.id > t2.t1_id
AND t1.n = 19;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 6150 | 276 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 50 | 6150 | 276 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 57 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 50 | 3300 | 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID">"T2"."T1_ID")
SELECT /*+ leading(t1) use_hash(t2)*/ *
FROM t1, t2
WHERE t1.id < t2.t1_id
AND t1.n = 19;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99950 | 11M| 276 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 99950 | 11M| 276 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 57 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 99950 | 6442K| 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"<"T2"."T1_ID")
SELECT /*+ leading(t1) use_hash(t2)*/ *
FROM t1, t2
WHERE t1.id <> t2.t1_id
AND t1.n = 19;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99999 | 11M| 276 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 99999 | 11M| 276 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 57 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 99999 | 6445K| 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"<>"T2"."T1_ID")
SELECT /*+ leading(t1) use_hash(t2)*/ *
FROM t1, t2
WHERE t1.id like t2.t1_id
AND t1.n = 19;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 600K| 276 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 5000 | 600K| 276 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 57 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 5000 | 322K| 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter(TO_CHAR("T1"."ID") LIKE TO_CHAR("T2"."T1_ID"))
各连接的使用限制之排序合并：
/*
结论：Merge Sort Join不支持不等，LIKE等连接条件,却支持大于，小于的连接条件。
*/
--环境构造
--研究Nested Loops Join访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS 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;
COMMIT;
select count(*) from t1;
select count(*) from t2;
set linesize 1000
set autotrace traceonly explain
SELECT /*+ leading(t1) use_merge(t2)*/ *
FROM t1, t2
WHERE t1.id > t2.t1_id
AND t1.n = 19;
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 6150 | | 1852 (1)| 00:00:23 |
| 1 | MERGE JOIN | | 50 | 6150 | | 1852 (1)| 00:00:23 |
| 2 | SORT JOIN | | 1 | 57 | | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 57 | | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 100K| 6445K| 15M| 1848 (1)| 00:00:23 |
| 5 | TABLE ACCESS FULL| T2 | 100K| 6445K| | 273 (1)| 00:00:04 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."N"=19)
4 - access(INTERNAL_FUNCTION("T1"."ID")>INTERNAL_FUNCTION("T2"."T1_ID"))
filter(INTERNAL_FUNCTION("T1"."ID")>INTERNAL_FUNCTION("T2"."T1_ID"))
SELECT /*+ leading(t1) use_merge(t2)*/ *
FROM t1, t2
WHERE t1.id < t2.t1_id
AND t1.n = 19;
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99950 | 11M| | 1852 (1)| 00:00:23 |
| 1 | MERGE JOIN | | 99950 | 11M| | 1852 (1)| 00:00:23 |
| 2 | SORT JOIN | | 1 | 57 | | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 57 | | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 100K| 6445K| 15M| 1848 (1)| 00:00:23 |
| 5 | TABLE ACCESS FULL| T2 | 100K| 6445K| | 273 (1)| 00:00:04 |
------------------------------------------------------------------------------------
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(t1) use_merge(t2)*/ *
FROM t1, t2
WHERE t1.id <> t2.t1_id
AND t1.n = 19;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99999 | 11M| 276 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 99999 | 11M| 276 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 57 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 99999 | 6445K| 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"<>"T2"."T1_ID")
SELECT /*+ leading(t1) use_merge(t2)*/ *
FROM t1, t2
WHERE t1.id like t2.t1_id
AND t1.n = 19;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 600K| 276 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 5000 | 600K| 276 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 57 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 5000 | 322K| 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter(TO_CHAR("T1"."ID") LIKE TO_CHAR("T2"."T1_ID"))
