Home » RDBMS Server » Server Administration » partitioning a non-partitioned table (Oracle 9.0.2.0.6 Windows Server 2003)
partitioning a non-partitioned table [message #304077] Mon, 03 March 2008 23:59 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Dear All,

I had a non-partitioned table with millions of record which getting bigger and bigger so the queries on this particular table are getting slower & slower
So v have decided to partition this table ,this table have a date column (e.g. start_date) and according to our environment we decided to create range partion based on the column start_date
My question is
can v create partitions based on a non-partioned table without creating new table and then move data to new table
i just want to partition my non-partitioned table
please suggest me how to achieve this?
Re: partitioning a non-partitioned table [message #304104 is a reply to message #304077] Tue, 04 March 2008 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the queries on this particular table are getting slower & slower So v have decided to partition this table ,

Why do you think partitioning will make your queries faster?

Quote:
can v create partitions based on a non-partioned table without creating new table and then move data to new table

In short, no.
Have a look at dbms_redefinition.

Regards
Michel
Re: partitioning a non-partitioned table [message #304124 is a reply to message #304077] Tue, 04 March 2008 03:31 Go to previous messageGo to next message
mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai
Member
Shakeel,
Following pointers can be of some help. But remember, incorrect partioning often proves to be a curse rather then blessing.

Quote:
http://www.oracle-base.com/articles/misc/PartitioningAnExistingTable.php
http://www.dbazine.com/oracle/or-articles/nanda6


Regards

Manjit Kumar [mkbhati]
Re: partitioning a non-partitioned table [message #305453 is a reply to message #304077] Mon, 10 March 2008 16:49 Go to previous message
shlomi_bt
Messages: 16
Registered: August 2005
Location: Canada
Junior Member

hi,
another thing you must see after you partition your table is
that your queries are using the partitioned index - the
start date.

you can check this out very simple by using explain plan and
see on the access to the new parition table which partitions are
been used.
shlomi.
Previous Topic: probelm creating function-based-index
Next Topic: Locking problem
Goto Forum:
  


Current Time: Wed Dec 07 07:01:41 CST 2016

Total time taken to generate the page: 0.12680 seconds