This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Dec 3, 2012 10:47 AM by 961716 RSS

where to find info on function based indexes

961716 Newbie
Currently Being Moderated
Hello,

Using Oracle 11.2, can someone tell me where I can find information about existing Function Based indexes in my database?

I want to shrink the unused space on a table, but there is a FB index that I have to drop in order to shrink the space.
Then, I need to recreate it after I shrink the space.
But how do I find out what functions the index is created to be used for?

I've already looked at dba_indexes, and dba_ind_expressions with no luck.
I see the index, but I don't see what the functions are that are being applied for the FB index.


Thanks in advance.
  • 1. Re: where to find info on function based indexes
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    The following probably has what you want:
    SQL> desc user_ind_expressions
     Name                   Null? Type
     ---------------------- ----- ------------
     INDEX_NAME                   VARCHAR2(30)
     TABLE_NAME                   VARCHAR2(30)
     COLUMN_EXPRESSION            LONG
     COLUMN_POSITION              NUMBER
    Here's a quick sample:
    SQL> create table t (c varchar2(46));
    
    Table created.
    
    SQL> create index t_idx on t (upper(c));
    
    Index created.
    
    SQL> @print_table "select * from user_ind_expressions"
    INDEX_NAME                    : T_IDX
    TABLE_NAME                    : T
    COLUMN_EXPRESSION             : UPPER("C")
    COLUMN_POSITION               : 1
    -----------------
    
    PL/SQL procedure successfully completed.
    Here you can see that the function (column_expression) is UPPER("C")

    EDIT1:

    I see you say you checked dba_ind_expressions already so not sure what you are missing/asking for if that and/or user_ind_expressions does not contain what you are looking for.

    Edited by: Mark Williams on Nov 29, 2012 4:56 PM
  • 2. Re: where to find info on function based indexes
    961716 Newbie
    Currently Being Moderated
    Well, as I mentioned, that's what I already looked at.

    The column 'COLUMN_EXPRESSION' only has a value of "C3"

    What does that mean? How can I determine what that means?

    Edited by: 958713 on Nov 29, 2012 1:57 PM

    I see you updated while I was replying.... so it appears something is strange about my FB index because it just has "C3" and no function is listed.
    Perhaps someone was attempting to create a FB index and did it incorrectly, but how can I verify this?
  • 3. Re: where to find info on function based indexes
    Justin Cave Oracle ACE
    Currently Being Moderated
    COLUMN_EXPRESSION is a LONG. What tool are you using to display the data? One possibility is that whatever tool you're using doesn't support the LONG data type (LONG has been depricated for quite some time).

    Justin
  • 4. Re: where to find info on function based indexes
    961716 Newbie
    Currently Being Moderated
    I was using SQL*Plus right on the database server (v.11.2).

    So, how come the output from the previous poster shows the function, but mine does not?
  • 5. Re: where to find info on function based indexes
    Justin Cave Oracle ACE
    Currently Being Moderated
    Using an 11.2 version of Oracle and SQL*Plus seems to work for me
    SQL> drop table t;
    
    Table dropped.
    
    SQL> create table t (c varchar2(46));
    
    Table created.
    
    SQL> create index t_idx on t (upper(c));
    
    Index created.
    
    SQL> column index_name format a10;
    SQL> column table_name format a10;
    SQL> column column_expression format a25;
    
    SQL> select *
      2    from user_ind_expressions
      3   where index_name = 'T_IDX';
    
    INDEX_NAME TABLE_NAME COLUMN_EXPRESSION         COLUMN_POSITION
    ---------- ---------- ------------------------- ---------------
    T_IDX      T          UPPER("C")                              1
    Mark is using a script called "print_table" to display the results. I'm assuming this is based on Tom Kyte's print_table script. You might try using that to display the results but, as I showed above, a straight SQL*Plus session should happily display a LONG.

    Justin
  • 6. Re: where to find info on function based indexes
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    Sorry for missing the bit about dba_ind_expressions the first time. That's what I get for reading too quickly I guess.

    Off the top of my head I don't know the reason for the C3 in your case and Justin has already suggested LONG could be at play (but that seems unlikely after reading your other follow-ups).

    Out of curiousity do you get the same results when using dbms_metadata? Something like the following (as the owner of the index):

    select dbms_metadata.get_ddl('INDEX', '<YOUR INDEX NAME HERE>') from dual;
  • 7. Re: where to find info on function based indexes
    rp0428 Guru
    Currently Being Moderated
    >
    So, how come the output from the previous poster shows the function, but mine does not?
    >
    Because that column of his index used a function and yours doesn't.

    If I create this index all I get is 'C3'
    CREATE INDEX SCOTT.D1_NDX2 ON SCOTT.D1
    ('C3')
    
    'C3'
    You should definitely extract the metadata before dropping the index just in case you need it
    select dbms_metadata.get_ddl('INDEX', 'D1_NDX2') from dual
    
      CREATE INDEX "SCOTT"."D1_NDX2" ON "SCOTT"."D1" ('C3') 
  • 8. Re: where to find info on function based indexes
    961716 Newbie
    Currently Being Moderated
    Yes, this is why I'm also confused, which is what I pointed out in the first entry of this thread.

    Notice here:
    SQL> select index_name, index_type from dba_indexes where table_name = 'TQR1';
    
    INDEX_NAME                     INDEX_TYPE
    ------------------------------ ---------------------------
    IDX$$_0FFD0001                 NORMAL
    IDX$$_211C0002                 NORMAL
    TQR1_C3                        FUNCTION-BASED NORMAL
    . . .
    Now, let's confirm another way....
    select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME 
    from dba_indexes where index_name = 'TQR1_C3';
    
    OWNER      INDEX_NAME INDEX_TYPE                  TABLE_OWNER  TABLE_NAME
    ---------- ---------- --------------------------- ------------ ----------
    TQ         TQR1_C3    FUNCTION-BASED NORMAL       TQ           TQR1
    
    1 row selected.
    Now, let's show the selection from dba_ind_expressions...
    INDEX_OWNER  INDEX_NAME TABLE_OWNER  TABLE_NAME COLUMN_EXPRESSI COLUMN_POSITION
    ------------ ---------- ------------ ---------- --------------- ---------------
    TQ           TQR1_C3    TQ           TQR1       "C3"                          1
    I don't know the password for the TQ owner, so I'll have to wait until I can find that out before I can run DBMS_METADATA.
  • 9. Re: where to find info on function based indexes
    Mohamed Houri Pro
    Currently Being Moderated
    INDEX_OWNER  INDEX_NAME TABLE_OWNER  TABLE_NAME COLUMN_EXPRESSI COLUMN_POSITION
    ------------ ---------- ------------ ---------- --------------- ---------------
    TQ           TQR1_C3    TQ           TQR1       "C3"                          1
    Just for curiosity, what is the output of the following command
     desc C3
    It might be that C3 is a deterministic function on which a function based index has been created
    SQL> drop table t_fbi purge;
    
    Table dropped.
    
    SQL> create table t_fbi (id number, n1 number);
    
    Table created.
    
    
    SQL>  create or replace function c3 return number
      2      deterministic
      3      is
      4      begin
      5         return 42;
      6      end;
      7      /
    
    Function created.
    
    SQL> create index indx_1 on t_fbi(c3());
    
    Index created.
    
    SQL> select index_name, column_expression
      2        from user_ind_expressions
      3       where index_name = 'INDX_1';
    
    INDEX_NAME             COLUMN_EXPRESSION
    -----------------------------------------
    INDX_1                 "C3"()
    But franckly speaking I doubt it is the case here.

    Best regards
    Mohamed Houri
    www.hourim.wordpress.com
  • 10. Re: where to find info on function based indexes
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    If you have sufficient privileges on another account (i.e. a DBA or similar which you may since you've been referencing the dba* views) you can specify the object owner with dbms_metadata.

    This would look like:

    select dbms_metadata.get_ddl('INDEX', 'TQR1_C3', 'TQ') from dual;

    I had not (obviously) thought about the situation proposed by rp0428; however, if this is the case the value in column_expression would be: 'C3' (i.e. single ticks around the literal). I've not seen those present in the output so far but maybe they are really there.

    I had thought about something similar to what Mohamed has proposed; however, again, the format of the value in column_expression does not match just a single, stand-alone C3. In this case it is: "C3"() as shown in Mohamed's example. But, would be interesting to verify all the same.
  • 11. Re: where to find info on function based indexes
    User286067 Journeyer
    Currently Being Moderated
    Justin Cave wrote:
    COLUMN_EXPRESSION is a LONG. What tool are you using to display the data? One possibility is that whatever tool you're using doesn't support the LONG data type (LONG has been depricated for quite some time).

    Justin
    While I agree long and long raw is pita to use, Oracle still uses LONG in data dictionary (11202 and 11203).
    select owner, count(*) cnt from dba_tab_columns where data_type like 'LONG%' and (owner,table_name) in (select owner, table_name from dba_tables) group by rollup(owner)
    /
    OWNER                                 CNT
    ------------------------------ ----------
    EXFSYS                                  1
    OUTLN                                   1
    SYS                                    40
    SYSTEM                                 13
                                           55
  • 12. Re: where to find info on function based indexes
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    I had thought that this may be due to virtual columns somehow but i can;t get it to produce the symptoms you are seeing. I think the dbms_metadata is the best way to go - it would also be interesting though to see a descrption of the table and if C3 is actually a column in it and not some function as the previous post described.

    Cheers,
    Harry
  • 13. Re: where to find info on function based indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    958713 wrote:
    select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME 
    from dba_indexes where index_name = 'TQR1_C3';
    
    OWNER      INDEX_NAME INDEX_TYPE                  TABLE_OWNER  TABLE_NAME
    ---------- ---------- --------------------------- ------------ ----------
    TQ         TQR1_C3    FUNCTION-BASED NORMAL       TQ           TQR1
    Now, let's show the selection from dba_ind_expressions...
    INDEX_OWNER  INDEX_NAME TABLE_OWNER  TABLE_NAME COLUMN_EXPRESSI COLUMN_POSITION
    ------------ ---------- ------------ ---------- --------------- ---------------
    TQ           TQR1_C3    TQ           TQR1       "C3"                          1
    If you have a descending column in an index the index is reported "function-based normal", but the expression simply echoes the column name without mentioning the descending bit. So one possibility is that your index is (c3 descending).

    Regards
    Jonathan Lewis
  • 14. Re: where to find info on function based indexes
    961716 Newbie
    Currently Being Moderated
    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
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points