Oracle SQL pull data for a selected date from a table that is daily partitioned -
what pull data set busy hour on every friday 1 year period. have used following query:
select to_char(datetimelocal,'dd/mm/yyyy hh24:mi:ss'), cola,colb,colc,cold schema_x.table_y datetimelocal between '1-apr-2014' , '1-apr-2015' , to_char(datetimelocal,'d')=6 , to_number(to_char(datetimelocal,'sssss')) between 57600 , 64800 this query worked, got following warning message system admin have exhausted system resources.
"there user xxx running query on schema_x tables , scanning whole table , not doing partition pruning. user should use partitioned field reduce date range, big"
i found table_x daily partitioned, don't know how use partitions wisely reduce system load.
partitions this:
partition_name,high_value,high_value_length,tablespace_name,compression,num_rows,blocks,empty_blocks,last_analyzed,avg_space,subpartition_count 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 specific partitions because of how clause expressed. if want fridays have give sql engine specific days. can done creation table fridays need or generating 1 on 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 friday_table values (v_particular_friday); v_particular_friday := v_particular_friday - 7; end loop; end; / select * tbl t ,friday_table f t.datetimelock between to_date(to_char(f.friday_date,'yyyy.mm.dd ')||'12:00:00','yyyy.mm.dd hh24:mi:ss') , to_date(to_char(f.friday_date,'yyyy.mm.dd ')||'13:00:00','yyyy.mm.dd hh24:mi:ss'); -- on fly select * tbl t ,(select to_date('2015.04.10','yyyy.mm.dd') - rownum * 7 friday_date dual connect rownum <= 52) f t.datetimelock between to_date(to_char(f.friday_date,'yyyy.mm.dd ')||'12:00:00','yyyy.mm.dd hh24:mi:ss') , to_date(to_char(f.friday_date,'yyyy.mm.dd ')||'13:00:00','yyyy.mm.dd hh24:mi:ss');