Home » SQL & PL/SQL » SQL & PL/SQL » data unload from a table with daily partition
data unload from a table with daily partition [message #278764] Mon, 05 November 2007 11:15 Go to next message
dr46014
Messages: 48
Registered: February 2007
Member
hi all
i have a table where in i have to unoad data from daily partitions between today()-1 and today()-31.the partition names of the table is as follows FDS_20071101,FDS_20071102 like this.
how can i write the query so that the data will be unloaded from all the partitioned.i want to parameterize the query so that every day when i run it will unload today()-1 and today()-31 range of data.
pleas advise
Re: data unload from a table with daily partition [message #278766 is a reply to message #278764] Mon, 05 November 2007 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't use a simple query with between sysdate-1 and sysdate-31 or the like?

Regards
Michel
Re: data unload from a table with daily partition [message #278771 is a reply to message #278764] Mon, 05 November 2007 11:39 Go to previous messageGo to next message
dr46014
Messages: 48
Registered: February 2007
Member
yes correct but how can i include all the partitions.can i use select * from table partition(partition name).
what should i write in the partition name place.
this should reperesent all 30 partitions.
Re: data unload from a table with daily partition [message #278773 is a reply to message #278764] Mon, 05 November 2007 11:43 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You do NOT need to specify any partition name!
Re: data unload from a table with daily partition [message #278775 is a reply to message #278764] Mon, 05 November 2007 11:44 Go to previous messageGo to next message
dr46014
Messages: 48
Registered: February 2007
Member
no actually i need to specify so that the data pull will be faster and i can also use my etl logic to put them in files
Re: data unload from a table with daily partition [message #278776 is a reply to message #278764] Mon, 05 November 2007 11:49 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Since you are convinced you already have the answer, then just implement your solution.
Write SQL to write SQL & then execute it.
Re: data unload from a table with daily partition [message #278779 is a reply to message #278764] Mon, 05 November 2007 11:55 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Oracle will automatically select from the correct partitions and no others. Why do you think that you need to specify a specific partition in your select.
Re: data unload from a table with daily partition [message #278780 is a reply to message #278775] Mon, 05 November 2007 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

the data pull will be faster

It won't. Oracle knows which partitions you want to query.

Regards
Michel
Re: data unload from a table with daily partition [message #278803 is a reply to message #278780] Mon, 05 November 2007 13:55 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Michel,

I believe you talk about the infamous BVP. Oracle knows which partition needs to be queried by doing BVP (Bind Variable Peeking). There is a huge discussion whether BVP is evil or not ? Because i have faced quite a number of problems in my previous project because of this (especially on partitioned tables with and without histograms) when the data is not equally distributed. Having said that, Oracle has changed this way of behaviour in 11g. I read this in asktom but i am not able to find the link. Just thought of updating the thread with this info.

Regards

Raj
Re: data unload from a table with daily partition [message #278804 is a reply to message #278803] Mon, 05 November 2007 13:59 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, I was talking queries with constants.
This unload is a maintenance query not a usual one, executed once from time to time, constants are the way to use it, so no problem with optimizer.

Regards
Michel
Previous Topic: COUNT of distinct values in 2 columns
Next Topic: insufficient privileges !!!!!
Goto Forum:
  


Current Time: Thu Dec 08 20:06:21 CST 2016

Total time taken to generate the page: 0.09387 seconds