Re: 오늘의 마지막 문제. emp 테이블을 리스트 파티션으로 생성하는데 job 을 기준으로

[21기_여상윤]

create table emp_list_parti
    partition by list(job)
    (
        partition p1 values('PRESIDENT'),
        partition p2 values('MANAGER'),
        partition p3 values('SALESMAN'),
        partition p4 values('CLERK'),
        partition p5 values('ANALYST')
    ) as select * from emp;

create index emp_list_parti_indx
on emp_list_parti(job) global
partition by hash(job) partitions 2;

-- 튜닝전 SQL 

select *
 from emp
 where job='SALESMAN';
 
@p

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      4 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |      4 |00:00:00.01 |       7 |

PLAN_TABLE_OUTPUT                                                                                
-----------------------------------------------------------------------------------------------

-- 튜닝후 SQL

select *
from emp_list_parti
where job = 'SALESMAN';

@p

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                     |      1 |        |      4 |00:00:00.01 |       2 |
|   1 |  PARTITION HASH SINGLE                      |                     |      1 |      3 |      4 |00:00:00.01 |       2 |

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| EMP_LIST_PARTI      |      1 |      3 |      4 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN                         | EMP_LIST_PARTI_INDX |      1 |      3 |      4 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------------------------