반응형
INDEX 컬럼 가공
: 인덱스가 정의된 컬럼을 가공하면 인덱스를 활용 할 수 없음
1. 명시적 형변환
: 사용자가 고의적으로 컬럼을 변형
2. 암시적 형변환
: 문자를 숫자나 날짜로 변환
[실습:명시적 형변환]
SELECT /*+ gather_plan_statistics INDEX(products PRODUCTS_PRICE_IDX)*/ *
FROM products
WHERE price BETWEEN 3350 AND 4500;
CREATE INDEX PRODUCTS_PRICE_IDX ON PRODUCTS(PRICE);
SQL_ID 1q4y0g7cm79ph, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics INDEX(products PRODUCTS_PRICE_IDX)*/
* FROM products WHERE price BETWEEN 3350 AND 4500
Plan hash value: 1668926985
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 24 (100)| | 22 |00:00:00.01 | 25 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTS | 1 | 22 | 440 | 24 (0)| 00:00:01 | 22 |00:00:00.01 | 25 | 4 |
|* 2 | INDEX RANGE SCAN | PRODUCTS_PRICE_IDX | 1 | 22 | | 2 (0)| 00:00:01 | 22 |00:00:00.01 | 3 | 4 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PRICE">=3350 AND "PRICE"<=4500)
[실습:명시적 형변환]
SELECT /*+ gather_plan_statistics INDEX(products PRODUCTS_PRICE_IDX)*/ *
FROM products
WHERE TRUNC(price) BETWEEN 3350 AND 4500;
SQL_ID fq9h6p1admawq, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics INDEX(products PRODUCTS_PRICE_IDX)*/
* FROM products WHERE TRUNC(price) BETWEEN 3350 AND 4500
Plan hash value: 1954719464
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 62 (100)| | 22 |00:00:00.01 | 337 |
|* 1 | TABLE ACCESS FULL| PRODUCTS | 1 | 3 | 60 | 62 (0)| 00:00:01 | 22 |00:00:00.01 | 337 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((TRUNC("PRICE")>=3350 AND TRUNC("PRICE")<=4500))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / PRODUCTS@SEL$1
U - INDEX(products PRODUCTS_PRICE_IDX)
[인덱스를 함수 인덱스로 생성]
DROP INDEX PRODUCTS_PRICE_IDX;
CREATE INDEX PRODUCTS_PRICE_IDX ON PRODUCTS(TRUNC(PRICE));
SQL_ID bqum4ra63bwbj, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics INDEX(products
PRODUCTS_PRICE_IDX)*/ * FROM products WHERE TRUNC(price) BETWEEN
3350 AND 4500
Plan hash value: 1668926985
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 22 |00:00:00.01 | 25 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTS | 1 | 3 | 99 | 7 (0)| 00:00:01 | 22 |00:00:00.01 | 25 | 4 |
|* 2 | INDEX RANGE SCAN | PRODUCTS_PRICE_IDX | 1 | 5 | | 2 (0)| 00:00:01 | 22 |00:00:00.01 | 3 | 4 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PRODUCTS"."SYS_NC00006$">=3350 AND "PRODUCTS"."SYS_NC00006$"<=4500)
[실습:암시적 형변환]
- ORDERS Table Desc
| Column_name | Type |
| ORDERNO | NUMBER(10) |
| ORDERDATE | DATE |
| TOTAL | NUMBER(10) |
| PAYTYPE | VARCHAR2(20) |
SELECT /*+ gather_plan_statistics INDEX(orders orders_orderdate_idx) */ *
FROM orders
WHERE orderdate BETWEEN '2019/01/01' AND '2019/02/01'
* Oracle Version 올라가면서 해당 구문 실행 시
"ORA-01722:수치가 부적합합니다." 발생
SELECT /*+ gather_plan_statistics INDEX(orders orders_orderdate_idx) */ *
FROM orders
WHERE orderdate BETWEEN to_date('2019/01/01','YYYY/MM/DD') AND to_date('2019/02/01','YYYY/MM/DD')
=> Type에 맞게 형변환을 시켜주는것이 올바른 방법이다.
[주의사항]
1. LIKE 안에 % 또는 _ 가 선두에 등장하면 인덱스를 사용할 수 없음
2. 문자열('19/01%')을 날짜로 형변환 할 수 없을때 왼쪽 컬럼(orderdate)이 문자열로 형변환 됨
Where orderdate like '19/01%'
[주의사항:LIKE]
CREATE INDEX CUSTOMERS_CITY_IDX ON CUSTOMERS(CITY);
select /*+ gather_plan_statistics */
*
from CUSTOMERS
where city like '포%'
[PLAN]
SQL_ID 7b4hfuzmu31nh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from CUSTOMERS where city
like '포%'
Plan hash value: 2047479253
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 438 (100)| | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | 1 | 482 | 16870 | 438 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | CUSTOMERS_CITY_IDX | 1 | 482 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CITY" LIKE '포%')
filter("CITY" LIKE '포%')
select /*+ gather_plan_statistics */
*
from CUSTOMERS
where city like '%항';
SQL_ID aqrr3b63vzgq3, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from CUSTOMERS where city
like '%항'
Plan hash value: 2008213504
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 592 (100)| | 1 |00:00:00.01 | 3341 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 1 | 500 | 17500 | 592 (1)| 00:00:01 | 1 |00:00:00.01 | 3341 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CITY" LIKE '%항' AND "CITY" IS NOT NULL))
select /*+ gather_plan_statistics */
*
from CUSTOMERS
where city like '_항';
SQL_ID 3j2tyz637vpfj, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from CUSTOMERS
where city like '_항'
Plan hash value: 2008213504
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 592 (100)| | 1 |00:00:00.01 | 3341 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 1 | 500 | 17500 | 592 (1)| 00:00:01 | 1 |00:00:00.01 | 3341 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CITY" LIKE '_항' AND "CITY" IS NOT NULL))
[주의사항:문자열 % ]
| Column_name | Type |
| ORDERNO | NUMBER(10) |
| ORDERDATE | DATE |
| TOTAL | NUMBER(10) |
| PAYTYPE | VARCHAR2(20) |
CREATE INDEX orders_orderdate_idx ON orders(orderdate);
SELECT /*+ gather_plan_statistics INDEX(orders orders_orderdate_idx) */ *
FROM orders
WHERE orderdate LIKE '19/01%';
=> orderdate 의 Type은 DATE 형식이지만, LIKE '19/01%' 조건으로 인해
orderdate 가 문자열로 형변환 된다.
[PLAN]
SQL_ID d0pc6k8y445c5, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics INDEX(orders orders_orderdate_idx)
*/ * FROM orders WHERE orderdate LIKE '19/01%'
Plan hash value: 1275100350
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 52599 (100)| | 27648 |00:00:02.67 | 300K| 300K|
|* 1 | TABLE ACCESS FULL| ORDERS | 1 | 45000 | 2285K| 52599 (1)| 00:00:03 | 27648 |00:00:02.67 | 300K| 300K|
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("ORDERDATE") LIKE '19/01%')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / ORDERS@SEL$1
U - INDEX(orders orders_orderdate_idx)
반응형
'Oracle > 튜닝' 카테고리의 다른 글
| INDEX RANGE SCAN 과 FULL TABLE SCAN (0) | 2022.02.16 |
|---|---|
| 클러스터링 팩터(CLUSTERING FACTOR) (0) | 2022.02.16 |
| INDEX RANGE SCAN(MIN/MAX) (0) | 2022.02.11 |
| INDEX RANGE SCAN DESCENDING (0) | 2022.02.10 |
| INDEX RANGE SCAN (0) | 2022.02.10 |