[오라클교육,튜닝교육,SQL교육학원추천_탑크리에듀][ORACLE SQL]count와 구체화뷰(materialized view in SQL count function),ORA-12034

[ORACLE SQL]count와 구체화뷰(materialized view in  SQL count function),ORA-12034 


SQL count(*)연산의 성능 


myemp1 테이블의 건수는 2000만건, PK는 empno 


PK 인덱의 이름은 다음과 같다. 


select index_name  from user_indexes  where table_name = 'MYEMP1' 
==> SYS_C0011116 


SQL> desc myemp1 
        
-------- -------- ------------- 
EMPNO    NOT NULL NUMBER 
ENAME          VARCHAR2(100) 
DEPTNO          VARCHAR2(1) 
ADDR          VARCHAR2(100) 
SAL          NUMBER 
SUNGBYUL          VARCHAR2(1) 


-- 5초 
select count(*) from myemp1 e 


------------------------------------------------------------------------------ 
|  0 | SELECT STATEMENT      |              |    1 | 10832  (2)| 00:02:10 | 
|  1 |  SORT AGGREGATE      |              |    1 |            |          | 
|  2 |  INDEX FAST FULL SCAN| SYS_C0011116 |    20M| 10832  (2)| 00:02:10 | 
------------------------------------------------------------------------------ 


CBO모드에서는 아래와 같은 결과다. 
-- 2초 
select /*+ index_ffs(e SYS_C0011116)  */ count(*) 
from myemp1 e 
where empno > 0 


대부분 count연산은 CBO에서 index fast full scan 한다. 




이번에는 index 힌트를 사용해보자. 


--1.5초 
select /*+ index(e SYS_C0011116)  */ count(*) 
from myemp1 e 
where empno > 0 


이번에는 구체화 뷰를 이용하자. 


1. MVIEW LOG 생성(count, sum등 사용시 리얼타임으로 mview쪽 갱신을 위해 필요) 


DROP MATERIALIZED VIEW LOG ON myemp1; 
CREATE MATERIALIZED VIEW LOG ON myemp1 WITH PRIMARY KEY, ROWID 
INCLUDING NEW VALUES 


2. mview 생성 


DROP MATERIALIZED VIEW myemp1_count 


CREATE MATERIALIZED VIEW myemp1_count 
BUILD IMMEDIATE -- MView 생성과 동시에 데이터들도 생성 
REFRESH FAST -- 원본의변경된 데이터만 mview에 갱신 
ON COMMIT -- Commit 이 일어날 때 뷰 Refresh 
ENABLE QUERY REWRITE 
AS 
select count(*) cnt from myemp1 


count 해보자. 


-- 0초 
select count(*) from myemp1 


-------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT            |              |    1 |    13 |    3  (0 
|  1 |  MAT_VIEW REWRITE ACCESS FULL| MYEMP1_COUNT |    1 |    13 |    3  (0 
-------------------------------------------------------------------------------- 


3. myemp1 테이블에 데이터 한건 삽입하고 mview쪽이 갱신되는지 확인하자. 


insert into myemp1(empno, ename) values (20000001, '2001길동')