topic Re: ODBC Connection Materialized vs Table in Library in SAS Data Management

ODBC Connection Materialized vs Table in Library

MB_Analyst — Thu, 12 Mar 2020 12:27:58 GMT

I am connecting to an Oracle DB via tan ODBC driver in SAS.

LIBNAME tables
ODBC
USER=USER
PASSWORD=*****
DSN="Name"
SCHEMA=SCEMA;

The Oracle database has some tables replicated as a materialized view to speed up processing for some users. When I connect with SAS does the library show a copy of the table, or the materialized view of the table? I would prefer not to use the materialized version as it updates less frequently than I need it.

Re: ODBC Connection Materialized vs Table in Library

SASKiwi — Thu, 12 Mar 2020 19:14:56 GMT

Is there a difference in the name of the table versus the view? If so both should show. What do you see if you run PROC CONTENTS on the library?

Re: ODBC Connection Materialized vs Table in Library

Patrick — Fri, 13 Mar 2020 05:53:22 GMT

Googling with keywords: oracle materialized view and table with same name

You cannot create a table and a materialized view of the same name. However, when you create a materialized view, Oracle automatically creates a table by the same name in the ... _objects data dictionary views. When you select from that, you are selecting from the materialized view.

The materialized view (which actually is a table loaded into memory) is the result of a SQL query. It will retrieve the data directly or indirectly from table(s). I guess you could execute a describe on the view to get to the table names (to which you might or might not have read access). If you've got the read access then you could use the query from the describe in your own code (not sure actually: You need eventually to use the DDL of the view to get to the query which loads the data).

But... looking into Oracle docu here if and how complicated it will get for you to query the underlying physical table(s) directly will depend on the view definition. And you'll likely also experience a big performance degradation if directly querying the underlying physical tables.