,, - - -
: DELETE : ROW (ROWID) row ROWID : I/O
Full Table Scan I/O Index Scan ROWID I/O Fast Full Index Scan scan scan scan I/O scan scan
Unique, nonunique. (Concatenated Index) B* Tree Bitmap Reverse Key Descending Function-based Domain Indexes
(Relation) RELATION (e.deptno = d.deptno) SELECT e.ename, d.dname FROM DEPT d, EMP e WHERE e.deptno = d.deptno; EMP table JOIN DEPT table EMPNO ENAME 7369 Smith 7499 Allen 7521 Ward 7566 Jones DEPTNO 50 10 30 30 e.ename Smith Allen Ward Jones d.dname BD R&D SALES SALES DEPTNO 20 10 30 50 DNAME HR R&D SALES BD...
Nested Loop Join (Driving) ( ) (?) EMPNO ENAME 7369 Smith 7499 Allen 7521 Ward 7566 Jones DEPTNO 50 10 30 30 e.ename d.dname Smith BD Allen R&D Ward SALES Jones SALES DEPTNO DNAME 10 20 30 50 20 10 30 50 HR R&D SALES BD...... * DEPT Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=RULE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF 'EMP' 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 4 3 INDEX (UNIQUE SCAN) OF 'DEPTNO_PK' (UNIQUE)
Sort Merge Join Allen 1 Ward 2 Jones 2 Smith 3 Allen Ward Jones Smith N : MERGE JOIN ROWID AAAB AABB AAFF AAAC 2) SORT JOIN(EMP) 4) SORT JOIN(DEPT) ROWID DEPTNO AAAB 10 AABB 30 AAFF 30 AAAC 50 M Join 10 30 30 50 1 2 3 ROWID BBBA BBAF BBAF BBAC 5) MERGE JOIN DEPTNO 10 20 30 50... R&D SALES SALES BD ROWID BBBA BBAA BBAF BBAC 5) 6). SELECT STATEMENT WHERE. 5) MERGE JOIN 2) 4) ( ) - 2) 4). SORT(JOIN) SORT(JOIN) R&D HR SALES BD 1) 3) SCOTT.EMP SCOTT.DEPT TABLE ACCESS(FULL) TABLE ACCESS(FULL)
Hash Join Hash PGA (HASH_AREA_SIZE) 3). SELECT STATEMENT. WHERE. : Oracle.. 3) HASH, MOD(x,6) 1) HASH (DEPT) + ROWID SALES (30,BBAF).. HR BD R&D (20,BBAA) (50,BBAC) 22.. (10,BBBA) 33 44. 55 HASH 00 11 3)ƒ h (50) = 2 ƒ h (10) = 4 2) FULL ACCESS(EMP) 4) HASH JOIN DEPTNO ENAME 50 Smith 10 Allen 30 Ward 30 Jones 1) 2) SCOTT.DEPT SCOTT.EMP TABLE ACCESS(FULL) TABLE ACCESS(FULL)
SELECT e.ename, d.dname FROM DEPT d, EMP e WHERE e.deptno = d.deptno ORDER BY e.ename; PGA 1) SORT_AREA_SIZE SCOTT.EMP TABLE ACCESS(FULL) 6) SELECT STATEMENT 5) SORT(ORDER BY) 4) NESTED LOOPS 3) 2) SCOTT.DEPT_PK INDEX(UNIQUE SCAN) 5) ORDER BY. SCOTT.DEPT TABLE ACCESS(BY INDEX ROWID)
Oracle Optimizer - - RBO CBO -Optimizer
2.1
Optimizer SQL (Procedural) (Declarative) SQL : X SQL.. O
Optimizer ( ) SQL Optimizer GOAL:. RDBMS Rule-Based Optimization (RBO) Cost-Based Optimization (CBO) (Cost)
2.2 Rule-Based Optimization Cost- Based Optimization
Rule-Based Optimization (RBO) SQL syntax SQL :, Oracle 6
RBO 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Single row by ROWID Single row by cluster join Single row by hash cluster key with unique or primary key Single row by unique or primary key Cluster join Hash cluster key Indexed cluster key Composite index Single-column index Bounded range search on indexed columns Unbounded range search on indexed columns Sort-merge join MAX or MIN of indexed column ORDER BY on indexed column Full table scan
Cost-Based Optimization (CBO) SQL I/O CPU Oracle7 RBO
CBO Partitioning (Index-organized table) Reverse key Function-based SELECT SAMPLE Query DML Star Join Star Optimizer Materialized View Query rewrite Enterprise Manager progress meter Join bitmap bitmap Join skip scan
2.3 Optimizer
Optimizer Oracle9i Optimizer SQL RBO CBO throughput - row Response time - row
Optimizer ( ) Optimizer OPTIMIZER_MODE ( alter system ) OPTIMIZER_MODE (alter session ) Optimizer Hint
OPTIMIZER_MODE CHOOSE Oracle9i Optimizer.. RULE RBO ALL_ROWS FIRST_ROWS[_n]. n = 1, 10, 100, 1000
SQL Optimizer Hint /*+ CHOOSE */ /*+ RULE */ /*+ ALL_ROWS */ Oracle9i Optimizer. RBO /*+ FIRST_ROWS[(n)] */. n =
RBO RBO SQL syntax, FROM WHERE syntax
CBO CBO Hint SQL CBO
Optimizer Hint Optimizer, Optimizer Optimizer Hint Optimizer Hint Stored Outline Hint /*+ RULE */ CBO
CBO OPTIMIZER_MODE OPTIMIZER_FEATURES_ENABLE CURSOR_SHARING DB_FILE_MULTIBLOCK_READ_COUNT SORT_AREA_SIZE, HASH_AREA_SIZE, PGA_AGGREGATE_TARGET HASH_JOIN_ENABLED OPTIMIZER_INDEX_CACHING OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_MAX_PERMUTATIONS PARTITION_VIEW_ENABLED QUERY_REWRITE_ENABLED STAR_TRANSFORMATION_ENABLED
SQL - -
3.1 SQL
SQL Response Time Resource Workload Workload re-scheduling report batch Workload Parallel Query, Parallel DML DW
SQL Step 1 SQL Step 2 Step 3 SQL
SQL feedback SQL Trace TKPROF STATSPACK Report Dictionary View I/O Sort
SQL SQL SQL View Base SQL CBO SQL
SQL Optimizer SQL Optimizer Optimizer Optimizer Optimizer Optimizer Hint
SQL ( ) SQL. Optimizer Logic, SQL DBMS - - contention
3.2
SELECT empno, ename, job FROM emp WHERE empno = :b1; empno Primary Key., empno VARCHAR2(4) bind Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMP'
( ) Full Table Scan, WHERE TO_NUMBER(emp_id) = :b1 WHERE 1 : SQL SELECT empno, ename, job FROM emp WHERE empno = TO_CHAR(:b1); 2: empno number.
SELECT 'Not Exists' FROM dept WHERE dname!= :b1; ( ). dname Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'DEPT' Full Table Scan
( ) : SELECT 'Not Exists' FROM dual WHERE NOT EXISTS ( SELECT * FROM dept WHERE dname = :b1 ); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF 'DUAL' 3 1 INDEX (RANGE SCAN) OF 'I_DEPT_DNAME' (NON-UNIQUE)
( ) 'NOTEXISTS ---------- Not Exists Not Exists Not Exists Not Exists n. 'NOTEXISTS ---------- Not Exists 1. Full Table Scan EXISTS
null SELECT empno, ename, job, comm FROM emp WHERE comm IS NULL;. comm Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMP' Full Table Scan IS NULL NULL
null ( ) SELECT empno, ename, job, comm FROM emp WHERE comm IS NOT NULL;. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMP' Full Table Scan.
null ( ) 1 : SQL SELECT empno, ename, job, comm FROM emp WHERE comm > 0; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 2 1 INDEX (RANGE SCAN) OF 'I_EMP_COMM' (NON-UNIQUE) comm > 0. NVL(comm, -1) > 0
null ( ) 2 : SELECT /*+ INDEX (emp i_emp_comm) */ empno, ename, job, comm FROM emp WHERE comm IS NOT NULL; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=20 Bytes=780) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=66 Card=20 Bytes=780) 2 1 INDEX (FULL SCAN) OF 'I_EMP_COMM' (NON-UNIQUE) (Cost=26 Card=20) scan : INDEX (FULL SCAN)
null ( ) 3 : CREATE TABLE emp (... comm NUMBER(7, 2) DEFAULT -1,... ); ALTER TABLE emp MODIFY(comm DEFAULT -1); IS NOT NULL IS NULL SELECT empno, ename, job, comm FROM emp WHERE comm > -1; SELECT empno, ename, job, comm FROM emp WHERE comm = -1;
SELECT * FROM registrations WHERE status = 'HOLD'; HOLD. status registrations Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=11250 Bytes=303750) 1 0 TABLE ACCESS (FULL) OF 'REGISTRATIONS' (Cost=31 Card=11250 Bytes=303750)
( ) Full Table Scan status SELECT status, COUNT(*) FROM registrations GROUP BY status; STAT COUNT(*) ---- -------- CANC 2400 HOLD 5 PEND 2 REGI 53575 WAIT 270 5. status skewed Optimizer selectivity = 1 / #distinct values = 1/ 5 = 20%
( ) Histogram ANALYZE TABLE registrations COMPUTE STATISTICS FOR COLUMNS status; Query Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=135) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'REGISTRATIONS' (Cost=2 Card=5 Bytes=135) 2 1 INDEX (RANGE SCAN) OF 'I_REGISTRATIONS_STATUS' (NON- UNIQUE) (Cost=1 Card=5)
( ) status = REGI Query Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=53575 Bytes=1446525) 1 0 TABLE ACCESS (FULL) OF 'REGISTRATIONS' (Cost=31 Card=53575 Bytes=1446525) Full Table Scan CBO.
( ) : SELECT * FROM registrations WHERE status = :b1; bind histogram. : ( REGI ) NULL. SELECT * FROM registrations WHERE status = :b1; SELECT * FROM registrations WHERE status IS NULL; Full Table Scan
sort SELECT empno, ename, job, comm FROM emp ORDER BY comm;. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2 1 TABLE ACCESS (FULL) OF 'EMP' sort. sort row.
sort ( ) : comm Query SELECT empno, ename, job, comm FROM emp WHERE comm >= 0; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 2 1 INDEX (RANGE SCAN) OF 'I_EMP_COMM' (NON-UNIQUE) sort. row.
sort ( ) SELECT empno, ename, job, hiredate FROM emp WHERE hiredate >= TO_DATE(:b1, 'YYYY') ORDER BY job DESC; : job. job job Query SELECT /*+ INDEX_DESC (emp i_emp_job) */ empno, ename, job, hiredate FROM emp WHERE hiredate >= TO_DATE(:b1, 'YYYY') AND job > ' '; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=35) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=35) 2 1 INDEX (RANGE SCAN DESCENDING) OF 'I_EMP_JOB' (NON-UNIQUE) (Cost=2 Card=4)
sort ( ) SELECT MAX(sal) FROM emp WHERE hiredate > TO_DATE(:b1, 'YYYY'); : 1980. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 3 2 INDEX (RANGE SCAN) OF 'I_EMP_HIREDATE_ JOB' (NON- UNIQUE) MIN/MAX sort.
sort ( ) : (sal,_hiredate) Query SELECT /*+ INDEX_DESC (emp i_emp_sal_hiredate) */ sal FROM emp WHERE hiredate > TO_DATE(:b1, 'YYYY') AND ROWNUM = 1; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1 Bytes=440) 1 0 COUNT (STOPKEY) 2 1 INDEX (FULL SCAN DESCENDING) OF 'I_EMP_SAL_HIREDATE' (NON-UNIQUE) (Cost=26 Card=20 Bytes=440) sort. STOPKEY row. select list sal scan
. SELECT * FROM registrations WHERE status = :b1 AND appr_by = :b2;. status appr_by registrations
( ) OPTIMIZER_MODE = RULE Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=RULE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'REGISTRATIONS' 2 1 AND-EQUAL 3 2 INDEX (RANGE SCAN) OF 'I_REGISTRATIONS_APPR_BY' (NON- UNIQUE) 4 2 INDEX (RANGE SCAN) OF 'I_REGISTRATIONS_STATUS' (NON- UNIQUE) AND-EQUAL Merge Merge rowid row scan
( ) CBO Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=112 Bytes=3024) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'REGISTRATIONS' (Cost=13 Card=112 Bytes=3024) 2 1 INDEX (RANGE SCAN) OF 'I_REGISTRATIONS_APPR_BY' (NON- UNIQUE) (Cost=1 Card=815) Merge Merge row scan QueryAND_EQUAL Hint Merge cost 227.
( ) (status, appr_by) Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=112 Bytes=3024) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'REGISTRATIONS' (Cost=5 Card=112 Bytes=3024) 2 1 INDEX (RANGE SCAN) OF 'I_REGISTRATIONS_STATUS_APPR_BY' (NON-UNIQUE) (Cost=1 Card=272) cost = 5 Merge Merge row scan rowid WHERE AND.
Skip Scan SELECT * FROM registrations WHERE appr_by = :b1;. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=815 Bytes=22005) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'REGISTRATIONS' (Cost=16 Card=815 Bytes=22005) 2 1 INDEX (SKIP SCAN) OF 'I_REGISTRATIONS_STATUS_APPR_BY' (NON-UNIQUE) (Cost=4 Card=12) Oracle8i Leading WHERE B* Tree :. Oracle9i Skip Scan cost = 16 appr_by (cost = 13)
SELECT * FROM registrations WHERE status = :b1 AND appr_by = :b2;. appr_by 1/74, status 1/5 : (appr_by, status)? (appr_by, status) Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=112 Bytes=3024) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'REGISTRATIONS' (Cost=5 Card=112 Bytes=3024) 2 1 INDEX (RANGE SCAN) OF 'I_REGISTRATIONS_APPR_BY_STATUS' (NON-UNIQUE) (Cost=1 Card=272) Scan
( ) SELECT * FROM registrations WHERE appr_by BETWEEN :b1 AND :b2 AND status = :b3;. Cost (status, appr_by) cost = 14 : status = :b3 appr_by (appr_by, status) cost = 15: :b1 <= appr_by <= :b2 status scan. 1.. 2. 1.
Fast Full Scan SELECT appr_by, status FROM registrations;. (appr_by, status) appr_by status NOT NULL Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=56252 Bytes=506268) 1 0 INDEX (FAST FULL SCAN) OF 'I_REGISTRATIONS_APPR_BY_STATUS' (NON-UNIQUE) (Cost=17 Card=56 252 Bytes=506268) Fast Full Scan
AND SELECT * FROM registrations WHERE class_id = :b1 OR appr_by = :b2;. (class_id, appr_by) Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=778 Bytes=21006) 1 0 TABLE ACCESS (FULL) OF 'REGISTRATIONS' (Cost=31 Card=778 Bytes=21006) OR?
AND ( ) class_id appr_by Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=761 Bytes=20547) 1 0 CONCATENATION 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'REGISTRATIONS' (Cost=6 Card=1 Bytes=27) 3 2 INDEX (RANGE SCAN) OF 'I_REGISTRATIONS_APPR_BY' (NON-UNIQUE) (Cost=2 Card=18) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'REGISTRATIONS' (Cost=6 Card=1 Bytes=27) 5 4 INDEX (RANGE SCAN) OF 'REG_PK' (UNIQUE) (Cost=2 Card=18) : OR Expansion SELECT * FROM registrations WHERE class_id = :b1 UNION ALL SELECT * FROM registrations WHERE appr_by = :b2;
AND ( ) SELECT * FROM registrations WHERE class_id IN (:b1, :b2, :b3);. : IN-List Iterator Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=54 Bytes=1458) 1 0 INLIST ITERATOR 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'REGISTRATIONS' (Cost=3 Card=54 Bytes=1458) 3 2 INDEX (RANGE SCAN) OF 'I_REGISTRATIONS_CLASS_ID' (NON- UNIQUE) (Cost=2 Card=54) AND.
AND ( ) SELECT * FROM registrations WHERE (status = 'HOLD' OR appr_by = 85617) AND NOT (status = 'WAIT' AND appr_by = 95482) AND (attended = 'Y');. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=127 Card=2117 Bytes=57159) 1 0 TABLE ACCESS (FULL) OF 'REGISTRATIONS' (Cost=127 Card=2117 Bytes=57159) OR Expansion. Bitmap
AND ( ) status, appr_by, attended Bitmap Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=81 Card=4855 Bytes=131085) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'REGISTRATIONS' (Cost=114 Card=4855 Bytes=131085) 2 1 BITMAP CONVERSION (TO ROWIDS) 3 2 BITMAP AND 4 3 BITMAP OR 5 4 BITMAP INDEX (SINGLE VALUE) OF 'BI_REGISTRATIONS_STATUS' 6 4 BITMAP INDEX (SINGLE VALUE) OF 'BI_REGISTRATIONS_APPR_BY' 7 3 BITMAP INDEX (SINGLE VALUE) OF 'BI_REGISTRATIONS_ATTENDED' : Query.
OPTIMIZER_MODE Join SELECT co.short_name, cl.start_date FROM classes cl, courses co WHERE cl.instr_id = co.dev_id;. join key classes.instr_id courses.dev_id OPTIMIZER_MODE = RULE Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=RULE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 TABLE ACCESS (FULL) OF 'COURSES' 4 1 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'CLASSES' Full Table Scan Sort/Merge : RBO Hash
OPTIMIZER_MODE Join ( ) OPTIMIZER_MODE = ALL_ROWS HASH_JOIN_ENABLED = FALSE HASH_JOIN_ENABLED = TRUE Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=24 Card=3599 Bytes=82777) 1 0 MERGE JOIN (Cost=24 Card=3599 Bytes=82777) 2 1 SORT (JOIN) (Cost=8 Card=1018 Bytes=14252) 3 2 TABLE ACCESS (FULL) OF 'COURSES' (Cost=3 Card=1018 Bytes=14252) 4 1 SORT (JOIN) (Cost=16 Card=1729 Bytes=15561) 5 4 TABLE ACCESS (FULL) OF 'CLASSES' (Cost=9 Card=1729 Bytes=15561) Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=3599 Bytes=82777) 1 0 HASH JOIN (Cost=13 Card=3599 Bytes=82777) 2 1 TABLE ACCESS (FULL) OF 'COURSES' (Cost=3 Card=1018 Bytes=14252) 3 1 TABLE ACCESS (FULL) OF 'CLASSES' (Cost=9 Card=1729 Bytes=15561)
OPTIMIZER_MODE Join ( ) OPTIMIZER_MODE = FIRST_ROWS Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=13 Card=3599 Bytes=82777) 1 0 HASH JOIN (Cost=13 Card=3599 Bytes=82777) 2 1 TABLE ACCESS (FULL) OF 'COURSES' (Cost=3 Card=1018 Bytes=14252) 3 1 TABLE ACCESS (FULL) OF 'CLASSES' (Cost=9 Card=1729 Bytes=15561) Join Key Nested Loop Full Table Scan Cardinality(courses) * Cardinality(classes) Join OPTIMIZER_MODE = FIRST_ROWS Hash.
Join Key Join SELECT co.short_name, cl.start_date FROM classes cl, courses co WHERE cl.instr_id = co.dev_id;. join keycourses.dev_id OPTIMIZER_MODE = RULE Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=RULE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COURSES' 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'CLASSES' 4 2 INDEX (RANGE SCAN) OF 'I_COURSES_DEV_ID' (NON-UNIQUE) Nested Loop Join Key classes driving
Join Key Join ( ) OPTIMIZER_MODE = ALL_ROWS Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=3599 Bytes=82777) 1 0 HASH JOIN (Cost=13 Card=3599 Bytes=82777) 2 1 TABLE ACCESS (FULL) OF 'COURSES' (Cost=3 Card=1018 Bytes=14252) 3 1 TABLE ACCESS (FULL) OF 'CLASSES' (Cost=9 Card=1729 Bytes=15561) courses driving Hash driving Join Key Hash cardinality courses classes courses.
Join Key Join ( ) OPTIMIZER_MODE = FIRST_ROWS Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3467 Card=3599 Bytes=82777) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COURSES' (Cost=2 Card=2 Bytes=28) 2 1 NESTED LOOPS (Cost=3467 Card=3599 Bytes=82777) 3 2 TABLE ACCESS (FULL) OF 'CLASSES' (Cost=9 Card=1729 Bytes=15561) 4 2 INDEX (RANGE SCAN) OF 'I_COURSES_DEV_ID' (NON-UNIQUE) (Cost=1 Card=5) OPTIMIZER_MODE Nested Loop Join Key classes driving RBO, CBO
Join Key Join ( ) OPTIMIZER_MODE FIRST_ROWS Join Key classes.instr_id Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2039 Card=3599 Bytes=82777) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CLASSES' (Cost=2 Card=4 Bytes=36) 2 1 NESTED LOOPS (Cost=2039 Card=3599 Bytes=82777) 3 2 TABLE ACCESS (FULL) OF 'COURSES' (Cost=3 Card=1018 Bytes=14252) 4 2 INDEX (RANGE SCAN) OF 'I_CLASSES_INSTR_ID' (NON-UNIQUE) (Cost=1 Card=4) Join Key courses driving cost OPTIMIZER_MODE FIRST_ROWS Nested Loop Join Key
Join Key Join ( ) OPTIMIZER_MODE = FIRST_ROWS Join Key Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2039 Card=3599 Bytes=82777) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CLASSES' (Cost=2 Card=4 Bytes=36) 2 1 NESTED LOOPS (Cost=2039 Card=3599 Bytes=82777) 3 2 TABLE ACCESS (FULL) OF 'COURSES' (Cost=3 Card=1018 Bytes=14252) 4 2 INDEX (RANGE SCAN) OF 'I_CLASSES_INSTR_ID' (NON-UNIQUE) (Cost=1 Card=4) courses driving driving courses cardinality driving Join Join Key row set cardinality
Non-Join Join SELECT co.short_name, cl.start_date FROM classes cl, courses co WHERE cl.instr_id = co.dev_id AND cl.class_id = :b1; class_id. join keycourses.dev_id classes.instr_id classes.class_id class_id classes Prinary Key. Unique
Non-Join Join ( ) OPTIMIZER_MODE = FIRST_ROWS Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=2 Bytes=54) 1 0 NESTED LOOPS (Cost=4 Card=2 Bytes=54) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CLASSES' (Cost=2 Card=1 Bytes=13) 3 2 INDEX (UNIQUE SCAN) OF 'CLS_PK' (UNIQUE) (Cost=1 Card=11163) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'COURSES' (Cost=2 Card=2 Bytes=28) 5 4 INDEX (RANGE SCAN) OF 'I_COURSES_DEV_ID' (NON-UNIQUE) (Cost=1 Card=5) classes driving Nested Loop OPTIMIZER_MODE = ALL_ROWS classes Non-Join Single Row Predicate Nested Loop Outer Loop row set cardinality 1!
Star Join SELECT cs.description, c.start_date FROM cls_statuses cs, class_types ct, classes c where cs.description = 'Class rescheduled or consolidated' and c.status = cs.cls_status and c.type = ct.type; Join Key class status cls_statuses Primary Key Join Key class type class_types Primary Key Classes Join Key
Star Join ( ) Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=60 Card=2847 Bytes=128115) 1 0 NESTED LOOPS (Cost=60 Card=2847 Bytes=128115) 2 1 HASH JOIN (Cost=60 Card=2847 Bytes=116727) 3 2 TABLE ACCESS (FULL) OF 'CLS_STATUSES' (Cost=2 Card=2 Bytes=52) 4 2 TABLE ACCESS (FULL) OF 'CLASSES' (Cost=24 Card=11163 Bytes=167445) 5 1 INDEX (UNIQUE SCAN) OF 'CTYPE_PK' (UNIQUE) Join Key classes drive 2 Nested Loop Join : cls_status class_types cardinalitycls_statuses driving classes hash join class_types nested loop join
Star Join ( ) Star Schema 1 CLASS #class_id CLS_STATUSES #cls_status. CLASS_TYPES #type.... dimension/lookup DW Star Join Star schemaoracle CBO : join key (Status + type)
Star Join ( ) Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=28 Card=2847 Bytes=128115) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CLASSES' (Cost=2 Card=1861 Bytes=27915) 2 1 NESTED LOOPS (Cost=28 Card=2847 Bytes=128115) 3 2 MERGE JOIN (CARTESIAN) (Cost=4 Card=12 Bytes=360) 4 3 TABLE ACCESS (FULL) OF 'CLS_STATUSES' (Cost=2 Card=2 Bytes=52) 5 3 BUFFER (SORT) (Cost=2 Card=1 Bytes=4) 6 5 INDEX (FULL SCAN) OF 'CTYPE_PK' (UNIQUE) (Cost=1 Card=1 Bytes=4) 7 2 INDEX (RANGE SCAN) OF 'I_CLASSES_STATUS_TYPE' (NON- UNIQUE) (Cost=1 Card=279) cls_statuses class_types Cartesian Product row set class nested loop Join classes
Top-N SQL SELECT * FROM (SELECT COUNT(*), class_id FROM registrations GROUP BY class_id ORDER BY COUNT(*) DESC) WHERE ROWNUM <= 10 10 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1443 Card=10 Bytes=234) 1 0 COUNT (STOPKEY) 2 1 VIEW (Cost=1443 Card=2828 Bytes=73528) 3 2 SORT (ORDER BY STOPKEY) (Cost=1443 Card=2828 Bytes=11312) 4 3 SORT (GROUP BY) (Cost=1443 Card=2828 Bytes=11312) 5 4 INDEX (FULL SCAN) OF 'REG_PK' (UNIQUE) (Cost=345 Card=56252 Bytes=225008) sort : sort
SQL SELECT COUNT (*) FROM employees WHERE salary < 2000; SELECT COUNT(*) FROM employees WHERE salary BETWEEN 2000 AND 4000; SELECT COUNT(*) FROM employees WHERE salary > 4000; 3 query
SQL ( ) SQL SELECT COUNT (CASE WHEN salary < 2000 THEN 1 ELSE null END) count1, COUNT (CASE WHEN salary BETWEEN 2001 AND 4000 THEN 1 ELSE null END) count2, COUNT (CASE WHEN salary > 4000 THEN 1 ELSE null END) count3 FROM employees; CASE query(decode ) Oracle Server 1/3
SQL cursor c is select crs_id, short_name from courses;.. loop fetch c into cid, sname; select count(*) into n from classes where crs_id = cid; if n = 0 then c_schedule := 'Not Scheduled'; else schedule := 'Scheduled'; select start_date into sdate from classes where crs_id = cid; if sdate is null then c_date := N/A ; elsif sdate > sysdate then c_date := to_char(sdate, 'YYYY-MM-DD'); else c_date := 'Already Started'; end if; end if; exit when c%notfound; end loop;.
SQL ( ) SQL SELECT co.crs_id, co.short_name, CASE WHEN cl.class_id IS NULL THEN 'Not Scheduled ELSE 'Scheduled' END class_id, CASE WHEN cl.start_date IS NULL THEN 'N/A' else CASE WHEN cl.start_date > sysdate THEN TO_CHAR(cl.start_date, 'YYYY-MM-DD') ELSE 'Arleady Started' END END start_date FROM courses co, classes cl WHERE co.crs_id = cl.crs_id(+); CASE outer join SQL Optimizer SQL Optimizer