Ask the Expert

Partitioned vs. non-partitioned tables

I'm attempting to address some performance issues by using list partitioning. As an experiment I created two small tables (with about 1,000 rows) of vehicle maintenance data, one with no partitions and the other partitioned by vehicle type:
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!

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: