Home » SQL & PL/SQL » SQL & PL/SQL » how sql fetches records from diffrent partitions of table (oracle 10g)
how sql fetches records from diffrent partitions of table [message #423243] Wed, 23 September 2009 01:16 Go to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Hi all,
I want to know the internals of sql query fetching while the query fetches records from multiple partitions. for example if I have 10 partitions of a table and my query is fetching data from 2 partitions,then how oracle will merge the records from both partitions.


Regards,
Navneet

[Updated on: Wed, 23 September 2009 01:38] by Moderator

Report message to a moderator

Re: how sql fetches records from diffrent partitions of table [message #423246 is a reply to message #423243] Wed, 23 September 2009 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does it very well.
What do you mean "how"?

Regards
Michel
Re: how sql fetches records from diffrent partitions of table [message #423263 is a reply to message #423246] Wed, 23 September 2009 03:11 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Hi,
I will explain my question by following example :

select * from my_table where country_id in ('US','CN');


my_table is partitioned on country_id column.Now I want to know how oracle fetch records from diffrent partitions (in my example 'US' and 'CN') and then merge the outputs into one single result.
What I mean is that how oracle provides this abstraction,means user is not bothered about how the data is saved internaly in diffrent partitions and how it would be fetched and merged into single output ,for him there is exatraction of data from single table.

I think now the question is clear.

[Updated on: Wed, 23 September 2009 03:22] by Moderator

Report message to a moderator

Re: how sql fetches records from diffrent partitions of table [message #423266 is a reply to message #423263] Wed, 23 September 2009 03:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
I think now the question is clear.
Sadly, it'w whether we can understand the question that matters, and I'm still confused.

I can't see what there is here to explain - all the partitions are part of the same table and they all have the same row structure.

Oracle fetches data from one or more partitions and returns it - what is there about this that needs explaining with regards to partitions?
Re: how sql fetches records from diffrent partitions of table [message #423271 is a reply to message #423263] Wed, 23 September 2009 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not use quote tags if you do not quote.

Oracle does it in the same way it returns rows from different extents. Do you ever care about this?

Regards
Michel
Re: how sql fetches records from diffrent partitions of table [message #423288 is a reply to message #423271] Wed, 23 September 2009 03:56 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
does oracle uses 'union' or 'union all' ?

if my query is
select * from my_table where country_id in ('US','CN') ;

Does oracle breaks it in

select * from my_table where country_id ='US'
union
select * from my_table where country_id ='CN'

?

regards,
Navneet
Re: how sql fetches records from diffrent partitions of table [message #423290 is a reply to message #423288] Wed, 23 September 2009 03:58 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, this does not mean it does parallel the execution between partitions if it can do it as it may do it between extents.

Regards
Michel

[Updated on: Wed, 23 September 2009 03:59]

Report message to a moderator

Previous Topic: how to load data from excel sheet to oracle table
Next Topic: Date function
Goto Forum:
  


Current Time: Wed Dec 07 08:40:43 CST 2016

Total time taken to generate the page: 0.22236 seconds