三大经典表连接探索(nested loop、hash、merge sort)
1、表的访问次数探索
- 表的访问次数之NL连接研究
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;
COUNT(*)
----------
100
select count(*) from t2;
COUNT(*)
----------
100000
设置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;
2、表的驱动顺序与性能的探索
3、表连接是否有排序
4、各个连接的使用限制