1 2 Previous Next 18 Replies Latest reply: Dec 3, 2012 12:47 PM by 961716 Go to original post RSS
      • 15. Re: where to find info on function based indexes
        sb92075
        958713 wrote:
        Thank you for your response Mohamed.
        Unfortunately, there is no C3 object in our database.

        SQL> desc c3
        ERROR:
        ORA-04043: object c3 does not exist


        SQL> select object_name, object_type from dba_objects where object_name = 'C3';

        no rows selected
        which USER issued the DESC C3?
        which USER owns C3?
        • 16. Re: where to find info on function based indexes
          961716
          I think you are correct Jonathan. You are good!
          How did you figure that out?

          So, here is from the dbms_metadata ouput: (Noitce the "C3" DESC).
          Not sure what the "C1" is about.


          CREATE INDEX "TQ"."TQR1_C3" ON "TQ"."TQR1" ("C3" DESC, "C1")
          PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
          STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
          PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
          TABLESPACE "TQ_DATA"

          So, now back to my original problem, in order to shrink the space on this table, can I just use the output from above to recreate it?
          It seems logical, so that would be good if that is true.

          Thanks again.
          • 17. Re: where to find info on function based indexes
            rp0428
            >
            Not sure what the "C1" is about.
            >
            The C1 is the second column of the multi-column index.
            >
            CREATE INDEX "TQ"."TQR1_C3" ON "TQ"."TQR1" ("C3" DESC, "C1")
            PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
            FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "TQ_DATA"

            So, now back to my original problem, in order to shrink the space on this table, can I just use the output from above to recreate it?
            It seems logical, so that would be good if that is true.
            >
            You could use that DDL to recreate the index. But notice that the DDL includes the current storage parameters for that particular index so if you use that DDL it will recreate using (or try to) those same storage parms.

            The default parms for that tablespace may have changed.

            So you need to decide whether to use those 'old' parms or remove the storage and other clauses and let the defaults be used.

            One way to decide is to think about what you would do if you were creating a different, new index. Would you really manually specify all of those parameters? Probably not.

            So one possibililty is to use
            CREATE INDEX "TQ"."TQR1_C3" ON "TQ"."TQR1" ("C3" DESC, "C1")
            COMPUTE STATISTICS
            TABLESPACE "TQ_DATA"
            • 18. Re: where to find info on function based indexes
              961716
              Thank you rp. Yeah, maybe I'll move all the indexes to a new TQ_IDX tablespace on a different LUN using different disks.
              That would make more sense anyway, and then use the default values for everything else.
              1 2 Previous Next