INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

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!

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!
3

Share

Partitioning and re-indexing - Methodology comments welcome!

Partitioning and re-indexing - Methodology comments welcome!

(OP)
Hello All!

I am maintaining for one of our customer one old Oracle 8.1.7.4 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?

Cheers/TurtleOp

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

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

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.

Bill
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).
noevil
 

----------------------------------------------------------------------------
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!

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

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

(OP)
(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!

Resources

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