programing

CASE 및 COALESCE 단락 평가는 PL/SQL의 시퀀스에서는 작동하지만 SQL에서는 작동하지 않습니다.

css3 2023. 6. 19. 21:56

CASE 및 COALESCE 단락 평가는 PL/SQL의 시퀀스에서는 작동하지만 SQL에서는 작동하지 않습니다.

설명서에 설명된 단락 평가가 다음에 대해 수행됩니까?CASE그리고.COALESCE()SQL에서 사용할 때 시퀀스에 적용됩니까?이런 일은 일어나지 않는 것 같습니다.

의 Oracle 설명서에는 다음과 같은 내용이 나와 있습니다.

Oracle Database는 단락 평가를 사용합니다. 단한경우순에 CASE 표현... 표현...이전 comparison_expr이 expr과 동일한 경우 Oracle은 comparison_expr을 평가하지 않습니다.검색된 CASE 식의 경우 데이터베이스...이전 조건이 참인 경우 조건을 평가하지 않습니다.

마찬가지로 지로가마에 입니다.COALESCE() 설명서에는 다음이 명시되어 있습니다.

Oracle Database는 단락 평가를 사용합니다.데이터베이스는 각 expr 값을 평가하고 NULL인지 여부를 확인하기 전에 모든 expr 값을 평가하지 않고 NULL인지 여부를 확인합니다.

SQL에서 시퀀스를 호출하면 단락이 발생하지 않고 시퀀스가 증가하는 것을 볼 수 있으므로 그렇지 않은 것 같습니다.

SQL> create sequence tmp_test_seq start with 1 increment by 1;
SQL> select tmp_test_seq.nextval from dual;

   NEXTVAL
----------
         1
SQL> select tmp_test_seq.currval from dual;

   CURRVAL
----------
         1
SQL> select coalesce(1, tmp_test_seq.nextval) from dual;

COALESCE(1,TMP_TEST_SEQ.NEXTVAL)
--------------------------------
                               1
SQL> select tmp_test_seq.currval from dual;

   CURRVAL
----------
         2
SQL> select case when 1 = 1 then 1 else tmp_test_seq.nextval end as s from dual;


         S
----------
         1
SQL> select tmp_test_seq.currval from dual;

   CURRVAL
----------
         3

SQL Fiddle.

그러나 PL/SQL에서 호출할 때 시퀀스는 증가하지 않습니다.

SQL> create sequence tmp_test_seq start with 1 increment by 1;
SQL> declare
  2     i number;
  3  begin
  4     i := tmp_test_seq.nextval;
  5     dbms_output.put_line(tmp_test_seq.currval);
  6     i := coalesce(1, tmp_test_seq.nextval);
  7     dbms_output.put_line(i);
  8     dbms_output.put_line(tmp_test_seq.currval);
  9     i := case when 1 = 1 then 1 else tmp_test_seq.nextval end;
 10     dbms_output.put_line(i);
 11     dbms_output.put_line(tmp_test_seq.currval);
 12  end;
 13  /
1
1
1
1
1
SQL> select tmp_test_seq.nextval from dual;

   NEXTVAL
----------
         2

PL/SQL에서 SQL의 시퀀스를 SQL과 동일한 결과로 호출하면 다음과 같이 수행됩니다.

SQL> create sequence tmp_test_seq start with 1 increment by 1;
SQL> declare
  2     i number;
  3  begin
  4     select tmp_test_seq.nextval into i from dual;
  5     dbms_output.put_line(tmp_test_seq.currval);
  6     select coalesce(1, tmp_test_seq.nextval) into i from dual;
  7     dbms_output.put_line(i);
  8     dbms_output.put_line(tmp_test_seq.currval);
  9     select case when 1 = 1 then 1 else tmp_test_seq.nextval end into i
 10       from dual;
 11     dbms_output.put_line(i);
 12     dbms_output.put_line(tmp_test_seq.currval);
 13  end;
 14  /
1
1
2
1
3

시퀀스 관리에 대한 관리자 안내서, 시퀀스 puce 열에 대한 SQL 언어 참조, CURRVAL 및 NEXTVAL에 대한 PL/SQL 언어 참조 또는 시퀀스에 대한 데이터베이스 개념 개요가 문서에 없는 것 같습니다.

다음의 단락 평가를 수행합니까?CASE그리고.COALESCE()SQL에서 사용할 때 시퀀스에 대해 발생합니까?문서화되어 있습니까?

관심만 있다면 11.2.0.3.5입니다.

PL/SQL Oracle의 경우 단락 평가를 사용합니다.

논리식을 평가할 때 PL/SQL은 단락 평가를 사용합니다.즉, PL/SQL은 결과를 확인하는 즉시 식 평가를 중지합니다.따라서 그렇지 않으면 오류가 발생할 수 있는 식을 작성할 수 있습니다.

시작: 2개의 PL/SQL 언어 기초

를 할 때nextvalSQL 코드에서, 우리는 다른 상황을 가지고 있습니다.

무엇보다도 우리는 명심해야 합니다.currval그리고.nextval유사 열:

유사 열은 테이블 열처럼 작동하지만 실제로 테이블에 저장되지는 않습니다.유사 열 중에서 선택할 수는 있지만 해당 값을 삽입, 업데이트 또는 삭제할 수는 없습니다.유사 열은 또한 인수가 없는 함수와 유사합니다(5장 "함수" 참조).그러나 인수가 없는 함수는 일반적으로 결과 집합의 모든 행에 대해 동일한 값을 반환하는 반면 유사 열은 일반적으로 각 행에 대해 다른 값을 반환합니다.

시작: 3개의 유사 열.

이 오라클을 평가하는 nextval아니면 이 행동이 어딘가에 명시되어 있습니까?

NEXTVAL에 대한 참조가 포함된 단일 SQL 문 내에서 Oracle은 시퀀스를 한 번씩 증가시킵니다.

  • SELECT 문의 외부 쿼리 블록에서 반환되는 각 행에 대해.이러한 쿼리 블록은 다음 위치에 나타날 수 있습니다.

    1. 최상위 SELECT 문
    2. INSERT ... SELECT 문(단일 테이블 또는 다중 테이블).다중 삽입의 경우, NEXTVAL에 대한 참조가 VALUEs 절에 표시되어야 하며, NEXTVAL이 다중 삽입의 여러 분기에서 참조될 수 있더라도 하위 쿼리에 의해 반환되는 각 행에 대해 시퀀스가 한 번 업데이트됩니다.
    3. 테이블 만들기... AS SELECT 문
    4. 구체화된 뷰 생성... SELECT 문

  • UPDATE 문에서 업데이트된 각 행에 대해

  • VALUES 절을 포함하는 각 INSERT 문에 대해

  • MERGE 문으로 병합된 각 행에 대해.NEXTVAL에 대한 참조는 merge_insert_clause 또는 merge_update_clause 또는 둘 다에 표시될 수 있습니다.업데이트 또는 삽입 작업에서 시퀀스 번호가 실제로 사용되지 않더라도 NEXT VALUE 값은 업데이트된 각 행과 삽입된 각 행에 대해 증분됩니다.이러한 위치에서 NEXTVAL을 두 번 이상 지정하면 시퀀스가 각 행에 대해 한 번씩 증가하고 해당 행에 대해 발생하는 모든 NEXTVAL에 대해 동일한 값을 반환합니다.

시작: 시퀀스 유사 열

당신의 경우는 분명히 "1"입니다. 문이라고 하는데, , 단지 "SELECT"라고 하는 입니다.nextval항상 평가됩니다.

단락 논리에 관심이 있는 경우에는 단락 논리를 제거하는 것이 좋습니다.nextval방정식에서

다음과 같은 쿼리는 하위 쿼리를 평가하지 않습니다.

select 6 c
  from dual
where  'a' = 'a' or 'a' = (select dummy from dual)

하지만 만약에 비슷한 일을 하려고 한다면,coalesce또는caseOracle Optimizer가 다음과 같은 하위 쿼리를 실행하기로 결정하는 것을 참조하십시오.

select 6 c
  from dual
where  'a' = coalesce('a', (select dummy from dual) )

이를 보여주기 위해 SQLFidle의 이 데모에서 주석이 달린 테스트를 만들었습니다.

하는 것처럼 은 OR 조건인 경우에는 단락 로직을 적용합니다.coalesce그리고.case모든 분기를 평가해야 합니다.

PL첫 테스트는 PL/SQL에서 할 수 합니다.coalsce그리고.caseOracle 상태와 같이 PL/SQL에서 단락 논리를 사용합니다.를 포함하여 번째 SQL이 다음과 같이 표시됩니다.nextval결과를 사용하지 않는 경우에도 평가되며 Oracle도 이를 문서화합니다.

두 가지를 합치는 것은 약간 이상해 보입니다, 왜냐하면coalesce그리고.case행동은 나에게도 정말 일관성이 없는 것처럼 보이지만, 우리는 또한 그 논리의 구현이 구현에 의존한다는 것을 명심해야 합니다(여기서 나의 출처).

단락 평가가 시퀀스에 적용되지 않는 이유는 다음과 같습니다.시퀀스란 무엇입니까?인 것은 차치하고,입니다.seq$데이터 사전 테이블) 및 일부 내부 SGA 구성 요소는 기능이 아니며 고려될 수 있지만 설명서에는 행 소스로 직접 명시되어 있지 않습니다(그러나 실행 계획은 해당됨).).또한 시퀀스가 쿼리의 선택 목록에서 직접 참조될 때마다 최적의 실행 계획을 검색할 때 Optimizer에 의해 평가되어야 합니다.최적의 실행 계획을 형성하는 과정에서 시퀀스는 다음과 같이 증가합니다.nextval유사 열이 참조됩니다.

SQL> create sequence seq1;
Sequence created

다음은 우리의 순서입니다.

SQL> select o.obj#
  2       , o.name
  3       , s.increment$
  4       , s.minvalue
  5       , s.maxvalue
  6       , s.cache
  7    from sys.seq$ s
  8    join sys.obj$ o
  9       on (o.obj# = s.obj#)
 10    where o.name = 'SEQ1'
 11  ;


      OBJ# NAME    INCREMENT$   MINVALUE   MAXVALUE      CACHE
---------- ------- ---------- ---------- ---------- ----------
     94442 SEQ1             1          1       1E28         20

아래 쿼리를 추적하고 실행 계획도 살펴봅니다.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 4';
Session altered

SQL> select case
  2           when 1 = 1 then 1
  3           when 2 = 1 then seq1.nextval
  4         end as res
  5    from dual;

       RES
----------
         1

/* sequence got incremented by 1 */

SQL> select seq1.currval from dual;

   CURRVAL
----------
         3

추적 파일 정보:

STAT #1016171528 id=1 cnt=1 pid=0 pos=1 obj=94442 op='SEQUENCE SEQ1...
STAT #1016171528 id=2 cnt=1 pid=1 pos=1 obj=0 op='FAST 듀얼...
dep*//CLOSE #1016171528:c=0,e=12,dep=0,type=0,tim=12896600071500 /* 서기닫 커 *▁*

실행 계획은 기본적으로 동일함을 보여줍니다.

SQL> explain plan for select case
  2                            when 1 = 1 then 1
  3                            else seq1.nextval
  4                          end
  5                      from dual
  6  /
Explained
Executed in 0 seconds

SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 51561390
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SEQUENCE        | SEQ1 |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
9 rows selected
Executed in 0.172 seconds

평가 측면에서, 상관된 하위 쿼리를 포함하는 것과 거의 동일하게 쿼리에서 시퀀스를 직접 참조합니다.상관된 하위 쿼리는 항상 최적화 도구에 의해 평가됩니다. 

SQL> explain plan for select case
  2                            when 1 = 1 then 1
  3                            when 2 = 1 then (select 1
  4                                               from dual)
  5                          end as res
  6                      from dual;
Explained
Executed in 0 seconds

SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
Plan hash value: 1317351201
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     4   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
|   2 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
9 rows selected
Executed in 0.063 seconds

는 것을 알 수 있습니다. dual테이블이 실행 계획에 두 번 포함되었습니다.

하위 쿼리와의 유사성은 서둘러 만들어졌습니다.물론 유사점보다는 차이점이 더 많습니다.시퀀스는 완전히 다른 메커니즘입니다.그러나 최적화 도구는 시퀀스를 행 소스로 보고 행 소스가 보이지 않는 한nextval에직 참는 에서 직접 열select최상위 쿼리 목록으로, 시퀀스를 평가하지 않습니다. 그렇지 않으면 단락 평가 로직이 사용되는지 여부에 관계없이 시퀀스가 증가합니다. PL/SQL 엔진(Oracle 11gr1부터 시작)은 시퀀스 값에 액세스하는 방법이 다릅니다.이전 11gR1 버전의 RDBMS에서는 PL/SQL 엔진이 SQL 엔진으로 직접 전송한 PL/SQL 블록의 시퀀스를 참조하는 쿼리를 작성해야 합니다.

"최적화기에 의해 실행 계획을 생성하는 동안 시퀀스가 증가하는 이유" 질문에 대한 대답은 시퀀스의 내부 구현에 있습니다.

언급URL : https://stackoverflow.com/questions/20550512/case-and-coalesce-short-circuit-evaluation-works-with-sequences-in-pl-sql-but-no

'programing' 카테고리의 다른 글

SQL Server 인스턴스의 데이터 디렉토리를 찾는 방법은 무엇입니까?  (0) 2023.06.19
When should I use fputs instead of fprintf?  (0) 2023.06.19
노드 JS에서 셀러리와 동등한 값  (0) 2023.06.19
Python의 열린 파일에서 경로 가져오기  (0) 2023.06.19
재귀 하위 쿼리 팩터링을 사용한 주기 감지  (0) 2023.06.19

'programing'의 다른글

  • 현재글CASE 및 COALESCE 단락 평가는 PL/SQL의 시퀀스에서는 작동하지만 SQL에서는 작동하지 않습니다.

관련글

댓글

티스토리툴바