Results 1 to 7 of 7

Thread: index

  1. #1
    Junior Member
    Join Date
    Feb 2009
    Answers
    11

    index

    hi
    iam created an index on a column in existing table .


    but i can not see that index .


    how too see the index in sql

    plz help me


  2. #2
    Contributing Member
    Join Date
    Jan 2009
    Answers
    31

    Re: index

    You can view index name by giving the query given below:-
    SQL>select index_name from user_indexes where table_name='TABLENAME';


  3. #3
    Contributing Member
    Join Date
    Dec 2008
    Answers
    76

    Re: index

    @Prasad,

    • Just do the explain plan of the SQL statements.
      (For that respective column, table).
    • ALL_INDEXES-Describes indexes on all the tables accessible to the current user.
    • DBA_INDEXES-Describes all indexes in the database.
    • USER_INDEXES-Describes the indexes owned by the current user.



  4. #4
    Contributing Member
    Join Date
    Dec 2008
    Answers
    76

    Re: index

    @Prasad,


    Just do the explain plan of the SQL statements.
    (For that respective column, table).

    Select * from ALL_INDEXES where TABLE_NAME='emp';

    ALL_INDEXES-Describes indexes on all the tables accessible to the current user.
    DBA_INDEXES-Describes all indexes in the database.
    USER_INDEXES-Describes the indexes owned by the current user.
    USER_IND_COLUMNS-Describes the columns of the indexes owned by the current user and columns of indexes on tables owned by the current user
    ALL_IND_COLUMNS-Describes the columns of indexes on all tables accessible to the current user.
    DBA_IND_COLUMNS-Describes the columns of indexes on all tables in the database.


    select index_name , table_name , column_name
    from user_ind_columns
    where table_name='emp';

    Last edited by ecearund; 04-06-2009 at 01:41 AM.

  5. #5
    Expert Member
    Join Date
    Nov 2008
    Answers
    300

    Re: index

    You can also try this SET option to display the explain plan for the query that you run:

    [code=oracle]

    SQL> SET AUTOTRACE ON EXPLAIN
    SQL>