Results 1 to 10 of 10

Thread: Invalid Indexes

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Invalid Indexes

    Hi

    I have just emptied a tablespace by moving it's data to another tablespace and dropped the old one.

    I have toad 8.6. Could anyone please tell me how I can find out whether the indexes of the moved tables are valid/invalid in toad?

    Thanks
    Regards

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> desc all_indexes
     Name					   Null?    Type
     ----------------------------------------- -------- ----------------------------
     OWNER					   NOT NULL VARCHAR2(30)
     INDEX_NAME				   NOT NULL VARCHAR2(30)
     INDEX_TYPE					    VARCHAR2(27)
     TABLE_OWNER				   NOT NULL VARCHAR2(30)
     TABLE_NAME				   NOT NULL VARCHAR2(30)
     TABLE_TYPE					    CHAR(5)
     UNIQUENESS					    VARCHAR2(9)
     COMPRESSION					    VARCHAR2(8)
     PREFIX_LENGTH					    NUMBER
     TABLESPACE_NAME				    VARCHAR2(30)
     INI_TRANS					    NUMBER
     MAX_TRANS					    NUMBER
     INITIAL_EXTENT 				    NUMBER
     NEXT_EXTENT					    NUMBER
     MIN_EXTENTS					    NUMBER
     MAX_EXTENTS					    NUMBER
     PCT_INCREASE					    NUMBER
     PCT_THRESHOLD					    NUMBER
     INCLUDE_COLUMN 				    NUMBER
     FREELISTS					    NUMBER
     FREELIST_GROUPS				    NUMBER
     PCT_FREE					    NUMBER
     LOGGING					    VARCHAR2(3)
     BLEVEL 					    NUMBER
     LEAF_BLOCKS					    NUMBER
     DISTINCT_KEYS					    NUMBER
     AVG_LEAF_BLOCKS_PER_KEY			    NUMBER
     AVG_DATA_BLOCKS_PER_KEY			    NUMBER
     CLUSTERING_FACTOR				    NUMBER
     STATUS 					    VARCHAR2(8)
     NUM_ROWS					    NUMBER
     SAMPLE_SIZE					    NUMBER
     LAST_ANALYZED					    DATE
     DEGREE 					    VARCHAR2(40)
     INSTANCES					    VARCHAR2(40)
     PARTITIONED					    VARCHAR2(3)
     TEMPORARY					    VARCHAR2(1)
     GENERATED					    VARCHAR2(1)
     SECONDARY					    VARCHAR2(1)
     BUFFER_POOL					    VARCHAR2(7)
     USER_STATS					    VARCHAR2(3)
     DURATION					    VARCHAR2(15)
     PCT_DIRECT_ACCESS				    NUMBER
     ITYP_OWNER					    VARCHAR2(30)
     ITYP_NAME					    VARCHAR2(30)
     PARAMETERS					    VARCHAR2(1000)
     GLOBAL_STATS					    VARCHAR2(3)
     DOMIDX_STATUS					    VARCHAR2(12)
     DOMIDX_OPSTATUS				    VARCHAR2(6)
     FUNCIDX_STATUS 				    VARCHAR2(8)
     JOIN_INDEX					    VARCHAR2(3)
     IOT_REDUNDANT_PKEY_ELIM			    VARCHAR2(3)
     DROPPED					    VARCHAR2(3)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool X marks the spot...

    In TOAD: Schema Browser -> Indexes

    Check out the ones marked with RED "X".
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    Well you see that's the thing. No red crosses.

    I moved the data out of the 9i tablespace with giving any rebuild indexes command. Data got moved fine so I was presuming the indexes needed to be rebuilt too.

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    I can see that the status of all the tables that I moved is N/A. I know if it is a valid index, it should say 'valid'.

    I rebuilt the index of one table that I moved and the status is still 'invalid' plus now when I query all_indexes, there is no 'b_level', 'leaf_blocks', 'distinct_keys', ave_leaf_blocks_per_key','ave_data_blocks_per_key' , 'clustering_factor','num_rows' info anymore but still have info like 'degree','instances', 'partitioned'..etc.

    And no 'Global_stats' anymore either whereas the other 6 tables that I have not rebuilt indexes for have 'global_stats'.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool How did you do it?

    How did you move the tables?

    If you used ALTER TABLE ... MOVE, then you do need to rebuild the indexes.

    If you used exp/imp or datapump, default is indexes are re-built.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    Yes, I used ALTER TABLE MOVE....

    Before building the indexes, I wanted to see why I had to....if you know what I mean? Like can I find out if it indicates anywhere in 'toad' that the indexes of the moved tables need to be rebuilt?

  8. #8
    Join Date
    Aug 2008
    Posts
    464
    Is the only draw-back of not rebuilding indexes that queries on those table will run slower?

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Rowid's changed

    Quote Originally Posted by shajju
    Is the only draw-back of not rebuilding indexes that queries on those table will run slower?
    You need to re-build indexes because the table rowid's changed and the indexes may be (are) rendered unusable.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #10
    Join Date
    Aug 2008
    Posts
    464
    Yes you are right. When I was searching for red crosses, I was looking in the wrong place.....The red crosses can be found under the 'partitions' tab. Rebuilt indexes and all is fine.

    Thanks everyone.

Posting Permissions

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