![[2015 07-06-???] Oracle ?? ??? ? ?? ??? 4](https://cdn.slidesharecdn.com/ss_thumbnails/2015-07-06-oracle4-150702090606-lva1-app6891-thumbnail.jpg?width=560&fit=bounds)
![[2015-06-12] Oracle ?? ??? ? ?? ??? 1](https://cdn.slidesharecdn.com/ss_thumbnails/2015-06-12oracle1-150615000001-lva1-app6892-thumbnail.jpg?width=560&fit=bounds)
![[2015-06-26] Oracle ?? ??? ? ?? ??? 3](https://cdn.slidesharecdn.com/ss_thumbnails/2015-06-26oracle3-150623063728-lva1-app6891-thumbnail.jpg?width=560&fit=bounds)
![[25D2S02]_Amazon Q Developer ?? AI????? ?????? ???????? ????????? ?????????.pdf](https://cdn.slidesharecdn.com/ss_thumbnails/25d2s02amazonqdeveloperai-250403123345-74f273c6-thumbnail.jpg?width=560&fit=bounds)
![[25D2S09]_Amazon Nova????? ????????? Amazon SageMaker HyperPod ??????????.pdf](https://cdn.slidesharecdn.com/ss_thumbnails/25d2s09amazonnovaamazonsagemakerhyperpod-250403123346-184ebeaa-thumbnail.jpg?width=560&fit=bounds)
More Related Content
Similar to Fundamentals of Oracle SQL (20)
Recently uploaded (8)
![[25D3T2S07]_What's new_MySQL 8.4? ??.pdf](https://cdn.slidesharecdn.com/ss_thumbnails/25d3t2s07mysql8-250407140508-b1fe3475-thumbnail.jpg?width=560&fit=bounds)
![[25D2S07]_Amazon Nova??? ??????? ????????? RAG ??????????.pdf](https://cdn.slidesharecdn.com/ss_thumbnails/25d2s07amazonnovarag-250403123346-b8e0a0a1-thumbnail.jpg?width=560&fit=bounds)
![[25D2S08]_Amazon Bedrock?? SageMaker??? ???????? LLM ?????????? ??? ?????????...](https://cdn.slidesharecdn.com/ss_thumbnails/25d2s08amazonbedrocksagemakerllm-250403123343-76e0215a-thumbnail.jpg?width=560&fit=bounds)
![[25D2S04]_Amazon Nova?? Bedrock??? ????????? ?????????? ????????? ????? ?????...](https://cdn.slidesharecdn.com/ss_thumbnails/25d2s04amazonnovabedrock-250403123346-05c18ffa-thumbnail.jpg?width=560&fit=bounds)
Fundamentals of Oracle SQL
- 1. Fundamentals of Oracle SQL Oracle doesn’t know the data the way you know it. ? ? ? mail: naivety1@naver.com 2011.06.24
- 2. ? ? ? ????? ??? SQL ? SQL Tuning ?? ? ?? ?? ? Join Method ?? (NL Join, SM Join, Hash Join) ? ????? ?? ? ?????? ??
- 3. Relational DBMS? ?? ? 2? ???? ?? Royal Air Force?? ?? ?? ?? ???? ??? ??? ???? ??? ? ?? ????. ?????? ? ??? ?? ??? ?????? ?????? ??? ??? ??? ??? ???? 10? ?? ? ??? ?? ?? ?? ?? ? ???? ?? ?? ? ??? ??? ??? ?? ????? ?. - E.F.CODD The Relational Model for Database Management, 1990 – ? 1970 Dr. E.F. Codd. “A Relational Model for Large Shared Data Banks” RDB ??? ?? ?1976 Peter Chen. “The Entity Relationship Model: Toward a Unified View of Data” ?? ??(ER) ?? ?? ? 1979 ??? ?? DBMS ORACLE ?? ? 1983 IBM DB2 ?? ? 1986 ANSI ??????? ??? ??? SQL ?? ??
- 4. ?????? ?? ?? (1) ??? (Hierarchical) ?????? ? ???? ??? ??, ?? ??? ????? ?? ? ?:? ??? ??? ?? ?? ??? ? ??? ??? ????? ???? ??? ??? ?? ? ???? ?? ?? ? ?? ???? ? ???? ??? (2) ?????(Network) ?????? ? ??? ??? ?? ? Owner-Member? ?? ? ? ???? ? ? ? ?? : 3?? ???? ???? ? ????? Set? ?? (3) ???(Relational) ?????? ? ??? ??? ??? ??? ?? ????? ???? ?? ?? ? ?:? ??? ??? ?? ?? ?? ? ?? ??? ???? ??? ??, ???? ??? ???? ??? ? ? ??? ?? ?? ? ???? ??? ???? ??? ?? ? ??? ??? ??(set)??? ???? ? ???? ??(Query)? ???? ???
- 5. ????? ?? ??? ?? ??? ??? ??? ?? ?? ?? ? ?? ?? ?? ? ???? ?? ??? ?? 1960?? ~ 1980?? ?? 1980?? ?? ~ 1990?? ?? 1990?? ?? ~ 2000?? 1990?? ?? ~ ?? ?? ?? ?? ????? ?? ???? ??? 3?? ?? ??/??? DB ????? ?? ?????/?? ???? ??? 4?? ?? ??? DB ?? CASE ??? ?? ??? SW?? ???? ?? ??? ??? DB UML ???? ?? ?? ???????? ??? ??? DB UML ?? ?? ???? ?????(DFD) ??? ???? ??? ?? ?????(ERD) ???? ??? Waterfall ?? ?? ?? Use Case Diagram ??/?? ?? ??/ ?? ?? ???? Class Diagram ???? ??, ??, ?? ???? ?? ??? ?? ??? ??? ??? ???? ??? USE CASE ??? ?? ??? USE CASE ?? ? ???? ???
- 6. ????(???????) ???? DATABASE?? ???? ??,??,??, ???? SQL? ????. ERD, Entity/Attribute???, Partition?? ?? ?? ? ?? SQL? ????. (?????)
- 7. Role & Responsibility ? Data Architect Business Rule ??, ??? ?? ?? ??? ??? ??? ? Database Administrator DBMS??, ??? ?? ??, Index/Partition ??, ???? ??, ACCESS PATH ?? ? Application ??? SQL???? ???? ?? ??? ????? ??? ?? SQL? ??
- 8. DBMS ? Database Management System ? ??? ????? ???? ?? ? ?? ?? ??(Database) ? ??? ?? ???? ???? ???? ????? ???? ??? ? ?? ?? ? ??? ?? ??? ?????(DBMS) ? ???.
- 9. Oracle Architecture - ???? SQL? ?? ?? ???? DISK I/O? ????. - SQL? ?? 1)??????? Shared Pool ?? 2) ??? ??????? dictionary ??? 3) ????? ? ?? 4) ?? ???? ??? ??????? ???? ?????. 5)???????? ???? client? ?? ?? ?? ???? DBWR SMON PMON CKPT LGWR ?? ??? ???? ?? ???? PGA ?? ?? ??? ?? ?????? ?? ?? ?? SGA ?? ?? ?? ?? ? ??? ???? ?? ????? ?? ?????? ?? ?? Java ? ??? ? ???? ?? ????? ?? ??
- 10. SQL ?? Parse Bind Execute Fetch SQL? ?? ????? Optimize Bind ???? ?? ??? ??(Histogram ????) ?? ???? ?? ??? ?? ??? ???? ??? ???? ?? ?? ?? ????? ?? DML?? ?? ??? ?? ?? ??? ??/?? ?? ??? ?? ???? ?? ?? INSERT, UPDATE, DELETE? ? ???? ?? Execute ???? ???? ??, array ?? User process? ??? SQL? Server process ? PGA? ?? ??
- 11. Soft parse vs. Hard parse ?? SQL(pcode)? Shared pool? ? ??? SQL?? ??? ?? Object? Column? ?? Dictionary Validation ? ??, ?? ?? CBO or RBO, Optimal execution plan ?? Shared pool? parse??? ?? SQL Execute Check syntax Search shared SQL area Search data dictionary Optimizer Save execution plan Parse Execute Soft parse Hard parse
- 14. Query Transformer ?? Select department_id, salary from employee Where (department_id = 60 and salary = 4) or (department_id = 60) Select department_id, salary from employee Where department_id = 60;
- 15. Warming Up with some stretches ? ACCESS PATH - Index Stretegy, partition ? JOIN METHOD - NL JOIN, HASH JOIN, SORT MERGE JOIN, HASH_SJ, HASH_AJ…… ? JOIN ORDER - emp ? dept, dept ? emp ? Transformer (Logical Optimizer) - Huristic Transformer / Cost Based Query Transformer ? ETC - null, not null, buffer pinning, OWI, filtering optimization, function, case, decode, nvl, pagenation, ….
- 16. SQL ?? ?? ? Block I/O? ??? ?? ? ??? ???? ??? ??
- 17. DATA Modeling & Business Rule INDEX?? employees : (JOB_ID) orders : (EMPLOYEE_ID, ORDER_DATE) ? SQL? ??? ??? ?? ???
- 18. DATA Modeling & Business Rule ? ???? Employees ???? job_id=‘J04’??? ????(order_date)? 2012?01?01? ?? 2012?06?01? ?? ?? ???? (order_status)? ‘10’? employee_id? last_name? ?????. ? Data modeling? Business Rule? ??? ??? SQL ?? ?? ???.
- 19. DATA Modeling & Business Rule ????? 1. Employees ????? employee_id? unique??. 2. Orders ??? ???(N?)? ?? join? ??? ??. 3. ???? ???? ??? ??? ??? Join? ????.
- 20. DATA Modeling & Business Rule ???? 11g??? Transformer? ?? ?? ??? SQL? ????? ?? ?. ???? ???(??)? SQL? ? ???? ??.
- 21. What is your DB management level? ? Level 1 DB? ??? ???? ????. ? Level 2 ????? ???? ??. ? Level 3 DB? ??? ???? ????. - ACCESS PATH? ????. ? ????? ????.
- 22. Application ???? vs. SQL ? ????? ??? ???. ? SQL? ????? ??? ?? ??. (10053 Trace file? ?? ? ? ??.) ? ?????? ????, ??? ?? ??.
- 23. ?? ?? ? DBMS_XPLAN.DISPLAY_CURSOR ? DBMS_XPLAN.DISPLAY_AWR ? SQL_MONITOR ? 10046 TRACE ? 10053 TRACE
- 24. DBMS_XPLAN.DISPLAY_CURSOR ? Grant select on v_$session to scott; ? Grant select on v_$sql_plan_statistics_all to scott; ? Grant select on v_$sql to scott; ?? ?? 1. Alter session set statistics_level = all; 2. /*+ gather_plan_statistics */
- 25. ?? ?? select /*+ gather_plan_statistics */ c4, c5, c6 from subquery_t1 t1 where c6 >= :b1 and c6 <= :b2 and exists ( select /*+ UNNEST */ --??????? NO_UNNEST? ?? 'x' from subquery_t2 t2 where t2.c1 = t1.c4 and t2.c3 >= :b3 and t2.c3 <= :b4); select * from table(dbms_xplan.display_cursor(null,null,'allstats last advanced'));
- 27. Plan_hash_valu e ?? SQL_ID?? ????? ????? plan_hash_value? ??? ??. Id ???? ???? ?? Operation ?? Operation ? Name ???? Object ? Starts ?? ??. NL Join?? SM ???? ?? ??? ?? E-Rows ?? ??? ?? ??. ??? –rows ? ???? ???? A-Rows ?? ??? ?? ?? A-Time ?? ??? ?? ??. ?? ??? ??? ?? ????? ?? ??? ?? ? ?? ?? ??? A-Time ?? ?? ??. Buffers Logical I/O(Buffer Cache?? ?? ??) ??. ?? ? Reads I/O(????? ?? ??)??. ?? ? OMem PGA ?? operation?? OPTIMAL? ? ???? PGA??? 1Mem PGA? ?? operaion?? onepass? ? ???? PGA??? Used-Mem PGA? ???? operation?? ?? ??? PGA??? ?. ONE PASS? ????(1)? ???? (0)? optimal? ???.
- 28. ??? Query Block ??? ?????, ?????? Query Transformation ? ???? ????? ? ? ??.
- 29. ORACLE? SQL? ??? ?? ????? ??? HINT?? ?? ????? ?? ?? ?? ?? Column? ?????? ??? ?. Access Predicate : Access Type? ??? ? ???? predicate(??)? ??. ?? Block? ?? ?? ?? ???? Block? ?? ???? ??. Index Lookup?? Join?? access predicate? ????. Filter Predicate : ?? Block? ?? ? Data? ???? ?? ???? predicate? ????.
- 30. <???? FORMAT>?? BASIC Operation ?? ? ???? ????. TYPICAL Partition? parallel ??? ?? ??? ??? ??? ????. ???? SERIAL TYPICAL? ??? parallel ??? ????. ALL ??? ? ?? ??? ? ????. Query Block Name / Object Alias section / the predicate section / the column projection section ADVANCED ALL + outline ? ??? ?? ?? ??? ????. LAST ??? ??? ??? ?? ?? ??? ??? OUTLINE ?????? ???? ??? ??? ??? PREDICATE Predicate ??? ???? IOSTATS ?? ??? ?? I/O ??? ??? BYTES ??? bytes ?? ??? COST Optimizer ? ?? ??? cost
- 34. ????? ??? ?? ???? ???. Bind variable? ?? ??.
- 35. tkprof
- 36. 10053 Event ? Physical Optimization? Logical Optimization? ?? ?? ??? Trace? ??? ? Access Path, Join Method, Join Order? ? ? ??. ? Query Transformation? ??? ??? ? ??. ? 10053 Event? ????? ?? ALTER SESSION SET EVENTS ‘10053 trace name context forever, level 1’; ?? ??? SQL? ?? ALTER SESSION SET EVENTS ‘10053 trace name off’; ? Trace File ?? ?? user_dump_dest ????? ???? ?? ????? ?? ? Trace file sample : ???? ??
- 38. ?? ?? ?? ?? ? ?? ?? ???? ??? ?? ??? ???. ? ?? ??? ??? ???? ??? ???. ? ??? 2?? 3?? ?? ????? ??? ??, 4?? 5?? ?? ???? ? ??? ??. ? 3 ? 2? 5? 4 ?1 ? ??? E-Rows ? A-Rows? ???? Optimizer? ??? ??? ??? ??
- 39. ID Operation Name 0 SELECT STATEMENT 1 NESTED LOOPS 2 TABLE ACCESS FULL EMPLOYEES 3 TABLE ACCESS BY INDEX ROWID DEPARTMENTS 4 INDEX UNIQUE SCAN IX_DEPARTMENTS_PK ???? (8i)? NL JOIN ? ?? ?? ???? ??? ?? ??? ???. ? ?? ??? ??? ???? ??? ???. ? ??? 2?? ??? ??, 3?? 4?? ??? ?? ? 2 ? 4? 3? 1 ? ??? E-Rows ? A-Rows? ???? Optimizer? ??? ??? ??? ??
- 40. ID Operation Name 0 SELECT STATEMENT 1 NESTED LOOPS 2 TABLE ACCESS FULL EMPLOYEES 3 TABLE ACCESS BY INDEX ROWID DEPARTMENTS 4 INDEX UNIQUE SCAN IX_DEPARTMENTS_PK NLJ_PREFETCH ? 3 ? 4 ? 2 ? 1 ? LEADING(A B) A??? ?? ???. ? USE_NL(B) Nested Loop ????? ?? ? NO_NLJ_BATCHING(B) 11g New Feature? Batching NLJ? ???? ??. ? NLJ_PREFETCH : rowid? ?? ??? access? ??? ?? ?? ??? ?? ?(????) ?? ?? ??? block? read??. - ??? ??? ?????? ??? : Advanced Nested Loop Join - Jonathan Lewis, CBO Fundamentals : Table Prefetching 9i, 10g ????? ???? ???? (8i)? NL JOIN
- 41. ? 3 ? 4 ? 2 ? 5 ? 1 ? NLJ_BATCHING(B) 11g New Feature ? NLJ_BATCHING : ?? ???? Index Scan ??? Index? ?? Prefetch ? ?????. NLJ_BATCHING 11g ????? ???? ???? ????? NJ_JOIN ?? Prefetch ??? ???? ?? ??? ? NL Join??? ????? ??? ???.
- 42. Optimizer? ???? ???? NL??, NLJ_PREFETCH, NLJ_BATCHING? ? ???. ?? NLJ??? ??? ?? ???? Outline ??? ??? ??
- 43. NL JOIN : ?????(Outer Table, Driving Table), ?????(Inner Table) HASH : ?????(Build Input ??), ????(Probe Input ??) 1. Customers? HASH TABLE? ?? 2. Employees? HASH TABLE? ?? 3. Orders? Scan??? 2?? ??? HASH TABLE SCAN 4. 3?? ??? Scan??? 1?? ??? HASH TABLE SCAN
- 44. 1 ???.. 2 ???.. 4 ???.. 5 ???.. 3 ???.. 9 ???.. 10???.. 11???.. 6 ???.. 16???.. 17???.. 7 ???.. 13???.. 8 ???.. 18???.. 14???.. 20???.. 15???.. 19???.. 12???.. ??? D1 ??? C4 ??? B1 ??? A3 ??? A2 ??? E1 ??? D4 ??? A4 ??? C2 ??? C1 ??? ? ??? ? ??? A1 ??? D3 ??? E3 ??? B4 ??? B2 ??? D2 ??? B3 ??? E2 ??? E4 ??? C3 ??? ?? ??? ?? ?? ?? ?? ?? WHERE ename = ??? A B C D E ? ?
- 45. INDEX ??
- 46. ROWID
- 47. B-Tree Index
- 48. Bitmap Indexexs
- 49. ???? ?? ??? ??
- 50. Between ???? ??
- 51. In? Between ???? ??
- 52. Sort?? Index? ??
- 53. ?? Index? ??
- 54. Rownum? ??
- 55. Exists ???? ??
- 56. Index ??? ??
- 57. NESTED LOOP TAB1 TAB2 INDEX (KEY2) KEY1 = KEY2 TABLE ACCESS BY ROWID ?? ?? o o o x COL1 = ’10’ FLD2 LIKE ‘ABC%’ x INDEX (FLD1) FLD1=‘111' TABLE ACCESS BY ROWID SELECT * FROM TAB1 A, TAB2 B WHERE A.KEY1 = B.KEY2 AND A.FLD1 = ‘111’ AND A.FLD2 LIKE ‘ABC’ AND B.COL1 = ’10’ ; SELECT * FROM TAB1 A, TAB2 B WHERE A.KEY1 = B.KEY2 AND A.FLD1 = ‘111’ AND A.FLD2 LIKE ‘ABC’ AND B.COL1 = ’10’ ; 1. FLD1 ??? INDEX? ???? 2. KEY2 ??? INDEX? ????
- 58. SORT MERGE JOIN TAB1 TAB2 ???? KEY1= KEY2 ?? ???? . . . . . . . . . . . . S O R T INDEX (COL1) COL1='10' TABLE ACCESS BY ROWID . . . . . . . . . FLD2 LIKE ‘ABC%’ x S O R T FLD1=‘111' TABLE ACCESS BY ROWID INDEX (FLD1) SELECT * FROM TAB1 A, TAB2 B WHERE A.KEY1 = B.KEY2 AND A.FLD1 = ‘111’ AND A.FLD2 LIKE ‘ABC’ AND B.COL1 = ’10’ ; SELECT * FROM TAB1 A, TAB2 B WHERE A.KEY1 = B.KEY2 AND A.FLD1 = ‘111’ AND A.FLD2 LIKE ‘ABC’ AND B.COL1 = ’10’ ; 1. KEY1 ??? KEY2 ??? INDEX ?? ??? ??? ???
- 59. HASH JOIN ???? HASH FUNCTION PARTITION TABLE HASH TABLE BITMAP VECTOR C31 C21 C41 C51 TAB1 HASH AREA UGA C11 P P P P P SELECT * FROM TAB1 A, TAB2 B WHERE A.KEY1 = B.KEY2 AND A.FLD1 = ‘111’ AND A.FLD2 LIKE ‘ABC’ AND B.COL1 = ’10’ ; SELECT * FROM TAB1 A, TAB2 B WHERE A.KEY1 = B.KEY2 AND A.FLD1 = ‘111’ AND A.FLD2 LIKE ‘ABC’ AND B.COL1 = ’10’ ; HASH FUNCTION TAB2 ?????(BUILD INPUT)? PGA ???? HASH TABLE? ?? ? ?? ?? ?? SCAN??? PGA?? ?? ???? ???? ????.
- 60. ?? ?? ?? ?????? ?? ??? ??? ???? ???? ?? NESTED LOOPS NESTED LOOPS TABLE ACCESS (BY INDEX ROWID) OF ‘SDAMASTER.SAM_ORG’ INDEX (UNIQUE SCAN) OF ‘SDAMASTER.PK1.SAM_ORG’ TABLE ACCESS (BY INDEX ROWID) OF ‘SDAMASTER.SAM_STF’ INDEX (RANGE SCAN) OF ‘SDAMASTER.INS_INS_PL’ TABLE ACCESS (BY INDEX ROWID) OF ‘SDAMASTER.INS_INS_PL’ INDEX (SKIP SCAN) OF ‘SDAMASTER.I02_INS_INS_PL’ ? NESTED LOOP ?? ??
- 61. ???? ?? ? SORT MERGE ?? ?? TABLE ACCESS FULL OF ‘??’ SORT (JOIN) VIEW SORT (UNIQUE) TABLE ACCESS BY ROWID OF ‘??’ INDEX RANGE SCAN OF ‘??_??_IDX’ ? HASH ?? ?? HASH JOIN TABLE ACCESS (BY INDEX ROWID) OF ORDER SORT (JOIN) INDEX(RANGE SCAN) OF ORDDATE_INDEX (NON-UNIQUE) TABLE ACCESS (FULL) OF DEPT ? ??? ??? ??? ??? ?? ??? ?? ? ?? ???? ??? ??? ???? ????? ??? 2??? ??
- 62. ???? ?? ????? ?? ?? ???? ?? ??? ???? ??? ???? ?? ROWS EXECUTION PLAN . 0 SELECT STATEMENT 1181 SORT (GROUP BY) 1181 NESTED LOOPS 1514 VIEW 256854 SORT (GROUP BY) 256854 HASH JOIN (OUTER) 256854 VIEW 256854 UNION-ALL 256852 NESTED LOOPS 0 INLIST ITERATOR (CONCATENATED) 256856 TABLE ACCESS (BY INDEX ROWID) OF ‘DPACCB’ 256908 INDEX (RANGE SCAN) OF ‘DPACCB_IDX1’ (NON-UNIQUE) 24915032 TABLE ACCESS (BY INDEX ROWID) OF ‘BR_INFO’ 40840104 INDEX (RANGE SCAN) OF ‘BR_INFO_IDX2’ (NON-UNIQUE) 0 INLIST ITERATOR (CONCATENATED) 422116 TABLE ACCESS (BY INDEX ROWID) OF ‘DPACCB’ 422118 INDEX (RANGE SCAN) OF ‘DPACCB_IDX2’ (NON-UNIQUE) 15 VIEW 15 SORT (GROUP BY) 15 TABLE ACCESS (BY INDEX ROWID) OF ‘DPDDBS’ 16080201 INDEX (RANGE SCAN) OF ‘DPDDBS_IDX1’ (NON-UNIQUE) 1688 TABLE ACCESS (BY INDEX ROWID) OF ‘DPGDPF’ 3202 INDEX (RANGE SCAN) OF ‘PK_DPGDPF’ (UNIQUE)
- 63. OUTER JOIN ? ?? ??? ??? ?? ???(?????)? row? ??? ?? ? ???? ???? row? ???? ?? ? ???? row? NULL? ?? select?? ?? ?? 1. NL JOIN? ?? ??? A??? driving?? ??. 2. HASH JOIN? ???? SWAP_JOIN_INPUTS ??? B???? BUILD INPUT ???? ?? ? ??.
- 64. SEMI JOIN TAB1 TAB2 INDEX (KEY2) KEY1 = KEY2 TABLE ACCESS BY ROWID ?? ?? o o o x COL1 = ’10’ FLD2 LIKE ‘ABC%’ x INDEX (FLD1) FLD1=‘111' TABLE ACCESS BY ROWID SELECT * FROM TAB1 A, TAB2 B WHERE A.KEY1 = B.KEY2 AND A.FLD1 = ‘111’ AND A.FLD2 LIKE ‘ABC’ AND B.COL1 = ’10’ ; “???” ??Table? Row? ??Table ? Row? Match??? ?? ?? Join? ??? ??? ???? ?? Row? ??? ? ? ??? ??? ???? ???. Semi Join? Exists ? In Operation? ???? ??? ?? ??? Join ????.
- 65. INDEX : T2 ???? C1 ?? HASH SEMI JOIN
- 66. Nested Loops Semi Join
- 67. ?? ??? ??
- 68. ????? ??
- 69. ?? ??? ?? ??? ????? ?????? ??? ??? ???? IX_DEPARTMENTS_PK ??? ??? Index Fast Full Scan? ?? ?? ? ?? ??? ???? ????. INDEX FULL SCAN : single block I/O INDEX FAST FULL SCAN : multi block I/O EMPLOYEES ??? TABLE FULL? ?? ? department_id???? Sorting
- 70. Sort Merge Join
- 72. Nested Loop ??(????) ?? RBO??? ?? ?? ????? ?? ? ???. CBO?? ????.
- 73. PAGENATION ? Oracle?? rownum? ???? TOP N ?? ? ??? ?? ?? ?? ?? ? ?? ? ?
- 74. ?? DISK I/O? 29 block??, Memory I/O? 4? block?? ????
- 75. ? CUSTOMER_ID ??? ????, ?? ??? ??? ??(database buffer cache ?? ?)? 4? ? 1651?? ????. ? ?? ??? 0.03 ? 0.06 ?? ????. ? ? ??? ?? ?? 6494 ??? ?? ???? 20% ??? ?? ? 10 ?? ????? ? ? ??.
- 76. ?? ?? ?? ??? ? ??? ???? ??? ??? ??? ? ?? ???? ?? ?? ??? ?? ? ?? ????. ? ?? ??? ??? FILTERING?? ? ? ?? ?? ?? ?? block? Scan?? ? ? ??? rownum ??? ???? ?? ?? ??? ?? ??? ????.
- 77. PAGENATION ?? ?? ? SELECT A.* FROM (SELECT ROWNUM AS RN, A.* FROM (………. ……….) A WHERE ROWNUM <= :b2 ) A WHERE RN >= :b1
- 79. ? (ORDER_DATE, ORDER_MODE, EMPLOYEE_ID) ???? ???? SORT? ?? ?? ???. ? ??? ? SQL? ???? ??? ?? ??? INDEX 1874?? ?? ??? ???? ? ???? 10?? FILTERING ???? ??. ? ? SLIDE?? ??? PAGENATION ??? ??? ???? ??? ????.
- 80. ? (ORDER_DATE, ORDER_MODE, EMPLOYEE_ID) ???? 10?? SCAN?? ?? ??. ? ??? ??? : ORDER BY?? ???? ??? INDEX ???? ?? ?? ? SORT ORDER BY OERATION? ???? ???.
- 81. ??? INDEX? ?? ?? IX_ORDERS_N2 : (EMPLOYEE_ID, ORDER_DATE) ? ??? INDEX? ?? ??? IX_ORDERS_N2 ???? ?????, ???? 1874?? ?? ??? ??? ???? 10?? ??? ? ???. ? SORT ORDER BY STOPKEY ?????? 10?? ??? ??? ?? ORDERS ????? ORDER_DATE? SORT??. ??????? ?? ? ?? ???
- 82. ? CUSTOMERS ???? OUTER JOIN?? ?? ??? CUSTOMERS ???? ?? ??? ?? ??? ??? ???. ? ? ????? ORDERS? CUSTOMERS? JOIN? ?? SORT? ?? ??. ? ??? CUSTOMERS? IX_CUSTOMERS_PK? 1874? ?? access?? ??. ? ???? ?? ORDERS ???? 10?? ?? ? CUSTOMERS??? JOIN? ?? CUSTOMERS? 10?? access? ???.
- 84. ? 7 ? 6 ? 5? 4? 3?2?9?8 ?1 ? ??? ???? ?? ??? SORT ORDER BY STOPKEY? ?? ?? ??. ? ORDERS ??? ?? pagenation?????, ? ?? ??(VIEW)? CUSTOMERS??? LESTED LOOPS OUTER ?? ?????. ? ?? CUSTOMERS ????? ??? INNER JOIN??? ?? ?? ? ?? ? ??? ?? ?? ??? ?? ?? ??? ?? ??? ? ?? ?? ?? ?.
- 86. ? ???(1? row ~ 10? row??)? LOADING?? SQL? ???? ? ??? ???(ORDER_DATE)? ??? SORT ORDER BY STOPKEY? ??? ? ??? ??? ??? ??? ??? ???? ??. ? JOIN I/O? ??? ??? CUSTOMERS ??? ???? ?? PAGING ?? ? ???. ? :PAGENUM ??? ?? ?? 1000?? ??(?, 1000 page? ???? ?? ?? ???. 10001 ~ 10010? row? ???)?? ????? ??? ??.
- 87. PAGE_NUM ???? ????(? ??? ??) A-Rows? ???. ??? ? ??? A-Rows? ??? ?? I/O? ?? ??. ?? ???? 1000 PAGE?? ???? ?? ?? ?? ???.
- 88. INDEX ??? ??? SELECT * FROM TAB1 WHERE A = '10' AND B = '941005' AND C = '123' AB C INDEX1 INDEX2 INDEX1 ? ?? AB = '10'||'941005' C = '123' ABD C INDEX1 INDEX2 D column ?? ABD LIKE '10'||'941005'||'%' C = '123' INDEX2 ? ??
- 89. ?? ?? SQL ?? ?? ?? ? ??? ???? SQL??? ?? ??? ??? ?? ? ??? ?? ?? ??? ??? ???? ? ???? ???? ??? ??? ?? ? ??? ?? ?
- 90. SUMMARY ? Cardinality and Clustering determine whether the “Big Job” or “Small Job” strategy should be preferred ? If the optimizer gets these estimates rigtht, the resulting execution plan will be efficient within the boundaries of the given access paths ? Know your data and business questions ? Help your optimizer. (Oracle doesn’t know the data the way you know it.)