Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: which columns are indexed

Re: which columns are indexed

From: Richard Scrivener <minotauris_at_hotmail.com>
Date: 2000/08/04
Message-ID: <398A874C.9FF1F009@hotmail.com>

If you haven't yet received an answer, here it is:

use the views all_ind_columns, dba_ind_columns or user_ind_columns. Here is some detail on the all_ind_columns view:

SQL> desc all_ind_columns

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 INDEX_OWNER                               NOT NULL VARCHAR2(30)
 INDEX_NAME                                NOT NULL VARCHAR2(30)
 TABLE_OWNER                               NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                                        VARCHAR2(4000)
 COLUMN_POSITION                           NOT NULL NUMBER
 COLUMN_LENGTH                             NOT NULL NUMBER
 DESCEND                                            VARCHAR2(4)


select *
from all_ind_columns
where index_name = 'EI_ATTRSTORE'

INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH DESCEND
----------- ---------- ----------- ------------ ----------- --------------- ------------- -------

ODS         EI_ATTRSTORE   ODS         DS_ATTRSTORE ENTRYID
1               22            ASC

so if you want to know which columns have been indexed in a table, simply execute the query:

select index_name, column_name
from all_ind_columns
where table_name = 'MYTABLE';

cheers

Richard Scrivener
Sydney
Australia

A D Hager wrote:

> Is there any way to determine which columns in a table have been
> indexed?
> Is there any way to determine what ann index indexes?
>
> Thanks,
>
> Aaron

--
Received on Fri Aug 04 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US