DDIT-SQL/20200715.sql at master · riboooo/DDIT-SQL · GitHub

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

CREATE INDEX idx_nu_emp_02 ON emp (job);

EXPLAIN PLAN FOR
SELECT *
FROM emp
WHERE job='MANAGER'
AND ename LIKE 'C%';

SELECT *
FROM TABLE(dbms_xplan.display);

CREATE INDEX idx_nu_emp_03 ON emp (job, ename);

EXPLAIN PLAN FOR
SELECT *
FROM emp
WHERE job='MANAGER'
AND ename LIKE 'C%';

SELECT *
FROM TABLE(dbms_xplan.display);


EXPLAIN PLAN FOR
SELECT *
FROM emp
WHERE job='MANAGER'
AND ename LIKE '%C';

SELECT *
FROM TABLE(dbms_xplan.display);


CREATE INDEX ipx_nu_emp_04 ON emp (ename,job);

SELECT ename, job, rowid
FROM emp
ORDER BY ename, job;

EXPLAIN PLAN FOR
SELECT *
FROM emp
WHERE ename LIKE 'C%'
AND job= 'MANANGER';

SELECT *
FROM TABLE(dbms_xplan.display);

DROP INDEX idx_nu_emp_03;

ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY (empno);
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) REFERENCES dept (deptno);


EXPLAIN PLAN FOR
SELECT *
FROM emp,dept
WHERE emp.deptno = dept.deptno
AND emp.empno =  7788;


SELECT *
FROM TABLE (dbms_xplan.display);

Plan hash value: 999219729

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 |   117 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |               |       |       |            |          |
|   2 |   NESTED LOOPS                |               |     1 |   117 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    87 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_NU_EMP_01 |     1 |       |     0   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | PK_DEPT       |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT          |   409 | 12270 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."EMPNO"=7788)
   5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

4 - 3 - 5 -2 -6 -1 - 0

;

DROP TABLE DEPT_TEST;

CREATE TABLE DEPT_TEST AS
SELECT *
FROM DEPT
WHERE 1=1;


CREATE UNIQUE INDEX idx_u_dept_test_01 ON dept_test (deptno);

CREATE INDEX idx_nu_dept_test_02 ON dept_test (dname);

CREATE INDEX idx_nu_dept_test_03 ON dept_test (deptno, dname);

DROP INDEX idx_u_dept_test_01;
DROP INDEX idx_nu_dept_test_02;
DROP INDEX idx_nu_dept_test_03;


EXPLAIN PLAN FOR
SELECT deptno, TO_CHAR(hiredate, 'yyyymm'), COUNT(*) cnt
FROM emp
GROUP BY deptno, TO_CHAR(hiredate, 'yyyymm');

SELECT *
FROM TABLE (dbms_xplan.display);


EXPLAIN PLAN FOR
SELECT *
FROM emp
WHERE sal BETWEEN 0 AND 10000
AND deptno = 30;


SELECT ROWID, emp.*
FROM emp;

CREATE INDEX idx_emp_001 ON emp (deptno);

DROP INDEX idx_emp_001;

EXPLAIN PLAN FOR
SELECT *
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND emp.deptno = 20
AND emp.empno IN (7902, 7876);


SELECT *
FROM TABLE (dbms_xplan.display);


CREATE UNIQUE INDEX idx_u_emp_0001 ON emp (empno);
CREATE INDEX idx_u_emp_0002 ON emp (deptno);
CREATE UNIQUE INDEX idx_u_dept_0001 ON dept (deptno);