Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table emp (
emp_no int not null primary key, 
title varchar(10) not null, 
salary int not null,
CHECK (title in ('WORK','BOSS')) enable,
CHECK (emp_no > 0) enable
);
alter table emp add constraint cc1 
    CHECK ( (title <> 'BOSS' OR salary > 100) ) enable;
alter table emp add constraint cc2
    CHECK ( (title <> 'WORK' OR salary <= 100)) enable;
explain plan set statement_id = 'Q1'
for
select * 
from emp 
where title = 'BOSS' and salary <= 100;
explain plan set statement_id = 'Q2'
for
select * 
from emp 
where title = 'BOSS' and salary <= 100 and 1=0
explain plan set statement_id = 'Q3'
for
select * 
from emp 
where title = 'BOSS' and salary <= 100 and ( (title <> 'BOSS' OR salary > 100) )
explain plan set statement_id = 'Q4'
for
select * 
from emp 
where title = 'BOSS' and salary <= 100 and ( NOT (title = 'BOSS' AND salary <= 100) )
SELECT id, LPAD(' ',2*(LEVEL-1))||operation operation, options,
       object_name, object_alias, position 
    FROM plan_table 
    START WITH id = 0 AND statement_id = 'Q1'
    CONNECT BY PRIOR id = parent_id AND statement_id = 'Q1'
    ORDER BY id;


ID
OPERATION
OPTIONS
OBJECT_NAME
OBJECT_ALIAS
POSITION


0
SELECT STATEMENT
null
null
null
2

1
  TABLE ACCESS
FULL
EMP
EMP@SEL$1
1


SELECT id, LPAD(' ',2*(LEVEL-1))||operation operation, options,
       object_name, object_alias, position 
    FROM plan_table 
    START WITH id = 0 AND statement_id = 'Q2'
    CONNECT BY PRIOR id = parent_id AND statement_id = 'Q2'
    ORDER BY id;


ID
OPERATION
OPTIONS
OBJECT_NAME
OBJECT_ALIAS
POSITION


0
SELECT STATEMENT
null
null
null
0

1
  FILTER
null
null
null
1

2
    TABLE ACCESS
FULL
EMP
EMP@SEL$1
1


SELECT id, LPAD(' ',2*(LEVEL-1))||operation operation, options,
       object_name, object_alias, position 
    FROM plan_table 
    START WITH id = 0 AND statement_id = 'Q3'
    CONNECT BY PRIOR id = parent_id AND statement_id = 'Q3'
    ORDER BY id;


ID
OPERATION
OPTIONS
OBJECT_NAME
OBJECT_ALIAS
POSITION


0
SELECT STATEMENT
null
null
null
0

1
  FILTER
null
null
null
1

2
    TABLE ACCESS
FULL
EMP
EMP@SEL$1
1


SELECT id, LPAD(' ',2*(LEVEL-1))||operation operation, options,
       object_name, object_alias, position 
    FROM plan_table 
    START WITH id = 0 AND statement_id = 'Q4'
    CONNECT BY PRIOR id = parent_id AND statement_id = 'Q4'
    ORDER BY id;


ID
OPERATION
OPTIONS
OBJECT_NAME
OBJECT_ALIAS
POSITION


0
SELECT STATEMENT
null
null
null
0

1
  FILTER
null
null
null
1

2
    TABLE ACCESS
FULL
EMP
EMP@SEL$1
1
ID	OPERATION	OPTIONS	OBJECT_NAME	OBJECT_ALIAS	POSITION
0	SELECT STATEMENT	null	null	null	2
1	TABLE ACCESS	FULL	EMP	EMP@SEL$1	1