What I like to do is to pull a data set only for busy hour on every Friday for 1 year period. I have used the following query:
select to_char(datetimelocal,'DD/MM/YYYY HH24:MI:SS'), colA,colB,colC,colD
from Schema_X.Table_Y
where DATETIMELOCAL between '1-Apr-2014' and '1-Apr-2015'
and to_char(datetimelocal,'D')=6
and to_number(to_char(datetimelocal,'sssss')) between 57600 and 64800
This query worked, but I got the following warning message from the System admin that I have exhausted the system resources.
"There is an user xxx running a query over Schema_X tables and those are scanning the whole table and not doing a partition pruning. So the user should use partitioned field also reduce the date range, which is too big"
I found the Table_X is daily partitioned, but don't know how to use the partitions wisely to reduce the system load.
Partitions are like this:
20121230,TO_DATE(' 2012-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),83,NATIONAL_RPT,DISABLED,,,,,,0
20121231,TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),83,NATIONAL_RPT,DISABLED,,,,,,0
20130101,TO_DATE(' 2013-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),83,NATIONAL_RPT,DISABLED,,,,,,0
20130102,TO_DATE(' 2013-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),83,NATIONAL_RPT,DISABLED,,,,,,0
20130103,TO_DATE(' 2013-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),83,NATIONAL_RPT,DISABLED,,,,,,0
....
As Jon said, Oracle can not zoom in to specific partitions because of how your where clause is expressed. If you want all fridays you have to give the sql engine specific days. This can be done by creation a table with all fridays you need or generating one on the fly.
-- generate table
CREATE TABLE friday_table (friday_date DATE);
DECLARE
v_last_friday_of_period DATE := to_date('2015.04.10','yyyy.mm.dd');
v_particular_friday DATE := v_last_friday_of_period;
BEGIN
WHILE v_last_friday_of_period - v_particular_friday < 365 LOOP
INSERT INTO friday_table VALUES (v_particular_friday);
v_particular_friday := v_particular_friday - 7;
END LOOP;
END;
/
SELECT *
FROM tbl t
,friday_table f
WHERE t.datetimelock BETWEEN to_date(to_char(f.friday_date,'yyyy.mm.dd ')||'12:00:00','yyyy.mm.dd hh24:mi:ss')
AND to_date(to_char(f.friday_date,'yyyy.mm.dd ')||'13:00:00','yyyy.mm.dd hh24:mi:ss');
-- on the fly
SELECT *
FROM tbl t
,(SELECT to_date('2015.04.10','yyyy.mm.dd') - rownum * 7 AS friday_date
FROM dual
CONNECT BY rownum <= 52) f
WHERE t.datetimelock BETWEEN to_date(to_char(f.friday_date,'yyyy.mm.dd ')||'12:00:00','yyyy.mm.dd hh24:mi:ss')
AND to_date(to_char(f.friday_date,'yyyy.mm.dd ')||'13:00:00','yyyy.mm.dd hh24:mi:ss');