Encontrei algumas anotações antigas minhas de 2011 e também reli o excelente artigo 10 Cool SQL Optimisations That Not Depend on the Cost Model de @lukas-eder. Então eu pensei em levar minhas notas para outra rodada. O cenário é bastante simples Fiddle
create table emp (
emp_no int not null primary key,
title varchar(10) not null,
salary int not null,
check (emp_no > 0),
check (title in ('BOSS','WORK'))
);
insert into emp with recursive t (n) as (
values (1)
union all
select n+1 from t where n+1 < 1000
) select n, case when mod(n,10) = 0 then 'BOSS' else 'WORK' end,
case when mod(n,10) = 0 then 110 else 0 end + mod(n,90)
from t;
Agora, dadas as regras:
(TITLE = BOSS) implies (SALARY > 100)
e
(TITLE = WORK) implies (SALARY <= 100)
que pode ser implementado como:
-- (TITLE = BOSS) => (SALARY > 100)
alter table emp add constraint cc1
CHECK ( (title <> 'BOSS' OR salary > 100) );
-- (TITLE = WORK) => (SALARY <= 100)
alter table emp add constraint cc2
CHECK ( (title <> 'WORK' OR salary <= 100) );
e a consulta:
select *
from emp
where title = 'BOSS' and salary <= 100
o DBMS pode detectar a contradição e retornar um conjunto de resultados vazio sem nem mesmo tocar nos dados?
Let A = ( title = ’BOSS’ ), B = ( salary > 100 )
select * from emp where (A ^ ~B)
select * from emp where (A ^ ~B) ^ (~A v B) # by cc1
select * from emp where (A ^ ~B ^ ~A) v (A ^ ~B ^ B)
select * from emp where (FALSE ^ ~B) v (A ^ FALSE)
select * from emp where (FALSE) v (FALSE)
select * from emp where (FALSE)
Eu tentei o Postgres 13 (veja o violino acima)
Seq Scan on emp (cost=0.00..26.50 rows=2 width=46) (actual time=0.134..0.134 rows=0 loops=1)
Filter: ((salary <= 100) AND ((title)::text = 'BOSS'::text))
Rows Removed by Filter: 999
Planning Time: 0.312 ms
Execution Time: 0.149 ms
e Db2 11.5.4.0:
Optimized Statement:
-------------------
SELECT
Q1.EMP_NO AS "EMP_NO",
Q1.TITLE AS "TITLE",
Q1.SALARY AS "SALARY"
FROM
DB2INST1.EMP AS Q1
WHERE
(Q1.SALARY <= 100) AND
(Q1.TITLE = 'BOSS')
Access Plan:
-----------
Total Cost: 51.8267
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
90.2441
TBSCAN
( 2)
51.8267
4
|
999
TABLE: DB2INST1
EMP
Q1
mas ambos não conseguem fazê-lo. Algum outro SGBD que possa identificar a contradição e agir sobre ela? É claro que isso é mais uma diversão do que um problema do mundo real, mas ainda assim.
Edit: A restrição sugerida por @federico-razzoli em sua resposta também não funciona:
alter table emp add constraint cc1
check ((title = 'BOSS' and salary > 100) or
(title = 'WORK' AND salary <= 100));
e a mesma consulta ainda causa acesso à tabela
Rows
RETURN
( 1)
Cost
I/O
|
90.2441
TBSCAN
( 2)
51.8267
4
|
999
TABELA: DB2INST1 EMP Q1
Sua pergunta é ótima, mas pula uma etapa. Você está assumindo que o planejador de consultas (ou otimizador, dependendo da terminologia que você preferir) pode levar em conta todos os CHECKs, mesclando-os.
Então você tem:
E:
E você assume que o planejador deve ser capaz de saber que:
Este pode ser o caso ou não.
Em geral, a pergunta para sua resposta depende da tecnologia que você está usando. MySQL e MariaDB não levam em conta CHECKs. Algumas outras tecnologias provavelmente o fazem. Você terá que testar as tecnologias nas quais está interessado.
Veja a seguir um resumo de como vários DBMS lidam com as restrições na questão. 3 outras consultas também são testadas:
Db2 V11.5.4.0
Falha ao identificar a contradição em q1, é bem-sucedido para q2, q3, q4
2,3,4.
MariaDB 10.5
Falha ao identificar a contradição na consulta 1,3,4, é bem-sucedida na consulta 2
Fiddle MariaDB
1,3,4.
MySQL 8.0
Falha ao identificar a contradição na consulta 1,3,4, é bem-sucedida na consulta 2
Fiddle MySQL
1,3,4.
Oráculo 18c
Não tenho 100% de certeza de como interpretar o plano, mas diz TABLE ACCESS Full para todas as 4 consultas, então acho que significa que falhou 1,2,3,4
violino Oracle 18c
Postgres 13
Falha ao identificar a contradição em q1, q3, q4, é bem-sucedido para q2
1,3,4. QUERY PLAN Seq Scan on emp (custo=0.00..26.50 linhas=2 largura=46) Filtro: ( ...
2. Resultado do PLANO DE CONSULTA (custo=0,00..0,00 linhas=0 largura=0) Filtro Único: falso
Fiddle Postgres
SQL Server 2019
Falha ao identificar a contradição em q1, q3, q4, é bem-sucedido para q2
Fiddle SQL-server
Conclusão
Nenhum dos DBMS testados conseguiu usar as informações das restrições.
O Db2 é o único que usa a informação se ela for injetada na consulta.
Ao que parece, o Oracle é o único que falha em todas as 4 consultas.