Results 1 to 5 of 5
  1. #1
    Join Date
    May 2015
    Posts
    3

    Unanswered: make it dynamic - partition(dailyp20150514)

    Hi,

    I am running stats on a table which is partitioned. Current I use the below query:

    select * from testing partition(dailyp20150514)

    It is possible I can run the above query for the whole month dynamically. Maybe put sysdate somewhere as it would make it dynamic.

    The "testing" table does has a time but running again the time column takes a lot of time so partition results are the fastest reults.

    Also I don't want to include the weekends (Fri, Sat)

    Could you please help.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >It is possible I can run the above query for the whole month dynamically.

    what does above mean?
    Does SELECT only run once per month?
    Today is 18 May. What does whole month mean if SELECT is run today?
    I doubt I would recognize any posted solution to be correct, since I don't understand what exactly are the requirments.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2015
    Posts
    3
    I meant that if I want to run the query for 30 days by using sysdate. Something like sysdate-30.

    Running the query like
    "where date>=trunc(sysdate)-30" will take ages.

    To clarify more, can I club the below queries in one query using sysdate as the dynamic date since I want to schedule a report for this and I don't want to hardcode any date.

    select * from testing partition(dailyp20150514)
    select * from testing partition(dailyp20150513)
    select * from testing partition(dailyp20150512)
    select * from testing partition(dailyp20150511)
    select * from testing partition(dailyp20150510)
    and so on....

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >select * from testing partition(dailyp20150514)
    >select * from testing partition(dailyp20150513)
    >select * from testing partition(dailyp20150512)

    above is not valid as a single query.

    Please prove that 30 SELECT statements will be faster than single SELECT "where date>=trunc(sysdate)-30"
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    if you don't have the "date" column global indexed, then do so using a function index on trunc(date_column). This will allow you to grab the rows very quickly.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •