Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> set linesize 200 SQL> set pagesize 200 SQL> drop table t_sales 2 / Table dropped. SQL> SQL> create table t_sales (sales_date not null) 2 partition by range (sales_date) 3 (partition Y2000 values less than (date '2001-01-01'), 4 partition Y2001 values less than (date '2002-01-01'), 5 partition Y2002 values less than (date '2003-01-01')) 6 as 7 select date '2000-01-01' + rownum - 1 8 from dual 9 connect by level <= date '2003-01-01' - date '2000-01-01' 10 / Table created. SQL> SQL> begin 2 dbms_stats.gather_table_stats( 3 ownname => user, 4 tabname => 't_sales', 5 granularity => 'all'); 6 end; 7 / PL/SQL procedure successfully completed. SQL> SQL> select partition_name, num_rows 2 from user_tab_partitions 3 where table_name = 'T_SALES' 4 order by 1 5 / PARTITION_NAME NUM_ROWS ------------------------------ ---------- Y2000 366 Y2001 365 Y2002 365 SQL> SQL> explain plan for 2 select * from t_sales 3 where sales_date between date '2000-01-01' and date '2000-12-31' 4 / Explained. SQL> select * from table(dbms_xplan.display) 2 / PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- Plan hash value: 2932426231 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 366 | 2928 | 2 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 366 | 2928 | 2 (0)| 00:00:01 | 1 | 1 | |* 2 | TABLE ACCESS FULL | T_SALES | 366 | 2928 | 2 (0)| 00:00:01 | 1 | 1 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("SALES_DATE">=TO_DATE('2000-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "SALES_DATE"<=TO_DATE('2000-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 15 rows selected. SQL> SQL> explain plan for 2 select * from t_sales 3 where trunc(sales_date) between date '2000-01-01' and date '2000-12-31' 4 / Explained. SQL> select * from table(dbms_xplan.display) 2 / PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- Plan hash value: 722415528 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 24 | 2 (0)| 00:00:01 | | | | 1 | PARTITION RANGE ALL| | 3 | 24 | 2 (0)| 00:00:01 | 1 | 3 | |* 2 | TABLE ACCESS FULL | T_SALES | 3 | 24 | 2 (0)| 00:00:01 | 1 | 3 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TRUNC(INTERNAL_FUNCTION("SALES_DATE"))>=TO_DATE('2000-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("SALES_DATE"))<=TO_DATE('2000-12-3 1 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 16 rows selected. SQL> SQL> alter table t_sales 2 add constraint trunc_sales_date check (sales_date = trunc(sales_date)); Table altered. SQL> SQL> explain plan for 2 select * from t_sales 3 where trunc(sales_date) between date '2000-01-01' and date '2000-12-31' 4 / Explained. SQL> select * from table(dbms_xplan.display) 2 / PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- Plan hash value: 2932426231 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 1 | 8 | 2 (0)| 00:00:01 | 1 | 1 | |* 2 | TABLE ACCESS FULL | T_SALES | 1 | 8 | 2 (0)| 00:00:01 | 1 | 1 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TRUNC(INTERNAL_FUNCTION("SALES_DATE"))>=TO_DATE('2000-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("SALES_DATE"))<=TO_DATE('2000-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "SALES_DATE">=TO_DATE('2000-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "SALES_DATE"<=TO_DATE('2000-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 18 rows selected. SQL>