Smart questions
Smart answers
Smart people
Join Tek-Tips Forums

Member Login

Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Partitioning and re-indexing - Methodology comments welcome!Helpful Member!(3) 

TurtleOp (IS/IT--Management) (OP)
8 May 09 4:48
Hello All!

I am maintaining for one of our customer one old Oracle database with 2 standby database using arc log file .
OS: AIX 5L on IBM P520 and RS-6000

In the production database I have the 2 most heavily used tables of the prod environment with a size of 10GB each one and 15M rows.
Btw, the total size of the db is 110GB.
In the meantime, I have a 18 and 20 indexes on them, but the majority of them are inconsistent because too old.
When I try to recreate only one index it costs me an average of 2 hours! (I extract the DDL code, drop the object and recreate the index)
 Then I am seriously thinking to export the entire table and then re-import it.

Anyway, the goal is to:
-    recreate all the indexes on those 2
-    Partitioning the 2 tables – probably with Composite type.

I would like to ask you advice on the methodology. I have only one week-end to do it.

Do you agree with me to say that it is better to export/import the tables before partitioning them?

Helpful Member!  Thargy (TechnicalUser)
8 May 09 8:23
The following might be better, but I'm not sure if DBMS_REDEFINITION is available in 8.



TurtleOp (IS/IT--Management) (OP)
8 May 09 8:56
Sounds good! I didn't know this package. I will check it now, Thanks a lot!
Helpful Member!  Beilstwh (Programmer)
2 Jun 09 14:35
Build your partitioned table structure and then do a select into into the new table. When it is done, everything is indexed and partitioned. When you get done, rename the new table to the correct name.

Oracle DBA/Developer
New York State, USA

Helpful Member!  LKBrwnDBA (MIS)
3 Jun 09 7:57

DBMS_REDEFINITION is not available in 8.1.7

Following Beilstwh's advice, write the DDL to create the partitions and execute CREATE TABLE ... AS SELECT in parallel sessions.

Also, avoid creating "global" indexes, use "local" indexes (Easier to maintain).

The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

TurtleOp (IS/IT--Management) (OP)
3 Jun 09 8:03
Thank you very much guy!
I will do it on this way.
Kindest Regards,
TurtleOp (IS/IT--Management) (OP)
3 Jun 09 8:04
(Guys and not guy)

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close