[sql] Oracle에서 대소 문자를 구분하지 않는 검색

전체 텍스트 인덱스를 사용하지 않고 Oracle에서 대소 문자를 구분하지 않는 검색을 수행하는 3 가지 주요 방법이 있습니다.

궁극적으로 어떤 방법을 선택 하느냐는 개인의 상황에 따라 다릅니다. 기억해야 할 것은 성능을 향상 시키려면 대소 문자를 구분하지 않는 검색을 위해 올바르게 색인을 작성해야한다는 것입니다.

1. 열과 문자열을 동일하게 입력하십시오.

당신은 사용하여 동일한 경우로 모든 데이터를 강제로 UPPER()또는 LOWER():

select * from my_table where upper(column_1) = upper('my_string');

또는

select * from my_table where lower(column_1) = lower('my_string');

또는에 column_1인덱스가없는 경우 전체 테이블 스캔을 수행 할 수 있습니다. 이를 피하기 위해 함수 기반 색인을 작성할 수 있습니다 .upper(column_1)lower(column_1)

create index my_index on my_table ( lower(column_1) );

LIKE를 사용하는 경우 %검색하려는 문자열 주위 를 연결해야합니다 .

select * from my_table where lower(column_1) LIKE lower('my_string') || '%';

이 SQL Fiddle 은 이러한 모든 쿼리에서 발생하는 상황을 보여줍니다. 설명 계획은 인덱스가 사용되는시기와 사용되지 않는시기를 나타냅니다.

2. 정규식을 사용하십시오.

Oracle 10g부터 REGEXP_LIKE()사용할 수 있습니다. 'i'대소 문자를 구분하지 않는 검색을 수행하기 위해 _match_parameter_를 지정할 수 있습니다 .

이것을 항등 연산자로 사용하려면 문자열의 시작과 끝을 지정해야하며 캐럿과 달러 기호로 표시됩니다.

select * from my_table where regexp_like(column_1, '^my_string$', 'i');

LIKE와 동등한 기능을 수행하기 위해 LIKE를 제거 할 수 있습니다.

select * from my_table where regexp_like(column_1, 'my_string', 'i');

문자열에 정규식 엔진에서 다르게 해석되는 문자가 포함될 수 있으므로주의하십시오.

이 SQL Fiddle 은 REGEXP_LIKE ()를 사용하는 것을 제외하고 동일한 출력 예를 보여줍니다.

3. 세션 레벨에서 변경하십시오.

NLS_SORT의 매개 변수는 주문 및 다양한 비교 연산자를 포함에 대한 정렬 순서 지배 =와 LIKE를. 세션을 변경하여 대소 문자를 구분하지 않는 이진 정렬을 지정할 수 있습니다. 이는 해당 세션에서 수행 된 모든 쿼리가 대소 문자를 구분하지 않는 매개 변수를 수행함을 의미합니다.

alter session set nls_sort=BINARY_CI

다른 언어를 지정하거나 BINARY_AI를 사용하여 악센트에 구애받지 않는 검색을 수행하려는 경우 언어 정렬 및 문자열 검색에 대한 추가 정보가 많이 있습니다 .

NLS_COMP 매개 변수도 변경해야합니다 . 인용 :

NLS_SORT 매개 변수를 따르는 정확한 연산자 및 쿼리 절은 NLS_COMP 매개 변수의 값에 따라 다릅니다. 연산자 또는 절이 NLS_COMP에 의해 결정된 NLS_SORT 값을 준수하지 않으면 사용 된 데이터 정렬은 BINARY입니다.

NLS_COMP의 기본값은 BINARY입니다. 그러나 LINGUISTIC은 Oracle이 NLS_SORT의 가치에주의를 기울여야한다고 지정합니다.

WHERE 절과 PL / SQL 블록의 모든 SQL 연산을 비교하려면 NLS_SORT 매개 변수에 지정된 언어 정렬을 사용해야합니다. 성능을 향상시키기 위해 언어 비교를 원하는 열에 언어 색인을 정의 할 수도 있습니다.

다시 한 번 세션을 변경해야합니다.

alter session set nls_comp=LINGUISTIC

문서에서 언급했듯이 언어 색인 을 작성하여 성능을 향상시킬 수 있습니다.

create index my_linguistc_index on my_table
   (NLSSORT(column_1, 'NLS_SORT = BINARY_CI'));

답변

SELECT user_name
FROM user_master
WHERE upper(user_name) LIKE '%ME%'

CREATE TABLE tab1(i INT PRIMARY KEY, name VARCHAR2(100));

INSERT INTO tab1(i, name) VALUES (1, 'John');
INSERT INTO tab1(i, name) VALUES (2, 'Joe');
INSERT INTO tab1(i, name) VALUES (3, 'Billy');
--========================================================================--
SELECT /*csv*/ *
FROM tab1
WHERE name = 'jOHN' ;
-- no rows selected

SELECT /*csv*/ *
FROM tab1
WHERE name COLLATE BINARY_CI = 'jOHN' ;
/*
"I","NAME"
1,"John"
*/

SELECT /*csv*/ *
FROM tab1
WHERE name LIKE 'j%';
-- no rows selected

SELECT /*csv*/ *
FROM tab1
WHERE name COLLATE BINARY_CI LIKE 'j%';
/*
"I","NAME"
1,"John"
2,"Joe"
*/

select user_name
from my_table
where nlssort(user_name, 'NLS_SORT = Latin_CI') = nlssort('%AbC%', 'NLS_SORT = Latin_CI')

where regexp_like(name, 'string$', 'i');