Go Premium for a chance to win a PS4. Enter to Win


select from partition range?

Posted on 2004-09-21
Medium Priority
Last Modified: 2012-05-05
Is it possible to select from a partition range?


something like

select * from table partition range (partition1,partition3);

Question by:morphman
LVL 23

Assisted Solution

seazodiac earned 150 total points
ID: 12111710
well, if you have a LOCAL index (as opposed to global),

you can achieve the same goal
by specifying the where clause :

for example: partition1 has the partition key from 1-50.
partition3 from 100-150.

you can do sth like this:

select * from <table_name>
where partition_key between 1 and 50
select * from <table_name>
where partition_key between 100 and 150.

Accepted Solution

pratikroy earned 225 total points
ID: 12111748
Not that I know of.

But you can certainly create a view that has the data from the range of partitions that you are interested in and select from the view.


create or replace view emp3 as
select * from emp partition (p1)
union all
select * from emp partition (p2)
union all
select * from emp partition (p3);

Hope that would help
LVL 11

Expert Comment

ID: 12112014
select p_no, empno from (
select 1 p_no, empno from emp partition (p1)
union all
select 2 p_no, empno  from emp partition (p2)
union all
select 3 p_no, empno  from emp partition (p3))
where p_no betwee 1 and 2;


Author Comment

ID: 12117079
I guess the answer is no then..

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question