-
1. Re: How can I verify the index for an internalized materialized view?
rareddy Oct 26, 2012 11:26 AM (in response to mikebailey)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..
-
2. Re: How can I verify the index for an internalized materialized view?
mikebailey Oct 26, 2012 11:52 AM (in response to rareddy)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 183This is from a select * from sys.tables where name = 'demoLparDataCached'
-
3. Re: How can I verify the index for an internalized materialized view?
rareddy Oct 26, 2012 1:00 PM (in response to mikebailey)1 of 1 people found this helpfultry
select * from sys.keys
-
4. Re: How can I verify the index for an internalized materialized view?
mikebailey Oct 26, 2012 1:18 PM (in response to rareddy)Thanks!! i see the index name in this table. The 'isIndexed' column shows 'false' but all entries in this table show false.
-
5. Re: How can I verify the index for an internalized materialized view?
shawkins Oct 29, 2012 12:13 PM (in response to mikebailey)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