How can I verify the index for an internalized materialized view?

[mikebailey]

I am creating a materialized view and then adding an index for the primary keys. I cannot see any reference to the index.  How can i verify that the index part is working? The view works perfectly.

Sample code:

final Table demoLparDataCached = metadataFactory

    .addTable("demoLparDataCached");

  demoLparDataCached.setMaterialized(true);

  demoLparDataCached.setVirtual(true);

  demoLparDataCached.setTableType(org.teiid.metadata.Table.Type.View);

  demoLparDataCached.setSelectTransformation("/* cache(ttl:30000) */ SELECT * FROM demo.demo_lpar_health_state");

metadataFactory.addColumn("TIMEEVENT",
    TypeFacility.getDataTypeNameFromSQLType(Types.TIMESTAMP),
    demoLparDataCached);

.... more columns

List<String> indx = new ArrayList<String>();
  indx.add("SYSID");
  indx.add("SYSPLEX");
 
  metadataFactory.addIndex("demoLparDataCached_INDEX", false, indx ,  demoLparDataCached);

[rareddy]

You can query tables in SYS model, and it will show metadata that the index is created on that view. The actual index of data is created in the buffer manager, which you can not see. You can probably see the performance difference between indexed on non-indexed in queries with lot of data.

Ramesh..

[mikebailey]

Thanks for the reply Ramesh.  Looking at the SYS model tables i do not see how to determine if there are indexes. Here is what I get:

VDBName, SchemaName,  Name,    Type, NameInSource, IsPhysical, SupportsUpdates, UID,     Cardinality, Description,  IsSystem, IsMaterialized, OID
Chorus,  demo demoLparDataCached View <null>  false false  tid:36e5b356535e-e3207cc4-00000053 -1 <null>  false  true 183

This is from a select * from sys.tables where name = 'demoLparDataCached'

[rareddy]

try

select * from sys.keys

[mikebailey]

Thanks!! i see the index name in this table. The 'isIndexed' column shows 'false' but all entries in this table show false.

[shawkins]

The internal materialization tables and associated indexes for materialized views are not yet exposed.  You are also correct that the isIndexed property that is shown will always report false.  This is legacy behavior and could be updated to report true for materialized views - as indexes/keys on the view will have a corresponding index.

> How can i verify that the index part is working?

Currently we do not pre-plan our internal table access as part of the overall query plan, so this is not displayed as part of the query plan.  At detail level logging you can see a log to the effect of "Choose index ...". 

Steve