Mysql 8 vs PostgreSQL 10 간단 비교

Knowledge/MySQL 2019. 1. 2. 15:59

2018년 11월쯤에 조사했던 내용

gru의 블로그나 제품 매뉴얼에서 조사

틀린 내용이 있을 수 있으니 일반 참고 측면에서만 참고


추가 비교할 사항들이 있겠지만 중점적으로 본 내용 위주로 정리한 내용

둘 다 가지고 있는 기능(FK등)이나 복제 관련은 논하지 않음


※ Mysql은 기본 스토리지 엔진인 InnoDB 기준


Mysql8

버전 정보

Mysql8 = Mysql 5.8
8.0.0 출시일 2016. 9. 12.
8.0.13 출시일 2018. 10. 22.
5.7부터 performance_schema 생성

기능

예전까지는 PostgreSQL에 비해 전체적으로 SQL 등 기능이 적었다
Mysql 8이 나오면서 간격은 매우 좁혀짐
Mysql 8부터 힌트도 더 나아짐
CTE(common table expressions), Windows Function 추가 등
PostgreSQL을 쓰는 주된 이유였지만 이젠 아니게 됐다

단점

Oracle사의 소유라는 공포

단, 개발은 Oracle 사로 넘어간 뒤 더 가속화

No Hash Join

MariaDB는 있음

대량 데이터 처리 불리

Merge Join도 없음

PostgreSQL 10

버전 정보

PostgreSQL10

10.0 출시일 2017. 10. 5.
10.6 출시일 2018. 11. 8.

PostgreSQL11

11.0 출시일 2018. 10. 18.

단점

복제 설정의 유연성 결여

우버가 mysql로 전환한 이유 중 하나

이젠 post도 된다고 하는데..

Overhead UPDATE

우버가 mysql로 전환한 이유 중 하나

하지만 많은 PostgreSQL 옹호자들이 반박하는 내용들이 존재합니다

SQL 성능이나 파일 크기에 대해 불안정하고 예상하기 어렵다

Vacuum은 너무 비싼 작업

Update에 관해선 Mysql이 더 낫다

비교


 항목

Mysql 8

PostgreSQL 10 

 비고

 프로세스 방식

MultiThread 

MultiProcess 

PostgreSQL이 세션당 메모리 사용량이 더 높다 

 테이블 구조

Cluster Index(IOT)

HEAP(IOT 미지원)

InnoDB 엔진 기준 

 Non-Partitioned Index

미지원

지원 

파티션 테이블 대상 

 Update 방식

별도 Rollback Segment 사용 

데이터와 같은 Block 사용 

 Redo/복제 로그

별도

동일 

 Block Size

16KB 

8KB

InnoDB 엔진 기준 

 Update Overhead

Bloat 현상 x

파일크기/SQL성능 Stable

Bloat 현상 o

파일크기/SQL성능 Unstable

Heavy UPDATE WORKLOAD 기준 

 대량 Insert

PostgreSQL 대비 불리 

Mysql 대비 유리


선택

Mysql8

OLTP에 BEST
DW에 불리

대량 처리용 Join이 없다

Only NL join

대량 처리용 Insert가 느리다(IOT)

PostgreSQL10

DW에 BEST
OLTP에 불리한 이유

빈번한 UPDATE 성격일 경우 성능/용량 불안정

감안하고 사용하더라도 세심한 Vacuum 관리 필요

INSERT 위주 성격일 경우에는 적합

결론

"Mysql이 좋은가 PostgreSQL이 좋은가" : X


"Mysql 이나 PostgreSQL 사용시 무엇을 고려 해야 하는가" : O

DB별 특징을 참고하여 구축시 성격에 맞는 DB 선택하고 테스트해봐야 합니다.

저작자표시 비영리 변경금지

'Knowledge > MySQL' 카테고리의 다른 글

Mysql 8.0 InnoDB Storage Engine Features  (0) 2019.02.12
reset root password  (0) 2015.01.19
mysql parameter 튜닝했던 기록  (0) 2015.01.14
Posted by neo-orcl
,

DDL log 별도 저장

Knowledge/12c New Feature 2018. 12. 19. 22:03

컨셉 매뉴얼을 잠깐 참조하다가 DDL Log가 있어 확인해본 내용 정리

 

예전에 audit 말고 ddl을 남기는 방법을 찾다가

 

ALERT LOG에 DDL 기록을 남기기 위해 enable_ddl_logging 파라미터를 확인해서 적용했던 적이 있는데

결국엔 다시 껐다.

 

이유는 당시 적용한 db는 주기적인 datapump 작업이 자주 일어나고 있었는데,

datapump 작업시의 임시 테이블이나 뷰 생성과 DROP에 기록도 남아 DB Alert log 를 더럽혔기 때문이다.

 

 

12c부터(R1인지 R2인지 정확히 모름) DDL 로그가 전용으로 생겼다

 

위치1 text file

    $ORACLE_BASE/diag/rdbms/<dbname>/<sid>/log/ddl_<sid>.log

위치2 xml file

    $ORACLE_BASE/diag/rdbms/<dbname>/<sid>/log/ddl/log.xml

 

설정:

SQL> show parameter ddl
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE
 
SQL> alter system set enable_ddl_logging=true;
 
System altered.

 

expdp를 수행해 본다

[oracle@test ~]$ expdp system schemas=scott
 
Export: Release 12.2.0.1.0 - Production on Wed Dec 19 21:09:45 2018
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password:
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=scott
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."T1"                                104.0 KB   10100 rows
. . exported "SCOTT"."EMP"                               8.781 KB      14 rows
. . exported "SCOTT"."DEPT"                              6.031 KB       4 rows
. . exported "SCOTT"."SALGRADE"                          5.960 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/orcl12c/dpdump/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Dec 19 21:11:10 2018 elapsed 0 00:01:21

 

ddl 로그 파일을 확인해보니

[oracle@test log]$ cat ddl_orcl12c.log
2018-12-19T21:09:21.251992+09:00
diag_adl:CREATE TABLE "SYSTEM"."SYS_EXPORT_SCHEMA_01" --생략--

2018-12-19T21:09:22.504407+09:00

diag_adl:CREATE OR REPLACE FORCE VIEW "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_F" sharing=none  AS SELECT --생략--

diag_adl:CREATE OR REPLACE FORCE VIEW "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_F" sharing=none  AS SELECT --생략--

2018-12-19T21:09:26.354288+09:00
diag_adl:DROP TABLE "SYSTEM"."SYS_EXPORT_SCHEMA_01" PURGE
2018-12-19T21:09:48.012644+09:00
diag_adl:CREATE TABLE "SYSTEM"."SYS_EXPORT_SCHEMA_01"  --생략--
diag_adl:CREATE OR REPLACE FORCE VIEW "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_F" sharing=none  AS SELECT --생략-- 

diag_adl:CREATE OR REPLACE FORCE VIEW "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_F" sharing=none  AS SELECT --생략--

2018-12-19T21:09:51.515314+09:00

diag_adl:ALTER TABLE "SYSTEM"."SYS_EXPORT_SCHEMA_01" ADD (UNIQUE (process_order, duplicate))
diag_adl:CREATE INDEX SYS_MTABLE_000005D25_IND_1 ON "SYSTEM"."SYS_EXPORT_SCHEMA_01" (object_schema, original_object_name, object_type)
diag_adl:CREATE INDEX SYS_MTABLE_000005D25_IND_2 ON "SYSTEM"."SYS_EXPORT_SCHEMA_01" (object_schema, object_name, object_type, partition_name, subpartition_name)
diag_adl:CREATE INDEX SYS_MTABLE_000005D25_IND_3 ON "SYSTEM"."SYS_EXPORT_SCHEMA_01" (base_process_order)
diag_adl:CREATE INDEX SYS_MTABLE_000005D25_IND_4 ON "SYSTEM"."SYS_EXPORT_SCHEMA_01" (original_object_schema, original_object_name, partition_name)
diag_adl:CREATE INDEX SYS_MTABLE_000005D25_IND_5 ON "SYSTEM"."SYS_EXPORT_SCHEMA_01" (seed)
2018-12-19T21:09:59.614785+09:00
diag_adl:truncate table "SYS"."IMPDP_STATS"
2018-12-19T21:10:03.507561+09:00
diag_adl:truncate table sys.spd_scratch_tab
2018-12-19T21:11:13.637296+09:00
diag_adl:DROP TABLE "SYSTEM"."SYS_EXPORT_SCHEMA_01" PURGE

 

이 내용들이 db alertlog에 남는다면 db alert log가 매우 지저분해질 수 있다.

 

일반 유저의 DDL은 당연히 남을 것이고

audit는 sys 유저는 별도 설정 안하면 안남는 이슈가 있는데, 과연 이 기능은 남을까?

 

SQL> show parameter audit_sys_op
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE  
 
SQL> show user
USER is "SYS"
SQL> create table t1 (c1 number);
 
Table created.

 

ddl 로그를 확인해본다.

[oracle@test log]$ cat ddl_orcl12c.log
2018-12-19T21:52:56.253252+09:00
diag_adl:create table t1 (c1 number)

sys에서 수행한 ddl도 남는다

 

ddl 전용 로그가 생겨서인지 db alert log에는 ddl 로그가 안남는걸 확인했다.

 

간편히 사용하고 싶다면 12c이상에선 이 방법도 고려해볼만 하겠다.

 

단, AUDIT과는 다르게 위 로그에서 알 수 있듯이 단순히 "언제","어떤 SQL" 정보만 남고 어떤 machine에서 어떤 유저가 수행했는지 나오지 않는다.


19-03 추가

11g에선 Oracle Change Management Pack 옵션 라이센스가 필요하고

12c부턴 Oracle Lifecycle Management Pack 옵션 라이센스가 필요한 파라미터이다

이 옵션은 사는 고객사를 본 적이 드물다. 파라미터 변경하지 말도록 하자.

저작자표시 비영리 변경금지

'Knowledge > 12c New Feature' 카테고리의 다른 글

19c long term support 기간  (0) 2019.07.22
19c 기대했던 기능들. automatic indexing, realtime-statistics. Exa/Cloud only  (0) 2019.03.08
Oracle 12c Default audit policy include release 2  (0) 2017.09.26
12c의 ORA_STIG_PROFILE  (0) 2016.08.26
RMAN recover table point in time  (0) 2016.03.06
Posted by neo-orcl
,

Local Partition INDEX Test

Knowledge/Oracle 2018. 4. 24. 00:33

■ 개요

유니크 인덱스를 위주로 관리 편의를 위해 LOCAL 인덱스로 변경하거나 처음부터 LOCAL 인덱스로 설계할 때 고려해야할 점을 간단히 테스트해보고자 한다.

최근 고객사에서 이걸 고려하는 건이 있어 정리겸.


■ 사전 스크립트

CREATE TABLE sales
  ( prod_id       NUMBER
  , cust_id       NUMBER
  , time_id       DATE
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_2017_12 VALUES LESS THAN (TO_DATE('01-JAN-2018','dd-MON-yyyy'))
    TABLESPACE users
 , PARTITION sales_2018_01 VALUES LESS THAN (TO_DATE('01-FEB-2018','dd-MON-yyyy'))
    TABLESPACE users
 , PARTITION sales_2018_02 VALUES LESS THAN (TO_DATE('01-MAR-2018','dd-MON-yyyy'))
    TABLESPACE users
 , PARTITION sales_2018_03 VALUES LESS THAN (TO_DATE('01-APR-2018','dd-MON-yyyy'))
    TABLESPACE users
 , PARTITION sales_2018_04 VALUES LESS THAN (TO_DATE('01-MAY-2018','dd-MON-yyyy'))
    TABLESPACE users
 );
 
 create sequence sales_seq;
 
 insert into sales values(sales_seq.nextval,1121,to_date('20180101','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1122,to_date('20180101','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1120,to_date('20180101','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1111,to_date('20180101','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1123,to_date('20180201','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1123,to_date('20180201','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1234,to_date('20180201','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1643,to_date('20180201','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1111,to_date('20180301','yyyymmdd'));
 insert into sales values(sales_seq.nextval,2222,to_date('20180301','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1321,to_date('20180301','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1955,to_date('20180301','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1754,to_date('20180401','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1027,to_date('20180401','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1013,to_date('20180401','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1100,to_date('20180401','yyyymmdd'));
 commit;
create unique index sales_pk on sales(prod_id) local;
--유니크 인덱스는 파티션 키를 포함해야 local로 만들어진다.
                                *
--ERROR at line 1:
--ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index 
create unique index sales_pk on sales(prod_id,time_id) local;
create index sales_custid on sales(cust_id) local;
exec dbms_stats.gather_table_stats(user,'sales');
alter session set statistics_level=all;
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
select /*+gather_plan_statistics*/ * from sales;
   PROD_ID    CUST_ID TIME_ID
---------- ---------- -----------------
        21       1121 20180101 00:00:00
        22       1122 20180101 00:00:00
        23       1120 20180101 00:00:00
        24       1111 20180101 00:00:00
        25       1123 20180201 00:00:00
        26       1123 20180201 00:00:00
        27       1234 20180201 00:00:00
        28       1643 20180201 00:00:00
        29       1111 20180301 00:00:00
        30       2222 20180301 00:00:00
        31       1321 20180301 00:00:00
        32       1955 20180301 00:00:00
        33       1754 20180401 00:00:00
        34       1027 20180401 00:00:00
        35       1013 20180401 00:00:00
        36       1100 20180401 00:00:00

■ 엑세스 테스트
이제 where prod_id=NN, where cust_id=NN 할 때 어떻게될까?
1. 파티션 하나의 인덱스만 액세스한다
2. 파티션 전부의 인덱스를 액세스한다?
select /*+gather_plan_statistics*/ * from sales where prod_id=28;
set long 5000 line 200 pages 1000
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |          |      1 |       |       |      1 |00:00:00.01 |       5 |
|   1 |  PARTITION RANGE ALL               |          |      1 |     1 |     5 |      1 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES    |      5 |     1 |     5 |      1 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN                | SALES_PK |      4 |     1 |     5 |      1 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------
 
 
select /*+gather_plan_statistics*/ * from sales where cust_id=1643;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |      1 |       |       |      1 |00:00:00.01 |       5 |
|   1 |  PARTITION RANGE ALL               |              |      1 |     1 |     5 |      1 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES        |      5 |     1 |     5 |      1 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN                | SALES_CUSTID |      4 |     1 |     5 |      1 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------------------------

파티션을 하나 추가한다면 버퍼가 하나 더 늘것이다. 데이터가 없어 인덱스 블럭이 하나이기 때문이다
alter session set deferred_segment_creation=false;
alter table sales add partition sales_2018_05 VALUES LESS THAN (TO_DATE('01-JUN-2018','dd-MON-yyyy'))
    TABLESPACE users;
exec dbms_stats.GATHER_TABLE_STATS(null,'sales',no_invalidate=>false);
 
select /*+gather_plan_statistics*/ * from sales where prod_id=28;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |          |      1 |       |       |      1 |00:00:00.01 |       6 |
|   1 |  PARTITION RANGE ALL               |          |      1 |     1 |     6 |      1 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES    |      6 |     1 |     6 |      1 |00:00:00.01 |       6 |
|*  3 |    INDEX RANGE SCAN                | SALES_PK |      5 |     1 |     6 |      1 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------------------
 
select /*+gather_plan_statistics*/ * from sales where cust_id=1643;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |      1 |       |       |      1 |00:00:00.01 |       6 |
|   1 |  PARTITION RANGE ALL               |              |      1 |     1 |     6 |      1 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES        |      6 |     1 |     6 |      1 |00:00:00.01 |       6 |
|*  3 |    INDEX RANGE SCAN                | SALES_CUSTID |      5 |     1 |     6 |      1 |00:00:00.01 |       5 |
--------------------------------------------------------------------------------------------------------------------

 

예상대로 하나가 늘었다. 추가된 sales_2018_05 Local Index 블럭이 하나 늘었기 때문이다.

당연히 partition 조건도 같이 들어온다면 single partition만 스캔할것이다.

select /*+gather_plan_statistics*/ * from sales where prod_id=28 and time_id=to_date('20180201 00:00:00','yyyymmdd hh24:mi:ss');
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |          |      1 |       |       |      1 |00:00:00.01 |       2 |
|   1 |  PARTITION RANGE SINGLE            |          |      1 |     3 |     3 |      1 |00:00:00.01 |       2 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES    |      1 |     3 |     3 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN               | SALES_PK |      1 |     3 |     3 |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------------------

 

마지막으로 prod_id는 시퀀스에 의해 right grow Index라서 Heavy하게 Insert될 경우 Index Split 현상으로 경합이 발생하기에
hash partition 인덱스를 만들어서 테스트해본다.

drop index sales_pk;
 
create index sales_pk on sales(prod_id) global
partition by hash(prod_id)
partitions 10
store in (users);
 
select /*+gather_plan_statistics*/ * from sales where prod_id=23;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |      1 |       |       |      1 |00:00:00.01 |       3 |
|   1 |  PARTITION HASH SINGLE              |          |      1 |     7 |     7 |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES    |      1 | ROWID | ROWID |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN                 | SALES_PK |      1 |     7 |     7 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------
 
실무에서 그럴일은 없겠지만 Hash 파티션된 대상에 부등호 조건으로 조회한다면 어찌될까
select /*+gather_plan_statistics*/ * from sales where prod_id<=22;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |      1 |       |       |      2 |00:00:00.01 |      13 |
|   1 |  PARTITION HASH ALL                 |          |      1 |     1 |    10 |      2 |00:00:00.01 |      13 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES    |     10 | ROWID | ROWID |      2 |00:00:00.01 |      13 |
|*  3 |    INDEX RANGE SCAN                 | SALES_PK |     10 |     1 |    10 |      2 |00:00:00.01 |      11 |
-----------------------------------------------------------------------------------------------------------------

 

비파티션은 테스트는 생략한다. 위 결과와 의미는 같다.


■결론

테이블을 날짜나 월 등으로 나누어 파티션하고 과거 파티션을 주기적으로 DROP하려고 한다면 LOCAL 인덱스가 관리에 유리하다.
단, 파티션 키가 조건에 같이 사용되지 않는다면 성능은 나쁠 것이다.
미래의 파티션을 만들어둘 경우 파티션의 갯수만큼 블럭 접근이 더 많아질 것이다만 한 파티션당 1 블럭 정도야 문제 없을 것이다.
문제는 과거의 파티션이다. 과거의 파티션은 RANSE SCAN을 위해 파티션 인덱스마다 root-branch를 거쳐 leaf 블럭을
하나는 접근하게 될 것이다.
데이터가 많으면 blevel이 높을 것이다. 추출 행이 적다면 포기할 성능 수준은 감안할 수 있지만추출행이
추출행이 많고 NL같은 조인이 일어난다면 그 차이는 심각해진다.
비파티션은 Leaf 블럭까지 접근은 한번만 일어날 것이다.

한가지 더 짚고 넘어가야 할 것은 중복 무결성 문제다.
UNIQUE 조건은 LOCAL 인덱스를 만들기 위해 파티션된 컬럼을 포함해줘야 한다.
만약 prod_id가 시퀀스를 통해 입력되지 않았다면? prod_id를 UPDATE하는 건이 있다면?

   PROD_ID    CUST_ID TIME_ID
---------- ---------- -----------------
        21       1121 20180101 00:00:00
        22       1122 20180101 00:00:00
        23       1120 20180101 00:00:00
        24       1111 20180101 00:00:00
        25       1123 20180201 00:00:00
        26       1123 20180201 00:00:00
        27       1234 20180201 00:00:00
        28       1643 20180201 00:00:00
        29       1111 20180301 00:00:00
        30       2222 20180301 00:00:00
        31       1321 20180301 00:00:00
        32       1955 20180301 00:00:00
        33       1754 20180401 00:00:00
        34       1027 20180401 00:00:00
        35       1013 20180401 00:00:00
        36       1100 20180401 00:00:00
 
Insert into sales values(36, 1231 , to_date('20180401 00:00:01','yyyymmdd hh24:mi:ss'));


 

만약 global이나 non-partition으로 unique 인덱스를 만들었다면 에러 나겠지만,
local로 prod_id+time_id를 만들었다면 정상적으로 입력되어 버린다.

drop index sales_pk;
create unique index sales_pk on sales(prod_id,time_id) local;
 
SQL> Insert into sales values(36, 1231 , to_date('20180401 00:00:01','yyyymmdd hh24:mi:ss'));
1 row created.
SQL> commit;
Commit complete.
 
select /*+gather_plan_statistics*/ * from sales where prod_id=36;
 
   PROD_ID    CUST_ID TIME_ID
---------- ---------- -----------------
        36       1100 20180401 00:00:00
        36       1231 20180401 00:00:01


회원정보 테이블의 중복을 허용하지 말아야 하는 유저닉네임 이라면 어찌될까? 잘 생각하자
이런 점들 때문에 OLTP는 일반적으로 GLOBAL이나 비파티션을 권장하게 된다.

 

그런데 주로 history성 테이블에 파티션을 range로 주로 적용하기에 문제가 안될 경우도 많다.

■정리 script

drop table sales purge;
drop sequence sales_seq;


저작자표시 비영리 변경금지

'Knowledge > Oracle' 카테고리의 다른 글

Oracle DBA와 개발자의 필독서  (2) 2016.09.23
Data guard와 Active data guard license  (0) 2016.08.12
sqlplus 접속 hang시에 접속 방법  (0) 2016.05.27
impdp, imp 주의사항  (0) 2016.03.20
Oracle Create Table 내부 절차  (0) 2015.10.21
Posted by neo-orcl
,

티스토리툴바