Home » SQL & PL/SQL » SQL & PL/SQL » Need to select data from two partitions (oracle 10g)
Need to select data from two partitions [message #351454] Tue, 30 September 2008 23:08 Go to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
hi,
I need to select data from two diffrent partitions of a table.
i tried
sql >select *from T1 partition(t11);

it worked , but when i tried
sql>select * from T1 partition(T11,T12);
/
this query is not working.Is there any workaround.

Regards,
NAvneet


Re: Need to select data from two partitions [message #351470 is a reply to message #351454] Wed, 01 October 2008 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use UNION ALL.

Regards
Michel
Re: Need to select data from two partitions [message #351509 is a reply to message #351454] Wed, 01 October 2008 03:58 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Why do you want to use partition keyword when you can use partition pruning? Any reasons ?

Regards

Raj
Re: Need to select data from two partitions [message #352104 is a reply to message #351509] Mon, 06 October 2008 03:17 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Raj,
actualy i wanted to select data from two partitions of the table that's why I was using partition key word.
I m not aware of partition pruning,Will be thankful if you can please explain, how would it help here?

regards,
Navneet
Re: Need to select data from two partitions [message #352106 is a reply to message #352104] Mon, 06 October 2008 03:39 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Have a look at this link.

It's basically the conditions in where clause.

Partition Pruning

Some Definition:-

Quote:

Partitioning Pruning: Partitioning pruning is the simplest and also the most substantial means to improve performance using partitioning. Partition pruning can often improve query performance by several orders of magnitude. For example, suppose an application contains an Orders table containing a historical record of orders, and that this table has been partitioned by week. A query requesting orders for a single week would only access a single partition of the Orders table. If the Orders tables had 2 years of historical data, this query would access one partition instead of 104 partitions. This query could potentially execute 100x fast simply because of partition-pruning. Partition pruning works with all of Oracle's other performance features. Oracle will utilize partition pruning in conjunction with any indexing technique, join technique, or parallel access method.



Regards,
Rajat Ratewal

[Updated on: Mon, 06 October 2008 03:43]

Report message to a moderator

Re: Need to select data from two partitions [message #352112 is a reply to message #352106] Mon, 06 October 2008 03:53 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Thanks Rajat,
so you mean to say that if I put the same condition in where clause from which the partitions have been created , the intended partitions will be scanned.M I right???
how oracle creates explain plan in such cases.does it check the partition definitions of the table
Re: Need to select data from two partitions [message #352118 is a reply to message #352112] Mon, 06 October 2008 04:06 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

so you mean to say that if I put the same condition in where clause from which the partitions have been created , the intended partitions will be scanned.M I right???



yup.(As Far As My knowledge Is Concerned) Razz

Quote:

Just like indexing, if you’re getting back too large a percentage of the table, Oracle won’t prune partitions.





Quote:

how oracle creates explain plan in such cases.does it check the partition definitions of the table



Check the explain plan with or without conditions.
You can know. Cool

Regards,
Rajat

[Updated on: Mon, 06 October 2008 04:14]

Report message to a moderator

Re: Need to select data from two partitions [message #352141 is a reply to message #352118] Mon, 06 October 2008 05:29 Go to previous message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
thanks a lot for explaination Cool
Previous Topic: How to pass a whole column as parameter?
Next Topic: Display Collection
Goto Forum:
  


Current Time: Fri Dec 02 20:26:23 CST 2016

Total time taken to generate the page: 0.25473 seconds