Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Partition in a Single Query (Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit)
Multiple Partition in a Single Query [message #436099] Mon, 21 December 2009 08:59 Go to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

Is there any option avaiable to define multiple partition in a query?

As in below SQL Condition to search the table is same but partition differs used UNION.


SELECT * FROM TAB_NAME PARTITION( TAB_0912)
WHERE COL1 >= TO_DATE('01/12/2009 00:08:00', 'DD/MM/YYYY HH24:MI:SS')
  AND COL1 <= TO_DATE('01/12/2009 00:09:00', 'DD/MM/YYYY HH24:MI:SS')
UNION
SELECT * FROM TAB_NAME PARTITION( TAB_0911)
WHERE COL1 >= TO_DATE('01/12/2009 00:08:00', 'DD/MM/YYYY HH24:MI:SS')
  AND COL1 <= TO_DATE('01/12/2009 00:09:00', 'DD/MM/YYYY HH24:MI:SS')
UNION
SELECT * FROM TAB_NAME PARTITION( TAB_0910)
WHERE COL1 >= TO_DATE('01/12/2009 00:08:00', 'DD/MM/YYYY HH24:MI:SS')
  AND COL1 <= TO_DATE('01/12/2009 00:09:00', 'DD/MM/YYYY HH24:MI:SS');



Please suggest.
Re: Multiple Partition in a Single Query [message #436101 is a reply to message #436099] Mon, 21 December 2009 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Multiple Partition in a Single Query [message #436105 is a reply to message #436101] Mon, 21 December 2009 09:21 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

Please find DDL & Criteria?

Result: Whenever data is search for COL1 value it should be limited to 50 Days from that date of COL1 value

TABLE NAME : TAB_NAME
PARTITION:    COL3
INDEX : COL2, COL3, COL1
ATTRIBUTE:
           COL2   VARCHAR2(10)
           COL3   DATE
           COL1   DATE
           COL4   NUMBER
           COL5  VARCHAR2(10)
           COL6  VARCHAR2(10)

Criteria : Data should search for 50 days by checking the COL1



Please suggest and if more clarification is pls revert.
Re: Multiple Partition in a Single Query [message #436106 is a reply to message #436105] Mon, 21 December 2009 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Data should search for 50 days by checking the COL1

I see the words, but still don't know what rows should be returned.
Re: Multiple Partition in a Single Query [message #436108 is a reply to message #436099] Mon, 21 December 2009 09:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any option avaiable to define multiple partition in a query?

No.

Regards
Michel
Re: Multiple Partition in a Single Query [message #436109 is a reply to message #436099] Mon, 21 December 2009 10:07 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Why can't you take advantage of partition pruning ?

Regards

Raj

Re: Multiple Partition in a Single Query [message #436302 is a reply to message #436109] Tue, 22 December 2009 21:13 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It seems that COL1 is not your partitioning column, otherwise the values for 1/12/2009 would all be in the December partition.

Whichever column IS the partition column, you could just add it to the query:

SELECT * FROM TAB_NAME 
WHERE COL1 >= TO_DATE('01/12/2009 00:08:00', 'DD/MM/YYYY HH24:MI:SS')
  AND COL1 <= TO_DATE('01/12/2009 00:09:00', 'DD/MM/YYYY HH24:MI:SS')
  AND PART_COL BETWEEN TO_DATE('01/10/2009 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AND
                       TO_DATE('31/12/2009 23:59:59', 'DD/MM/YYYY HH24:MI:SS')


Ross Leishman
Previous Topic: how to stop total execution in procedure via condition
Next Topic: dateadd in plsql?
Goto Forum:
  


Current Time: Sun Dec 04 02:55:00 CST 2016

Total time taken to generate the page: 0.07144 seconds