Nested Loop Join 优化要点
要点1：驱动表的连接条件考虑建立索引
--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;
实验1、没有建立索引之前的执行计划。
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'));
实验2、在驱动表的连接条件上建立索引后的执行计划
CREATE INDEX t1_n ON t1 (n);
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'));
结果，在驱动表的连接条件上建立索引之后，buffer从2019下降到了2015。
要点2：被驱动的限制条件（where）建立索引
实验3：在被驱动表的限制条件建立索引。
CREATE INDEX t2_t1_id ON t2(t1_id)
alter session set statistics_level=all ;
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n = 19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
结果，buffer从2015下降到了11。