블로그 이미지
장피디
나는야 장피디님

calendar

      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28        

Notice

2016.11.26 17:40 비공개내용










 

sqlplus scott/oracle

 

INDEX의 정보 : USER_INDEXES, USER_IND_COLUMNS

SELECT A.TABLE_NAME, A.INDEX_NAME, COLUMN_NAME, COLUMN_POSITION

FROM USER_INDEXES A, USER_IND_COLUMNS B

WHERE A.INDEX_NAME = B.INDEX_NAME

  AND   A.TABLE_NAME = 'EMP';

  

TABLE_NAME  INDEX_NAME  COLUMN_NAME COLUMN_POSITION

---------------------- ----------------------- ------------------------ ---------------

EMP        PK_EMP               EMPNO               1

 

5. full table scan 으로 실행되도록

 

select  ename from emp

    where deptno = 20----

    and empno between 100 and 200

    order by ename;

6. emp 테이블에 deptno 컬럼에 인덱스를 만든 후 생성한 인덱스로 실행계획이 만들어지도록

 

create index emp_deptno on emp(deptno);

 

select  ename from emp

    where deptno = 10;

 

 

 

7. 6번에서 생성한 인덱스를 옵티마이저가 인덱스를 사용하지 못하도록

 

select ename from emp

    where deptno = 10;

 

 

8. 인덱스가 내림차순으로 실행되도록

 

select ename from emp

    where deptno = 10;

 

9. 인덱스가 반드시 올림차순으로 실행되도록

 

select  ename from emp

    where deptno = 10;

    

   

10. 인덱스로 index fast full 실행계획이 만들어지도록

 

select  empno from emp

    where empno > 200;

 

 

 

11. 다음 여러 개의 테이블을 조인할 때 emp 테이블이 가장 먼저 검색되는 실행계획이 만들어지도록

 

select emp.empno, dept.deptno

from dept , emp

where emp.deptno = dept.deptno; 

============================================

 

 

6. emp 테이블에 deptno 컬럼에 인덱스를 만든 후 생성한 인덱스로 실행계획이 만들어지도록

 

select  ename from emp

    where deptno = 10;

    

===========================================

 

create index emp_deptno on emp(deptno);

 

select /*+ index(emp emp_deptno)*/ ename from emp

    where deptno = 10;

 

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |     5 |    40 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP        |     5 |    40 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | EMP_DEPTNO |     5 |       |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

 

7. 6번에서 생성한 인덱스를 옵티마이저가 인덱스를 사용하지 못하도록

 

select /*+ no_index(emp emp_deptno)*/ ename from emp

    where deptno = 10;

 

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     5 |    40 |     2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |     5 |    40 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

 

8. 인덱스가 내림차순으로 실행되도록

 

select /*+ index_desc(emp emp_deptno)*/ ename from emp

    where deptno = 10;

 

-------------------------------------------------------------------------------------------

| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |            |     5 |    40 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID | EMP        |     5 |    40 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN DESCENDING| EMP_DEPTNO |     5 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

 

 

9. 인덱스가 반드시 올림차순으로 실행되도록

 

select /*+ index_asc(emp emp_deptno)*/ ename from emp

    where deptno = 10;

 

 

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |     5 |    40 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP        |     5 |    40 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | EMP_DEPTNO |     5 |       |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

 

 

 

10.인덱스로 index fast full 실행계획이 만들어지도록

 

select /*+ index_ffs(emp pk_emp)*/ empno from emp

    where empno > 200;

 

 

----------------------------------------------------------------------------------

| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |           |    14 |    56 |     2   (0)| 00:00:01 |

|*  1 |  INDEX FAST FULL SCAN| pk_emp    |    14 |    56 |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------------

 

 

11. 다음 여러 개의 테이블을 조인할 때 emp 테이블이 가장 먼저 검색되는 실행계획이 만들어지도록

 

select emp.empno, dept.deptno

from dept , emp

where emp.deptno = dept.deptno;

 

 

========================================

 

     select /*+ leading(emp)*/emp.empno, dept.deptno

     from dept , emp

     where emp.deptno = dept.deptno;

 

 

                                                                            

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    14 |   126 |     5  (20)| 00:00:01 |

|*  1 |  HASH JOIN         |      |    14 |   126 |     5  (20)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |    84 |     2   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| DEPT |     4 |    12 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------

 

 

 

DROP TABLE TMP ;

CREATE TABLE TMP

(

  login_id      NUMBER NOT NULL,

  userid  VARCHAR2(10) NOT NULL,

  login_date VARCHAR2(8)

) ;

 

INSERT INTO TMP VALUES (  1,'user1','20100101') ;

INSERT INTO TMP VALUES (  2,'admin',NULL) ;

INSERT INTO TMP VALUES (  3,'user2','20100301') ;

INSERT INTO TMP VALUES (  6,'user1','20100601') ;

INSERT INTO TMP VALUES (  7,'admin',NULL) ;

INSERT INTO TMP VALUES (  8,'user3','20100801') ;

INSERT INTO TMP VALUES (101,'user1','20100102') ;

INSERT INTO TMP VALUES (102,'admin',NULL) ;

INSERT INTO TMP VALUES (103,'user2','20100302') ;

INSERT INTO TMP VALUES (106,'user1','20100602') ;

INSERT INTO TMP VALUES (107,'admin',NULL) ;

INSERT INTO TMP VALUES (108,'user3','20100802') ;

INSERT INTO TMP VALUES (201,'user1','20100602') ;

INSERT INTO TMP VALUES (202,'admin',NULL) ;

INSERT INTO TMP VALUES (203,'user2','20100802') ;

COMMIT ;




-----------------------------------------------

-- 1.인덱스 범위 검색                        --

-----------------------------------------------

 

 

1-1) 테스트 테이블을 생성한다.

날짜가 RANGE 에 의해 순차적으로 들어갈 수 있도록 생성한다.

 

DROP TABLE TMP ;

 

CREATE TABLE TMP

AS

SELECT TO_CHAR(TO_DATE('20000101','YYYYMMDD')+ROWNUM-1,'YYYYMMDD')  STRT_DT , ORDER_NO , CUST_ID ,PROD_ID ,  ORD_EMP_NO , DEV_DATE , COMP_DATE , QUANTITY

 FROM ORDERS

WHERE ROWNUM <= 50000

ORDER BY ORDER_NO;

 

 

 

INSERT INTO TMP

SELECT * FROM TMP ;

 

-- 5 번 정도 수행한다.

 

/

/

/

/

 

 

COMMIT;

 

 

 

1-2) 현재 입력되어 있는 건수는 1600만건이고 이 중

PROD_ID = '205' 조건의 데이타가 20만건 정도 되고 , 날짜에 의해서는

거의 동일하게 분포되어 생성되어 있으므로

각각의 인덱스를 생성하여 ACCESS 되는 블록 갯수를 비교해 본다.

 

 

즉 , 인덱스의 선두 컬럼이 = 조건인지 , 범위 검색인지 따라서 ACCESS 되는 블록 갯수가

차이 나는 것을 확인해 보세요.

 

 

 

SELECT COUNT(*) FROM TMP;

--1600000 ROWS

 

 

1-3)PROD_ID  + STRT_DT 컬럼에 대한 인덱스를 생성한다.

 

DROP INDEX TMP_IDX01 ;

 

CREATE INDEX TMP_IDX01

ON TMP ( PROD_ID  , STRT_DT  );

 

1-4)다음 SQL 의 실행계획을 확인하고 TRACE를 확인한다.

 

 

SELECT *

 FROM TMP

WHERE PROD_ID = '205'

  AND STRT_DT BETWEEN '20050101' AND '20091231' ;

 

 

 

 

all     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch      414    0.000        0.012          0       4556          0       4128

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Total      416    0.000        0.012          0       4556          0       4128

 

Misses in library cache during parse: 0

Optimizer goal: ALL_ROWS

Parsing user: SCOTT (ID=84)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  STATEMENT

   4128   TABLE ACCESS BY INDEX ROWID TMP (cr=4556 pr=0 pw=0 time=7996 us cost=10068 size=909585 card=10455)

   4128    INDEX RANGE SCAN TMP_IDX01 (cr=428 pr=0 pw=0 time=773 us cost=37 size=0 card=10455)(Object ID 74751)

 

 

 

 

 

 

1-5) STRT_DT + PROD_ID  컬럼에 대한 인덱스를 생성한다.

 

DROP INDEX TMP_IDX01 ;

 

CREATE INDEX TMP_IDX02

ON TMP (  STRT_DT , PROD_ID  );

 

1-6)다음 SQL 의 실행계획을 확인하고 TRACE를 확인한다.

 

 

 

SELECT *

 FROM TMP

WHERE PROD_ID = '205'

  AND STRT_DT BETWEEN '20050101' AND '20091231'

 

 

 

 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch      414    0.047        0.017          0       4739          0       4128

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Total      416    0.047        0.017          0       4739          0       4128

 

Misses in library cache during parse: 0

Optimizer goal: ALL_ROWS

Parsing user: SCOTT (ID=84)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  STATEMENT

   4128   TABLE ACCESS BY INDEX ROWID TMP (cr=4739 pr=0 pw=0 time=6964 us cost=339 size=909585 card=10455)

   4128    INDEX RANGE SCAN TMP_IDX02 (cr=611 pr=0 pw=0 time=215764 us cost=158 size=0 card=188)(Object ID 74752)

 

 

 

 

 

 

1-7) 부가적인 조건이 있을 때에 테이블 RANDOM ACCCESS 에 의해 걸러지는 TRACE ROWS 수를 확인해 보세요.

 

SELECT *

 FROM TMP

WHERE PROD_ID = '205'

  AND STRT_DT BETWEEN '20050101' AND '20091231'

  AND CUST_ID = '1000148'

  

 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch        5    0.000        0.010          0       4330          0         32

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Total        7    0.000        0.010          0       4330          0         32

 

Misses in library cache during parse: 0

Optimizer goal: ALL_ROWS

Parsing user: SCOTT (ID=84)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  STATEMENT

     32   TABLE ACCESS BY INDEX ROWID TMP (cr=4330 pr=0 pw=0 time=0 us cost=6168 size=2784 card=32)

   4128    INDEX RANGE SCAN TMP_IDX02 (cr=202 pr=0 pw=0 time=185457 us cost=27 size=0 card=4128)(Object ID 74752)

 

 

 

 

 

1-8)위 인덱스를 3개 컬럼으로 구성하고 ACCESS 되는  블록 갯수를 TRACE를 통해 확인해 보세요.

 

 

DROP INDEX TMP_IDX02 ;

 

CREATE INDEX TMP_IDX03

ON TMP (   PROD_ID , CUST_ID , STRT_DT   );

 

 

 

 

SELECT *

 FROM TMP

WHERE PROD_ID = '205'

  AND STRT_DT BETWEEN '20050101' AND '20091231'

  AND CUST_ID = '1000148'

;

 

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          2          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        4      0.00       0.00          0         38          0          32

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        6      0.00       0.00          0         40          0          32

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 109  

 

Rows     Row Source Operation

-------  ---------------------------------------------------

     32  TABLE ACCESS BY INDEX ROWID TMP (cr=38 pr=0 pw=0 time=0 us cost=34 size=2784 card=32)

     32   INDEX RANGE SCAN TMP_IDX03 (cr=6 pr=0 pw=0 time=0 us cost=3 size=0 card=32)(object id 75689)

 

 

 

 

 

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

'비공개내용' 카테고리의 다른 글

DB 튜닝 강의 2(2016/11/26)  (1) 2016.11.26
DB 튜닝 강의 1(2016/11/19)  (1) 2016.11.19
posted by 장피디
prev 1 2 3 4 5 6 7 8 9 ... 249 next

티스토리 툴바