Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Saibabu.

Asked: May 22, 2019 - 6:46 am UTC

Last updated: May 29, 2019 - 6:28 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

Hi,

We have two db schemas(assume schemaA and schemaB) and created materialized views in schemaB for all tables in schemaA and running cognos reports on schemaB but have been facing some performance issues while generating cognos reports.

Q1) We have been planned to create BITMAP indexes on tables, Do we have any disadvantages to having more bitmap indexes on single table ?
Q2) Table indexes are not copied into materialized views, Can we create indexes on mv's ?

Thanks in advance.

Regards,
Sai

and Connor said...

Q1) We have been planned to create BITMAP indexes on tables, Do we have any disadvantages to having more bitmap indexes on single table ?

They will impact DML (ins,upd,del) performance. A common technique when doing large loads is to

- set the bitmap index unusable
- do the load
- rebuild the index

Q2) Table indexes are not copied into materialized views, Can we create indexes on mv's ?

- yes you can, but make sure they are not *unique* indexes. We don't guarantee uniqueness of data during the refresh period.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Saibabu Mondi, May 28, 2019 - 9:09 am UTC

Awesome inputs, Thanks for your help !!!
Connor McDonald
May 29, 2019 - 6:28 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.