SQLP(01) 실습 세팅

SQLP 세팅

SQLP 시험 준비를 위한 실습 세팅

---

1. https://www.oracle.com/kr/cloud/
2. ADB 생성
3. 접속: DBeaver

-- 계정 생성
CREATE USER SQLP IDENTIFIED BY 비밀번호;

-- 권한 부여
GRANT CONNECT, RESOURCE TO SQLP;
GRANT UNLIMITED TABLESPACE TO SQLP;

-- 실행계획 권한
GRANT SELECT ANY DICTIONARY TO SQLP;

FULL SCAN (인덱스 없어서)

-- 테이블 하나 만들고
CREATE TABLE T1 (
    ID   NUMBER,
    NAME VARCHAR2(100)
);

-- 데이터 넣고
INSERT INTO T1
SELECT ROWNUM, 'NAME_' || ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10000;

-- 실행계획
EXPLAIN PLAN FOR
SELECT * FROM T1 WHERE id = 500;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);


PLAN_TABLE_OUTPUT                                                         |
--------------------------------------------------------------------------+
Plan hash value: 3617692013                                               |
                                                                          |
--------------------------------------------------------------------------|
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     ||
--------------------------------------------------------------------------|
|   0 | SELECT STATEMENT  |      |     1 |    65 |     9   (0)| 00:00:01 ||
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    65 |     9   (0)| 00:00:01 ||
--------------------------------------------------------------------------|
                                                                          |
Predicate Information (identified by operation id):                       |
---------------------------------------------------                       |
                                                                          |
   1 - filter("ID"=500)                                                   |
                                                                          |
Note                                                                      |
-----                                                                     |
   - dynamic statistics used: dynamic sampling (level=2)                  |

INDEX

-- 인덱스 생성
CREATE INDEX IDX_T1_ID ON T1(ID);

-- 다시 실행계획
EXPLAIN PLAN FOR
SELECT * FROM T1 WHERE ID = 500;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                                |
-------------------------------------------------------------------------------------------------+
Plan hash value: 2067802950                                                                      |
                                                                                                 |
-------------------------------------------------------------------------------------------------|
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     ||
-------------------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT                    |           |     1 |    14 |     2   (0)| 00:00:01 ||
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1        |     1 |    14 |     2   (0)| 00:00:01 ||
|*  2 |   INDEX RANGE SCAN                  | IDX_T1_ID |     1 |       |     1   (0)| 00:00:01 ||
-------------------------------------------------------------------------------------------------|
                                                                                                 |
Predicate Information (identified by operation id):                                              |
---------------------------------------------------                                              |
                                                                                                 |
   2 - access("ID"=500)                                                                          |