Hi team,
I need to partition TEST_PARTITIONS table on the basis of end_date using RAGE INTERVAL partition.
create table TEST_PARTITIONS
partition by range(end_date)
( partition p2010 values less than (to_date('01-Jan-2011','dd-mon-yyyy')) ,
partition p2011 values less than (to_date('01-Jan-2012','dd-mon-yyyy')) ,
partition p2012 values less than (to_date('01-Jan-2013','dd-mon-yyyy')) ,
partition p2013 values less than (to_date('01-Jan-2014','dd-mon-yyyy')) ,
partition p2014 values less than (to_date('01-Jan-2015','dd-mon-yyyy')) ,
partition p2015 values less than (to_date('01-Jan-2016','dd-mon-yyyy')) ,
partition p2016 values less than (to_date('01-Jan-2017','dd-mon-yyyy')) ,
partition p2017 values less than (to_date('01-Jan-2018','dd-mon-yyyy')) )
as
select a.* , created-1 as begin_date,
created+1 as end_date
from all_objects a;When I try to fetch orders for a particular day i.e. 13/04/2016 it result into No record found
select *
from TEST_PARTITIONS
where end_date = to_date('13/04/2016','DD/MM/YYYY');When i try to to char end_date column query give output as expected, but it goes to search for all partitions -
select *
from TEST_PARTITIONS
where end_date = to_date('13/04/2016','DD/MM/YYYY');
Plan hash value: 3588510895
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 3696 | 470 (2)| 00:00:06 | | |
| 1 | PARTITION RANGE ALL| | 21 | 3696 | 470 (2)| 00:00:06 | 1 | 8 |
|* 2 | TABLE ACCESS FULL | TEST_PARTITIONS | 21 | 3696 | 470 (2)| 00:00:06 | 1 | 8 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_CHAR(INTERNAL_FUNCTION("END_DATE"),'DD/MM/YYYY')='13/04/2016')
Note
-----
- dynamic sampling used for this statement (level=2)Could you please suggest any alternative to fetch day wise order retrieval.
Dates in Oracle Database always have a time component. When you do:
where end_date = to_date('13/04/2016','DD/MM/YYYY')You're comparing end_date to date time 13 April, 2016 at midnight. It's unlikely you have any objects created at this precise time.
To find rows created on a given day, you need to look for those that happened anytime that day:
set serveroutput off
alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss';
select min(created)
from all_objects a;
MIN(CREATED)
24-AUG-2013 18:26:49
create table TEST_PARTITIONS
partition by range(end_date)
( partition p2015 values less than (to_date('01-Jan-2016','dd-mon-yyyy')) ,
partition p2016 values less than (to_date('01-Jan-2017','dd-mon-yyyy')) ,
partition p2017 values less than (to_date('01-Jan-2018','dd-mon-yyyy')) ,
partition p2019 values less than (to_date('01-Jan-2019','dd-mon-yyyy')) )
as
select a.* , created as end_date
from all_objects a;
select count(*)
from TEST_PARTITIONS
where end_date = to_date('24/08/2013','DD/MM/YYYY');
COUNT(*)
0
select count(*)
from TEST_PARTITIONS
where end_date = to_date('24/08/2013 18:26:49 ','DD/MM/YYYY HH24:MI:SS');
COUNT(*)
131
select count(*)
from TEST_PARTITIONS
where end_date >= to_date('24/08/2013','DD/MM/YYYY')
and end_date < to_date('24/08/2013','DD/MM/YYYY') + 1;
COUNT(*)
74,263 I'm not sure what you mean by "tried to char end_date". But if you look at the predicate section at the bottom your plan you'll see:
2 - filter(TO_CHAR(INTERNAL_FUNCTION("END_DATE"),'DD/MM/YYYY')='13/04/2016')If so, Oracle Database does an implicit conversion to a date when you compare it to a date. This means it applies a function to end_date. It's not able to do partition pruning when your query has a function on the partition column(s). Which is why you see it access all partitions.
Which is also why you should use the dt <= end_date < dt + 1 method above to find rows on a given day. Not applying trunc or similar to end_date:
select count(*)
from TEST_PARTITIONS
where trunc(end_date) = to_date('24/08/2013','DD/MM/YYYY');
COUNT(*)
74,263
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select count(*) from TEST_PARTITIONS where trunc(end_date) =
to_date('24/08/2013','DD/MM/YYYY')
Plan hash value: 4117722176
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | PARTITION RANGE ALL| |
|* 3 | TABLE ACCESS FULL | TEST_PARTITIONS |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TRUNC(INTERNAL_FUNCTION("END_DATE"))=TO_DATE(' 2013-08-24
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
select count(*)
from TEST_PARTITIONS
where end_date >= to_date('24/08/2013','DD/MM/YYYY')
and end_date < to_date('24/08/2013','DD/MM/YYYY') + 1;
COUNT(*)
74,263
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select count(*) from TEST_PARTITIONS where end_date >=
to_date('24/08/2013','DD/MM/YYYY') and end_date <
to_date('24/08/2013','DD/MM/YYYY') + 1
Plan hash value: 1044590884
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | PARTITION RANGE SINGLE| |
|* 3 | TABLE ACCESS FULL | TEST_PARTITIONS |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("END_DATE">=TO_DATE(' 2013-08-24 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "END_DATE"<TO_DATE(' 2013-08-25 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))