Oracle table partitions

I'm attempting to address some performance issues by using list partitioning. As an experiment I created two small tables (~1000 rows) of vehicle maintenance data, one with no partitions 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;
Problem is that queries into the partitioned table take approx. twice as long to complete as compared to the non-partitioned table. Am I missing something here?
Thanks in advance!

Answer Wiki

From what i could understand from your question, you have partitioned the table based on vehicle_type and you are trying to select ALL the records from the table using select * from myschema.myoldtable;

The above query would result into a FULL TABLE SCAN and would not give you any performance benefit. The basic idea behind partitioning is to avoid access to the unwanted data by accessing only the relevant data. In your case, select * from myschema.myoldtable where vehicle_type = ‘TRUCK’ would be the correct query to benchmark the performance.

Also, note that partitioning is geneally recommended for very large tables (in hundereds of Megabytes) and it would not be a great idea to partition a table sizing few MBs.

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

Processing your response...

Discuss This Question: 1 &nbspReply

There was an error processing your information. Please try again later.

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

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

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

To follow this tag...

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