SQL Tuning Business Development DB SQL - -SQL -SQL

Size: px

Start display at page:

Download "SQL Tuning Business Development DB SQL - -SQL -SQL"

새롬 봉
6 years ago
Views:

1 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

2 SQL Tuning Business Development DB SQL - -SQL -SQL

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

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

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

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

7 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

8 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

9 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 -

10 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

11 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.

12 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 TABLE/PROCEDURE INDEX CLUSTER BODY TRIGGER OBJECT PIPE JAVA SOURCE JAVA RESOURCE 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 from emp e, dept d where e.empno=d.deptno

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

14 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

15 SQL*Plus Autotrace PLUSTRACE SQL> conn / as sysdba PLUSTRACE SQL> conn / as sysdba Connected. SQL> grant PLUSTRACE to scott; PLAN Table 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

Statistics ------------------------------------------------------- 78 recursive calls 0 db block gets 27 consistent gets 7 physical reads 0 redo size 629

16 SQL*Plus Autotrace SQL> set autotrace on SQL> select * from dept where deptno = 0; DEPTNO DNAME LOC ACCOUNTING NEW YORK Execution Plan SELECT STATEMENT Optimizer=CHOOSE 0 TABLE ACCESS (FULL) OF 'DEPT' Statistics 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

17 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);

18 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 Execute Fetch total Misses in library cache during parse: Optimizer goal: CHOOSE Parsing user id: 62 (SCOTT) Rows Row Source Operation 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 Execute Fetch total

19 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

20 TKPROF DML insert into dept values (80,'Human Resource','HQ') call count cpu elapsed disk query current rows Parse Execute Fetch total update dept set dname = 'Financial' where deptno = '80' call count cpu elapsed disk query current rows Parse Execute Fetch total delete from dept where deptno = '80' call count cpu elapsed disk query current rows Parse Execute Fetch total TKPROF OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows Parse Execute Fetch total OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows Parse Execute Fetch total

21 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

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

23 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

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

25 B* Tree (2) WHERE PK >= ' ' AND PK <= ' ' :. Root (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 e.ename Smith Allen Ward Jones d.dname BD R&D SALES SALES DEPTNO DNAME HR R&D SALES BD...

26 EMPNO ENAME 7369 Smith 7499 Allen 752 Ward 7566 Jones DEPTNO Smith 4 ( ) e.ename d.dname Smith BD Allen R&D Ward SALES Jones SALES BD DEPTNO DNAME HR R&D SALES BD... 2 ( ) DEPT EMPNO ENAME 7369 Smith 7499 Allen 752 Ward 7566 Jones DEPTNO Smith ( ) e.ename d.dname Smith BD Allen R&D Ward SALES Jones SALES BD DEPTNO DNAME HR R&D SALES BD α ( ) Nested Loop Join (Driving) ( ) (?) EMPNO ENAME DEPTNO 7369 Smith Allen Ward Jones 30 e.ename d.dname Smith BD Allen R&D Ward SALES Jones SALES DEPTNO DNAME HR R&D SALES BD Execution Plan 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

27 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)

28 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 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) = Smith.. 0 Allen 30 Ward HR (20,BBAA) ƒ h (0) = 4 BD (50,BBAC) Jones ) 2).. 33 R&D (0,BBBA) 44 SCOTT.DEPT SCOTT.EMP. 55 TABLE ACCESS(FULL) TABLE ACCESS(FULL)

29 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.

30 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)

31 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

32 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) ) 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

33 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 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) Allen (0,AAAB) 44 O,, O3O. 55 SCOTT.EMP SCOTT.DEPT 2 HASH, MOD(x,6) TABLE ACCESS(FULL) TABLE ACCESS(FULL)

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

35 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

36 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

37 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

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

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

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

41 [ ] 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

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

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

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

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

46 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 ) = SELECT ename FROM emp WHERE hiredate = to_date( , 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( , YY/DD/DD ) SELECT * FROM emp WHERE empno = 7936 SELECT * FROM emp WHERE empno = to_number( 7936 ); SQL ( ) ( )

47 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 ( )

48 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

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

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

51 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, , > <Central,.2.3, , > <West,.2.3, , > 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

52 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 )

53 Reverse Key create index employees_employee_id_rix on employees(employee_id) REVERSE...; Reverse Key KEY KEY ROWID F F F F F F F Employees EMPLOYEE_ID LAST_NAME ALLEN SMITH WARD WARD JONES MARTIN BLAKE CLARK Reverse Key Right-growing,, Reverse Key, Right-growing leaf Range scan ( ) Leaf

54 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,

55 Cluster ORD_NO PROD QTY A A G N A W ORD_NO ORD_DT CUST_CD JAN-97 R JAN-97 N45 Cluster Key (ORD_NO) 0 ORD_DT CUST_CD 05-JAN-97 R0 PROD QTY A A W ORD_DT CUST_CD 07-JAN-97 N45 PROD QTY A209 G N orders order_item Cluster orders order_item Cluster DML

56 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;

57 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

58 Temporary Temporary Temporary SORT_AREA temporary tablespace dictionary. Temporary CREATE OR REPLACE VIEW sales_detail AS SELECT cu.cust_last_name, cu.cust_ , 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 ;

59 Temporary ( ) CREATE GLOBAL TEMPORARY TABLE sales_detail_temp ( cust_last_name VARCHAR2(50), cust_income_level VARCHAR2(30), cust_ 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

60 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

Jerry Held

Jerry Held ,, - - - : 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