Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • 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!

*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.


Partitioning and re-indexing - Methodology comments welcome!

Partitioning and re-indexing - Methodology comments welcome!

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?


RE: Partitioning and re-indexing - Methodology comments welcome!

Sounds good! I didn't know this package. I will check it now, Thanks a lot!

RE: Partitioning and re-indexing - Methodology comments welcome!

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

RE: Partitioning and re-indexing - Methodology comments welcome!

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

RE: Partitioning and re-indexing - Methodology comments welcome!

Thank you very much guy!
I will do it on this way.
Kindest Regards,

RE: Partitioning and re-indexing - Methodology comments welcome!

(Guys and not guy)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

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!


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