Oracle table partitions

0 pts.
Tags:
Oracle
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!
ASKED: March 22, 2006  8:51 AM
UPDATED: March 26, 2006  11:35 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

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.

Hope this helps.

Regards,
Rajesh.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

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
  • Wornout
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/oracle-table-partitions/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

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

Your password has been sent to:

To follow this tag...

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

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

Thanks! We'll email you when relevant content is added and updated.

Following