PL/SQL - where-clause의 선택 조건 - 동적 sql 없이?
모든 조건이 필요한 것은 아닌 질문이 있습니다.다음은 모든 조건을 사용했을 때의 모습을 보여주는 예입니다.
select num
from (select distinct q.num
from cqqv q
where q.bcode = '1234567' --this is variable
and q.lb = 'AXCT' --this is variable
and q.type = 'privt' --this is variable
and q.edate > sysdate - 30 --this is variable
order by dbms_random.value()) subq
where rownum <= 10; --this is variable
으로 된 부품.--this is variable은 다양합니다!음입니다! 조건이 지정되지 않으면 기본값이 없습니다.예를 들어, q.type에 대해 입력이 "*"를 지정하는 경우(다른 모든 것은 동일하게 유지), 쿼리는 유형에 대한 모든 것과 일치해야 하며 다음과 같이 실행됩니다.
select num
from (select distinct q.num
from cqqv q
where q.bcode = '1234567' --this is variable
and q.lb = 'AXCT' --this is variable
--and q.type = 'privt' --this condition ignored because of "type=*" in input
and q.edate > sysdate - 30 --this is variable
order by dbms_random.value()) subq
where rownum <= 10; --this is variable
dynamic sql을 사용하여 이 쿼리를 즉시 구축하는 것이 가능하다는 것은 알고 있지만, 이것이 어떤 종류의 성능 문제를 일으킬 수 있는지, 그리고 이를 위한 더 나은 방법이 있는지 궁금합니다.
당신이 이걸 할 수 있는 동안...
select num
from (select distinct q.num
from cqqv q
where 1=1
and (:bcode is null or q.bcode = :bcode)
and (:lb is null or q.lb = :lb)
and (:type is null or q.type = :type)
and (:edate is null or q.edate > :edate - 30)
order by dbms_random.value()) subq
where rownum <= :numrows
... 동적 SQL을 사용하는 성능은 일반적으로 더 나은 것입니다. 보다 표적화된 쿼리 계획을 생성하기 때문입니다.위 쿼리에서 Oracle은 bcode 또는 lb에 인덱스를 사용할지 또는 type 또는 edate에 사용할지 여부를 알 수 없으며, 매번 전체 테이블 스캔을 수행할 수 있습니다.
물론 동적 쿼리에서 바인딩 변수를 사용해야 하며, 리터럴 값을 문자열에 연결해서는 안 됩니다. 그렇지 않으면 성능(및 확장성, 보안)이 매우 저하됩니다.
분명히 말씀드리지만, 제가 생각하고 있는 동적 버전은 다음과 같이 작동합니다.
declare
rc sys_refcursor;
q long;
begin
q := 'select num
from (select distinct q.num
from cqqv q
where 1=1';
if p_bcode is not null then
q := q || 'and q.bcode = :bcode';
else
q := q || 'and (1=1 or :bcode is null)';
end if;
if p_lb is not null then
q := q || 'and q.lb = :lb';
else
q := q || 'and (1=1 or :lb is null)';
end if;
if p_type is not null then
q := q || 'and q.type = :type';
else
q := q || 'and (1=1 or :type is null)';
end if;
if p_edate is not null then
q := q || 'and q.edate = :edate';
else
q := q || 'and (1=1 or :edate is null)';
end if;
q := q || ' order by dbms_random.value()) subq
where rownum <= :numrows';
open rc for q using p_bcode, p_lb, p_type, p_edate, p_numrows;
return rc;
end;
이는 결과 쿼리 실행이 (예를 들어) 다음과 같으므로 결과 쿼리가 "sargable"(내가 인정해야 할 새로운 단어)이 된다는 것을 의미합니다.
select num
from (select distinct q.num
from cqqv q
where 1=1
and q.bcode = :bcode
and q.lb = :lb
and (1=1 or :type is null)
and (1=1 or :edate is null)
order by dbms_random.value()) subq
where rownum <= :numrows
그러나 이 예제에서는 최대 16개의 하드 구문이 필요할 수 있습니다.native dynamic SQL을 사용할 경우 "and :bv is null" 절이 필요하지만 DBMS_SQL을 사용하면 피할 수 있습니다.
의 :(1=1 or :bindvar is null)bind 변수가 null인 경우, 최적화자가 절을 제거할 수 있도록 허용하기 때문에 Michal Pravda의 코멘트에서 제안되었습니다.
동적 SQL을 사용하는 성능이 더 좋다는 Tony의 의견에는 동의하지만, 바인딩 변수를 사용하는 것보다 컨텍스트 변수가 더 나은 접근 방식입니다.
으로.IN_VARIABLE IS NULL OR table.fieldx = IN_VARIABLE옵션 값을 처리하는 데 이상적이지 않습니다.쿼리가 제출될 때마다 Oracle은 먼저 공유 풀에서 문이 이전에 제출된 적이 있는지 확인합니다.있는 경우 쿼리에 대한 실행 계획이 검색되고 SQL이 실행됩니다.공유 풀에서 문을 찾을 수 없는 경우 Oracle은 문을 파싱하고 다양한 실행 경로를 계산하며 최적의 액세스 계획(일명 "최적 경로")을 작성한 후 실행해야 합니다.이 프로세스를 "하드 구문 분석"이라고 하며 쿼리 자체보다 더 오래 걸릴 수 있습니다.여기서는 Oracle의 하드/소프트 구문에 대해 자세히 알아보고, 여기서는 Tom에게 물어 보십시오.
간단히 말해서 - 이것:
and (:bcode is null or q.bcode = :bcode)
...동적이든 다른 방식이든 동일하게 실행합니다.선택적 매개변수에 대해 동적 SQL에서 바인딩 변수를 사용하면 이점이 없습니다.SARGability(SARGability)가 여전히 파괴됩니다.
컨텍스트 매개 변수는 Oracle 9i에 도입된 기능입니다.이들은 패키지에 연결되어 있으며 속성 값을 설정하는 데 사용할 수 있습니다(패키지에 EXECUTE 권한이 있는 사용자에 한하며 스키마에 CREATE CONCONTIC을 부여해야 합니다).컨텍스트 변수를 사용하여 필터/검색 기준에 따라 쿼리에 필요한 내용만 포함할 수 있도록 동적 SQL을 맞춤화할 수 있습니다.이에 비해 Bind 변수(Dynamic SQL에서도 지원됨)는 다음과 같은 결과를 초래할 수 있는 값을 지정해야 합니다.IN_VARIABLE IS NULL OR table.fieldx = IN_VARIABLE검색 쿼리의 테스트입니다.실제로는 가치 오염의 위험을 제거하기 위해 각 절차나 기능에 별도의 컨텍스트 변수를 사용해야 합니다.
컨텍스트 변수를 사용한 쿼리는 다음과 같습니다.
L_CURSOR SYS_REFCURSOR;
L_QUERY VARCHAR2(5000) DEFAULT 'SELECT num
FROM (SELECT DISTINCT q.num
FROM CQQV q
WHERE 1 = 1 ';
BEGIN
IF IN_BCODE IS NOT NULL THEN
DBMS_SESSION.SET_CONTEXT('THE_CTX',
'BCODE',
IN_BCODE);
L_QUERY := L_QUERY || ' AND q.bcode = SYS_CONTEXT(''THE_CTX'', ''BCODE'') ';
END IF;
IF IN_LB IS NOT NULL THEN
DBMS_SESSION.SET_CONTEXT('THE_CTX',
'LB',
IN_LB);
L_QUERY := L_QUERY || ' AND q.lb = SYS_CONTEXT(''THE_CTX'', ''LB'') ';
END IF;
IF IN_TYPE IS NOT NULL THEN
DBMS_SESSION.SET_CONTEXT('THE_CTX',
'TYPE',
IN_TYPE);
L_QUERY := L_QUERY || ' AND q.type = SYS_CONTEXT(''THE_CTX'', ''TYPE'') ';
END IF;
IF IN_EDATE IS NOT NULL THEN
DBMS_SESSION.SET_CONTEXT('THE_CTX',
'EDATE',
IN_EDATE);
L_QUERY := L_QUERY || ' AND q.edate = SYS_CONTEXT(''THE_CTX'', ''EDATE'') - 30 ';
END IF;
L_QUERY := L_QUERY || ' ORDER BY dbms_random.value()) subq
WHERE rownum <= :numrows ';
FOR I IN 0 .. (TRUNC(LENGTH(L_QUERY) / 255)) LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(L_QUERY, I * 255 + 1, 255));
END LOOP;
OPEN L_CURSOR FOR L_QUERY USING IN_ROWNUM;
RETURN L_CURSOR;
END;
예제에서는 값이 선택 사항이 아니기 때문에 로넘에 대해 바인딩 변수를 여전히 사용합니다.
DBMS_SESSION.SET_CONTEXT('THE_CTX', 'LB', IN_LB);
SET_CONTECT 매개변수는 다음과 같습니다.
- 컨텍스트 변수 이름입니다.여기에 관련된 인스턴스 생성이 없습니다.
- 컨텍스트 변수 내의 변수입니다.컨텍스트 변수는 웹 응용 프로그램 및 세션 개체에 대한 친숙도를 가정할 때 세션 변수와 같습니다.
- 매개 변수 #2에 정의된 변수의 값입니다.
바인딩 대 컨텍스트
Bind variables는 Oracle이 변수 참조가 채워질 것으로 예상하며, 그렇지 않으면 ORA 오류가 됩니다.예를 들어,
... L_QUERY USING IN_EXAMPLE_VALUE
...바인드 변수 참조가 하나만 채워질 것으로 예상됩니다. 만약IN_EXAMPLE_VALUEnull입니다. 다음이 있어야 합니다.:variable쿼리에 저장합니다. IE:AND :variable IS NULL
컨텍스트 변수를 사용하면 관련성이 없는/중복 논리를 포함하지 않아도 되고 값이 null인지 확인할 수 있습니다.
중요: 바인딩 변수는 이름이 아닌 발생 순서(순서대로 알려짐).데이터 유형에 대한 선언이 없다는 것을 알게 될 것입니다.USING절 서수는 이상적이지 않습니다를 업데이트하지 않고 업데이트하지 않고 쿼리에서 변경하는 경우USING조항, 수정될 때까지 쿼리를 중단합니다.
제가 정한 솔루션은 다음과 같은 동적 SQL 쿼리를 생성하는 솔루션입니다.
select num
from (select distinct q.NUM
from cqqv q
where (q.bcode = :bcode)
and (1=1 or :lb is null)
and (1=1 or :type is null)
and (q.edate> :edate)
order by dbms_random.value()) subq
where rownum <= :numrows
(이 예에서 bcode와 edate 조건은 선택 사항이 아니지만 lb와 type은 선택 사항이 되었습니다.)
이는 Michaal Pravda가 제안했던 것과 유사하거나 유사하다고 생각합니다. 여기 있는 DBA는 컨텍스트 변수 솔루션보다 이 솔루션을 선호합니다.도움을 주시고 조언을 해주신 모든 분들께 감사드립니다!
DBA의 링크를 통해 이 솔루션에 대한 세부 정보를 확인할 수 있었습니다.
저는 그냥 이렇게 하겠습니다.
select num
from (select distinct q.num
from cqqv q
where q.bcode = '1234567' --this is variable
and q.lb = 'AXCT' --this is variable
and q.type = nvl(<variable-type>, q.type) --this condition ignored because of "type=*" in input
and q.edate > sysdate - 30 --this is variable
order by dbms_random.value()) subq
where rownum <= 10; --this is variable
q일 때 변수 유형이 null임을 보장하기만 하면 됩니다.TYPE 필터링은 무시됩니다.
여기서 (열 A = passedValue 또는 passedValue = -1 )
sql에 전달된 값이 -1이면 열 A는 무엇이든 될 수 있습니다.
언급URL : https://stackoverflow.com/questions/1716590/pl-sql-optional-conditions-in-where-clause-without-dynamic-sql
'programing' 카테고리의 다른 글
| n++가 n=n+1보다 빨리 실행되는 이유는 무엇입니까? (0) | 2023.10.01 |
|---|---|
| AngularJS 보간 오류 (0) | 2023.10.01 |
| -moz- and -webkit-는 무엇입니까? (0) | 2023.10.01 |
| XML 문자열을 인쇄할 형식 지정 XML 문자열 (0) | 2023.10.01 |
| plsql에서 익명 블록을 사용하여 표 전체를 인쇄하는 방법? (0) | 2023.10.01 |