Oracle table indexes

[deleted_user]

Hi:

Is there anyway i can see the indexes created on the Oracle table from EG.

Proc contents shows the indexes for SAS datasets but for oracle table its saying Zero( which is not true).

I know the way to find the indexes, but my Business people has no knowledge abt the way i do and also they have no user accounts( they access the oracle tables through a pri-assigned library).

proc sql;
connect to oracle (user=xxx password=xxx path=xxxx);
create table test as
select * from connection to oracle
(select index_name, column_name
from All_Ind_Columns);
disconnect from oracle;

please let me know the other alternative way

Message was edited by: Srini Message was edited by: Srini

[deleted_user]

I have had a similar issue with SQLServer tables as well, and it comes down to the way in which the indexes are created and stored in foreign database structures. It is not the same way as SAS creates and records indexes on its tables, so some or many SAS procedures will be unable to natively use these indexes. That is one reason why recourse to SQL Passthrough is often so effective in selection of data to SAS tables.

When replicating data from a foreign data source to a SAS source, I found it necessary to write a macro that would take each table in turn, copy its data, then query the key and index structure and apply that to the SAS copy of the table. Your solution may be to create a macro that performs the query you have already outlined, and surfaces the information to the user in whatever manner is most helpful.

Kind regards

David