BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
1 REPLY 1
deleted_user
Not applicable
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 1694 views
  • 0 likes
  • 1 in conversation