| Get data from various partitions with having one extra column [message #639505] |
Wed, 08 July 2015 18:35  |
 |
shumail
Messages: 149
Registered: September 2012
Location: Canada
|
Senior Member |
|
|
HI All
I did my interview and interviewer asked me the following question:
How to Fetch table data in 10 partitions with having one extra column
but unfortunately I didn't understand the above question but I'm curious what should be the answer for this question, Please guide.
So far I tried to do it by using Oracle 11g express but I think express edition not supported this feature, see below the steps I took and advice me accordingly
Sample Data
CREATE TABLE test2
(sal NUMBER(5)
)
PARTITION BY RANGE(sal)
(
PARTITION sal_100 VALUES LESS THAN (101),
PARTITION sal_200 VALUES LESS THAN (201)
)
;
Query on single partition
select * from test2 partition ( sal_100);
Query on Multiple partition
select * from test2 partition ( sal_100)
union all
select * from test2 partition ( sal_200)
Please correct me if you find me wrong....
Regards
Muzz
|
|
|
|
|
|
|
|
| Re: Get data from various partitions with having one extra column [message #639520 is a reply to message #639519] |
Thu, 09 July 2015 03:22  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
What Michel means is that you don't change your sql just because you're selecting from a partitioned table.
Imagine your table had no partitions and you wanted to get all of the data where salary was less than 200.
Would you
select *
from table where sal between 100 and 200
union
select *
from table where sal between 0 and 100;
Or would you select *
from table
where sal <200;
|
|
|
|
|
|
| Re: Get data from various partitions with having one extra column [message #639529 is a reply to message #639527] |
Thu, 09 July 2015 04:24 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
shumail wrote on Thu, 09 July 2015 10:07Thanks for the reply but why oracle doc says that in order to select data from partition table you need to use the following query:
Query
select * from test2 partition ( sal_100);
Note:- sal_100 is a partition name
Please link to the doc that says that, thanks (my bet is that, read in context, that's not actually what it's saying)
|
|
|
|
|
|
|
|
|
|
| Re: Get data from various partitions with having one extra column [message #639533 is a reply to message #639532] |
Thu, 09 July 2015 05:06 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
IMHO, you are absolutely correct. To quote a very knowledgeable guy (Billy~Verreynne) oft seen over on OTN:
"It is wrong to use the partition clause in standard end-user or application SQL, for a very sound reason.
That reason is that the end-user and/or application: DOES NOT NEED TO KNOW THE PHYSICAL STORAGE CHARACTERISTICS OF THE OBJECTS BEING QUERIED.
Just as end-user/app does not need to know whether EMP is a synonym, a view, an index organised table, a hash table, or whatever else.. an end-user/app does not need to know in what partition data is to be found.
"
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
