CODEWITHSUNDEEP
sqloracle-databaseoverloadingpartition
notilas
notilas

Reputation: 2485

Oracle SQL pull data for a selected date from a table that is daily partitioned

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
....

Upvotes: 0

Views: 358

Answers (3)

David Aldridge
David Aldridge

Reputation: 52336

I think that ideally you'd write a query to generate a list of the days that you want to run the query for.

Something like ...

(select <somedate_that_is_a_friday> + rownum * 7
from   dual
connect by level <= <however_many_you_want>)

If you then:

select ...
where  DATETIMELOCAL in (select <somedate_that_is_a_friday> + ... etc

... then an explain plan ought to show that the partitions are being chosen by KEY, which indicates that partition pruning will take place but the optimiser does not know which partitions will be accessed until execution time.

Upvotes: 1

jva
jva

Reputation: 2807

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');

Upvotes: 2

user565869
user565869

Reputation:

Your query is filtering in stages, first by year then by day, then by time. That means that in principal any record could be counted; the system cannot easily zoom in on the partitions you need. You need to express the filter in terms of specific dates:

SELECT *
FROM Tbl
WHERE
        DateTimeLock >= DATE'2015-04-03' AND DateTimeLock < DATE'2015-04-04' AND 
    AND EXTRACT(HOUR FROM DateTimeLock)) BETWEEN 16 AND 17  -- Inclusive

This will zoom in on the exact partition you need.

However, it obviously just gives you the one day's data. You'll probably need to use a loop to query each Friday in a separate query, collecting the results in a table.

You could try to keep it as a single query by using OR in the first DateTimeLock filter:

(
   DateTimeLock >= DATE'2015-04-03' AND DateTimeLock < DATE'2015-04-04'
OR DateTimeLock >= DATE'2015-03-27' AND DateTimeLock < DATE'2015-03-28'
OR DateTimeLock >= DATE'2015-03-20' AND DateTimeLock < DATE'2015-03-21'
)
AND EXTRACT(HOUR FROM DateTimeLock)) BETWEEN 16 AND 17  -- Inclusive

...however, I suspect that the query engine will convert this to a table scan, which is what you started out with.

Upvotes: 2

Related Questions