Q

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. Queries into the partitioned table take approximately twice as long to complete as compared to the non-partitioned table. Am I missing something here?

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!

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close