인덱스: <>연산자 의 사용 (부정형)
Q. orders 테이블에서 SHIP_MODE 가 'first class' 가 아닌 행들의 SHIP_MODE 를 출력한다.
실행 계획도 같이 출력한다. (힌트로 인덱스의 사용을 강제한다.)
explain plan for
select ship_mode from orders
where ship_mode <> 'first class';
select * from table(dbms_xplan.display);
Plan hash value: 1789227693
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51290 | 701K| 51 (2)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_ORDERS_SHIP_MODE | 51290 | 701K| 51 (2)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SHIP_MODE"<>'first class')
##힌트로 강제 사용할 경우
select * from table(dbms_xplan.display);
explain plan for
select /*+*(orders idx_orders_ship_mode)*/ship_mode from orders
where ship_mode <> 'first class';
Plan hash value: 1789227693
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51290 | 701K| 51 (2)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_ORDERS_SHIP_MODE | 51290 | 701K| 51 (2)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SHIP_MODE"<>'first class')##부정형 연산자를 수정하여 range 스켄을 사용하는 방법
select * from table(dbms_xplan.display);
explain plan for
select /*+*(orders idx_orders_ship_mode)*/ship_mode from orders
where not exists (select ship_mode from orders where ship_mode='first class');
Plan hash value: 3050141967
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51290 | 701K| 480 (1)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| ORDERS | 51290 | 701K| 479 (1)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_ORDERS_SHIP_MODE | 1 | 14 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "ORDERS" "ORDERS" WHERE
"SHIP_MODE"='first class'))
3 - access("SHIP_MODE"='first class')
In 연산자의 사용
in연산자 사용시, index range scan이 발생하지 않는다. 보통 in 연산자를 사용함으로 인해서 sql 성능이 더 좋아지는 경우도 많기 때문에 상황에 따라서 sql 명령문을 작성해야한다.
##HR스키마에서 employees 테이블에서 employee_id 가 100,101,102 인 사원들의 정보를 불러온다
select * from employees where employee_ID in (100,101,102);
100 Steven King SKING 515.123.4567 03/06/17 AD_PRES 24000 90
101 Neena Kochhar NKOCHHAR 515.123.4568 05/09/21 AD_VP 17000 100 90
102 Lex De Haan LDEHAAN 515.123.4569 01/01/13 AD_VP 17000 100 90
select * from table(dbms_xplan.display)
Plan hash value: 1977235694
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 207 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 207 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 3 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"=100 OR "EMPLOYEE_ID"=101 OR "EMPLOYEE_ID"=102)In 에서 100 을 먼저, 그 다음 101, 102 순으로 접근 하면서 union all 하면서 "Inlist Iterator" 가 발생한다.
선택도가 높은 경우
explain plan for
select * from employees where employee_ID in (select employee_id from employees where mod(employee_id,2)=1);
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 69 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(MOD("EMPLOYEE_ID",2)=1)
##쿼리 변환이 일어난 쿼리의 경우 서브쿼리 unnesting 괄호를 없애버리고 합병한 경우, table access full 발생하지 x
##옵티마이저는 Unnesting을 하는 것이 최적일지 아닐지를 고민
select * from employees where employee_ID in (select employee_id from employees where employee_id between 100 and 200);
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102 | 7038 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 102 | 7038 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 102 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID">=100 AND "EMPLOYEE_ID"<=200)
Subst의 사용 연산자
orders 테이블에서 ship_mode의 앞 글자가 'fir' 인 행들의 ship_mode를 출력하는데, 힌트로 인덱스의 사용을 강조한다.
##index생성
create index idx_orders_row_id on orders(row_id);
create index idx_orders_ship_mode on orders(ship_mode);
explain plan for
select /*+index(orders idx_orders_ship_mode)*/ship_mode
from orders where substr(ship_mode,1,3)='fir';
select * from table(dbms_xplan.display);
Plan hash value: 1275100350
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 513 | 7182 | 481 (1)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| ORDERS | 513 | 7182 | 481 (1)| 00:00:06 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR("SHIP_MODE",1,3)='fir')
explain plan for
select /*+index(orders idx_orders_ship_mode)*/ship_mode
from orders where ship_mode like '%fir%';
select * from table(dbms_xplan.display);
Plan hash value: 1561486612
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2565 | 35910 | 180 (1)| 00:00:03 |
|* 1 | INDEX FULL SCAN | IDX_ORDERS_SHIP_MODE | 2565 | 35910 | 180 (1)| 00:00:03 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SHIP_MODE" LIKE '%fir%' AND "SHIP_MODE" IS NOT NULL)
##cost 차이가 확 줄어든 것을 알 수 있다.where 절에서 like 연산자 사용시 반드시 숫자, 문자앞에 있어야 하며, 와일드카드가 앞에 있을 경우에는 index range scan 을 사용하지 않는다.
- 처음에 %로 시작하는 것이 아닌 'f' 로 시작한다는 힌트를 줄 경우에 범위가 좁혀지고 index를 타기가 쉬워 지기 때문에 cost에도 영향을 준다.
산술연산자(*,/,+,-) 의 사용
아래의 예시와 같이 where 조건 절에서 row_id의 합산으로 인한 출력을 요구할 경우, 계산을 위해 full scan 후 값을 내오기 때문에, index를 타지 않게 된다.
explain plan for
select /*+index(orders IDX_orders_row_id)*/row_id
from orders where row_id+4000<7000;
select * from table(dbms_xplan.display);
Plan hash value: 1275100350
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2565 | 12825 | 479 (1)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| ORDERS | 2565 | 12825 | 479 (1)| 00:00:06 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_ID"+4000<7000)full scan 이 안타도록 조정할려면,
explain plan for
select /*+index(orders IDX_orders_row_id)*/row_id
from orders where row_id <7000;
select * from table(dbms_xplan.display);
Plan hash value: 2241526111
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6999 | 34995 | 17 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_ORDERS_ROW_ID | 6999 | 34995 | 17 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ROW_ID"<7000)로 수정하여 cost 가 17로 낮아진 것을 볼 수 있다.
|| 연산자의 사용
ship_mode 가 'first class' 그리고 segment 가 corporate 인 정보들을 출력한다.
explain plan for
select /*+index(orders idx_orders_ship_mode)*/ row_id
from orders where ship_mode||segment='First ClassCorporate';
select * from table(dbms_xplan.display);
Plan hash value: 1275100350
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 513 | 14877 | 479 (1)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| ORDERS | 513 | 14877 | 479 (1)| 00:00:06 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SHIP_MODE"||"SEGMENT"='First ClassCorporate')
#인덱스를 실행 안하고 full scan 으로 실행됨인덱스는 일반적으로 특정 열의 값에 대한 검색을 효율적으로 수행할 수 있도록 만들어지는데,
그러나 ship_mode || segment와 같이 열을 결합를 적용한 경우,
데이터베이스는 이러한 결합 결과에 대한 사전 인덱스를 사용하지 않는다.
이는 데이터베이스가 인덱스를 사용하지 못하고 테이블의 모든 행에 대해 직접 계산을 하기 때문이다.
따라서 인덱스를 사용하게 쿼리를 변형할려면, 아래와 같이 진행해야 한다.
explain plan for
select /*+index(orders idx_orders_ship_mode)*/ row_id
from orders where ship_mode = 'First Class' and segment='Corporate';
Plan hash value: 3719245824
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4274 | 121K| 1604 (1)| 00:00:20 |
|* 1 | TABLE ACCESS BY INDEX ROWID| ORDERS | 4274 | 121K| 1604 (1)| 00:00:20 |
|* 2 | INDEX RANGE SCAN | IDX_ORDERS_SHIP_MODE | 12823 | | 45 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SEGMENT"='Corporate')
2 - access("SHIP_MODE"='First Class')
'SQLP' 카테고리의 다른 글
| [SQLP] 조인 수행 원리 (0) | 2024.08.12 |
|---|---|
| [SQLP] 인덱스 성능 저하 요인3 ★★★★★ (0) | 2024.08.10 |
| [SQLP] 인덱스 성능 저하 요인 ★★★★★ (2) | 2024.08.06 |
| [SQLP] 옵티마이저 연산 (0) | 2024.08.04 |
| [SQLP] 테이블 & 인덱스 액세스 (0) | 2024.08.01 |