Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

If I alter the partitioning in oracle and trying to retrieval the data,is necessary to mention the partition name or not?

share|improve this question
Do you mean mention the partition name in a DML (SELECT/INSERT/UPDATE) statement or in an ALTER TABLE statement? – Phil Apr 25 '13 at 10:36
yes,I means the same – Rizwan Sheriff Apr 25 '13 at 11:31
Which of the two? – Phil Apr 25 '13 at 11:41
I mean the SELECT. – Rizwan Sheriff Apr 25 '13 at 11:54

The whole point behind partitioning in Oracle is that it is transparent to the queries. And if your query only needs data from one partition, then Oracle will (if it can) only read that single partition.

So if you have a table partitioned on, for example, year so that each year's data is stored in a separate partition, a query such as:

select max(column_name)
from my_partitioned_table
where year = 2013;

Oracle will only scan the partition for the year 2013.

On the other hand, if you are altering the table to add or remove partitions, you will more often than not need to name the partitions. This isn't always true: for example, Oracle 11g has interval partitioning where Oracle can automatically create new partitions for new data as needed -- perfect for the example I just mentioned.

share|improve this answer
Thank you for your kind response.Thank you for your support. – Rizwan Sheriff Apr 25 '13 at 12:36

You don't necessarily need to mention the name of your partition in the select statement since oracle can figure this out as part of it's execution plan, however to avoid that overhead of oracle figuring out the partition itself, you can mention the name.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.