SQL Tuning Business Development DB SQL - -SQL -SQL

0:00-0:50 SQL :00-2:00 2:00-3:30 3:30-4:20 SQL 4:30-5:20 5:30-7:20

SQL Tuning Business Development DB SQL - -SQL -SQL

SQL () H/W( ) CPU, Memory, Network ( ) SQL I/O ( ) SQL (2) ( ) ( )

SQL SQL SQL SQL ( ), ( /SQL ), ( ),. / / 2 / / 3 / /

SQL 3 SQL Tuning, (OLTP), SQL SQL SQL

SQL, CPU,, N/W - RAC(Real Application Cluster) - DB SQL,,,

SQL - SQL -,, - - Oracle Architecture Oracle Instance SMON SNPn LCKn RECO Snnn PMON DBWR Shared Pool SGA Database Buffer Cache Redo Log Buffer Dnnn Pnnn CKPT LGWR ARCH Server Server Processes User User Processes Oracle Database PGA PGA Archived Log Control Files Datafiles Redo Log Files Parameter File Password File

Shared Pool SQL Library Cache : SQL PL/SQL Row Cache : SQL Oracle Instance - SGA Shared Pool Library Cache Shared SQL Area (Shared Cursor Area) Shared PL/SQL Area Row Cache (Dictionary Cache) Shared SQL Area SQL SQL. / Shared Pool Shared SQL Area SELECT Context Area (Cursor) SELECT 2 Context Area (Cursor) User A User B User C SELECT SELECT 2 SELECT

PGA( ) () (UGA) Server Process PGA Stack space (MTS) Server Process Stack space User Session data PGA UGA Cursor state Sort area Shared Pool User Session data UGA Cursor state Sort area SQL PGA ( ) SQL Syntax Semantic - Data Dictionary - -,, - DB I/O -

SQL Execution Plan/Parse Tree SQL ( ) SQL????? SQL (PARSE) Shared SQL (SQL Get Hit), Syntax (SQL ) Semantic ( ) SQL SQL -, Data Dictionary (Recursive SQL) Query Rewrite SQL

SQL? (,, ) SQL,, SQL SQL PL/SQL Recursive SQL SQL SQL SQL SQL SQL SQL semantic Data Dictionary DDL DB Data Dictionary Sub Query, View Sub Query View SQL.

V$LIBRARYCACHE, V$SQLAREA HITRATIO 90% SELECT namespace, gets, gethitratio, pinhitratio, reloads, invalidations FROM V$LIBRARYCACHE ORDER BY gets DESC NAMESPACE GETS GETHITRATIO PINHITRATIO RELOADS INVALIDATIONS --------------- ----- ----------- ----------- -------- ------------- SQL AREA 6606.9394623.974660877 2 0 TABLE/PROCEDURE 638.949852.926080477 0 0 INDEX 739.97584889.975409836 0 0 CLUSTER 76.960227273.965957447 0 0 BODY 40.7.7794878 0 0 TRIGGER 26.73076923.73076923 0 0 OBJECT 0 0 0 PIPE 0 0 0 JAVA SOURCE 0 0 0 JAVA RESOURCE 0 0 0 JAVA DATA 0 0 0, / SQL PIN Recursive SQL Data Dictionary SELECT sql_text, version_count, loads, invalidations, parse_calls, sorts FROM V$SQLAREA WHERE parsing_user_id > 0 AND -- no SYS command_type = 3 -- SELECT Only ORDER BY sql_text SQL_TEXT VERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS SORTS ---------------------- ------------- ----- ------------- ----------- ----- select e.ename,d.dname 4 3 5 0 from emp e, dept d where e.empno=d.deptno

SQL BIND EXCUTE DML / INSERT, UPDATE, DELETE FETCH SELECT SQL SQL,,

SQL -SQL * SQL*Plus AutoTrace * SQL * Explain Plan * SQL Trace TKPROF SQL SQL SQL Plus Autotrace,, Oracle Enterprise Manager SQL (GUI ) EXPLAIN PLAN PLAN_TABLE SQL Trace TKPROF SQL TKPROF

SQL*Plus Autotrace PLUSTRACE SQL> conn / as sysdba SQL> @?/sqlplus/admin/plustrce.sql PLUSTRACE SQL> conn / as sysdba Connected. SQL> grant PLUSTRACE to scott; PLAN Table SQL> @?/rdbms/admin/utlxplan.sql Autotrace SQL> set autotrace on SQL> set autot off SQL> set autotrace traceonly SQL> set autotrace traceonly explain SQL> set autotrace traceonly statistics SET AUTOTRACE OFF ON TRACE[ONLY] SHOW AUTOTRACE EXPLAIN STATISTICS

SQL*Plus Autotrace SQL> set autotrace on SQL> select * from dept where deptno = 0; DEPTNO DNAME LOC ---------- -------------- ------------- 0 ACCOUNTING NEW YORK Execution Plan ------------------------------------------------------ 0 SELECT STATEMENT Optimizer=CHOOSE 0 TABLE ACCESS (FULL) OF 'DEPT' Statistics ------------------------------------------------------- 78 recursive calls 0 db block gets 27 consistent gets 7 physical reads 0 redo size 629 bytes sent via SQL*Net to client 655 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) rows processed OEM SQL

SQL Trace SQL Trace ( ), USER_DUMP_DEST TIMED_STATISTICS = true SQL Trace, SQL_TRACE = TRUE SQL> alter session set aql_trace = true; or SQL> EXECUTE dbms_session.set_sql_trace(true) v$session SID SERIAL# SQL> EXECUTE dbms_session.set_sql_trace_in_session 2 (sid, serial#, true);

SQL Trace USER_DUMP_DEST trace PKTROF $ tkprof ora92_ora_2820.trc scott_stat.txt explain=scott/tiger sys=no aggregate=no sort=execpu Sample TKPROF Output ************************************************************* SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno call count cpu elapsed disk query current rows ------- ------ ------- -------- ----- ------ -------- ----- Parse 0.05 0.05 2 0 0 Execute 0.00 0.00 0 0 0 0 Fetch 2 0.0 0.04 5 0 4 ------- ------ ------- -------- ----- ------ -------- ----- total 4 0.06 0.0 2 7 0 4 Misses in library cache during parse: Optimizer goal: CHOOSE Parsing user id: 62 (SCOTT) Rows Row Source Operation ------- --------------------------------------------------- 4 HASH JOIN (cr=5 r= w=0 time=48250 us) 4 TABLE ACCESS FULL EMP (cr=7 r=6 w=0 time=2349 us) 5 TABLE ACCESS FULL DEPT (cr=8 r=5 w=0 time=33294 us) ************************************************************* Parse 0.00 0.00 0 0 0 Execute 0.00 0.00 0 0 0 0 Fetch 2 0.0 0.00 0 5 0 4 ------- ------ ------- -------- ----- ------ -------- ----- total 4 0.0 0.00 0 6 0 4

PARSE SQL SQL EXECUTE INSERT, UPDATE, DELETE FETCH SELECT TKPROF,, Count CPU Elapsed Disk Query Current Rows CPU ( ) ( ) (Consistent Read) / SELECT (Current Read) INSERT,UPDATE,DELETE Fetch : SELECT Execute : INSERT, UPDATE, DELETE

TKPROF DML insert into dept values (80,'Human Resource','HQ') call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0.04 0.0 4 0 0 Execute 0.00 0.00 3 4 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.04 0.02 4 5 4 update dept set dname = 'Financial' where deptno = '80' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0.02 0.0 4 0 0 Execute 0.00 0.00 5 9 4 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.02 0.02 6 3 4 delete from dept where deptno = '80' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0.02 0.0 4 0 0 Execute 0.00 0.00 5 8 4 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.02 0.02 6 2 4 TKPROF OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ ------- -------- ----- ------ -------- ----- Parse 3 0.0 0.08 5 0 0 Execute 4 0.02 0.04 3 25 24 5 Fetch 4 0.02 0.05 30 0 28 ------- ------ ------- -------- ----- ------ -------- ----- total 3 0.4 0.8 5 60 24 33 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ ------- -------- ----- ------ -------- ----- Parse 20 0.02 0.02 0 0 0 0 Execute 40 0.0 0.0 0 0 0 0 Fetch 55 0.02 0.04 9 08 0 34 ------- ------ ------- -------- ----- ------ -------- ----- total 5 0.05 0.09 9 08 0 34

Parse > Parse=Execute=Fetch Parse=, Execute=Fetch=0 Parse=Execute=, Fetch=0 Fetch=0, Rows=200 SQL rows SQL 0 : select into SQL 0 20 Array Processing CPU Elapsed, (I/O ) Rows CPU (SQL ) rows SQL disk, query, current. SQL SQL*Plus Autotrace EXPLAIN PLAN OEM SQL SQL Trace TKPROF

,, - - - : 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. (Composite Index) B* Tree Bitmap Reverse Key Descending Function-based Domain Indexes

B* Tree Root Ix <= < Ix < Ix <= Key IOT Branch ROWID Leaf Key B* Tree () WHERE PK = ' ' AND PK2 = 200 Root 2 3 4

B* Tree (2) WHERE PK >= ' ' AND PK <= ' ' :. Root 2 3 4 5 (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 752 Ward 7566 Jones DEPTNO 50 0 30 30 e.ename Smith Allen Ward Jones d.dname BD R&D SALES SALES DEPTNO 20 0 30 50 DNAME HR R&D SALES BD...

EMPNO ENAME 7369 Smith 7499 Allen 752 Ward 7566 Jones DEPTNO 50 0 30 30 Smith 4 ( ) e.ename d.dname Smith BD Allen R&D Ward SALES Jones SALES BD 4 2 3 3 DEPTNO 20 0 30 50 DNAME HR R&D SALES BD... 2 ( ) DEPT EMPNO ENAME 7369 Smith 7499 Allen 752 Ward 7566 Jones DEPTNO Smith 50 0 30 30 4 ( ) e.ename d.dname Smith BD Allen R&D Ward SALES Jones SALES BD 2 2 2 2 DEPTNO DNAME 0 20 30 50 20 0 30 50 HR R&D SALES BD...... 8+α ( ) Nested Loop Join (Driving) ( ) (?) EMPNO ENAME DEPTNO 7369 Smith 50 7499 Allen 0 752 Ward 30 7566 Jones 30 e.ename d.dname Smith BD Allen R&D Ward SALES Jones SALES DEPTNO DNAME 0 20 30 50 20 0 30 50 HR R&D SALES BD...... Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=RULE 0 NESTED LOOPS 2 TABLE ACCESS (FULL) OF 'EMP' 3 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 4 3 INDEX (UNIQUE SCAN) OF 'DEPTNO_PK' (UNIQUE) * DEPT

SQL : Nested Loop 4) 5) ( SELECT STATEMENT ) ( ). WHERE 4). NESTED LOOPS 5) SELECT. 3) ROWID DEPT. ) SCOTT.EMP TABLE ACCESS(FULL) 3) SCOTT.DEPT TABLE ACCESS(BY INDEX ROWID) ) EMP. 2) SCOTT.DEPT_PK INDEX(UNIQUE SCAN) 2) B* DEPT_PK ROWID. DEPT EMP Nested Loop (Optimizer) RBO(Rule Based Optimizer) Sort Merge Join 0 SELECT STATEMENT Optimizer=RULE 0 MERGE JOIN 2 SORT (JOIN) 3 2 TABLE ACCESS (FULL) OF 'DEPT' 4 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'EMP' CBO(Cost Based Optimizer) Hash Join 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=7 Bytes=323) 0 HASH JOIN (Cost=5 Card=7 Bytes=323) 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=7 Bytes=36) 3 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=44)

Sort Merge Join MERGE JOIN 5) 6). ROWID M Join ROWID SELECT STATEMENT Allen AAAB 0 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 0 AABB 30 AAFF 30 AAAC 50 2 3 DEPTNO ROWID 0 BBBA 20 BBAA 30 BBAF 50 BBAC... R&D HR SALES BD ) 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) ) HASH (DEPT) 2) FULL ACCESS(EMP) 4) HASH JOIN + ROWID HASH DEPTNO ENAME SALES (30,BBAF) 3)ƒ h (50) = 2 00 50 Smith.. 0 Allen 30 Ward HR (20,BBAA) ƒ h (0) = 4 BD (50,BBAC) 22 30 Jones ) 2).. 33 R&D (0,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 ) 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) SELECT e.ename, d.dname FROM DEPT d, EMP e WHERE e.deptno = d.deptno ORDER BY e.ename; 6) SELECT STATEMENT 5) NESTED LOOPS 3) 4) SCOTT.EMP TABLE ACCESS(BY INDEX ROWID) SCOTT.DEPT TABLE ACCESS(BY INDEX ROWID) ) B* ) 2) ENAME_IDX ROWID. SCOTT.ENAME_IDX INDEX(FULL_SCAN) * ENAME NOT NULL SCOTT.DEPT_PK INDEX(UNIQUE SCAN)

- - - - Nested Loop (Filtering) DEPT.DNAME Unique Range Scan SELECT e.ename, d.dname FROM DEPT d, EMP e WHERE e.deptno = d.deptno AND e.ename BETWEEN 'ALLEN' AND 'KING' AND d.dname BETWEEN 'HR' AND 'SALES', 2) RANGE SCAN (EMP) by ENAME_IDX EMP TABLE ENAME_IDX 3, 4) UNIQUE SCAN (DEPT) by DEPT_PK UNIQUE SCAN DEPT_PK DEPT TABLE 2 3 2) * : EMP.ENAME DEPT.DEPTNO(PK) SCOTT.EMP TABLE ACCESS (BY INDEX ROWID) 6) SELECT STATEMENT 5) 3 NESTED LOOPS 4) SCOTT.DEPT TABLE ACCESS (BY INDEX ROWID) 2 ) 2 3) RANGE SCAN SCOTT.ENAME_IDX INDEX(RANGE SCAN) SCOTT.DEPT_PK INDEX(UNIQUE SCAN)

Merge Join Nested Loop, 6) SELECT e.ename, d.dname FROM DEPT d, EMP e WHERE e.deptno = d.deptno AND e.ename BETWEEN 'ALLEN' AND 'KING' AND d.dname BETWEEN 'HR' AND 'SALES', 2) RANGE SCAN (EMP) by ENAME_IDX EMP TABLE ENAME_IDX 3, 5) RANGE SCAN (DEPT) by DNAME_IDX DNAME_IDX 2 3 DEPT TABLE 2 3 * : EMP.ENAME DEPT.DNAME SELECT STATEMENT 5) SCOTT.DEPT TABLE ACCESS(BY INDEX ROWID) 4) NESTED LOOPS 2) 3) SCOTT.EMP TABLE ACCESS(BY INDEX ROWID) ) 2 SCOTT.ENAME_IDX INDEX(RANGE SCAN) SCOTT.DNAME_IDX INDEX(RANGE SCAN) 3 From FROM DEPT.DNAME. 6) SELECT e.ename, d.dname FROM EMP e, DEPT d WHERE e.deptno = d.deptno AND e.ename BETWEEN 'ALLEN' AND 'KING' AND d.dname BETWEEN 'HR' AND 'SALES' 2 3 * : EMP.ENAME SELECT STATEMENT 5) SCOTT.EMP TABLE ACCESS(BY INDEX ROWID), 2) RANGE SCAN (DEPT) by DNAME_IDX DEPT TABLE DNAME_IDX ENAME_IDX 3 2 3, 5) RANGE SCAN (EMP) by ENAME_IDX EMP TABLE 4) NESTED LOOPS 2) 3) SCOTT.DEPT TABLE ACCESS(BY INDEX ROWID) ) 3 SCOTT.DNAME_IDX INDEX(RANGE SCAN) SCOTT.ENAME_IDX INDEX(RANGE SCAN) 2

CBO CBO, RBO COST. SELECT e.ename, d.dname FROM EMP e, DEPT d WHERE e.deptno = d.deptno AND e.ename BETWEEN 'ALLEN' AND 'KING' AND d.dname BETWEEN 'HR' AND 'SALES', 2) RANGE SCAN (EMP) by ENAME_IDX EMP TABLE ENAME_IDX 3, 5) RANGE SCAN (DEPT) by DNAME_IDX DNAME_IDX DEPT TABLE * : EMP.ENAME DEPT.DNAME 6) SELECT STATEMENT 5) SCOTT.DEPT TABLE ACCESS(BY INDEX ROWID) 4) NESTED LOOPS 2) 3) SCOTT.EMP TABLE ACCESS(BY INDEX ROWID) 2 3 2 ) SCOTT.ENAME_IDX INDEX(RANGE SCAN) 2 3 3) SCOTT.DNAME_IDX INDEX(RANGE SCAN) 3 RBO : between equal Range SELECT e.ename, d.dname FROM EMP e, DEPT d WHERE e.deptno = d.deptno AND e.ename = 'ALLEN' AND d.dname BETWEEN 'HR' AND 'SALES' 2 3 * : EMP.ENAME DEPT.DNAME 6) SELECT STATEMENT 5) SCOTT.DEPT TABLE ACCESS(BY INDEX ROWID), 2) RANGE SCAN (EMP) by ENAME_IDX EMP TABLE ENAME_IDX 2 3, 5) RANGE SCAN (DEPT) by DNAME_IDX DNAME_IDX 3 DEPT TABLE 4) NESTED LOOPS 2) 3) SCOTT.EMP TABLE ACCESS(BY INDEX ROWID) ) 2 SCOTT.ENAME_IDX INDEX(RANGE SCAN) SCOTT.DNAME_IDX INDEX(RANGE SCAN) 3

N Sort Merge Join * : (RBO ) Sort Merge Join SELECT e.ename, d.dname FROM DEPT d, EMP e WHERE e.deptno = d.deptno AND e.ename BETWEEN 'ALLEN' AND 'KING' AND d.dname BETWEEN 'HR' AND 'SALES' Allen Jones 2 ROWID AAAB AABB M Join 0 30 ROWID BBBA BBAF 5) MERGE JOIN 2) SORT JOIN(EMP) 4) SORT JOIN(DEPT) ALLEN KING (HR SALES ) ROWID DEPTNO DEPTNO ROWID Allen Jones AAAB AABB 2 0 30 2 0 20 30... 3 R&D SALES BBBA BBAA BBAF 2 3 R&D HR SALES 2) SORT(JOIN) ) 6) SELECT STATEMENT 2 5) MERGE JOIN 4) SORT(JOIN) 3) SCOTT.EMP SCOTT.DEPT TABLE ACCESS(FULL) TABLE ACCESS(FULL) 3 Hash Join (CBO ) Hash : 2 FULL ACCESS Hash, Where ROW 3 * : 4) SELECT STATEMENT SELECT e.ename, d.dname FROM EMP e, DEPT d WHERE e.deptno = d.deptno AND e.ename BETWEEN 'ALLEN' AND 'KING' 2 AND d.dname BETWEEN 'HR' AND 'R&D' 3 3) 3 ) HASH (EMP) 2) FULL ACCESS(DEPT) ALLEN KING HR SALES Hash 3)ƒ HASH JOIN h (20) = 2 + ROWID HASH DEPTNO DNAME Jones (30,AABB) 00 O,X3 20 HR.. 0 R&D X. 30 SALES 22 X 50 BD 2.. 33 ) 2) Allen (0,AAAB) 44 O,, O3O. 55 SCOTT.EMP SCOTT.DEPT 2 HASH, MOD(x,6) TABLE ACCESS(FULL) TABLE ACCESS(FULL)

RBO(Rule Based Optimizer) ( ) CBO(Cost Based Optimizer) RBO RBO Nested Loop RBO. FIRST_ROWS CBO RBO Nested Loop Join

Sort Merge Join (Sort) (Merge) ALL_ROWS : ( ) RBO :. CBO : Hash Join ALL_ROWS, Merge Hash Join Hash Table FULL ACCESS Hash CBO : cardinality, CBO RBO

UGA (SORT_AREA_SIZE) SORT Run Merge, SORT_AREA_SIZE PGA or SGA Sort run Sort run 2 Server process X Temporary segment Sort run 2 WORKAREA_SIZE_POLICY Manual : *_AREA_SIZE Auto : PGA_AGGREGATE_TARGET Oracle 9i HASH_JOIN_ENABLED HASH_AREA_SIZE HASH_MULTIBLOCK_IO_COUNT SORT_AREA_SIZE SORT_AREA_RETAINED_SIZE SORT_MULTIBLOCK_READ_COUNT

Nested Loop, Hash, Sort Merge 0:00-0:50 SQL :00-2:00 2:00-3:30 3:30-4:20 SQL 4:30-5:20 5:30-7:20

SQL - SQL * * * SQL - RBO RBO.. 2., / 3., 4., FROM

: WHERE 4 OTN. Unique Scan Range Scan Full Table Scan Range Scan Full Table Scan Full Table Scan

( ) : ( ( ) ) : : 4 : 2 3, FROM 7 6 : 5...... 4..,,.,. Unique Index = * ROWID. 2.. 2. FROM

[ ] SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'ALLEN' 2 AND d.dname BETWEEN 'HR' AND 'SALES' 3 : : : 6) EMP.EANME EMP. : 6) EMP.ENAME EMP. SELECT STATEMENT SELECT DEPT.DNAME STATEMENT DEPT.DNAME DEPT.DEPTNO DNAME_IDX. 5) SCOTT.DEPT TABLE ACCESS(BY INDEX ROWID) 4) NESTED LOOPS :, SQL. FROM EMP (2), (,3). 5) SCOTT.DEPT TABLE ACCESS(BY INDEX ROWID) e.deptno = d.deptno 3 d.dname BETWEEN 4) 'HR' AND 'SALES' NESTED LOOPS 2) 3) SCOTT.EMP TABLE ACCESS(BY INDEX ROWID) 3) SCOTT.DNAME_IDX INDEX(RANGE SCAN) 2) 3) SCOTT.EMP TABLE ACCESS(BY INDEX ROWID) 3) SCOTT.DEPTNO_IDX INDEX(RANGE SCAN) ) SCOTT.ENAME_IDX INDEX(RANGE SCAN) 3 d.dname BETWEEN 'HR' AND 'SALES' ) SCOTT.ENAME_IDX INDEX(RANGE SCAN) e.deptno = d.deptno 2 e.ename = 'ALLEN' 2 e.ename = 'ALLEN' * PK, Unique

(=) IN LIKE A% BETWEEN LIKE %A leaf block. Highwatermark,.,., ORDER BY.

Range Scan DML BitMap IOT WHERE (~5%, 5% ) 2.

,.,. AND SQL SQL (Upset) SQL. SQL

SQL Unique 5% SQL SQL DISTINCT, ORDER BY, GROUP BY IN EXISTS( ) FROM SQL NULL LIKE

SQL SELECT ename FROM emp WHERE sal * 2. > 950 SELECT ename FROM emp WHERE sal > 950 / 2. SELECT ename FROM emp WHERE to_char(hiredate, DDMMYY ) = 250884 SELECT ename FROM emp WHERE hiredate = to_date( 250884, DDMMYY ) create index ename_fidx on emp substr(ename,,) ; >> SELECT ename FROM emp WHERE substr(ename,,) = S SELECT * FROM emp WHERE hiredate = 4-JAN-85 SELECT * FROM emp WHERE hiredate = to_date( 7-0-22, YY/DD/DD ) SELECT * FROM emp WHERE empno = 7936 SELECT * FROM emp WHERE empno = to_number( 7936 ); SQL ( ) ( )

NULL NULL SQL SELECT ename FROM emp WHERE comm IS null SELECT ename FROM emp WHERE comm IS NOT null SELECT ename FROM emp WHERE ename > ; SELECT ename FROM emp WHERE comm >= 0 NULL SQL SELECT ename FROM emp WHERE deptno!= 30 SELECT ename FROM emp WHERE deptno < 30 OR deptno > 30 SELECT ename FROM emp WHERE NOT EXISTS ( SELECT '' FROM emp WHERE empno = 30) SELECT ename FROM emp MINUS SELECT ename FROM emp WHERE empno = 30 ( )

LIKE SELECT * FROM emp WHERE ename LIKE S% ; SELECT * FROM emp WHERE ename LIKE %S% ; LKE SQL SELECT * FROM document WHERE contains(doc, oracle and database ) > Oracle Text SQL RBO SQL

- - Bitmap Bitmap Join Function-Based Reverse Key Index Organized Table Cluster Materialized View Query Rewrite Temporary Table

Bitmap File 3 Block 0 CREATE BITMAP INDEX...; Block Block 2 ROWID ROWID Bitmap <Blue, 0.0.3, 2.8.3, 0000000000000> <Green, 0.0.3, 2.8.3, 000000000000000> <Red, 0.0.3, 2.8.3, 00000000000000> <Yellow, 0.0.3, 2.8.3, 000000000000000> Bitmap Cardinality AND/OR WHERE col = 'A' AND col2 = 'B' A B A and B A or B 0 0 0 0 Bit-wise DSS 0 0 0 0 not A 0 0

Bitmap Join Sales Customers CREATE BITMAP INDEX cust_sales sales_bji ON sales(c.region region) FROM sales s, customers c WHERE c.cust cust_id = s.cust cust_id; <East,.2.3, 0.8000.3, 0000000000000> <Central,.2.3, 0.8000.3, 000000000000000> <West,.2.3, 0.8000.3, 00000000000000> SELECT SUM(s.cost) FROM sales s, customers c WHERE s.cust_id = c.cust_id AND c.region = 'East'; query sales. Bitmap Join Dimension Star schema query Bitmap Query Bitmap

Function-Based Function-Based create index employees_upper_first_name_fix on employees(upper(first_name)); Function-Based Query select * from employees where upper(first_name) = 'BETTINA'; Function-Based : upper() : substr() NLS : nlssort(col, NLS_SORT=FRENCH )

Reverse Key create index employees_employee_id_rix on employees(employee_id) REVERSE...; Reverse Key KEY KEY ROWID ----- ------------------- 257 257 0000000F.0002.000 2877 2877 0000000F.0006.000 4567 4567 0000000F.0004.000 6657 6657 0000000F.0003.000 8967 8967 0000000F.0005.000 9637 9637 0000000F.000.000 9947 9947 0000000F.0000.000............ Employees EMPLOYEE_ID LAST_NAME...... ----------- --------- 7499 7499 ALLEN 7369 7369 SMITH 752 752 WARD WARD...... 7566 7566 JONES 7654 7654 MARTIN 7698 7698 BLAKE 7782 7782 CLARK.................. Reverse Key Right-growing,, Reverse Key, Right-growing leaf Range scan ( ) Leaf

Index Organized Table (IOT) IOT CREATE TABLE... ORGANIZATION INDEX... ROWID Primary Key Primary Key Row IOT B*Tree ( ) Primary Key ROWID IOT ROW ID 2 UPDATE,

Cluster ORD_NO PROD QTY... ----- ------ ------ 0 A402 20 02 A209 02 G7830 20 02 N9587 26 0 A5675 9 0 W0824 0 ORD_NO ORD_DT CUST_CD ------ ------ ------ 0 05-JAN-97 R0 02 07-JAN-97 N45 Cluster Key (ORD_NO) 0 ORD_DT CUST_CD 05-JAN-97 R0 PROD QTY A402 20 A5675 9 W0824 0 02 ORD_DT CUST_CD 07-JAN-97 N45 PROD QTY A209 G7830 20 N9587 26 orders order_item Cluster orders order_item Cluster DML

Materialized View SQL ( ) Query rewrite. Materialized View CREATE MATERIALIZED VIEW cls_summ AS select cl.class_id, co.short_name, cl.start_date, l.city, count(r.stud_id) as tot_reg from classes cl, courses co, locations l, registrations r where cl.loc_id = l.loc_id and cl.crs_id = co.crs_id and cl.class_id = r.class_id group by cl.class_id, co.short_name, cl.start_date, l.city;

Materialized View Query Rewrite select cl.class_id, co.short_name, cl.start_date, l.city, count(r.stud_id) as tot_reg from classes cl, courses co, locations l, registrations r where cl.loc_id = l.loc_id and cl.crs_id = co.crs_id and cl.class_id = r.class_id and l.city = 'San Francisco' group by cl.class_id,co.short_name, cl.start_date, l.city having count(r.stud_id) < 0; SQL> select class_id, short_name 2, start_date, city, tot_reg 3 from cls_summ 4 where city = 'San Francisco' 5 and tot_reg < 0; cls_summ summ full table scan Query Rewrite Query Rewrite Optimizer Query Rewrite QUERY_REWRITE_ENABLED QUERY_REWRITE_INTEGRITY REWRITE NOREWRITE

Temporary Temporary Temporary SORT_AREA temporary tablespace dictionary. Temporary CREATE OR REPLACE VIEW sales_detail AS SELECT cu.cust_last_name, cu.cust_email, cu.cust_income_level, pr.prod_name, ch.channel_desc, pm.promo_name, sa.amount_sold FROM customers cu, products pr, channels ch, promotions pm, sales sa WHERE sa.cust_id = cu.cust_id AND sa.prod_id = pr.prod_id AND sa.channel_id = ch.channel_id AND sa.promo_id = pm.promo_id AND sa.time_id BETWEEN 0-DEC-999 and 3-DEC-999 ;

Temporary ( ) CREATE GLOBAL TEMPORARY TABLE sales_detail_temp ( cust_last_name VARCHAR2(50), cust_income_level VARCHAR2(30), cust_email VARCHAR2(30), prod_name VARCHAR2(50), channel_desc VARCHAR2(20), promo_name VARCHAR2(50), amount_sold NUMBER ) ON COMMIT PRESERVE ROWS; INSERT INTO sales_detail_temp SELECT * FROM sales_detail; SQL SQL

0:00-0:50 SQL :00-2:00 2:00-3:30 3:30-4:20 SQL 4:30-5:20 5:30-7:20