Oracle/DBA

특정 컬럼만 Masking

2015. 9. 3. 11:32

[문제]

- 일반 유저가 특정 테이블을 select 할 때 특정 컬럼의 내용들을 masking 하기를 원한다. (ex - 주민번호)

- 물론 특정 유저는 해당 테이블을 select 할 때 masking 안된 모든 내용을 보기로 원함

[테이블 생성]

CREATE TABLE KIM.MASKING_TEST
AS SELECT

OBJECT_ID, SUBOBJECT_NAME, DATA_OBJECT_ID

, OBJECT_TYPE, CREATED, OBJECT_NAME, OWNER

FROM DBA_OBJECTS ;

- 이 후, 원하는 값을 masking 하기 위해 FUNCTION 을 생성 (OWNER 에 대해서 masking)

CREATE OR REPLACE FUNCTION KIM.MSK_OWNER_FUN
(schema in varchar2, tab in varchar2) return varchar2 as
predicate varchar2(8) default NULL;
BEGIN
IF LOWER(SYS_CONTEXT('USERENV','POLICY_INVOKER')) in ( 'kim', 'system')

--해당 아이디들은 모든 컬럼이 보여지도록 설정
THEN predicate := '1=1';
ELSE predicate := '1=2';

-- 이 외의 ID 들은 OWNER 라는 컬럼이 안보여 지도록 설정
END IF;
RETURN predicate;
END;
/

-- function 을 정책으로 설정

begin
dbms_rls.add_policy(object_schema => 'KIM', --해당 유저
    object_name => 'MASKING_TEST',   -- 해당 테이블
    policy_name =>'MSK_OWN_POLICY',  -- 정책 명
    function_schema => 'KIM',  -- 해당 유저
    policy_function =>'MSK_OWNER_FUN', -- 정책 함수
    statement_types => 'SELECT', --select 만 가능
    SEC_RELEVANT_COLS     => 'OWNER', --owner라는 column 설정
    SEC_RELEVANT_COLS_OPT => DBMS_RLS.ALL_ROWS
    );
end;
/

[확인] User로는 Kim 이라는 메인 User / System 계정 / Scott 계정

1. Kim 으로 접속 후 확인 (정상확인 - OWNER 이 정상적으로 보여짐)

2. SYSTEM 계정으로 확인(정상확인 - OWNER 이 정상적으로 보여짐)

3. SCOTT 계정으로 확인(확인이 안됨 - OWNER가 null 값으로 표시)

뭔가가 아쉽네...

더 유동적인 방법이 필요한데....능력부족...1

[출처들]

SYS_CONTEXT | http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm
| 한국어 http://yysvip.tistory.com/250

https://docs.oracle.com/cd/E11882_01/server.112/e41481/tdm_data_masking.htm
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_rls.htm
http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm
http://jhroom.co.kr/22627

https://oracle-base.com/articles/misc/dbms_session

저작자표시 비영리

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

[Oracle] Merge 구문 1 (0)	2015.11.12
[펌] [ORACLE] SYS_CONTEXT 및 USERENV 정리 (0)	2015.09.03
[펌] DBMS_XPLAN (0)	2015.08.28
[펌]Index Access and Filter (0)	2015.08.28
DBMS_RANDOM (0)	2015.08.18

[펌] DBMS_XPLAN

2015. 8. 28. 10:59

# DBMS_XPLAN

[실행 계획 예측]
DBMS_XPLAN Package 는 Oracle 9i 에서 소개되었으며 Version Up 에 따라 기능이 점점 확장되고 있다.
Package 이름이 암시하는 것처럼, 이 Package 의 기본 목적은 실행 계획의 예측을 보여주는 것이다.

- 기본적인 사용법

SCOTT@ora10g>drop table t1 purge;
 
Table dropped.
 
SCOTT@ora10g>create table t1(c1 int, c2 char(10));
 
Table created.
 
SCOTT@ora10g>insert into t1
  2  select level, 'dummy'
  3  from dual
  4  connect by level commit;
 
Commit complete.
 
SCOTT@ora10g>create index t1_n1 on t1(c1);
 
Index created.

EXPLAIN PLAN 명령과 DBMS_XPLAN.DISPLAY 호출의 조합으로 실행 계획을 보여준다.
가장 기본적인 방법은 다음과 같다.

SCOTT@ora10g>explain plan for
  2  select *
  3  from t1
  4  where c1 = 1 and c2 = 'dummy'
  5  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display);
 
--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    25 |     2   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    25 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)|
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"='dummy')
   2 - access("C1"=1)
 
Note
-----
   - dynamic sampling used for this statement
 
18 rows selected.

# DBMS_XPLAN.DISPLAY Function 은 기본적을 다음과 같은 세 가지 종류의 값을 보여준다.

* Row Source Operation 을 보여준다.
   위의 예에서는 Index t1_n1 을 INDEX RANGE SCAN 으로 Access 하는 실행 계획이 수립될 것임을 보여준다.

* Predicate 정보를 보여준다.
2번 Operation, 즉 Index t1_n1 에 대한 Range Scan 단계에서는 access("C1"=1) Predicate 가 사용되었다.
Index Access 를 통해서 걸러진 Data 는 1번 단계, 즉 TABLE ACCESS BY INDEX ROWID Operation 에서 filter("C2"='dummy') Predicate 를 이용해 다시 Filtering 된다.
Predicate 정보는 이를 이해하지 못하면 Execution Plan 의 절반 밖에 이해하지 못한다고 할 정도로 중요한 정보이다.

* Note 정보를 통해 부가적으로 필요한 정보를 제공한다.
   이 예제에서는 Dynamic Sampling 이 사용되었음을 알려 준다.
   Oracle 10g 에서는 통계 정보가 없는 Table 에 대해서 Dynamic Sampling 을 수행한다.

# Access Predicate 와 Filter Predicate 의 차이는 다음과 같다.

* Access Predicate 는 Access Type을 결정하는데 사용되는 Predicate(조건)를 의미한다.
   더 정확하게 말하면 실제 Block 을 읽기 전에 어떤 방법으로 Block 을 읽을 것인가를 결정한다는 의미이다.
   따라서 Index Lookup 이나 Join 등은 Access Predicate 로 표현된다.

* Filter Predicate 는 실제 Block 을 읽은 후 Data 를 걸러 내기 위해 사용되는 Predicate(조건)을 의미한다.

# Access Predicate 와 Filter Predicate 가 표현되는 방식에 대한 정확한 이해는 실행 계획 해석에 있어 필수적인 지식이다.
   다음 예제를 보면 Access Predicate 와 Filter Predicate 가 어떻게 표현되는지 잘 알 수 있다.

SCOTT@ora10g>create table t1(c1 int, c2 int);
 
Table created.
 
SCOTT@ora10g>create table t2(c1 int, c2 int);
 
Table created.
 
SCOTT@ora10g>create index t1_n1 on t1(c1);
 
Index created.
 
SCOTT@ora10g>create index t1_n2 on t2(c1);
 
Index created.
 
SCOTT@ora10g>insert into t1 select level, level
  2  from dual
  3  connect by level insert into t2 select level, level
  2  from dual
  3  connect by level explain plan for
  2  select /*+ use_nl(t1 t2) */
  3  *
  4  from t1, t2
  5  where t1.c1 = t2.c1
  6  and   t1.c2 = 1
  7  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display);
 
--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    52 |     5   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    26 |     2   (0)|
|   2 |   NESTED LOOPS              |       |     1 |    52 |     5   (0)|
|*  3 |    TABLE ACCESS FULL        | T1    |     1 |    26 |     3   (0)|
|*  4 |    INDEX RANGE SCAN         | T1_N2 |     1 |       |     1   (0)|
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("T1"."C2"=1)
   4 - access("T1"."C1"="T2"."C1")

반면 Hash Join 에서는 Join 단계(1번)에서 Access Predicate 정보가 출력된다.

SCOTT@ora10g>explain plan for
  2  select /*+ use_hash(t1 t2) */
  3  *
  4  from t1, t2
  5  where t1.c1 = t2.c1
  6  and   t1.c2 = 1
  7  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display);
 
----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    52 |     7  (15)|
|*  1 |  HASH JOIN         |      |     1 |    52 |     7  (15)|
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    26 |     3   (0)|
|   3 |   TABLE ACCESS FULL| T2   |  1000 | 26000 |     3   (0)|
----------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."C1"="T2"."C1")
   2 - filter("T1"."C2"=1)

왜 Nested Loops Join 과 Hash Join 에서 Access Predicate 가 표현된느 방식의 차이가 발생하는가?
두 Join 의 동작 방식의 차이에서 비롯된다.
Nested Loops Join 은 선행 Table 에서 Key 값을 하나씩 읽으면서 후행 Table 에서 Key 값에 해당하는 값을 읽어 들이는 방식이다.
따라서 실제 Join 은 후행 Table 에 대한 Access 에서 발생한다.
따라서 후행 Table 을 읽는 단계가 Access Predicate 가 된다.

반면에 Hash Join 은 선행 Table 을 먼저 Build 한 후, 후행 Table 과 한번에 Join 하는 방식이다.
따라서 실제 Join 이 발생하는 Hash Join 단계가 Access Predicate 로 표현된다.

DBMS_XPLAN.DISPLAY Function 이 제공하는 Parameter 들을 잘 이용하면 더 많은 종류의 정보를 추출할 수 있다.
예를 들어 다음 2개의 문장은 동일한 결과를 Return 한다.

SCOTT@ora10g>explain plan for
select *
  2    3  from t1
  4  where c1 = 1 and c2 = 'dummy'
  5  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display);
 
---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    26 |     3   (0)|
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |     3   (0)|
---------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C1"=1 AND "C2"=TO_NUMBER('dummy'))
 
SCOTT@ora10g>explain plan for
select *
  2    3  from t1
  4  where c1 = 1 and c2 = 'dummy'
  5  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table', null, 'typical',null));
 
---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    26 |     3   (0)|
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |     3   (0)|
---------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C1"=1 AND "C2"=TO_NUMBER('dummy'))

EXPLAIN PLAN 수행 시 Statement ID 를 부여한 경우에는 다음과 같이 ID 값을 지정해 주어야 한다.

SCOTT@ora10g>explain plan
set statement_id = 'test' for
  2    3  select *
  4  from t1
  5  where c1 = 1 and c2 = 'dummy'
  6  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table', 'test', 'typical',null));
 
---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    26 |     3   (0)|
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |     3   (0)|
---------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C1"=1 AND "C2"=TO_NUMBER('dummy'))

# 다양한 출력 Format
DBMS_XPLAN.DISPLAY Function 은 Basic, Typical, Serial, All 네 가지의 출력 Format 을 제공하며 기본값은 Typical 이다.
더불어 Outline, Advanced 라는 문서화되지 않은 Format 도 제공한다.
각 Format 의 출력 결과와 의미는 다음과 같다.

Basic Format 은 실행 계획의 단계별 Operation 과 Object 이름만을 보여주는 말 그대로 매우 기본적인 Format 이다.
실제로 사용할 경우는 없을 것이다.

SCOTT@ora10g>explain plan for
select *
  2    3  from t1
  4  where c1 = 1 and c2 = 'dummy'
  5  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table',null,'basic'));
 
----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T1   |
----------------------------------

Typical Format 이 가장 일반적인 용도로 고안되었다.
단계별 Operation 과 Object 명, 예측 Row 수와 Result Set 의 크기(Bytes), Cost 와 예측 실행 시간등의 정보를 보여준다.
무엇보다 Predicate 정보를 보여준다는 사실이 중요하다.

SCOTT@ora10g>explain plan for
select *
  2    3  from t1
  4  where c1 = 1 and c2 = 'dummy'
  5  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table',null,'typical'));
 
--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    26 |     5   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    26 |     5   (0)|
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)|
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"='dummy')
   2 - access("C1"=1)

Predicate 정보가 왜 그렇게 중요한가?
실행 계획이 복잡해지면 Where 절의 특정 조건이 실행 계획의 각 단계에서 정확하게 어떻게 사용되었는지가 매우 중요해진다.
Query Transformation 이라는 특별한 과정 때문에 Predicate 의 변형이 발생할 때는 이 정보가 특히 중요해진다.

All Format 은 실행 계획을 분석하는데 있어서 없어서는 안될 중요한 두 가지 정보를 추가적으로 제공한다.

첫째, Query Block 정보를 제공한다.
Oracle 은 우리가 수행 요청한 SQL 문장을 Query Block 이라는 단위로 나눈다.
Query Block 은 Transformation 및 Optimization 의 기본 단위가 된다.
아래 예제에서는 SWL$1 이라는 암호화 같은 Query Block 명이 사용된 것을 확인할 수 있다.
Query Block 명은 Inline View 와 Subquery 가 많이 사용되는 복잡한 Query 를 해석할 때 특히 유용하다.

둘째, Column Projection 정보를 제공한다.
Column Projection 이란 실행 계획의 특정 단계에서 어떤 Column 을 추출하는가를 의미한다.
아래 예제에서는 2번 단게에서는 Index t1_n1 으로부터 ROWID 와 Column c1 을 추출하며, 1번 단계에서는 Column c1, c2 를 추출한다는 것을 알 수 있다.
Column Projection 정보 또한 특별한 유형의 Query Transformation 을 Troubleshooting 할 때 유용한 정보가 된다.

SCOTT@ora10g>create table t1(c1 int, c2 char(10));
 
Table created.
 
SCOTT@ora10g>insert into t1
select level, 'dummy'
from dual
connect by level <= 10000
;
 
10000 rows created.
 
SCOTT@ora10g>commit;
 
Commit complete.
 
SCOTT@ora10g>create index t1_n1 on t1(c1);
 
Index created.
 
SCOTT@ora10g>explain plan for
select *
from t1
where c1 = 1 and c2 = 'dummy'
;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table',null,'all'));
 
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    25 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / T1@SEL$1
   2 - SEL$1 / T1@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"='dummy')
   2 - access("C1"=1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "C1"[NUMBER,22], "C2"[CHARACTER,10]
   2 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]
 
Note
-----
   - dynamic sampling used for this statement

QB_NAME Hint 를 사용하면 Query Block 명을 직접 조작할 수 있다.
복잡한 Query 의 실행 계획을 해석할 때 매우 유용한 기능이다.

SCOTT@ora10g>explain plan for
select /*+ qb_name(x) */
*
from t1
where c1 = 1 and c2 = 'dummy'
;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table',null,'all'));
 
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    25 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - X / T1@X
   2 - X / T1@X
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"='dummy')
   2 - access("C1"=1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "C1"[NUMBER,22], "C2"[CHARACTER,10]
   2 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]
 
Note
-----
   - dynamic sampling used for this statement

Outline Format 은 매우 재미있는 추가적인 정보를 제공한다.
Outline 은 실행 계획을 수립하는데 필요한 Hint 들의 목록을 의미한다.
특정 실행 계획을 재현하기 위해 어떤 Hint 가 필요한지 확인하기 위한 용도로 사용 가능하다.
다음 예제에서는 INDEX Hint 와 ALL_ROWS Hint 가 사용된 것을 알 수 있다.

SCOTT@ora10g>explain plan for
select *
from t1
where c1 = 1 and c2 = 'dummy'
;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table',null,'outline'));
 
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    25 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"='dummy')
   2 - access("C1"=1)
 
Note
-----
   - dynamic sampling used for this statement

Advanced Format 은 All Format 과 Outline Format 을 합친 것과 같다.

SCOTT@ora10g>explain plan for
select *
 from t1
where c1 = 1 and c2 = 'dummy'
;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table',null,'advanced'));
 
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    25 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / T1@SEL$1
   2 - SEL$1 / T1@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"='dummy')
   2 - access("C1"=1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "C1"[NUMBER,22], "C2"[CHARACTER,10]
   2 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]
 
Note
-----
   - dynamic sampling used for this statement

출처 : http://develop.sunshiny.co.kr/827

원본 : Optimizing Oracle Optimizer - 조동욱

저작자표시 비영리

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

[Oracle] Merge 구문 1 (0)	2015.11.12
[펌] [ORACLE] SYS_CONTEXT 및 USERENV 정리 (0)	2015.09.03
특정 컬럼만 Masking (0)	2015.09.03
[펌]Index Access and Filter (0)	2015.08.28
DBMS_RANDOM (0)	2015.08.18

[펌]Index Access and Filter

2015. 8. 28. 10:31

Distinguishing Access and Filter-Predicates

The Oracle database uses three different methods to applywhere clauses (predicates):

Access predicate (“access”): The access predicates express the start and stop conditions of the leaf node traversal.
Index filter predicate (“filter” for index operations): Index filter predicates are applied during the leaf node traversal only. They do not contribute to the start and stop conditions and do not narrow the scanned range.
Table level filter predicate (“filter” for table operations): Predicates on columns that are not part of the index are evaluated on table level. For that to happen, the database must load the row from the table first.

Note

Index filter predicates give a false sense of safety; even though an index is used, the performance degrades rapidly on a growing data volume or system load.

Execution plans that were created using the DBMS_XPLAN utility (see “Getting an Execution Plan”), show the index usage in the “Predicate Information” section below the tabular execution plan:

------------------------------------------------------
| Id | Operation         | Name       | Rows  | Cost |
------------------------------------------------------
|  0 | SELECT STATEMENT  |            |     1 | 1445 |
|  1 |  SORT AGGREGATE   |            |     1 |      |
|* 2 |   INDEX RANGE SCAN| SCALE_SLOW |  4485 | 1445 |
------------------------------------------------------

Predicate Information (identified by operation id):
   2 - access("SECTION"=:A AND "ID2"=:B)
       filter("ID2"=:B)

The numbering of the predicate information refers to the “Id” column of the execution plan. There, the database also shows an asterisk to mark operations that have predicate information.

This example, taken from the chapter “Performance and Scalability”, shows anINDEX RANGE SCAN that has access and filter predicates. The Oracle database has the peculiarity of also showing some filter predicate as access predicates—e.g., ID2=:B in the execution plan above.

Important

If a condition shows up as filter predicate, it is a filter predicate—it does not matter if it is also shown as access predicate.

This means that the INDEX RANGE SCAN scans the entire range for the condition"SECTION"=:A and applies the filter "ID2"=:B on each row.

Filter predicates on table level are shown for the respective table access such asTABLE ACCESS BY INDEX ROWID or TABLE ACCESS FULL.

Please note that different tools display the predicate information differently. Oracle SQL Developer, for example, shows the predicate information below the respective operation.

Figure A.1. Access and Filter Predicates in Oracle SQL Developer

Some tools don’t show the predicate information at all. Remember that you can always fall back to DBMS_XPLAN as explained in “Getting an Execution Plan”.

출처 : http://use-the-index-luke.com/sql/explain-plan/oracle/filter-predicates

저작자표시 비영리

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

[Oracle] Merge 구문 1 (0)	2015.11.12
[펌] [ORACLE] SYS_CONTEXT 및 USERENV 정리 (0)	2015.09.03
특정 컬럼만 Masking (0)	2015.09.03
[펌] DBMS_XPLAN (0)	2015.08.28
DBMS_RANDOM (0)	2015.08.18

DBMS_RANDOM

2015. 8. 18. 12:21

테스트를 하다보면 의례 많은 양의 데이터가 필요하다.

그럴때마다 별거 아니지만 난감하기도 하고 귀찮기도 하다.

할때마다 이 random 을 찾다 보니 이럴때 정리해야될 것 같아서 추가해 본다.

아래의 쿼리는 구루비에서 가져온 쿼리들이다.

출처 : http://www.gurubee.net/lecture/1400


DBMS_RANDOM.VALUE

- 랜덤한 숫자를 생성한다.
- Syntax : DBMS_RANDOM.VALUE(low IN NUMBER, high IN NUMBER)
- 파라미터 low는: 최소 범위, high는: 최대 범위 값이다.


 




 
 
 


-- 1000에서 10000만 사이의 임이의 수를 조회한다. 
SELECT DBMS_RANDOM.VALUE(1000, 10000) rand FROM DUAL;
 
 
-- 아래와 같이 소수점 까지 생성되는 것을 확인 할 수 있다. 
RAND
----------
5942.39469
 
 
-- 정수로만 1000에서 10000만 사이의 임이의 수를 10개를 생성해 보자 (빈칸을 채워보자)
SELECT CEIL(DBMS_RANDOM.VALUE(1000, 10000)) rand 
FROM DUAL
CONNECT BY LEVEL <= 10;
 
 
-- 아래와 같이 정수가 조회되는 것을 확인 할 수 있다. 
RAND
------
  7228
  3686
  8998
  5591
  ...
 
 
DBMS_RANDOM.STRING

- 랜덤한 문자열을 생성한다.
- Syntax : DBMS_RANDOM.STRING opt IN CHAR, len IN NUMBER)
- opt (옵션)은 아래와 같다.
'u', 'U' : 대문자
'l', 'L' : 소문자
'a', 'A' : 대소문자 구분없는 영문자
'x', 'X' : 영문자와 숫자 혼합
'p', 'P' : 문자 혼합


 




 


-- WITH문을 활용해서 옵션을 한 번에 조회해 보자
WITH t AS(
    SELECT DBMS_RANDOM.STRING('U',  10) rand FROM DUAL -- 대문자
    UNION ALL
    SELECT DBMS_RANDOM.STRING('L',  10) rand  FROM DUAL -- 소문자
    UNION ALL
    SELECT DBMS_RANDOM.STRING('A',  10) rand  FROM DUAL -- 영문자
    UNION ALL
    SELECT DBMS_RANDOM.STRING('X',  10) rand FROM DUAL -- 영숫자
    UNION ALL
    SELECT DBMS_RANDOM.STRING('P',  10) rand FROM DUAL  --문자혼합
)
SELECT * FROM t;
 
 
-- 아래와 같이 랜덤한 문자열이 조회되는 것을 확인 할 수 있다. 
RAND
-----------
NSBJGKKQCL
iumlemfhtd
BGycevXKpp
JVM7L9COBI
7tdKU83j@{
 
 
DBMS_RANDOM.RANDOM

- DBMS_RANDOM.RANDOM은 랜덤 한 숫자를 생성해 준다.
- 한번 실행해 보자 SELECT DBMS_RANDOM.RANDOM FROM DUAL
- DBMS_RANDOM.RANDOM 함수를 이용하면 데이터를 랜덤한 순서로 정렬 할 수 있다.


 




 


-- 랜덤하게 정렬하여 3개의 로우를 출력한다. 
SELECT * 
  FROM
     (SELECT * 
        FROM emp 
       ORDER BY DBMS_RANDOM.RANDOM()) 
 WHERE ROWNUM <= 3;
 
 
-- 위 SQL을 여러번 반복해서 실행해 보자..
  EMPNO ENAME                JOB              MGR HIREDATE        SAL       COMM     DEPTNO
------- -------------------- --------- ---------- -------- ---------- ---------- ----------
   7782 CLARK                MANAGER         7839 81/06/09       2450                    10
   7902 FORD                 ANALYST         7566 81/12/03       3000                    20
   7876 ADAMS                CLERK           7788 87/05/23       1100                    20

내가 자주 사용하는 쿼리 문

insert /*+ APPEND */ into c
select /*+ PARALLEL(a 2) */ CEIL(dbms_random.value(100,level)) B
from dual a
connect by level <= 1 * 1000000;

알고 있으면 유용한 쿼리문들이다.

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

[Oracle] Merge 구문 1 (0)	2015.11.12
[펌] [ORACLE] SYS_CONTEXT 및 USERENV 정리 (0)	2015.09.03
특정 컬럼만 Masking (0)	2015.09.03
[펌] DBMS_XPLAN (0)	2015.08.28
[펌]Index Access and Filter (0)	2015.08.28

PREV 1 2 NEXT

Louis.Kim 의 제 3막

Oracle/DBA

특정 컬럼만 Masking

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

[펌] DBMS_XPLAN

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

[펌]Index Access and Filter

Distinguishing Access and Filter-Predicates

Note

Important

Figure A.1. Access and Filter Predicates in Oracle SQL Developer

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

DBMS_RANDOM

DBMS_RANDOM.VALUE

DBMS_RANDOM.STRING

DBMS_RANDOM.RANDOM

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

+ Recent posts

티스토리툴바

Louis.Kim 의 제 3막

Oracle/DBA

특정 컬럼만 Masking

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

[펌] DBMS_XPLAN

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

[펌]Index Access and Filter

Distinguishing Access and Filter-Predicates

Note

Important

Figure A.1. Access and Filter Predicates in Oracle SQL Developer

Links

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

DBMS_RANDOM

DBMS_RANDOM.VALUE

DBMS_RANDOM.STRING

DBMS_RANDOM.RANDOM

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

+ Recent posts

티스토리툴바