OraFAQ Forum: Server Administration » partitioning a non-partitioned table

Home » RDBMS Server » Server Administration » partitioning a non-partitioned table (Oracle 9.0.2.0.6 Windows Server 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

partitioning a non-partitioned table [message #304077]

Mon, 03 March 2008 23:59

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?

Report message to a moderator

Re: partitioning a non-partitioned table [message #304104 is a reply to message #304077]

Tue, 04 March 2008 01:12

Michel Cadot
Messages: 68683
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

Report message to a moderator

Re: partitioning a non-partitioned table [message #304124 is a reply to message #304077]

Tue, 04 March 2008 03:31

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]

Report message to a moderator

Re: partitioning a non-partitioned table [message #305453 is a reply to message #304077]

Mon, 10 March 2008 16:49

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.

Report message to a moderator

Previous Topic:	probelm creating function-based-index
Next Topic:	Locking problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 16 05:14:16 CDT 2024