what happens, if i fire a select statement in oracle having partition in it. if selected partitions does not exist?

Akhil Md
Akhil Md used Ask the Experts™
does it go for full table scan or what?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
I suppose it depends on what type of partitioning you have set up and what types of indexes you have on those partitions.

Best way to tell: explain plan the select and see.

Can you provide more information behind this question?
Database Administrator
You will likely get this error:
ORA-02149: Specified partition does not exist

But, this rarely happens in Oracle since it isn't necessary to include a partition name in Oracle when querying partitioned tables.  If your query provides a "where" clause value that matches the partition column, Oracle can efficiently use the partitions even though the query doesn't provide partition name.

I agree with slightwv.  There must be a bigger issue that this question is part of.  When would you expect this to happen?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial