LIKE/BETWEEN 조건 활용

아래와 같이 변별력이 좋은 필수 조건이 있는 상황에서 해당 패턴을 사용하는 것은 나쁘지 않을 수 있다.

'필수 조건' 컬럼을 선두에 두고 액세스 조건으로 사용하면 LIKE/BETWEEN이 인덱스 필터 조건이어도 충분히 좋은 성능을 낸다.

 

-- 인덱스 : 등록일시 + 상품분류코드

select * from 상품

where 등록일시 >= trunc(sysdate) -- 필수 조건(당일 등록 상품)

and 상품분류코드 like :prd_cls_cd | | '% ' -- 옵션 조건

 

필수조건이 아래와 같이 '=' 이면 옵션조건인 상품 분류도 인덱스 액세스 조건으로 풀리므로 최적화 성능을 낼 수 있다.

 

-- 인덱스 : 등록일시 + 상품분류코드

select * from 상품

where 등록일시 = : prd_nm -- 필수조건

and 상품분류코드 like :prd_cls_cd | | '% ' -- 옵션 조건

 

 

LIKE/BETWEEN 패턴을 사용하고자 할 때에는 아래 네가지 경우에 속하는지 반드시 점검한다.

 

1. INDEX 선두 컬럼

[고객ID + 거래일자]

SELECT * FROM 거래

WHERE 고객ID LIKE :CUST_ID | | '%'

AND 거래일자 BETWEEN :DT1 AND :DT2;

 

사용자가 고객ID를 입력하면 변별력이 좋기 때문에 비교적 빠르게 조회되지만, 고객 ID를 입력하지 않으면

인덱스에서 모든 '데이터'를 스캔하면서 거래일자 조건을 필터링하게되는 불상사가 생김.

 

 

2. NULL 허용 컬럼
 

거래일자 조건에 해당하는 모든 고객의 고객 거래를 선택해야 한다.
고객ID가 NULL 허용컬럼이고 실제 NULL 값이 입력되어 있다면 그 데이터는 결과집합에서 누락된다.

 

SELECT * FROM DUAL WHERE NULL LIKE 1 || '%'; Total elapsed time 00:00:00.003236 SQL ID: 5qrgpk84vc1mn Child number: 1925 Plan hash value: 2319139241 Execution Plan --------------------------------------------------------------------------------    1  FILTER (Cost:1, %%CPU:0, Rows:1)    2    DPV: _VT_DUAL (Cost:1, %%CPU:0, Rows:1) Predicate Information --------------------------------------------------------------------------------    1 - filter: (0 = 1) (1.000)

 

즉 BETWEEN 조건을 사용해도 컬럼값이 NULL인 데이터는 결과집합 제외이다.

 

3. 숫자형 컬럼

인덱스를 「거래일자 + 고객ID」 순으로 구성

select * from 거래

where 거래일자 = :trd_dt

and 고객ID like :cust_id  || '%';

 

만약 고객ID가 숫자형 컬럼이면 자동 형변환이 일어나므로 고객 ID가 필터 조건으로 사용되게된다

 

4. 가변 길이 컬럼

LIKE를 옵션조건에 사용할 때는 컬럼 값 길이가 고정적이어야 한다.

예를 들어 고객명컬럼에는 김훈/김훈남 같이 길이가 다른 값이 입력될 수 있는데

고객명에 대한 옵션 조건을 LIKE 로 처리하면 '김훈' 고객을 찾기위해 김훈남고객도 찾게된다.

where 고객명 like :cust_nm || '%' -- :cust_nm = ＇김훈'

 

이 떄는 변수값 길이가 같은 레코드만 조회하도록 조건을 추가하는 방법도 있다.

 

and length(고객명) = length(nlv(:cust_nm,고객명))