drop table t_sales
/

create table t_sales (sales_date not null)
partition by range (sales_date)
(partition Y2000 values less than (date '2001-01-01'),
partition Y2001 values less than (date '2002-01-01'),
partition Y2002 values less than (date '2003-01-01'))
as
select date '2000-01-01' + rownum - 1
from dual
connect by level <= date '2003-01-01' - date '2000-01-01'
/

begin
dbms_stats.gather_table_stats(
   ownname => user,
   tabname => 't_sales',
   granularity => 'all');
end;
/

select partition_name, num_rows
from user_tab_partitions
where table_name = 'T_SALES'
order by 1
/

explain plan for
select * from t_sales
where sales_date between date '2000-01-01' and date '2000-12-31'
/
select * from table(dbms_xplan.display)
/

explain plan for
select * from t_sales
where trunc(sales_date) between date '2000-01-01' and date '2000-12-31'
/
select * from table(dbms_xplan.display)
/

alter table t_sales
add constraint trunc_sales_date check (sales_date = trunc(sales_date))
disable novalidate;

explain plan for
select * from t_sales
where trunc(sales_date) between date '2000-01-01' and date '2000-12-31'
/
select * from table(dbms_xplan.display)
/

alter table t_sales modify constraint trunc_sales_date rely
/

explain plan for
select * from t_sales
where trunc(sales_date) between date '2000-01-01' and date '2000-12-31'
/
select * from table(dbms_xplan.display)
/