Oracle Partition Table Index 기본 구성기준 및 SQL 실행계획

Oracle Partition  Table  Index 구성 기준 및 SQL 실행계획

Oracle Partition Table 기본구성 참고

 

#기존 인덱스
INDEX_OWNER	INDEX_NAME	INDEX_TYPE	UNIQUENESS	STATUS	PARTITIONED	COLUMN_LIST
PCW	IX_ORDERS_PT_01	NORMAL	NONUNIQUE	N/A	YES	EMPLOYEE_ID + ORDER_DATE
PCW	IX_ORDERS_PT_02	NORMAL	NONUNIQUE	N/A	YES	CUSTOMER_ID + ORDER_DATE
PCW	IX_ORDERS_PT_03	NORMAL	NONUNIQUE	VALID	NO	CUSTOMER_ID + ORDER_DATE + ORDER_TOTAL
PCW	PX_ORDERS_PT	NORMAL	UNIQUE	N/A	YES	ORDER_ID + ORDER_DATE

 

SQL 예제

SELECT /*+ gather_plan_statistics */ * FROM PCW.ORDERS_PT

WHERE ORDER_MODE ='test_:'

AND CUSTOMER_ID ='test_G'

AND EMPLOYEE_ID ='test_Z';

select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL ALLSTATS LAST'));

실행계획 확인

SQL_ID  ack11hkdffmyq, child number 0
-------------------------------------
  SELECT /*+ gather_plan_statistics */ * FROM PCW.ORDERS_PT  WHERE 
ORDER_MODE ='test_:'  AND CUSTOMER_ID ='test_G'  AND EMPLOYEE_ID 
='test_Z'
 
Plan hash value: 4104968381
 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                 |      1 |        |       |  1858 (100)|          |       |       |     16 |00:00:00.01 |    2289 |       |       |          |
|   1 |  PARTITION RANGE ALL                       |                 |      1 |      1 |    69 |  1858   (1)| 00:00:01 |     1 |    12 |     16 |00:00:00.01 |    2289 |       |       |          |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ORDERS_PT       |     12 |      1 |    69 |  1858   (1)| 00:00:01 |     1 |    12 |     16 |00:00:00.06 |    2289 |       |       |          |
|   3 |    BITMAP CONVERSION TO ROWIDS             |                 |     12 |        |       |            |          |       |       |   1634 |00:00:00.05 |     850 |       |       |          |
|   4 |     BITMAP AND                             |                 |     12 |        |       |            |          |       |       |     11 |00:00:00.05 |     850 |       |       |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS         |                 |     12 |        |       |            |          |       |       |     12 |00:00:00.03 |     406 |       |       |          |
|   6 |       SORT ORDER BY                        |                 |     12 |        |       |            |          |       |       |  42167 |00:00:00.02 |     406 |  4468K|   890K|  330K (0)|
|*  7 |        INDEX RANGE SCAN                    | IX_ORDERS_PT_01 |     12 |  47391 |       |   471   (0)| 00:00:01 |     1 |    12 |  42167 |00:00:00.01 |     406 |       |       |          |
|   8 |      BITMAP CONVERSION FROM ROWIDS         |                 |     12 |        |       |            |          |       |       |     12 |00:00:00.03 |     444 |       |       |          |
|   9 |       SORT ORDER BY                        |                 |     12 |        |       |            |          |       |       |  42399 |00:00:00.03 |     444 |  4887K|   921K|  361K (0)|
|* 10 |        INDEX RANGE SCAN                    | IX_ORDERS_PT_02 |     12 |  47391 |       |   478   (0)| 00:00:01 |     1 |    12 |  42399 |00:00:00.01 |     444 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("ORDER_MODE"='test_:')
   7 - access("EMPLOYEE_ID"='test_Z')
       filter("EMPLOYEE_ID"='test_Z')
  10 - access("CUSTOMER_ID"='test_G')
       filter("CUSTOMER_ID"='test_G')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

 

 

#SQL 분포도 조회

SELECT  SUM(COUNT(*)) AS "총ROW카운트" , COUNT(*) AS "칼럼 중복 데이터" ,    ROUND(COUNT(*)/SUM(COUNT(*))*100,2) || '%' AS "분포도" FROM PCW.ORDERS_PT GROUP BY  ORDER_MODE,CUSTOMER_ID ,EMPLOYEE_ID --PK컬럼 제외 ORDER BY 1 DESC;

--SQL 조건 칼럼 인덱스 선정기준 10% 미만  --결과 5%

SELECT  SUM(COUNT(*)) AS "총ROW카운트" , COUNT(*) AS "ORDER_MODE 칼럼 중복 데이터" ,    ROUND(COUNT(*)/SUM(COUNT(*))*100,5) || '%' AS "분포도" FROM PCW.ORDERS_PT GROUP BY  ORDER_MODE --PK컬럼 제외 UNION ALL SELECT  SUM(COUNT(*)) AS "총ROW카운트" , COUNT(*) AS "CUSTOMER_ID 칼럼 중복 데이터" ,    ROUND(COUNT(*)/SUM(COUNT(*))*100,5) || '%' AS "분포도" FROM PCW.ORDERS_PT GROUP BY  CUSTOMER_ID --PK컬럼 제외 UNION ALL SELECT  SUM(COUNT(*)) AS "총ROW카운트" , COUNT(*) AS "EMPLOYEE_ID 칼럼 중복 데이터" ,    ROUND(COUNT(*)/SUM(COUNT(*))*100,5) || '%' AS "분포도" FROM PCW.ORDERS_PT GROUP BY  EMPLOYEE_ID --PK컬럼 제외 ORDER BY 1 DESC;

#SQL 조건 칼럼 분포도
ORDER_MODE	1100603	95	.00863%
CUSTOMER_ID	1100603	26	.00236%
EMPLOYEE_ID	1100603	26	.00236%

 

#결합 인덱스 생성

1번째 , 2번째 컬럼  CUSTOMER_ID,EMPLOYEE_ID .00236%  3번째 컬럼 ORDER_MODE .00863% 인덱스 포지션 순서 결정

CREATE  INDEX PCW.IX_ORDERS_PT_04 ON PCW.ORDERS_PT (CUSTOMER_ID,EMPLOYEE_ID,ORDER_MODE) TABLESPACE TEST LOCAL; -- LOCAL 인덱스 생성 ALTER INDEX PCW.IX_ORDERS_PT_04 INVISIBLE;  -- 인덱스 생성시  옵티마이저 성능 이슈가 존재하여 생성후 바로 INVISIBLE 합니다. -- 힌트 /*+ NO_use_invisible_indexes */ -- 힌트 /*+ use_invisible_indexes */

#예제 SQL

SELECT /*+ gather_plan_statistics use_invisible_indexes(IX_ORDERS_PT_04) */ *  FROM PCW.ORDERS_PT -- INVISIBLE PCW.IX_ORDERS_PT_04 인덱스 사용 WHERE ORDER_MODE ='test_:' AND CUSTOMER_ID ='test_G' AND EMPLOYEE_ID ='test_Z'; select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL ALLSTATS LAST'));

#실행계획 확인

SQL_ID  am237haxxb01s, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics use_invisible_indexes(IX_ORDERS_PT_04)  */ * FROM PCW.ORDERS_PT  WHERE ORDER_MODE ='test_:'  AND CUSTOMER_ID  ='test_G'  AND EMPLOYEE_ID ='test_Z'   Plan hash value: 2565839650   --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                                  | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                           |                 |      1 |        |       |    26 (100)|          |       |       |     16 |00:00:00.80 |      51 |     22 | |   1 |  PARTITION RANGE ALL                       |                 |      1 |      1 |    69 |    26   (0)| 00:00:01 |     1 |    12 |     16 |00:00:00.80 |      51 |     22 | |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ORDERS_PT       |     12 |      1 |    69 |    26   (0)| 00:00:01 |     1 |    12 |     16 |00:00:01.04 |      51 |     22 | |*  3 |    INDEX RANGE SCAN                        | IX_ORDERS_PT_04 |     12 |      1 |       |    25   (0)| 00:00:01 |     1 |    12 |     16 |00:00:01.04 |      35 |     22 | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      3 - access("CUSTOMER_ID"='test_G' AND "EMPLOYEE_ID"='test_Z' AND "ORDER_MODE"='test_:')   Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (E - Syntax error (1)) ---------------------------------------------------------------------------      1 -  SEL$1          E -  IX_ORDERS_PT_04   Note -----    - dynamic statistics used: dynamic sampling (level=2)

결과 : SQL 실행계획 확인 BUFFER 사용 감소 및 인덱스 IX_ORDERS_PT_04 사용으로 인한 수행속도 감소확인