|
Queries on a partitioned table will perform faster if your query can benefit from partition pruning. Partition pruning is when Oracle automatically removes partitions from consideration when processing your query. Using your example above, the following query will benefit from partition pruning:
SELECT * FROM myschema.mynewtable
WHERE vehicle_type IN ('van','car');
In this case, only the partitions CAR and VAN will be scanned. The other partitions will never be read since they do not contain values that can possibly be returned by the above query. However, the following query will not utilize partition pruning:
SELECT * FROM myschema.mynewtable
WHERE vehicle_age > 2;
In this query, it is possible that all partitions will contain a vehicle more than two years old.
Partitioning tables should start with a good analysis of the queries that most often use the table. If you rarely query the VEHICLE_TYPE column in the WHERE clause, then the partitioning above will not help your performance that much.
Aside from the above information, you will want to see how your query is being executed by the Optimizer. You will want to run EXPLAIN PLAN on the query to see the operations the Optimizer is performing to return your query's results.
|
