Created
April 22, 2022 05:12
-
-
Save kleontev/3124b19a1635e4e1486580cb0cca9a91 to your computer and use it in GitHub Desktop.
a simple approach to identify a broken predicate that makes a query return 0 rows
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Задача (из чата https://t.me/oracle_dbd): | |
Есть сложный запрос с большим количеством соединений и условий фильтрации. | |
При определенных условиях запрос возвращает существенно меньшее количество строк, | |
чем мы ожидаем, либо не возвращает вообще ни одной. | |
Нам нужно понять, какое из условий в запросе приводит к этому. | |
*/ | |
set echo on lines 200 pages 0 | |
whenever sqlerror continue | |
drop table t_parent purge; | |
drop table t_child_1 purge; | |
drop table t_child_2 purge; | |
whenever sqlerror exit failure | |
create table t_parent as | |
select rownum id, rownum || 'x' val | |
from dual | |
connect by rownum <= 10; | |
create table t_child_1 as | |
select rownum id, rownum id_p | |
from dual | |
connect by rownum <= 5; | |
create table t_child_2 as | |
select * from t_child_1; | |
alter session set statistics_level = all; | |
select --+leading(p c1 c2) | |
p.id, p.val | |
from t_parent p | |
join t_child_1 c1 on p.id = c1.id_p | |
join t_child_2 c2 on p.id = c2.id_p | |
where 1 = 1 | |
and c1.id < 10 | |
-- this condition breaks the query; no rows satisfy it. | |
-- we'd like to be able to identify it quickly, without | |
-- eliminating all conditions in the where clause one by one | |
and c2.id > 100 | |
/ | |
-- check "A-Rows" column; then check access/filter predicats for | |
-- the first zero A-Rows step. one of these likely breaks the query. | |
select * from table(dbms_xplan.display_cursor(format=> 'iostats')) | |
/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment