PK & UK index rebuild ....
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: PK & UK index rebuild ....

  1. #1
    Join Date
    Aug 2001
    Posts
    11
    Hi,

    I have few tables under a user schema, which shows that their PK & UK indexes have reached an extent of >20. Now I would like to rebuild then and the oracle is not allowing me to do that. Since it was a STD edition, option of online rebuild is ruled out.

    1. Could some one give me the steps on how to do this?

    2. Does this pk fragmentation means that the table is also fragemted?

    3. On the above scenario, if I do an export then truncate the tables and import,

    3a) Would those indexes constrains and their indexes would get dropped, and rebuilt when the import takes place, or is it something that I have to do it manually?

    Since this being a pretty well netted, schema, I'm kind of confused on how to do things...

    Could some one please help me with some steps?

    Mr. T


  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    number of extents does not mean fragmentation

    if you really want to reduce number of extents of indexes you drop them and recreate them (if alter index rebuild does not work? Or you mean alter index rebuild online?)


  3. #3
    Join Date
    Aug 2001
    Posts
    11
    Thanx pando for the reply, but here are my scenarios

    1. Since they are of PK used by FKs, dropping them then not possible, unless and other wise you first disable the FKs and then the PKs.

    2. Disabling the PK. keeping the schema online of what I'm worried of much.

    3. I think both the rebuild options are ruled out on the STD edition. Thats the big pain!

    I'm very much interested in doing the drop and rebuild, but considering points one and two, I'm a bit confused on how to do it.

    T


  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    The STD edition does support REEBUILD and you can rebuild your PK/UK/FK using this rebuild option.


    ALTER INDEX index_name REBUILD;

    If you want it to be rebuilt on another tablespace, you could make use of the TABLESPACE option after the REBUILD clause.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    May 2001
    Posts
    70
    Sam,

    Is it correct stating that Standard Edition doesn't support

    alter index index_name rebuild online;


  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    That is true.


    Here you can see what is in and what is out


    Partitioning---------------------------------INSTALLABLE
    Objects-------------------------------------ENTERPRISE|STANDARD|PERSONAL,
    Parallel Server-----------------------------INSTALLABLE,
    Advanced replication (see NOTE)---------ENTERPRISE,
    Bit-mapped indexes------------------------ENTERPRISE,
    Connection multiplexing--------------------ENTERPRISE|STANDARD,
    Connection pooling-------------------------ENTERPRISE|STANDARD,
    Database queuing--------------------------ENTERPRISE,
    Incremental backup and recovery--------ENTERPRISE,
    Instead-of triggers--------------------------ENTERPRISE|STANDARD|PERSONAL,
    Parallel backup and recovery--------------ENTERPRISE,
    Parallel execution---------------------------ENTERPRISE,
    Parallel load---------------------------------ENTERPRISE|STANDARD,
    Point-in-time tablespace recovery--------ENTERPRISE,
    Fine-grained access control---------------ENTERPRISE,
    N-Tier authentication/authorization-------ENTERPRISE,
    Function-based indexes--------------------ENTERPRISE,
    Plan Stability--------------------------------ENTERPRISE,
    Online Index Build--------------------------ENTERPRISE,
    Coalesce Index-----------------------------ENTERPRISE,
    Managed Standby---------------------------ENTERPRISE,
    Materialized view rewrite-------------------ENTERPRISE,
    Materialized view warehouse refresh------ENTERPRISE,
    Database resource manager---------------ENTERPRISE,
    Spatial----------------------------------------ENTERPRISE,
    Visual Information Retrieval----------------ENTERPRISE,
    Export transportable tablespaces----------ENTERPRISE,
    Transparent Application Failover-----------ENTERPRISE,
    Fast-Start Fault Recovery-------------------ENTERPRISE,
    Sample Scan---------------------------------ENTERPRISE,
    Duplexed backups---------------------------ENTERPRISE,
    Java-------------------------------------------INSTALLABLE,
    OLAP Window Functions---------------------ENTERPRISE


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by rcherch
    Sam,

    Is it correct stating that Standard Edition doesn't support

    alter index index_name rebuild online;
    Well, theN simply rebuild them without ONLINE option. What this means is that existing index will be locked during the rebuild, so no data modification on thos index will be allowed during the rebuilding time. But the index will be still used for queries!

    This is in all respect better than droping an index and recreating it:
    a) for data integrity reasons - if you drop PK index, how would you enforce uniqueness for new records during DROP/CREATE rebuild? How would you enforce referential integrity since you must disable FKs pointing to this PK?
    b) for speed of recreate - rebuild will be *much* faster than DROP/CREATE.
    c) for queries execution speed - during DROP/CREATE some of your queries might bring your system to its knees without that index available...

    But if your main objective for rebuild is to decrease the number of index extents, then the best advice is: DON'T REBUILD AT ALL!!!! It will have avsolutely no performance impact!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width