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.
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'
1 of 1 people found this helpful
select * from sys.keys
Thanks!! i see the index name in this table. The 'isIndexed' column shows 'false' but all entries in this table show false.
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 ...".