<11/26> HIGH HIGH

Query Transformer는 계속해서 공부해야할 분야다

_optimizer_push_pred_cost_based

9i = false rule-based

orcl@SYSTEM> alter table tab1 add(col1 varchar(10),col2 varchar(10),

col3 varchar(10),col4 varchar(10));

orcl@SYSTEM> alter table tab1 add(col5 varchar(10));

orcl@SYSTEM> alter table tab2 add(pk1 varchar(10), pk2 varchar(10));

orcl@SYSTEM> insert into tab1(col5) values ('AAAA');

orcl@SYSTEM> insert into tab1(col5) values ('BBBB');

orcl@SYSTEM> insert into tab1(col5) values ('CCCC');

orcl@SYSTEM> insert into tab1(col5) values ('DDDD');

orcl@SYSTEM> create index idx_05 on tab1(col5);

orcl@SYSTEM> create index tab2_pk1_pk2 on tab2(pk1,pk2);

select LAST_ANALYZED from dba_tables order by last_analyzed;

ndv number of distinct value

고객 번호 PK 비디오테입 FK

ERD의 관계성은 1대N N대N의 관계성을 가지고 있다

모델링에서 부서는 고정적 프로젝트는 유동적 트랜잭션 발생 가능성 높음 따로 정규화 필요

acid 모든 테이블의 속성

지나친 정규화는 성능 x 최적화에 대한 고민

------------------------------------------

파티션 테이블의 장점

I/O 분산효과 동시성 제어

대용량 데이터라는 전제하에 물리적으로 쪼갠다(논리적 속성은 같다)

하나의 세그먼트에 저장됨

가용성 부분적 복구

관리성 부분적 backup 추가 삭제 변경

성능 DML QUERY 성능 향상 DW에서 주로 사용

OLTP에서는? Hot Spot 분산

결과집합산출을 위해 스캔하는 데이터량을 줄일 수 있기 때문에 성능 향상 기대

대용량 heap table 문제

보관 주기 관리의 어려움 

지우는 양 만큼의 언두 및 리두량 발생 및 인덱스에 대한 리빌드 요구

1row access 성능 저하

branch node의 길이가 길어지고 leaf block depth가 깊어진다 더 많은 block access 발생

주기적 월 리포트 작업의 성능저하

손익분기점의 초과로 인한 FULL TABLE SCAN 발생 

파티션 변경

drop partition  언두 리두 소량 발생

인덱스의 파티션화로 인한 depth가 짧아짐

parallel processcion 을 이용한 partition full scan 권장

파티션 테이블에서의 parallel processing 파티션테이블의 parallel 프로세싱시에는

명시해줘야 한다  

11g new feature interval 물리적 조정x 관리의 용이성

T1(a,b,a+b) a+b를 통한 virtual column based partitioning

=====================================

chmod +x *

SELECT COUNT(*)

  2  FROM   products p

  3  WHERE  prod_list_price < 1.15 * (SELECT avg(unit_cost)

  4                                   FROM costs c

  5                                   WHERE c.prod_id = p.prod_id);

--------------------------------------------------------------------------------------------

| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |          |     1 |     9 |  2651   (1)| 00:00:32 |        |

|   1 |  SORT AGGREGATE        |          |     1 |     9 |            |          |        |

|*  2 |   FILTER               |          |       |       |            |          |        |

|   3 |    TABLE ACCESS FULL   | PRODUCTS |    72 |   648 |     3   (0)| 00:00:01 |        |

|   4 |    SORT AGGREGATE      |          |     1 |     9 |            |          |        |

|   5 |     PARTITION RANGE ALL|          |  1140 | 10260 |    74   (2)| 00:00:01 |     1  |

|*  6 |      TABLE ACCESS FULL | COSTS    |  1140 | 10260 |    74   (2)| 00:00:01 |     1  |

--------------------------------------------------------------------------------------------

  2 - filter("PROD_LIST_PRICE"<1.15* (SELECT AVG("UNIT_COST") FROM "COSTS" "C"  WHERE

              "C"."PROD_ID"=:B1))

   6 - filter("C"."PROD_ID"=:B1)

SELECT COUNT(*)

  2  FROM   products p, (SELECT prod_id, AVG(unit_cost) ac FROM costs GROUP BY prod_id) c

  3  WHERE  p.prod_id = c.prod_id AND

  4         p.prod_list_price < 1.15 * c.ac;

---------------------------------------------------------------------------------------------

| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstarp |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |          |     1 |    35 |    80   (5)| 00:00:01 |        |

|   1 |  SORT AGGREGATE         |          |     1 |    35 |            |          |        |

|*  2 |   HASH JOIN             |          |     4 |   140 |    80   (5)| 00:00:01 |        |

|   3 |    TABLE ACCESS FULL    | PRODUCTS |    72 |   648 |     3   (0)| 00:00:01 |        |

|   4 |    VIEW                 |          |    72 |  1872 |    77   (6)| 00:00:01 |        |

|   5 |     HASH GROUP BY       |          |    72 |   648 |    77   (6)| 00:00:01 |        |

|   6 |      PARTITION RANGE ALL|          | 82112 |   721K|    74   (2)| 00:00:01 |     18 |

|   7 |       TABLE ACCESS FULL | COSTS    | 82112 |   721K|    74   (2)| 00:00:01 |     18 |

---------------------------------------------------------------------------------------------

 2 - filter("PROD_LIST_PRICE"<1.15* (SELECT AVG("UNIT_COST") FROM "COSTS" "C" WHERE

              "C"."PROD_ID"=:B1))

   6 - filter("C"."PROD_ID"=:B1)

orcl@HR> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 72609621

--------------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |    10 |   170 |     3   (0)| 00:00:01 |

|*  1 |  FILTER            |           |       |       |            |          |

|*  2 |   TABLE ACCESS FULL| EMPLOYEES |    10 |   170 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(CASE  WHEN ("SALARY">=10000 AND "SALARY"<=15000) THEN

              SYS_AUDIT('HR','EMPLOYEES','EMP_POL1',3) ELSE NULL END  IS NULL)

   2 - filter("SALARY">=10000 AND "SALARY"<=12000)

16 rows selected.

create table drawing (

dwg1 number(3),

dwg2 number(3),

dwg3 number(3) );

insert into drawing values(4,3,4);

insert into drawing values(0,2,35);

insert into drawing values(12,3,2);

VARIABLE dwg1 NUMBER

VARIABLE dwg2 NUMBER

VARIABLE dwg3 NUMBER

SELECT  DWG_QTY

FROM    DRAWING

WHERE   DWG1  ||  DWG2 || DWG3   >  :DWG1 ||  :DWG2  ||  :DWG3

AND     ROWNUM  =  1 ;

alter table drawing add ( dwg_qty varchar2(10));

create index dwg_x1 on drawing(dwg1);

create index dwg_x2 on drawing(dwg2);

create index dwg_x3 on drawing(dwg3);

SELECT  /*+ USE_CONCAT */ DWG_QTY

FROM    DRAWING

WHERE   ((DWG1 = :DWG1  AND  DWG2 = :DWG2  AND  DWG3 > :DWG3)

        OR (DWG1 = :DWG1  AND  DWG2 > :DWG2)

        OR (DWG1 > :DWG1))

AND     ROWNUM  =  1 ;