SQL 쿼리 동등성 입증

SQL 쿼리 동등성 입증

두 쿼리가 기능적으로 동일하다는 것을 어떻게 증명하시겠습니까? 예를 들어, 두 쿼리 모두 항상 동일한 결과 집합을 반환합니다.

---

제가 이것을 할 때 특정한 질문을 염두에 두었기 때문에, 저는 @dougman이 제안한 대로, 관련된 테이블의 약 10% 이상의 행을 수행하고 결과를 비교하여, 잘못된 결과가 발생하지 않도록 했습니다.

주어진 입력 집합을 기준으로 두 쿼리 출력을 비교하여 차이점을 찾는 것이 최선입니다.모든 입력에 대해 항상 동일한 결과를 반환한다고 말하는 것은 실제로 데이터에 따라 다릅니다.

Oracle의 경우 최상의 접근 방식(매우 효율적)은 아니지만 다음과 같습니다(+CtrlF 두 표의 내용 비교).
http://www.oracle.com/technetwork/issue-archive/2005/05-jan/.htmlhttp ://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html

결론은 다음과 같습니다.

select c1,c2,c3, 
       count(src1) CNT1, 
       count(src2) CNT2
  from (select a.*, 
               1 src1, 
               to_number(null) src2 
          from a
        union all
        select b.*, 
               to_number(null) src1, 
               2 src2 
          from b
       )
group by c1,c2,c3
having count(src1) <> count(src2);

코제트를 통한 실질 동등성 증명:
코제트는 2개의 SQL 쿼리가 동일한지 여부를 (증명을 사용하여) 확인하고, 동일하지 않을 경우 예제를 카운터합니다.그것이 거의 확실하게 할 수 있는 유일한 방법입니다. 거의 ;) 당신은 심지어 그들의 웹사이트에 2개의 쿼리를 집어넣고 바로 동등성을 확인할 수 있습니다.

코제트 링크: https://cosette.cs.washington.edu/

코제트의 작동 방식을 잘 설명하는 기사 링크: https://medium.com/ @uwdb/introuting-cosette-527898504bd6


만약이 빠른실용적인 해결책을 : 는신속고찾해있고경우는을:
다음 스택 오버플로 답변을 시도해 보십시오. [sql - 두 개의 선택 항목이 동일한지 확인]
그 결과는 다음과 같습니다.

(select * from query1 MINUS select * from query2) 
UNION ALL
(select * from query2 MINUS select * from query1)

이 쿼리는 쿼리 중 하나에서만 반환되는 모든 행을 제공합니다.

이것은 나에게 NP 완전한 문제처럼 들립니다.이런 종류의 것을 증명할 확실한 방법이 있는지 모르겠습니다.

이것은 꽤 쉽습니다.

쿼리 이름이 a와 b라고 가정합니다.

마이너스 b

당신에게 빈 세트를 줄 겁니다.그렇지 않으면 쿼리가 서로 다른 집합을 반환하고 결과 집합에 서로 다른 행이 표시됩니다.

그럼 하시오

b 빼기 a

당신에게 빈 세트를 줄 것입니다.이 경우 쿼리는 동일한 집합을 반환합니다. 비어 있지 않으면 쿼리는 어떤 면에서 서로 다르며 결과 집합은 서로 다른 행을 표시합니다.

DBMS 공급업체들은 매우 오랜 시간 동안 이 문제를 해결해 왔습니다.리크의 말처럼 아마 다루기 어려운 문제일 것입니다만, 문제 공간의 NP-완전성에 대한 공식적인 분석은 이루어지지 않았다고 생각합니다.

그러나 DBMS를 최대한 활용하는 것이 최선입니다.모든 DBMS 시스템은 SQL을 일종의 쿼리 계획으로 변환합니다.쿼리의 추상화된 버전인 이 쿼리 계획을 좋은 시작점으로 사용할 수 있습니다(DBMS는 많은 최적화 작업을 수행하여 쿼리를 보다 실행 가능한 모델로 평준화합니다).

참고: 최신 DBMS는 통계 업데이트 전반에 걸쳐 결정론적이지 않은 "비용 기반" 분석기를 사용하므로 쿼리 플래너는 시간이 지남에 따라 동일한 쿼리에 대한 쿼리 계획을 변경할 수 있습니다.

Oracle(버전에 따라 다름)에서는 최적화 도구에 SQL 힌트를 사용하여 비용 기반 분석기에서 결정론적 규칙 기반 분석기로 전환하도록 지시할 수 있습니다(이를 통해 계획 분석이 단순해집니다).

SELECT /*+RULE*/ FROM yourtable

규칙 기반 옵티마이저는 8i 이후로 사용되지 않고 있지만 여전히 10g까지 남아 있습니다(나는 '11' 정도는 모릅니다).그러나 규칙 기반 분석기는 훨씬 덜 복잡합니다. 잠재적으로 오류율이 훨씬 높습니다.

IBM은 보다 일반적인 특성에 대한 추가적인 읽기를 위해 쿼리 최적화 특허를 상당히 많이 보유하고 있습니다.SQL을 "확장 계획"으로 변환하는 방법에 대한 이 설명은 좋은 출발점입니다. http://www.patentstorm.us/patents/7333981.html

Venn Diagram을 사용하여 쿼리와 결과를 (수동으로) 도출하고 동일한 다이어그램을 생성하는지 확인할 수 있습니다.Venn 다이어그램은 데이터 집합을 나타내는 데 유용하며 SQL 쿼리는 데이터 집합에서 작동합니다.Venn 다이어그램을 그리면 두 쿼리가 기능적으로 동일한지 시각화하는 데 도움이 될 수 있습니다.

이 정도면 효과가 있을 겁니다.이 쿼리가 0 행을 반환하는 경우 두 쿼리는 동일한 결과를 반환합니다.추가적으로 단일 쿼리로 실행되므로 두 쿼리 간에 데이터가 변경되지 않도록 분리 수준을 설정할 필요가 없습니다.

select * from ((<query 1> MINUS <query 2>) UNION ALL (<query 2> MINUS <query 1>))

다음은 유용한 셸 스크립트입니다.

#!/bin/sh

CONNSTR=$1
echo query 1, no semicolon, eof to end:; Q1=`cat` 
echo query 2, no semicolon, eof to end:; Q2=`cat`

T="(($Q1 MINUS $Q2) UNION ALL ($Q2 MINUS $Q1));"

echo select 'count(*)' from $T | sqlplus -S -L $CONNSTR

조심해야 해!기능적 "동등성"은 종종 데이터를 기반으로 하며, 많은 경우에 대한 결과를 비교하여 두 쿼리의 동등성을 "증명"할 수 있지만 데이터가 특정 방식으로 변경되면 여전히 틀릴 수 있습니다.

예:

SQL> create table test_tabA
(
col1 number
)

Table created.

SQL> create table test_tabB
(
col1 number
)

Table created.

SQL> -- insert 1 row

SQL> insert into test_tabA values (1)

1 row created.

SQL> commit

Commit complete.

SQL> -- Not exists query:

SQL> select * from test_tabA a
where not exists
(select 'x' from test_tabB b
where b.col1 = a.col1)

      COL1

----------

         1

1 row selected.

SQL> -- Not IN query:

SQL> select * from test_tabA a
where col1 not in
(select col1
from test_tabB b)

      COL1

----------

         1

1 row selected.


-- THEY MUST BE THE SAME!!! (or maybe not...)


SQL> -- insert a NULL to test_tabB

SQL> insert into test_tabB values (null)

1 row created.

SQL> commit

Commit complete.

SQL> -- Not exists query:

SQL> select * from test_tabA a
where not exists
(select 'x' from test_tabB b
where b.col1 = a.col1)


      COL1

----------

         1

1 row selected.

SQL> -- Not IN query:

SQL> select * from test_tabA a
where col1 not in
(select col1
from test_tabB b)

**no rows selected.**

넌 아냐.

예를 들어 성능 변경으로 인해 쿼리 출력이 변경되지 않았다는 높은 수준의 신뢰성이 필요한 경우 이를 테스트해 보십시오.

만약 당신이 정말 높은 수준의 자신감이 필요하다면..그러면 errm, 더 많이 테스트해 보세요.

방대한 수준의 테스트는 SQL 쿼리를 위해 함께 작업하는 것이 그리 어렵지 않습니다.가능한 대규모/완전한 파라메터 집합을 반복할 proc를 작성하고, 각 쿼리를 각 파라메터 집합으로 호출하고, 각 테이블에 출력을 기록합니다.두 테이블을 비교해 보면 알 수 있습니다.

정확히 과학적이지는 않습니다. OP의 질문이었던 것 같습니다. 하지만 동등성을 증명하는 공식적인 방법에 대해서는 잘 모르겠습니다.

언급URL : https://stackoverflow.com/questions/56895/proving-sql-query-equivalency