1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
|
drop table bigemp
/
drop table bigdept
/
CREATE TABLE bigemp (
empno primary key, ename, job, mgr, hiredate, sal, comm, deptno
) AS
SELECT empno+x AS empno, ename, job, mgr, hiredate, sal, comm, deptno+x AS deptno
FROM emp
CROSS JOIN (select level*10000 AS x FROM dual CONNECT BY level <= 100000)
/
CREATE TABLE bigdept (
deptno PRIMARY KEY, dname, loc
) AS
SELECT deptno+x AS deptno, dname, loc
FROM dept
CROSS JOIN (select level*10000 AS x FROM dual CONNECT BY level <= 100000)
/
exec dbms_stats.gather_table_stats(user, 'BIGEMP')
exec dbms_stats.gather_table_stats(user, 'BIGDEPT')
Select bigemp.*
From bigemp
Where Exists (Select Null
From bigdept
Where bigdept.deptno = bigemp.deptno)
/
Plan d'exécution
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=936 Card=1400000
Bytes=71400000)
1 0 NESTED LOOPS (SEMI) (Cost=936 Card=1400000 Bytes=71400000)
2 1 TABLE ACCESS (FULL) OF 'BIGEMP' (Cost=934 Card=1400000 B
ytes=61600000)
3 1 INDEX (UNIQUE SCAN) OF 'SYS_C0021149' (UNIQUE)
Select bigemp.*
From bigemp
Join bigdept on (bigdept.deptno = bigemp.deptno)
/
SQL> 2 3 4
Plan d'exécution
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=936 Card=1400000
Bytes=71400000)
1 0 NESTED LOOPS (Cost=936 Card=1400000 Bytes=71400000)
2 1 TABLE ACCESS (FULL) OF 'BIGEMP' (Cost=934 Card=1400000 B
ytes=61600000)
3 1 INDEX (UNIQUE SCAN) OF 'SYS_C0021149' (UNIQUE) |
Partager