drop table ch1;
create table ch1 (f1 number(1) constraint ch1_ck_f1 check (f1 in ('1','2','3')));
insert into ch1 values (1);
insert into ch1 values (2);
insert into ch1 values (3);
insert into ch1 values (4);
commit;
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'ch1')
select * from ch1 where f1=1;
explain plan for
select * from ch1 where f1=1;
select * from table(dbms_xplan.display);
Как то так вышло у моего коллеги, что для поля с типом NUMBER был создан чек-констрейнт ...in ('1','2','3').
Оптимизатор сгенерил фактически лишний и не корректный предикат из чек-констрейнта.
В итоге результат таков:
SQL> select * from ch1 where f1=1;
no rows selected
SQL> explain plan for
2 select * from ch1 where f1=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3341507894
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| CH1 | 1 | 3 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("F1"=1)
а ежели еще вот так
alter table ch1 disable constraint ch1_ck_f1;
select * from ch1 where f1=1;
explain plan for
select * from ch1 where f1=1;
select * from table(dbms_xplan.display);
то
SQL> alter table ch1 disable constraint ch1_ck_f1;
Table altered.
SQL> select * from ch1 where f1=1;
F1
----------
1
SQL> delete from plan_table;
3 rows deleted.
SQL> explain plan for
2 select * from ch1 where f1=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2138608289
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CH1 | 1 | 3 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("F1"=1)
Проверял на следующих версиях: 9.2.0.4, 10.2.0.1, 10.2.0.4, 11.1.0.6 - проявлялось только на 10.2.0.1.
