SQL Tuning Business Development DB

SQL Tuning Business Development DB

Oracle Optimizer 4.1

Optimizer SQL SQL.. SQL Optimizer :.. Rule-Based Optimization (RBO), Cost-Based Optimization (CBO) SQL Optimizer SQL Query Parser Dictionary Rule-Based Optimizer RBO Optimizer Mode? CBO Cost-Based Optimizer Row Source Generator Query SQL Execution

Optimizer 1. (expression) 2. SQL SQL 3. RBO? CBO? (CBO )?? 4. join join SQL SQL Oracle Step Step Step (Execution Plan)

SQL SQL : SELECT emp_id, job_id, sal, dept_name FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS (SELECT * FROM jobs WHERE emp.sal BETWEEN min_sal AND max_sal); SQL ( ) EXPLAIN PLAN ID OPERATION OPTIONS OBJECT_NAME ------------------------------------ 0 SELECT STATEMENT 1 FILTER 2 NESTED LOOPS 3 TABLE ACCESS FULL EMP 4 TABLE ACCESS BY ROWID DEPT 5 INDEX UNIQUE SCAN PK_DEPTNO 6 TABLE ACCESS FULL JOBS

SQL ( ) Graphical 3 Table Access (Full) emp 2 Nested Loops 1 Filter 4 Table Access (By ROWID) dept 5 Index (Unique Scan) pk_deptno 6 Table Access (Full) Jobs Step Row Source. Step Row Set. Step Step ( Box) Row set ( Box) 4.2 Rule-Based Optimization Cost- Based Optimization

Rule-Based Optimization (RBO) SQL Syntax (Cost) 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) CPU I/O 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

4.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 /*+ CHOOSE */ /*+ RULE */ /*+ ALL_ROWS */ Oracle9i Optimizer. RBO /*+ FIRST_ROWS[(n)] */. n = RBO RBO SQL syntax, FROM WHERE syntax

CBO CBO SQL CBO Optimizer Hint Optimizer, Optimizer Optimizer Hint Optimizer Hint /*+ RULE */ CBO

Hint Syntax SELECT INSERT /*+ hint */ UPDATE DELETE comment text SELECT INSERT --+ hint UPDATE DELETE comment text Hint SQL, alias alias.

Hint UPDATE /*+ INDEX(e EMPLOYEES_JOB_IX) */ employees e SET e.salary = (SELECT /*+ INDEX(m EMPLOYEES_SALARY_IX) */ (e.salary + m.salary)/2 FROM employees m WHERE m.emp_id = e.mgr_id AND m.salary > e.salary) WHERE e.job = 'INSTRUCTOR' AND e.hiredate > TO_DATE('01/01/1998', 'dd/mm/yyyy'); Hint Category Optimization Hint Hint Query Hint Join Order Hint Join Operation Hint Hint Hint

CBO OPTIMIZER_MODE OPTIMIZER_FEATURES_ENABLE CURSOR_SHARING DB_FILE_MULTIBLOCK_READ_COUNT HASH_AREA_SIZE HASH_JOIN_ENABLED OPTIMIZER_INDEX_CACHING OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_MAX_PERMUTATIONS PARTITION_VIEW_ENABLED QUERY_REWRITE_ENABLED SORT_AREA_SIZE STAR_TRANSFORMATION_ENABLED 4.4 CBO

CBO Parser Query Estimator Parse Query Query Query + Cost Plan Dictionary Estimator Cardinality Selectivity Cost Plan Cost Plan Query Plan Row Source Generator Cost : Cardinality row set row Selectivity row set row Query (Predicate)

Estimator Output: Cost SQL I/O CPU Cardinality Selectivity Cost CBO Cardinality, Selectivity, Cost Static Dictionary : ANALYZE DBMS_STATS Category (Column),

ANALYZE TABLE ANALYZE INDEX CLUSTER schema. name COMPUTE STATISTICS DELETE STATISTICS ESTIMATE STATISTICS for clause for clause sample clause ANALYZE ( ) for clause FOR TABLE FOR ALL INDEXES FOR ALL INDEXED FOR COLUMNS SIZE n COLUMNS column SIZE n SIZE n sample clause SAMPLE n ROWS PERCENT

DBMS_STATS Oracle 8.1.6 Dictionary ANALYZE, schema, ANALYZE DBMS_STATS dbms_stats.gather_table_stats ( sh, -- schema customers, -- table NULL, -- partition 20, -- sample size(%) TRUE, -- block sample? 'FOR ALL COLUMNS, -- column spec 4, -- degree of parallelism 'DEFAULT, -- granularity TRUE -- cascade to indexes );

( ) ( ) ANALYZE DBA_TABLES ALL_TABLES USER_TABLES Distinct ANALYZE DBA_TAB_COL_STATISTICS ALL_TAB_COL_STATISTICS USER_TAB_COL_STATISTIC S

( ) Leaf Distinct Key Key Leaf Key Clustering Factor ANALYZE DBA_INDEXES ALL_ INDEXES USER_ INDEXES Histogram selectivity :

Histogram ANALYZE Histogram DBMS_STATS Histogram Histogram : Height-balanced Histogram F(val 1) F(val 2) F(val 3) Val 1 Val 2 Val 3 Val 4.. Val k-1 #total rows / #bucket Val 4 Val k F(val k) Bucket 1 Bucket 2.. Bucket n

Histogram selectivity Popular Value (P) Non-Popular Value (NP) P : bucket NP : bucket Histogram Histogram

SQL 5.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 SQL Trace TKPROF Dictionary View I/O Sort SQL SQL SQL View Base SQL CBO SQL

SQL 1. Optimizer Optimizer Optimizer Optimizer Hint 2. SQL Logic 5.2

SELECT empno, ename, job FROM emp WHERE empno = :b1; empno 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; 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)

( ) SELECT * FROM registrations WHERE appr_by BETWEEN :b1 AND :b2 AND status = :b3;. Cost (status, appr_by) cost = 14 (appr_by, status) cost = 15 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) 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);. 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) CONCATENATION. 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) OPTIMIZER_MODE = FIRST_ROWS Nested Loop 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 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 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 cardinality cls_status 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 class nested loop Join 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=9 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

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