Re: Materialized Views

Thanks for the testing!

On 09/24/2015 06:51 PM, Jean-Marc Guazzo wrote:
> Same test with Oracle Express 11g:
>
> create table login_test.test1 (id int primary key, contenu varchar(100));
> create materialized view login_test.test_vm as select * from login_test.tes
> t1;
>
> MSQRY32         192c-1b0c ENTER SQLTablesW
> HSTMT               0x00C6CBC0
> WCHAR *             0x00000000 <null pointer>
> SWORD                       -3
> WCHAR *             0x00000000 <null pointer>
> SWORD                       -3
> WCHAR *             0x00000000 <null pointer>
> SWORD                       -3
> WCHAR *             0x00C6DDE0 [      24] "'TABLE','VIEW','SYNONYM'"
> SWORD                       24

Hmm. I don't understand how to interpret this. If you create a
materialized view on Oracle, and then call SQLTables(), what object type
does it report the materialized view as? TABLE? Or VIEW? Or MATERIALIZED
VIEW?

> For DB2, I found this :
>
> *Valid table type identifiers can include: TABLE, VIEW, SYSTEM TABLE,
> ALIAS, SYNONYM, GLOBAL TEMPORARY TABLE, AUXILIARY TABLE, MATERIALIZED QUERY
> TABLE, or ACCEL-ONLY TABLE.*
> src
> https://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.odbc/src/tpc/db2z_fntables.dita

Ok, so they use MATERIALIZED QUERY TABLE for the equivalent of our
materialized views. What about foreign tables? Are they just TABLEs?

> On a more rethorical pov,what's the difference between a materialized view
> and a view for the end user behind ODBC ? Should there be one ?

Well, depends on what the application is using the information for. If
you just want to SELECT, there is no difference between a materialized
view, a foreign table, a regular table or a regular view. But other
operations are only applicable to certain kinds of object. For example,
you cannot REINDEX a regular view or a foreign table, but a materialized
view or a regular table you can.

- Heikki