If I alter the partitioning in oracle and trying to retrieval the data,is necessary to mention the partition name or not?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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:
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.
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.