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

  • 1
    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
  • 1
    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.

  • 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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