,, - - - : DELETE : ROW (ROWID) row ROWID : I/O

Full Table Scan scan I/O scan Index Scan ROWID scan I/O Fast Full Index Scan scan scan I/O Unique, nonunique. (Concatenated Index) B* Tree Bitmap Reverse Key Descending Function-based Domain Indexes

(Relation) SELECT e.ename, d.dname FROM DEPT d, EMP e WHERE e.deptno = d.deptno; RELATION (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 DEPTNO 7369 Smith 50 7499 Allen 10 7521 Ward 30 7566 Jones 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...... 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) * DEPT

Sort Merge Join MERGE JOIN 5) 6). ROWID M Join ROWID SELECT STATEMENT Allen 1 AAAB 10 BBBA R&D WHERE Ward 2 AABB 30 BBAF SALES Jones 2 AAFF 30 BBAF SALES Smith 3 AAAC 50 BBAC BD. 5) MERGE JOIN 2) 4) N : 5) MERGE JOIN ( ) - 2). 4) 2) SORT JOIN(EMP) 4) SORT JOIN(DEPT) SORT(JOIN) SORT(JOIN) Allen Ward Jones Smith ROWID DEPTNO AAAB 10 AABB 30 AAFF 30 AAAC 50 1 2 3 DEPTNO ROWID 10 BBBA 20 BBAA 30 BBAF 50 BBAC... R&D HR SALES BD 1) 3) SCOTT.EMP SCOTT.DEPT TABLE ACCESS(FULL) TABLE ACCESS(FULL) Hash Join Hash PGA (HASH_AREA_SIZE) 4) 3). SELECT STATEMENT. WHERE. : Oracle.. 3) HASH, MOD(x,6) 1) HASH (DEPT) 2) FULL ACCESS(EMP) HASH JOIN + ROWID HASH DEPTNO ENAME SALES (30,BBAF) 3)ƒ h (50) = 2 00 50 Smith.. 11 10 Allen 30 Ward HR (20,BBAA) ƒ h (10) = 4 BD (50,BBAC) 22 30 Jones 1) 2).. 33 R&D (10,BBBA) 44 SCOTT.DEPT SCOTT.EMP. 55 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; 6) SELECT STATEMENT 4) PGA 1) SCOTT.EMP SORT_AREA_SIZE TABLE ACCESS(FULL) 5) SORT(ORDER BY) NESTED LOOPS 3) 2) 5) ORDER BY. SCOTT.DEPT TABLE ACCESS(BY INDEX ROWID) SCOTT.DEPT_PK INDEX(UNIQUE SCAN) Oracle Optimizer - - RBO CBO - Optimizer

2.1 Optimizer SQL (Procedural) (Declarative) SQL : O X SQL..

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 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 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 ); 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 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;. 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; 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; 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 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 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 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;. 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; 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 > ' '; 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. 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; 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 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 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 Query AND_EQUAL Hint Merge cost 227. ( ) (status, appr_by) 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;. 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) 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 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) 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 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 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');. 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 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 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 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) 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 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 key courses.dev_id OPTIMIZER_MODE = RULE 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 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 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 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 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 key courses.dev_id classes.instr_id classes.class_id class_id classes Prinary Key. Unique

Non-Join Join ( ) OPTIMIZER_MODE = FIRST_ROWS 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 ( ) 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 cardinality cls_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 schema Oracle CBO : join key (Status + type)

Star Join ( ) 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 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