create table myschema.mynewtable tablespace mynewtablespace parallel ( degree default ) nologging monitoring partition by list (vehicle_type) ( partition TRUCK values ('truck'), partition CAR values ('car'), partition SUV values ('suv'), partition VAN values ('van')) as select * from myschema.myoldtable;The problem is that queries into the partitioned table take approximately twice as long to complete as compared to the non-partitioned table. Am I missing something here? Thanks in advance!
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.
This was first published in March 2006