In Oracle8i range paritioned table, I want to make queries without hardcoding the partition name. But I want to get the benefit of faster access by the partitioned table.
Is it possible? Please help.
you can always use view instead of the actual partioned table.
And since the underlying table is partitioned, then your view should be much faster compare to regular views.
To answer your first question, yes, if your queries "prune" the data according to the way the table is partitioned, you will still get faster performance whether or not you specify the partition name in the queries.
Also, if you have setup the server and table for parallel query, you will get faster performance even on a full scan, because the several partitions can be scanned, concurrently.
you dont have to hardcode the partition name to use partitioning advantages, if that was the case (hardcoding partition name in application) then I doubt many people would use this feature.
Oracle's optimizer is clever enough to use this feature without partition name, let's say you have 3 partitions with 3 values in each, (1,2,3 4,5,6 7,8,9) if your select says
where X = 3 oracle will do a partition elimination, it will read the first partition only not all of them
Reply With Quote
Bookmarks