Jerry Held

Size: px
Start display at page:

Download "Jerry Held"

Transcription

1

2

3 ,, - - -

4 : DELETE : ROW (ROWID) row ROWID : I/O

5 Full Table Scan I/O Index Scan ROWID I/O Fast Full Index Scan scan scan scan I/O scan scan

6 Unique, nonunique. (Concatenated Index) B* Tree Bitmap Reverse Key Descending Function-based Domain Indexes

7 (Relation) RELATION (e.deptno = d.deptno) SELECT e.ename, d.dname FROM DEPT d, EMP e WHERE e.deptno = d.deptno; EMP table JOIN DEPT table EMPNO ENAME 7369 Smith 7499 Allen 7521 Ward 7566 Jones DEPTNO e.ename Smith Allen Ward Jones d.dname BD R&D SALES SALES DEPTNO DNAME HR R&D SALES BD...

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

9 Sort Merge Join Allen 1 Ward 2 Jones 2 Smith 3 Allen Ward Jones Smith N : MERGE JOIN ROWID AAAB AABB AAFF AAAC 2) SORT JOIN(EMP) 4) SORT JOIN(DEPT) ROWID DEPTNO AAAB 10 AABB 30 AAFF 30 AAAC 50 M Join ROWID BBBA BBAF BBAF BBAC 5) MERGE JOIN DEPTNO R&D SALES SALES BD ROWID BBBA BBAA BBAF BBAC 5) 6). SELECT STATEMENT WHERE. 5) MERGE JOIN 2) 4) ( ) - 2) 4). SORT(JOIN) SORT(JOIN) R&D HR SALES BD 1) 3) SCOTT.EMP SCOTT.DEPT TABLE ACCESS(FULL) TABLE ACCESS(FULL)

10 Hash Join Hash PGA (HASH_AREA_SIZE) 3). SELECT STATEMENT. WHERE. : Oracle.. 3) HASH, MOD(x,6) 1) HASH (DEPT) + ROWID SALES (30,BBAF).. HR BD R&D (20,BBAA) (50,BBAC) 22.. (10,BBBA) HASH )ƒ h (50) = 2 ƒ h (10) = 4 2) FULL ACCESS(EMP) 4) HASH JOIN DEPTNO ENAME 50 Smith 10 Allen 30 Ward 30 Jones 1) 2) SCOTT.DEPT SCOTT.EMP TABLE ACCESS(FULL) TABLE ACCESS(FULL)

11 SELECT e.ename, d.dname FROM DEPT d, EMP e WHERE e.deptno = d.deptno ORDER BY e.ename; PGA 1) SORT_AREA_SIZE SCOTT.EMP TABLE ACCESS(FULL) 6) SELECT STATEMENT 5) SORT(ORDER BY) 4) NESTED LOOPS 3) 2) SCOTT.DEPT_PK INDEX(UNIQUE SCAN) 5) ORDER BY. SCOTT.DEPT TABLE ACCESS(BY INDEX ROWID)

12 Oracle Optimizer - - RBO CBO -Optimizer

13 2.1

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

15 Optimizer ( ) SQL Optimizer GOAL:. RDBMS Rule-Based Optimization (RBO) Cost-Based Optimization (CBO) (Cost)

16 2.2 Rule-Based Optimization Cost- Based Optimization

17 Rule-Based Optimization (RBO) SQL syntax SQL :, Oracle 6

18 RBO 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

19 Cost-Based Optimization (CBO) SQL I/O CPU Oracle7 RBO

20 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

21 2.3 Optimizer

22 Optimizer Oracle9i Optimizer SQL RBO CBO throughput - row Response time - row

23 Optimizer ( ) Optimizer OPTIMIZER_MODE ( alter system ) OPTIMIZER_MODE (alter session ) Optimizer Hint

24 OPTIMIZER_MODE CHOOSE Oracle9i Optimizer.. RULE RBO ALL_ROWS FIRST_ROWS[_n]. n = 1, 10, 100, 1000

25 SQL Optimizer Hint /*+ CHOOSE */ /*+ RULE */ /*+ ALL_ROWS */ Oracle9i Optimizer. RBO /*+ FIRST_ROWS[(n)] */. n =

26 RBO RBO SQL syntax, FROM WHERE syntax

27 CBO CBO Hint SQL CBO

28 Optimizer Hint Optimizer, Optimizer Optimizer Hint Optimizer Hint Stored Outline Hint /*+ RULE */ CBO

29 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

30 SQL - -

31 3.1 SQL

32 SQL Response Time Resource Workload Workload re-scheduling report batch Workload Parallel Query, Parallel DML DW

33 SQL Step 1 SQL Step 2 Step 3 SQL

34 SQL feedback SQL Trace TKPROF STATSPACK Report Dictionary View I/O Sort

35 SQL SQL SQL View Base SQL CBO SQL

36 SQL Optimizer SQL Optimizer Optimizer Optimizer Optimizer Optimizer Hint

37 SQL ( ) SQL. Optimizer Logic, SQL DBMS - - contention

38 3.2

39 SELECT empno, ename, job FROM emp WHERE empno = :b1; empno Primary Key., empno VARCHAR2(4) bind Execution Plan SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMP'

40 ( ) 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.

41 SELECT 'Not Exists' FROM dept WHERE dname!= :b1; ( ). dname Execution Plan SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'DEPT' Full Table Scan

42 ( ) : SELECT 'Not Exists' FROM dual WHERE NOT EXISTS ( SELECT * FROM dept WHERE dname = :b1 ); Execution Plan 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)

43 ( ) 'NOTEXISTS Not Exists Not Exists Not Exists Not Exists n. 'NOTEXISTS Not Exists 1. Full Table Scan EXISTS

44 null SELECT empno, ename, job, comm FROM emp WHERE comm IS NULL;. comm Execution Plan SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMP' Full Table Scan IS NULL NULL

45 null ( ) SELECT empno, ename, job, comm FROM emp WHERE comm IS NOT NULL;. Execution Plan SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMP' Full Table Scan.

46 null ( ) 1 : SQL SELECT empno, ename, job, comm FROM emp WHERE comm > 0; Execution Plan 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

47 null ( ) 2 : SELECT /*+ INDEX (emp i_emp_comm) */ empno, ename, job, comm FROM emp WHERE comm IS NOT NULL; Execution Plan 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)

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

49 SELECT * FROM registrations WHERE status = 'HOLD'; HOLD. status registrations Execution Plan SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=11250 Bytes=303750) 1 0 TABLE ACCESS (FULL) OF 'REGISTRATIONS' (Cost=31 Card=11250 Bytes=303750)

50 ( ) Full Table Scan status SELECT status, COUNT(*) FROM registrations GROUP BY status; STAT COUNT(*) CANC 2400 HOLD 5 PEND 2 REGI WAIT status skewed Optimizer selectivity = 1 / #distinct values = 1/ 5 = 20%

51 ( ) Histogram ANALYZE TABLE registrations COMPUTE STATISTICS FOR COLUMNS status; Query Execution Plan 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)

52 ( ) status = REGI Query Execution Plan SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=53575 Bytes= ) 1 0 TABLE ACCESS (FULL) OF 'REGISTRATIONS' (Cost=31 Card=53575 Bytes= ) Full Table Scan CBO.

53 ( ) : 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

54 sort SELECT empno, ename, job, comm FROM emp ORDER BY comm;. Execution Plan SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2 1 TABLE ACCESS (FULL) OF 'EMP' sort. sort row.

55 sort ( ) : comm Query SELECT empno, ename, job, comm FROM emp WHERE comm >= 0; Execution Plan 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.

56 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 > ' '; Execution Plan 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)

57 sort ( ) SELECT MAX(sal) FROM emp WHERE hiredate > TO_DATE(:b1, 'YYYY'); : Execution Plan 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.

58 sort ( ) : (sal,_hiredate) Query SELECT /*+ INDEX_DESC (emp i_emp_sal_hiredate) */ sal FROM emp WHERE hiredate > TO_DATE(:b1, 'YYYY') AND ROWNUM = 1; Execution Plan 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

59 . SELECT * FROM registrations WHERE status = :b1 AND appr_by = :b2;. status appr_by registrations

60 ( ) OPTIMIZER_MODE = RULE Execution Plan 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

61 ( ) CBO Execution Plan 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 QueryAND_EQUAL Hint Merge cost 227.

62 ( ) (status, appr_by) Execution Plan 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.

63 Skip Scan SELECT * FROM registrations WHERE appr_by = :b1;. Execution Plan 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)

64 SELECT * FROM registrations WHERE status = :b1 AND appr_by = :b2;. appr_by 1/74, status 1/5 : (appr_by, status)? (appr_by, status) Execution Plan 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

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

66 Fast Full Scan SELECT appr_by, status FROM registrations;. (appr_by, status) appr_by status NOT NULL Execution Plan 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= Bytes=506268) Fast Full Scan

67 AND SELECT * FROM registrations WHERE class_id = :b1 OR appr_by = :b2;. (class_id, appr_by) Execution Plan SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=778 Bytes=21006) 1 0 TABLE ACCESS (FULL) OF 'REGISTRATIONS' (Cost=31 Card=778 Bytes=21006) OR?

68 AND ( ) class_id appr_by Execution Plan 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;

69 AND ( ) SELECT * FROM registrations WHERE class_id IN (:b1, :b2, :b3);. : IN-List Iterator Execution Plan 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.

70 AND ( ) SELECT * FROM registrations WHERE (status = 'HOLD' OR appr_by = 85617) AND NOT (status = 'WAIT' AND appr_by = 95482) AND (attended = 'Y');. Execution Plan 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

71 AND ( ) status, appr_by, attended Bitmap Execution Plan 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.

72 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 Execution Plan 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

73 OPTIMIZER_MODE Join ( ) OPTIMIZER_MODE = ALL_ROWS HASH_JOIN_ENABLED = FALSE HASH_JOIN_ENABLED = TRUE Execution Plan 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) Execution Plan 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)

74 OPTIMIZER_MODE Join ( ) OPTIMIZER_MODE = FIRST_ROWS Execution Plan 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.

75 Join Key Join SELECT co.short_name, cl.start_date FROM classes cl, courses co WHERE cl.instr_id = co.dev_id;. join keycourses.dev_id OPTIMIZER_MODE = RULE Execution Plan 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

76 Join Key Join ( ) OPTIMIZER_MODE = ALL_ROWS Execution Plan 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.

77 Join Key Join ( ) OPTIMIZER_MODE = FIRST_ROWS Execution Plan 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

78 Join Key Join ( ) OPTIMIZER_MODE FIRST_ROWS Join Key classes.instr_id Execution Plan 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

79 Join Key Join ( ) OPTIMIZER_MODE = FIRST_ROWS Join Key Execution Plan 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

80 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 keycourses.dev_id classes.instr_id classes.class_id class_id classes Prinary Key. Unique

81 Non-Join Join ( ) OPTIMIZER_MODE = FIRST_ROWS Execution Plan 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!

82 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

83 Star Join ( ) Execution Plan 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 cardinalitycls_statuses driving classes hash join class_types nested loop join

84 Star Join ( ) Star Schema 1 CLASS #class_id CLS_STATUSES #cls_status. CLASS_TYPES #type.... dimension/lookup DW Star Join Star schemaoracle CBO : join key (Status + type)

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

86 Top-N SQL SELECT * FROM (SELECT COUNT(*), class_id FROM registrations GROUP BY class_id ORDER BY COUNT(*) DESC) WHERE ROWNUM <= Execution Plan 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

87 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

88 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

89 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;.

90 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

91

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

More information

SQL Tuning Business Development DB

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

More information

SQL Tuning Business Development DB SQL - -SQL -SQL

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

More information

Microsoft PowerPoint - Oracle Data Access Pattern.ppt

Microsoft PowerPoint - Oracle Data Access Pattern.ppt Special Key Note Oracle Data Access Pattern ( 주 ) 오픈메이드컨설팅 오동규수석컨설턴트 1 What is Data Access Pattern? > 데이터를 I/O 하는방식 Index Scan Full Table Scan Rowid 2 Why is The Pattern Important? >SQL 의성능을좌지우지함. >SQL

More information

Microsoft Word - SQL튜닝_실습교재_.doc

Microsoft Word - SQL튜닝_실습교재_.doc * 실습환경 * 1. 오라클데이터베이스의튜닝실습을하기위해서는기본적인테이블과데이터가필요합니다. 다음과같은절차에의해환경설정을하십시오. 1) 강사가제공하는 Export 된파일 (scott.dmp) 을자신의 ORACLE 경로에저장하십시오. [C: ] cd C: ORACLE ORA92 BIN [C: ] dir scott.dmp scott.dmp 2) SYSTEM 사용자로접속하여

More information

歯sql_tuning2

歯sql_tuning2 SQL Tuning (2) SQL SQL SQL Tuning ROW(1) ROW(2) ROW(n) update ROW(2) at time 1 & Uncommitted update ROW(2) at time 2 SQLDBA> @ UTLLOCKT WAITING_SESSION TYPE MODE_REQUESTED MODE_HELD LOCK_ID1

More information

ePapyrus PDF Document

ePapyrus PDF Document Goodus 기술노트 [38 회 ] Author 윤병길, 이은정 Creation Date 2009-02-27 Last Updated Version 1.0 Copyright(C) 2004 Goodus Inc. All Rights Reserved Version 변경일자변경자 ( 작성자 ) 주요내용 1 2009-02-27 윤병길, 이은정문서최초작성 Contents

More information

untitled

untitled (shared) (integrated) (stored) (operational) (data) : (DBMS) :, (database) :DBMS File & Database - : - : ( : ) - : - : - :, - DB - - -DBMScatalog meta-data -DBMS -DBMS - -DBMS concurrency control E-R,

More information

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL 다양한예제로쉽게배우는 오라클 SQL 과 PL/SQL 서진수저 4 장 JOIN 을배웁니다 1 2 1. Cartesian Product ( 카티션곱, CROSS Join) - Oracle Join 문법 SQL> SELECT e.ename, d.dname 2 FROM emp e, dept d ; - ANSI Join 문법 SQL> SELECT e.ename, d.dname

More information

목 차

목      차 Oracle 9i Admim 1. Oracle RDBMS 1.1 (System Global Area:SGA) 1.1.1 (Shared Pool) 1.1.2 (Database Buffer Cache) 1.1.3 (Redo Log Buffer) 1.1.4 Java Pool Large Pool 1.2 Program Global Area (PGA) 1.3 Oracle

More information

13주-14주proc.PDF

13주-14주proc.PDF 12 : Pro*C/C++ 1 2 Embeded SQL 3 PRO *C 31 C/C++ PRO *C NOT! NOT AND && AND OR OR EQUAL == = SQL,,, Embeded SQL SQL 32 Pro*C C SQL Pro*C C, C Pro*C, C C 321, C char : char[n] : n int, short, long : float

More information

결과보고서

결과보고서 오픈 소스 데이터베이스 시스템을 이용한 플래시 메모리 SSD 기반의 질의 최적화 기법 연구 A Study on Flash-based Query Optimizing in PostgreSQL 황다솜 1) ㆍ안미진 1) ㆍ이혜지 1) ㆍ김지민 2) ㆍ정세희 2) ㆍ이임경 3) ㆍ차시언 3) 성균관대학교 정보통신대학 1) ㆍ시흥매화고등학교 2) ㆍ용화여자고등학교 3)

More information

객관식 1. 아래의쿼리를만족하는결과를가장잘설명한것은? SELECT A.* FROM HR.EMPLOYEES A, HR.EMPLOYEES B WHERE 1=1 AND A.MANAGER_ID = B.EMPLOYEE_ID AND B.SALARY >= ANY A.SALARY;

객관식 1. 아래의쿼리를만족하는결과를가장잘설명한것은? SELECT A.* FROM HR.EMPLOYEES A, HR.EMPLOYEES B WHERE 1=1 AND A.MANAGER_ID = B.EMPLOYEE_ID AND B.SALARY >= ANY A.SALARY; Study Room Doc.02 : SQLD 예상문제 네이버 Cafe : 데이터베이스전문가포럼 Study Room http://cafe.naver.com/sqlpd SQLD 21 회기출문제를바탕으로작성 작성자 : 월야루 2016-09-04 객관식 1. 아래의쿼리를만족하는결과를가장잘설명한것은? SELECT A.* FROM HR.EMPLOYEES A, HR.EMPLOYEES

More information

Microsoft PowerPoint - Oracle Data Join Method.pptx [읽기 전용]

Microsoft PowerPoint - Oracle Data Join Method.pptx [읽기 전용] Special Key Note Oracle Data Join Method ( 주 ) 오픈메이드컨설팅 오동규수석컨설턴트 1 What is Join? JOIN is Multiply. 2 Why is the Join Method so important? 잘못사용하면큰재앙이따른다.( 위의그림처럼 ) 두개의집합을연결할수있는유일한수단. Join Method 는모든 DBMS

More information

Oracle Database 10g: Self-Managing Database DB TSC

Oracle Database 10g: Self-Managing Database DB TSC Oracle Database 10g: Self-Managing Database DB TSC Agenda Overview System Resource Application & SQL Storage Space Backup & Recovery ½ Cost ? 6% 12 % 6% 6% 55% : IOUG 2001 DBA Survey ? 6% & 12 % 6% 6%

More information

RDB개요.ppt

RDB개요.ppt 1 2 3 < > 1 SQL SQL 2 SQL 3 column DEPT DEPT# DNAME BUDGET D1 D2 D3 Marketing Development Research 10M 12M 5M tuple EMP EMP# ENAME DEPT# SALARY D1 40 D1 45 E1 E2 E3 Lopez Cheng Finzi D2 30 E4 Satio D2

More information

Query Process 단계처리내용 Parse 단계 Syntax, Security, Semantics의체크및Simple transformation 을수행한다 < 표 2>. Query Rewrite 단계서브질의와뷰의병합을수행하고, OR Expansion 작업을수행한다.

Query Process 단계처리내용 Parse 단계 Syntax, Security, Semantics의체크및Simple transformation 을수행한다 < 표 2>. Query Rewrite 단계서브질의와뷰의병합을수행하고, OR Expansion 작업을수행한다. Oracle Optimizer 의원리이해및 SQL & 애플리케이션의튜닝 ( 상 ) 옵티마이저의원리와특징 글 최세훈 ( 한국오라클 Tech Sales Consulting 본부 DB Tech 팀 ) sehoon.choi@oracle.com 다수의데이타베이스튜닝과 SQL / 애플리케이션튜닝을통해튜닝의효과를확신하는필자가유익한튜닝정보를제공한다. 여기에서필자는 SQL

More information

I T C o t e n s P r o v i d e r h t t p : / / w w w. h a n b i t b o o k. c o. k r

I T C o t e n s P r o v i d e r h t t p : / / w w w. h a n b i t b o o k. c o. k r I T C o t e n s P r o v i d e r h t t p : / / w w w. h a n b i t b o o k. c o. k r -------------------------------------------------------------------- -- 1. : ts_cre_bonsa.sql -- 2. :

More information

3 S Q L A n t i p a t t e r n s Trees/intro/parent.sql CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, parent_id BIGINT UNSIGNED, comment TEXT

3 S Q L A n t i p a t t e r n s Trees/intro/parent.sql CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, parent_id BIGINT UNSIGNED, comment TEXT 3 S Q L A n t i p a t t e r n s Trees/intro/parent.sql CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, parent_id BIGINT UNSIGNED, comment TEXT NOT NULL, FOREIGN KEY (parent_id) REFERENCES Comments(comment_id)

More information

MS-SQL SERVER 대비 기능

MS-SQL SERVER 대비 기능 Business! ORACLE MS - SQL ORACLE MS - SQL Clustering A-Z A-F G-L M-R S-Z T-Z Microsoft EE : Works for benchmarks only CREATE VIEW Customers AS SELECT * FROM Server1.TableOwner.Customers_33 UNION ALL SELECT

More information

90

90 89 3 차원공간질의를위한효율적인위상학적데이터모델의검증 Validation of Efficient Topological Data Model for 3D Spatial Queries Seokho Lee Jiyeong Lee 요약 키워드 Abstract Keywords 90 91 92 93 94 95 96 -- 3D Brep adjacency_ordering DECLARE

More information

ETL_project_best_practice1.ppt

ETL_project_best_practice1.ppt ETL ETL Data,., Data Warehouse DataData Warehouse ETL tool/system: ETL, ETL Process Data Warehouse Platform Database, Access Method Data Source Data Operational Data Near Real-Time Data Modeling Refresh/Replication

More information

Jerry Held

Jerry Held DB / TSC Oracle Database 10g (Self-Managing Database) (Common Infrastructure) (Automatic Workload Repository) (Server-generated Alerts) (Automated Maintenance Tasks) (Advisory Framework) (ADDM) (Self-Managing

More information

단답형 (26 회기출문제 ) 1. 아래와같은테이블이있을때아래의 SQL 결과에대해서 Oracle, SQL Server 순서로적으시오 TAB1 COL1 CHAR(10) COL2 CHAR(10) INSERT INTO TAB1 VALUES ('1',''); INSERT INT

단답형 (26 회기출문제 ) 1. 아래와같은테이블이있을때아래의 SQL 결과에대해서 Oracle, SQL Server 순서로적으시오 TAB1 COL1 CHAR(10) COL2 CHAR(10) INSERT INTO TAB1 VALUES ('1',''); INSERT INT Study Room Doc.03 : SQLD 예상문제 ( 단답형 ) 네이버 Cafe : 데이터베이스전문가포럼 Study Room http://cafe.naver.com/sqlpd SQLD 26,25,24,21 회기출문제를바탕으로작성 작성자 : 월야루 도움 : 빙수민외카페댓글 2017-11-30 단답형 (26 회기출문제 ) 1. 아래와같은테이블이있을때아래의 SQL

More information

FlashBackt.ppt

FlashBackt.ppt 1. Flashback 목적 Flashback 이란? 사용자실수에의한손상된데이터를 Database 의크기와상관없이복구를할수있는기능이다. 이 Flashback 기능은일반적인복구에서우려되는데이터베이스의크기를걱정하지않아도된다. 보통의사용자실수는커다란시스템장애가수반되며, 이를복구하기위해서는많은자원과시간이필요하다. 하지만 9i 에서지원되느 flashback query

More information

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL 다양한예제로쉽게배우는 오라클 SQL 과 PL/SQL 서진수저 9 장인덱스를배웁니다 1 1. 인덱스란무엇인가? 2 - ROWID ( 주소 ) 조회하기 SCOTT>SELECT ROWID, empno, ename 2 FROM emp 3 WHERE empno=7902 ; ROWID EMPNO ENAME --------------------------------- ----------

More information

PowerPoint 프레젠테이션

PowerPoint 프레젠테이션 Reasons for Poor Performance Programs 60% Design 20% System 2.5% Database 17.5% Source: ORACLE Performance Tuning 1 SMS TOOL DBA Monitoring TOOL Administration TOOL Performance Insight Backup SQL TUNING

More information

Bind Peeking 한계에따른 Adaptive Cursor Sharing 등장 엑셈컨설팅본부 /DB 컨설팅팀김철환 Bind Peeking 의한계 SQL 이최초실행되면 3 단계의과정을거치게되는데 Parsing 단계를거쳐 Execute 하고 Fetch 의과정을통해데이터

Bind Peeking 한계에따른 Adaptive Cursor Sharing 등장 엑셈컨설팅본부 /DB 컨설팅팀김철환 Bind Peeking 의한계 SQL 이최초실행되면 3 단계의과정을거치게되는데 Parsing 단계를거쳐 Execute 하고 Fetch 의과정을통해데이터 Bind Peeking 한계에따른 Adaptive Cursor Sharing 등장 엑셈컨설팅본부 /DB 컨설팅팀김철환 Bind Peeking 의한계 SQL 이최초실행되면 3 단계의과정을거치게되는데 Parsing 단계를거쳐 Execute 하고 Fetch 의과정을통해데이터를사용자에게전송하게되며 Parsing 단계에서실행계획이생성된다. Bind 변수를사용하는 SQL

More information

10.ppt

10.ppt : SQL. SQL Plus. JDBC. SQL >> SQL create table : CREATE TABLE ( ( ), ( ),.. ) SQL >> SQL create table : id username dept birth email id username dept birth email CREATE TABLE member ( id NUMBER NOT NULL

More information

ORANGE FOR ORACLE V4.0 INSTALLATION GUIDE (Online Upgrade) ORANGE CONFIGURATION ADMIN O

ORANGE FOR ORACLE V4.0 INSTALLATION GUIDE (Online Upgrade) ORANGE CONFIGURATION ADMIN O Orange for ORACLE V4.0 Installation Guide ORANGE FOR ORACLE V4.0 INSTALLATION GUIDE...1 1....2 1.1...2 1.2...2 1.2.1...2 1.2.2 (Online Upgrade)...11 1.3 ORANGE CONFIGURATION ADMIN...12 1.3.1 Orange Configuration

More information

Result Cache 동작원리및활용방안 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 ORACLE DBMS 를사용하는시스템에서 QUERY 성능은무엇보다중요한요소중하나이며그 성능과직접적인관련이있는것이 I/O 이다. 많은건수를 ACCESS 해야만원하는결과값을얻을수있는 QUER

Result Cache 동작원리및활용방안 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 ORACLE DBMS 를사용하는시스템에서 QUERY 성능은무엇보다중요한요소중하나이며그 성능과직접적인관련이있는것이 I/O 이다. 많은건수를 ACCESS 해야만원하는결과값을얻을수있는 QUER Result Cache 동작원리및활용방안 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 ORACLE DBMS 를사용하는시스템에서 QUERY 성능은무엇보다중요한요소중하나이며그 성능과직접적인관련이있는것이 I/O 이다. 많은건수를 ACCESS 해야만원하는결과값을얻을수있는 QUERY 을실행하게된다면 BLOCK I/O 가많이발생하게된다. 이런이유로 QUERY 의성능은좋지못할것이다.

More information

0. Intro ORACLE 社 Oracle, My SQL, Exadata IBM 社 DB2, Informix SAP 社 ASE, IQ, ASA Microsoft 社 SQL Server Teradata 社 Teradata 공통점은? Altibase 社 Altibase

0. Intro ORACLE 社 Oracle, My SQL, Exadata IBM 社 DB2, Informix SAP 社 ASE, IQ, ASA Microsoft 社 SQL Server Teradata 社 Teradata 공통점은? Altibase 社 Altibase 목 차 SQL 기본과활용 2010. 09. 29 삼성 S D S 정성철수석 ( D A / T A ) 1. RDBMS 2. SQL 3. SELECT 4. INDEX 5. MODELING 6. JOIN 7. DRIVING TABLE 8. SUBQUERY 9. OPTIMIZER 10. 과목2. ERD 11. 과목2. 목차 0. Intro ORACLE 社 Oracle,

More information

DBMS & SQL Server Installation Database Laboratory

DBMS & SQL Server Installation Database Laboratory DBMS & 조교 _ 최윤영 } 데이터베이스연구실 (1314 호 ) } 문의사항은 cyy@hallym.ac.kr } 과제제출은 dbcyy1@gmail.com } 수업공지사항및자료는모두홈페이지에서확인 } dblab.hallym.ac.kr } 홈페이지 ID: 학번 } 홈페이지 PW:s123 2 차례 } } 설치전점검사항 } 설치단계별설명 3 Hallym Univ.

More information

Microsoft Word - [Unioneinc] 특정컬럼의 통계정보 갱신_ _ldh.doc

Microsoft Word - [Unioneinc] 특정컬럼의 통계정보 갱신_ _ldh.doc 특정 Column 통계정보갱신가이드 유니원아이앤씨 DB 사업부이대혁 2015 년 03 월 02 일 문서정보프로젝트명서브시스템명 버전 1.0 문서명 특정 Column 통계정보갱신가이드 작성일 2015-03-02 작성자 DB사업부이대혁사원 최종수정일 2015-03-02 문서번호 UNIONE-201503021500-LDH 재개정이력 일자내용수정인버전 문서배포이력

More information

Intra_DW_Ch4.PDF

Intra_DW_Ch4.PDF The Intranet Data Warehouse Richard Tanler Ch4 : Online Analytic Processing: From Data To Information 2000. 4. 14 All rights reserved OLAP OLAP OLAP OLAP OLAP OLAP is a label, rather than a technology

More information

목차 BUG 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG ROLLUP/CUBE 절을포함하는질의는 SUBQUE

목차 BUG 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG ROLLUP/CUBE 절을포함하는질의는 SUBQUE ALTIBASE HDB 6.3.1.10.1 Patch Notes 목차 BUG-45710 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG-45730 ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG-45760 ROLLUP/CUBE 절을포함하는질의는 SUBQUERY REMOVAL 변환을수행하지않도록수정합니다....

More information

歯PLSQL10.PDF

歯PLSQL10.PDF 10 - SQL*Pl u s Pl / SQL - SQL*P lus 10-1 1 0.1 PL/ SQL SQL*Pl u s. SQL*P lus 10-2 1 0.2 S QL* Pl u s PL/ S QL SQL*Pl u s, Pl / SQL. - PL/ SQL (i npu t ), (s t or e ), (r un). - PL/ SQL s cr i pt,,. -

More information

OSR Analyzer Report

OSR Analyzer Report SQL 튜닝및개발가이드 1 목차 목차...2 OPTIMIZER 관련권장사항요약...4 SQL TUNING 을위한 GUIDE... 6 SQL Tuning 시주의점...6 Execution Plan 보기...7 Execution Plan 보기실행예 (9i)... 7 Instance level 에동적으로 SQL_TRACE Enable/Disable... 8 9iR2

More information

Microsoft Word - 03_SQL_CURSOR.doc

Microsoft Word - 03_SQL_CURSOR.doc SQL Cursor SQL 커서소개오라클서버에서는 SQL 문을실행할때마다처리 (Parse, Execution) 를위한메모리공간, 즉 SQL 커서를사용하게된다. 이메모리공간은 Private SQL Area 라고도불리우며, 오라클의작업환경이 Dedicated Server 환경이냐또는 MTS(Multi- Threaded Server) 환경이냐에따라서버내에위치되는곳이다르다.

More information

SQL 실행계획을 이용한 패턴튜닝_ _최종.ppt [호환 모드]

SQL 실행계획을 이용한 패턴튜닝_ _최종.ppt [호환 모드] SQL 과실행계획을이용한튜팅 엔코아컨설팅 컨설팅사업본부본부장 김동훈이사 CONTENTS SQL 의개념실행계획패턴실행계획의최적화 SQL 의개념 - 수행단계 SQL 은데이터처리방법을기술한것이아니라단지필요한데이터를요구한것임 SQL Parser Parsed Query Query Transfrmer Transfrmed Query Estimatr Plan Generatr

More information

< 그림 1> Nested Loop Join - 이너테이블에인덱스가있을경우 < 그림 2> Nested Loop Join - 이너테이블에인덱스가없는경우 간은느리다. 즉첫번째로우를받을준비가되어있는단계까지를실행시간으로볼때실행시간은빠르나 Fetch 시간은느리다. NLJ는메모리

< 그림 1> Nested Loop Join - 이너테이블에인덱스가있을경우 < 그림 2> Nested Loop Join - 이너테이블에인덱스가없는경우 간은느리다. 즉첫번째로우를받을준비가되어있는단계까지를실행시간으로볼때실행시간은빠르나 Fetch 시간은느리다. NLJ는메모리 Oracle Optimizer 의원리이해및 SQL & 애플리케이션의튜닝 ( 하 ) 오라클튜닝기법의 100% 활용 글 최세훈 ( 한국오라클 DB Tech 팀 ) sehoon.choi@oracle.com 지난회에서는튜닝에들어가기위해먼저 Oracle Optimizer 의원리와특징에대해서설명했다. 이번회에서는조인메소드별특징과플랜보는법을이해하고, 실제오라클에서제공하는튜닝기법들을활용해보도록하자.

More information

How To Write Efficient SQL Queries with Tips N Tricks

How To Write Efficient SQL Queries with Tips N Tricks ORACLE 9 9i 개발자튜닝가이드 v0.92 (with SQLTools for Oracle) ORACLE 9i 개발자튜닝가이드 v0.92 Mail:heiya@nate.com Homepage:http://myhome.naver.com/heiya Last edited : 2003.06.10 목차 - 목차 - I. ORACLE 의이해 1. ORACLE Optimizer

More information

The Self-Managing Database : Automatic Health Monitoring and Alerting

The Self-Managing Database : Automatic Health Monitoring and Alerting The Self-Managing Database : Automatic Health Monitoring and Alerting Agenda Oracle 10g Enterpirse Manager Oracle 10g 3 rd Party PL/SQL API Summary (Self-Managing Database) ? 6% 6% 12% 55% 6% Source: IOUG

More information

TECHNICAL WHITE PAPER Tibero Optimizer SQL Execution Plan October 2012

TECHNICAL WHITE PAPER Tibero Optimizer SQL Execution Plan October 2012 Tibero Optimizer SQL Execution Plan 목차 1. Introduction 2. Watching SQL Plan 2.1. SQL Plan 이란? 2.2. SQL Plan 확인하기 2.3. Understanding SQL Plan 3. Conclusion Optimizer 에의해만들어진 SQL 플랜을확인한는여러방법들을소개하고플랜에서보여주는정보의의미에대해알아본다.

More information

Connection 8 22 UniSQLConnection / / 9 3 UniSQL OID SET

Connection 8 22 UniSQLConnection / / 9 3 UniSQL OID SET 135-080 679-4 13 02-3430-1200 1 2 11 2 12 2 2 8 21 Connection 8 22 UniSQLConnection 8 23 8 24 / / 9 3 UniSQL 11 31 OID 11 311 11 312 14 313 16 314 17 32 SET 19 321 20 322 23 323 24 33 GLO 26 331 GLO 26

More information

Microsoft PowerPoint - The overview of MView.ppt

Microsoft PowerPoint - The overview of MView.ppt The Overview of Materialized View Getting the most out of MetaLink 최창권 한국오라클제품지원실 안녕하십니까? 한국오라클에서주최하는 Technical iseminar Mview Overview 에참석해주신여러분께감사드립니다. 저는이번세미나를진행하게될한국오라클제품지원실에근무하는최창권입니다. 본 seminar 는

More information

슬라이드 1

슬라이드 1 { Query Optimizing } 김정선 DB 사업부수석컨설턴트필라넷 (Feel@NET) Microsoft SQL Server MVP 김정선 (Jungsun Kim) Email: jskim@feelanet.com Blog: http://blog.naver.com/visualdb ( 현재소속 ) 필라넷, DB 사업부수석컨설턴트 SQL Server Academy/

More information

ORACLE-SQL

ORACLE-SQL ORACLE-SQL SELECT 문 2014-04-12 Blog.ksh123jjang.me 내용 SELECT문이란?... 2 SLELECT문사용하기... 3 모든열선택... 4 특정열검색... 5 SQL문작성방법... 6 열머리글기본값... 7 산술식... 8 NULL... 9 열 alias... 10 연결연산자... 11 대체인용연산자 (q)... 12 중복행제거...

More information

Microsoft Word - PLSQL.doc

Microsoft Word - PLSQL.doc PL/SQL 2008 DB system and programming 보충자료 PL/SQL의실행절 BEGIN 절에서의몇가지규칙 - 실행문은여러라인에걸쳐사용할수있다. - 변수명의명명규칙은오라클의일반적인명명규칙과동일하다. PL/SQL 블록내에서 SQL 문을사용할때에는컬럼명과같은변수명은피해야한다. - SQL에서와마찬가지로날짜와문자는홑따옴표 ( ) 를사용하여인용하여야한다.

More information

@OneToOne(cascade = = "addr_id") private Addr addr; public Emp(String ename, Addr addr) { this.ename = ename; this.a

@OneToOne(cascade = = addr_id) private Addr addr; public Emp(String ename, Addr addr) { this.ename = ename; this.a 1 대 1 단방향, 주테이블에외래키실습 http://ojcedu.com, http://ojc.asia STS -> Spring Stater Project name : onetoone-1 SQL : JPA, MySQL 선택 http://ojc.asia/bbs/board.php?bo_table=lecspring&wr_id=524 ( 마리아 DB 설치는위 URL

More information

ESQL/C

ESQL/C 20 장. PL/SQL 커서 주요내용 암시적커서 명시적커서선언 명시적커서열기및닫기 명시적커서에서데이터 Fetch 커서의속성 (%ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUND) 커서 FOR 루프 PL/SQL 의커서 (Cursor) 커서 SQL 문과프로그램실행과정에서결과를저장할수있는오라클메모리구조 ( 개별 SQL 작업영역 ) 암시적커서 (Implicit

More information

PowerPoint Presentation

PowerPoint Presentation Server I/O utilization System I/O utilization V$FILESTAT V$DATAFILE Data files Statspack Performance tools TABLESPACE FILE_NAME PHYRDS PHYBLKRD READTIM PHYWRTS PHYBLKWRT WRITETIM ------------- -----------------------

More information

예제소스는 에서다운로드하여사용하거나툴바의 [ 새쿼리 ]( 에아래의소스를입력한다. 입력후에는앞으로실습을위해서저장해둔다. -- 실습에필요한 Madang DB 와 COMPANY DB 를모두생성한다. -- 데이터베이스생성 US

예제소스는  에서다운로드하여사용하거나툴바의 [ 새쿼리 ]( 에아래의소스를입력한다. 입력후에는앞으로실습을위해서저장해둔다. -- 실습에필요한 Madang DB 와 COMPANY DB 를모두생성한다. -- 데이터베이스생성 US A.4 마당서점데이터베이스생성 1 마당서점의데이터베이스 Madang을생성하기위해윈도우의 [ 시작 ]-[ 모든프로그램 ]- [Microsoft SQL Server 2012]-[SQL Server Management Studio] 를선택한다. 인증을 [Windows 인증 ] 으로선택한후 < 연결 > 을클릭한다. 2 1 3 서버이름 MADANG_DB\SQLEXPRESS

More information

TITLE

TITLE CSED421 Database Systems Lab MySQL Basic Syntax SQL DML & DDL Data Manipulation Language SELECT UPDATE DELETE INSERT INTO Data Definition Language CREATE DATABASE ALTER DATABASE CREATE TABLE ALTER TABLE

More information

5장 SQL 언어 Part II

5장 SQL 언어 Part II 5 장 SQL 언어 Part II 박창이 서울시립대학교통계학과 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 1 / 26 데이터조작문 데이터검색 : SELECT 문데이터추가 : INSERT 문데이터수정 : UPDATE 문데이터삭제 : DELETE 문 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 2 / 26 SELECT

More information

NLJ BATCH 과부분범위처리 엑셈컨설팅본부 / DB 컨설팅팀오수영 개요 오라클은새로운버전이출시될때마다한층업그레이드된기능들이추가된다. 이기능들은사용자에게편리함을제공함은물론이고, 기존의기능들이성능적으로업그레이드되어보다강력해지기도한다. 그러나때로는새롭게추가된기능으로인해,

NLJ BATCH 과부분범위처리 엑셈컨설팅본부 / DB 컨설팅팀오수영 개요 오라클은새로운버전이출시될때마다한층업그레이드된기능들이추가된다. 이기능들은사용자에게편리함을제공함은물론이고, 기존의기능들이성능적으로업그레이드되어보다강력해지기도한다. 그러나때로는새롭게추가된기능으로인해, NLJ BATCH 과부분범위처리 엑셈컨설팅본부 / DB 컨설팅팀오수영 개요 오라클은새로운버전이출시될때마다한층업그레이드된기능들이추가된다. 이기능들은사용자에게편리함을제공함은물론이고, 기존의기능들이성능적으로업그레이드되어보다강력해지기도한다. 그러나때로는새롭게추가된기능으로인해, 사용자들이큰혼란을겪기는경우도발생된다. 그 대표적인예로는 GROUP BY 가 SORT GROUP

More information

I. - II. DW ETT Best Practice

I. - II. DW ETT Best Practice IBM Business Intelligence Solution Seminar 2005 - IBM Business Consulting Service (cslee@kr.ibm.com) I. - II. DW ETT Best Practice (DW)., (EDW). Time 1980 ~1990 1995 2000 2005 * 1980 IBM Information Warehouse

More information

oracle9i_newfeatures.PDF

oracle9i_newfeatures.PDF Oracle 9i .?.?.? DB.? Language.?.?.? (DW,OLAP,MINING,OLTP ) DB.?.? Technology Evolution High Availability Scalability Manageability Development Platform Business Intelligence Technology Evolution Technology

More information

오라클 데이터베이스 10g 핵심 요약 노트

오라클 데이터베이스 10g 핵심 요약 노트 1 10g 10g SYSAUX 10g 22 Oracle Database 10g, 10g. 10g. (Grid), 10g.. 10g SYSAUX (ASM, Automatic Storage Management) 10g 10g. g. (DBA).,., 1).,..? 10g,.. (Larry Ellison).. (Leverage Components), (ASM) (

More information

Microsoft PowerPoint - 27.pptx

Microsoft PowerPoint - 27.pptx 이산수학 () n-항관계 (n-ary Relations) 2011년봄학기 강원대학교컴퓨터과학전공문양세 n-ary Relations (n-항관계 ) An n-ary relation R on sets A 1,,A n, written R:A 1,,A n, is a subset R A 1 A n. (A 1,,A n 에대한 n- 항관계 R 은 A 1 A n 의부분집합이다.)

More information

[ 목차 ] 5.1 데이터베이스프로그래밍개념 5.2 T-SQL T-SQL 문법 5.3 JAVA 프로그래밍 2

[ 목차 ] 5.1 데이터베이스프로그래밍개념 5.2 T-SQL T-SQL 문법 5.3 JAVA 프로그래밍 2 5 장 SQL 응용 데이터베이스실험실 1 [ 목차 ] 5.1 데이터베이스프로그래밍개념 5.2 T-SQL 5.2.1 T-SQL 문법 5.3 JAVA 프로그래밍 2 5.1 데이터베이스프로그래밍개념 프로그래밍 이라고하면프로그램소스를설계하고, 작성하고, 디버깅하는과정을말한다. 프로그램 혹은소프트웨어는컴퓨터에서주어진작업을하는명령어나열을말한다. 데이터베이스프로그래밍은명확한정의는없지만데이터베이스에데이터를정의하고,

More information

목차 BUG offline replicator 에서유효하지않은로그를읽을경우비정상종료할수있다... 3 BUG 각 partition 이서로다른 tablespace 를가지고, column type 이 CLOB 이며, 해당 table 을 truncate

목차 BUG offline replicator 에서유효하지않은로그를읽을경우비정상종료할수있다... 3 BUG 각 partition 이서로다른 tablespace 를가지고, column type 이 CLOB 이며, 해당 table 을 truncate ALTIBASE HDB 6.1.1.5.6 Patch Notes 목차 BUG-39240 offline replicator 에서유효하지않은로그를읽을경우비정상종료할수있다... 3 BUG-41443 각 partition 이서로다른 tablespace 를가지고, column type 이 CLOB 이며, 해당 table 을 truncate 한뒤, hash partition

More information

BSC Discussion 1

BSC Discussion 1 Copyright 2006 by Human Consulting Group INC. All Rights Reserved. No Part of This Publication May Be Reproduced, Stored in a Retrieval System, or Transmitted in Any Form or by Any Means Electronic, Mechanical,

More information

ALTIBASE HDB Patch Notes

ALTIBASE HDB Patch Notes ALTIBASE HDB 6.3.1.10.6 Patch Notes 목차 BUG-45060 offline replication start 와 replication drop 을동시에수행하는경우, replication start 가완료되지않았으면 replication drop 을수행하지못하도록수정하였습니다... 4 BUG-46193 메모리테이블의이중화병렬 sync

More information

ALTIBASE HDB Patch Notes

ALTIBASE HDB Patch Notes ALTIBASE HDB 6.5.1.5.6 Patch Notes 목차 BUG-45643 암호화컬럼의경우, 이중화환경에서 DDL 수행시 Replication HandShake 가실패하는문제가있어수정하였습니다... 4 BUG-45652 이중화에서 Active Server 와 Standby Server 의 List Partition 테이블의범위조건이다른경우에 Handshake

More information

Slide 1

Slide 1 Oracle Database 11gR2 의장점과 Real Application Testing 을활용한업그레이드베스트프랙티스 권희용 Principal Database Sales Consultant Database Technology, Technology Sales Consulting, Oracle Korea Oracle

More information

Microsoft PowerPoint - Tech-iSeminar_Logminer.ppt

Microsoft PowerPoint - Tech-iSeminar_Logminer.ppt Oracle LogMiner 의활용 Tips Getting the most out of MetaLink 천봉격, 김주연 한국오라클 ( 주 ) 제품지원실 기술적인질문은채팅으로 이번세미나에선 Oracle 8i, 9i, 10g 에서 Oracle Logminer 의활용팁에대해알아보도록하겠습니다. 본세미나에서는 Oracle 에서제공되는 Logminer 을사용하여 Online/Offline

More information

thesis

thesis ( Design and Implementation of a Generalized Management Information Repository Service for Network and System Management ) ssp@nile nile.postech.ac..ac.kr DPE Lab. 1997 12 16 GMIRS GMIRS GMIRS prototype

More information

Microsoft Word - 기술노트[19회] Flashback.doc

Microsoft Word - 기술노트[19회] Flashback.doc Goodus 기술노트 [19 회 ] Flashback Author 권웅원, 나지혜 Creation Date 2007-04-25 Last Updated 2007-04-25 Version 1.0 Copyright(C) 2004 Goodus Inc. All Rights Reserved Version 변경일자 변경자 ( 작성자 ) 주요내용 1 2007-04-25 권웅원,

More information

untitled

untitled A Leader of Enterprise e-business Solution FORCS Co., LTD 1 OZ Application Getting Started (ver 5.1) 2 FORCS Co., LTD A Leader of Enterprise e-business Solution FORCS Co., LTD 3 OZ Application Getting

More information

Spring Boot/JDBC JdbcTemplate/CRUD 예제

Spring Boot/JDBC JdbcTemplate/CRUD 예제 Spring Boot/JDBC JdbcTemplate/CRUD 예제 오라클자바커뮤니티 (ojc.asia, ojcedu.com) Spring Boot, Gradle 과오픈소스인 MariaDB 를이용해서 EMP 테이블을만들고 JdbcTemplate, SimpleJdbcTemplate 을이용하여 CRUD 기능을구현해보자. 마리아 DB 설치는다음 URL 에서확인하자.

More information

객관식 1번풀이사전지식 * 비교연산자 ANY ( 서브쿼리 ) - 서브쿼리의결과에존재하는어느하나의값이라도만족하는조건을의미. 비교연산자로 " > " 를사용했다면메인쿼리는서브쿼리의값들중어떤값이라도만족하면되므로서브쿼리의결과의최소값보다큰모든건이조건을만족 - SOME 과동일 1번

객관식 1번풀이사전지식 * 비교연산자 ANY ( 서브쿼리 ) - 서브쿼리의결과에존재하는어느하나의값이라도만족하는조건을의미. 비교연산자로  >  를사용했다면메인쿼리는서브쿼리의값들중어떤값이라도만족하면되므로서브쿼리의결과의최소값보다큰모든건이조건을만족 - SOME 과동일 1번 Study Room Doc.02 : SQLD 예상문제 네이버 Cafe : 데이터베이스전문가포럼 Study Room http://cafe.naver.com/sqlpd SQLD 21 회기출문제를바탕으로작성 작성자 : 월야루 2016-09-04 객관식 1번풀이사전지식 * 비교연산자 ANY ( 서브쿼리 ) - 서브쿼리의결과에존재하는어느하나의값이라도만족하는조건을의미.

More information

PowerPoint Presentation

PowerPoint Presentation FORENSIC INSIGHT; DIGITAL FORENSICS COMMUNITY IN KOREA SQL Server Forensic AhnLab A-FIRST Rea10ne unused6@gmail.com Choi Jinwon Contents 1. SQL Server Forensic 2. SQL Server Artifacts 3. Database Files

More information

Cache_cny.ppt [읽기 전용]

Cache_cny.ppt [읽기 전용] Application Server iplatform Oracle9 A P P L I C A T I O N S E R V E R i Improving Performance and Scalability with Oracle9iAS Cache Oracle9i Application Server Cache... Oracle9i Application Server Web

More information

USER GUIDE

USER GUIDE Solution Package Volume II DATABASE MIGRATION 2010. 1. 9. U.Tu System 1 U.Tu System SeeMAGMA SYSTEM 차 례 1. INPUT & OUTPUT DATABASE LAYOUT...2 2. IPO 중 VB DATA DEFINE 자동작성...4 3. DATABASE UNLOAD...6 4.

More information

歯CRM개괄_허순영.PDF

歯CRM개괄_허순영.PDF CRM 2000. 8. KAIST CRM CRM CRM CRM :,, KAIST : 50%-60%, 20% 60%-80%. AMR Research 10.. CRM. 5. Harvard Business review 60%, 13%. Michaelson & Associates KAIST CRM? ( ),,, -,,, CRM needs,,, dynamically

More information

最即時的Sybase ASE Server資料庫診斷工具

最即時的Sybase ASE Server資料庫診斷工具 TOAD 9.5 Toad Oracle 料 SQL 料 行 理 SQLprofile Quest Software 了 Oracle -Toad Tools of Oracle Application Developers Toad 了 DBA DBA 理 易 度 Toad 料 SQL PL/SQL Toad Oracle PL/SQL Toad Schema Browser Schema Browser

More information

Jerry Held

Jerry Held Principal Sales Consultant e-business Intelligence Topics Business Intelligence e-business Intelligence BI System Oracle9i BI Applications BI architectures BI architecture Conclusions 4 5 Typical Data

More information

ecorp-프로젝트제안서작성실무(양식3)

ecorp-프로젝트제안서작성실무(양식3) (BSC: Balanced ScoreCard) ( ) (Value Chain) (Firm Infrastructure) (Support Activities) (Human Resource Management) (Technology Development) (Primary Activities) (Procurement) (Inbound (Outbound (Marketing

More information

Microsoft PowerPoint - CNVZNGWAIYSE.pptx

Microsoft PowerPoint - CNVZNGWAIYSE.pptx 대용량데이터처리를위한 Sharding 2013.1. 이동현 DBMS 개발랩 /NHN Business Platform SQL 기술전략세미나 2 대용량데이터를위한솔루션은 NoSQL 인가, RDBMS 인가? 모든경우에대해어떤하나의선택을하자는게아닙니다. SQL 기술전략세미나 3 언제, 그리고왜 RDBMS 를선택해야하는가? NoSQL 과다른 RDBMS 만의특징이필요할때

More information

´ÙÁß Row °á°ú¸¦ ´ÜÀÏÇàÀ¸·Î Äĸ¶·Î ºÐ¸®ÇØ Ãâ·ÂÇÏ´Â ¹æ¹ý

´ÙÁß Row °á°ú¸¦ ´ÜÀÏÇàÀ¸·Î Äĸ¶·Î ºÐ¸®ÇØ Ãâ·ÂÇÏ´Â ¹æ¹ý 5 중 1 2007-06-12 오후 5:52 Home Login Register SQL Query SQL Tuning Oracle Administration Tools References Boards SoQooL? 쏘쿨 SoQooL) 이란? Q&A Tips Lectures Function Lectures Oracle Spatial Tips Scripts SQL

More information

DB 성능고도화핵심원리 비투엔컨설팅 수석컨설턴트 조시형

DB 성능고도화핵심원리 비투엔컨설팅 수석컨설턴트 조시형 DB 성능고도화핵심원리 비투엔컨설팅 수석컨설턴트 조시형 원리를알아야답이보인다!! SQL 개발자 (Developer) 데이터모델을통해업무를이해하고, SQL 을정확히구사하는능력 DB 성능고도화전문가양성 SQL 전문가 (Professional) 성능을고려한고급 SQL 작성능력 DB 성능고도화핵심원리실습문제 declare l_ 수납금액 number; begin for

More information

CRM Fair 2004

CRM Fair 2004 easycrm Workbench ( ) 2004.04.02 I. CRM 1. CRM 2. CRM 3. II. easybi(business Intelligence) Framework 1. 2. - easydataflow Workbench - easycampaign Workbench - easypivot Reporter. 1. CRM 1.?! 1.. a. & b.

More information

김기남_ATDC2016_160620_[키노트].key

김기남_ATDC2016_160620_[키노트].key metatron Enterprise Big Data SKT Metatron/Big Data Big Data Big Data... metatron Ready to Enterprise Big Data Big Data Big Data Big Data?? Data Raw. CRM SCM MES TCO Data & Store & Processing Computational

More information

ARMBOOT 1

ARMBOOT 1 100% 2003222 : : : () PGPnet 1 (Sniffer) 1, 2,,, (Sniffer), (Sniffer),, (Expert) 3, (Dashboard), (Host Table), (Matrix), (ART, Application Response Time), (History), (Protocol Distribution), 1 (Select

More information

Microsoft Word - 07_TRIGGER.doc

Microsoft Word - 07_TRIGGER.doc DATABASE TRIGGER INTRODUCTION 프로그램단위 1 의하나인트리거 2 (Trigger) 는테이블, 뷰, 스키마또는데이터베이스에관련된 PLSQL 블록 ( 또는프로시저 ) 으로관련된특정사건 (Event) 이발생될때마다묵시적 ( 자동 ) 으로실행이이루어진다 (Fire). 트리거를생성할때관련된사건 (Event) 도함께정의되며, 실행부에서저장된프로시저

More information

Simplify your Job Automatic Storage Management DB TSC

Simplify your Job Automatic Storage Management DB TSC Simplify your Job Automatic Storage Management DB TSC 1. DBA Challenges 2. ASM Disk group 3. Mirroring/Striping/Rebalancing 4. Traditional vs. ASM 5. ASM administration 6. ASM Summary Capacity in Terabytes

More information

NoSQL

NoSQL MongoDB Daum Communications NoSQL Using Java Java VM, GC Low Scalability Using C Write speed Auto Sharding High Scalability Using Erlang Read/Update MapReduce R/U MR Cassandra Good Very Good MongoDB Good

More information

세미나(장애와복구-수강생용).ppt

세미나(장애와복구-수강생용).ppt DB PLAN Consultant jina6678@yahoo.co.kr 011-864-1858 - - 1. 2. DB 3. - 4. - 5. 6. 1 INSTANCE MMAN RECO RFS MRP ORBn RBAL MMON Dnnn Snnn Data Buffer Cache SGA Stream Pool Shared pool Large Pool PGA Log

More information

Microsoft Word - 05_SUBPROGRAM.doc

Microsoft Word - 05_SUBPROGRAM.doc ORACLE SUBPROGRAM INTRODUCTION PLSQL 은오라클에서제공하는프로그래밍언어이다. 이는데이터베이스언어인 SQL 과함께효과적으로데이터베이스에접근할수있는방법을제공하고있다. Procedural LanguageSQL 의약자에서볼수있듯이절차적인기능을기본적으로가지는프로그래밍언어이다. PLSQL 은기본적으로블록 (BLOCK) 구조를가지고있다. 블록의기본적인구성은선언부

More information

6주차.key

6주차.key 6, Process concept A program in execution Program code PCB (process control block) Program counter, registers, etc. Stack Heap Data section => global variable Process in memory Process state New Running

More information

그리고.. 엑셀에하나둘완료된쿼리가늘어날때마다... 희열을느낀다... 이글을보는당신은어떻게할것인가? A 군의판단이잘못된것인가? 잘못된판단이아니다최선의판단이다... 11g 전까지는... 11g New Feature 인 Pending Statistics 를 SPA 와함께사용

그리고.. 엑셀에하나둘완료된쿼리가늘어날때마다... 희열을느낀다... 이글을보는당신은어떻게할것인가? A 군의판단이잘못된것인가? 잘못된판단이아니다최선의판단이다... 11g 전까지는... 11g New Feature 인 Pending Statistics 를 SPA 와함께사용 SPA(SQL Performance Analyze) 를이용한통계정보 수집 엑셈컨설팅본부 /DB 컨설팅팀오경렬 1. SPA 란? SPA(SQL Performance Analyze) 는 RAT(Real Application Testing) 서비스의옵션중에하나 이다. 본문서는 SPA 를이용해좀더안정적으로통계정보를생성하는 Process 에대해다룬다. 2. 통계정보와

More information

zb 2) zb3) 나 위 시와 보기의 공통적인 표현 방법이 아닌 것은? 뻐꾹새야 뻐꾹새야 뻐꾹뻐꾹 울어 주면 < 보기> 고개를 넘어서 마을로 뻐꾹새야 뻐꾹새야 뻐꾹뻐꾹 울어 주면 밭을 매는 우리 엄마 허리 허리 덜 아프고 ᄂ밭을 매는 우리 엄마 허리 허리 덜 아프고

zb 2) zb3) 나 위 시와 보기의 공통적인 표현 방법이 아닌 것은? 뻐꾹새야 뻐꾹새야 뻐꾹뻐꾹 울어 주면 < 보기> 고개를 넘어서 마을로 뻐꾹새야 뻐꾹새야 뻐꾹뻐꾹 울어 주면 밭을 매는 우리 엄마 허리 허리 덜 아프고 ᄂ밭을 매는 우리 엄마 허리 허리 덜 아프고 zb1) 중 2014 년 1학기 중간고사 대비 국어 콘텐츠산업 진흥법 시행령 제33조에 의한 표시 1) 제작연월일 : 2014-03-04 2) 제작자 : 교육지대 3) 이 콘텐츠는 콘텐츠산업 진흥법 에 따라 최초 제작일부터 5 년간 보호됩니 콘텐츠산업 진흥법 외에도 저작권법 에 의하여 보호되는 콘텐츠의 경우, 그 콘텐츠의 전부 또는 일부 를 무단으로 복제하거나

More information

PowerPoint 프레젠테이션

PowerPoint 프레젠테이션 @ Lesson 3 if, if else, if else if, switch case for, while, do while break, continue : System.in, args, JOptionPane for (,, ) @ vs. logic data method variable Data Data Flow (Type), ( ) @ Member field

More information

Oracle Apps Day_SEM

Oracle Apps Day_SEM Senior Consultant Application Sales Consulting Oracle Korea - 1. S = (P + R) x E S= P= R= E= Source : Strategy Execution, By Daniel M. Beall 2001 1. Strategy Formulation Sound Flawed Missed Opportunity

More information

Manufacturing6

Manufacturing6 σ6 Six Sigma, it makes Better & Competitive - - 200138 : KOREA SiGMA MANAGEMENT C G Page 2 Function Method Measurement ( / Input Input : Man / Machine Man Machine Machine Man / Measurement Man Measurement

More information

ORACLE EXADATA HCC 압축방식이해하기 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 시간이지나면서데이터는급속하게증가하고있다. 데이터가증가함에따라 DBMS 에서관리되어지는정보도급속하게증가하고있다. 이로인해저장공간의부족으로하드웨어비용의증가와데이터처리성능에많은문제점들

ORACLE EXADATA HCC 압축방식이해하기 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 시간이지나면서데이터는급속하게증가하고있다. 데이터가증가함에따라 DBMS 에서관리되어지는정보도급속하게증가하고있다. 이로인해저장공간의부족으로하드웨어비용의증가와데이터처리성능에많은문제점들 ORACLE EXADATA HCC 압축방식이해하기 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 시간이지나면서데이터는급속하게증가하고있다. 데이터가증가함에따라 DBMS 에서관리되어지는정보도급속하게증가하고있다. 이로인해저장공간의부족으로하드웨어비용의증가와데이터처리성능에많은문제점들이나타나고있다. 이러한문제점들을해결하고자 ORACLE 에서는 EXADATA 라는시스템을통해스토리지공간부족현상과데이터처리성능을향상시키고자하였다.

More information