on 11-06-2009 3:31 AM
Hi Guys,
I was going through some stuff to improve the performance for our Queries.Could anybody please give in details about the indexing mechanism for infocubes.Can we change the index type from Bit map to B-tree in fact table.How can I get the details about indexes which are already created in system like index type, whether primary or secondary and also can I look at the index structure where system stores it?
Thanks for your help.
Rgds
Amit
Edited by: AJ0099 on Nov 6, 2009 4:31 AM
Hi,
See the excellent pdf on Performance...
Thanks
Reddy
Edited by: Surendra Reddy on Nov 6, 2009 6:36 AM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi
The advantages of using bitmap indexes are greatest for low cardinality columns: that is, columns in which the number of distinct values is small compared to the number of rows in the table. If the number of distinct values of a column is less than 1% of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index. Even columns with a lower number of repetitions and thus higher cardinality can be candidates if they tend to be involved in complex conditions in the WHERE clauses of queries.
For example, on a table with 1 million rows, a column with 10,000 distinct values is a candidate for a bitmap index. A bitmap index on this column can out-perform a B-tree index, particularly when this column is often queried in conjunction with other columns.
B-tree indexes are most effective for high-cardinality data: that is, data with many possible values, such as CUSTOMER_NAME or PHONE_NUMBER. In some situations, a B-tree index can be larger than the indexed data. Used appropriately, bitmap indexes can be significantly smaller than a corresponding B-tree index.
In ad hoc queries and similar situations, bitmap indexes can dramatically improve query performance. AND and OR conditions in the WHERE clause of a query can be quickly resolved by performing the corresponding Boolean operations directly on the bitmaps before converting the resulting bitmap to rowids. If the resulting number of rows is small, the query can be answered very quickly without resorting to a full table scan of the table.
go through this link
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm#5381
BI query checks the available resources in the following order;
1) OLAP cache
2) BI Accelerator indexes
3) Cube aggregates
4) Cube indexes
As long as the BIA indexes are green for this cube, having the cube indexes deleted won't impact the user experience
Santosh
Hi,
Please check the Blog on Indexing, I hope which will help you about Indexing..
/people/debanshu.mukherjee2/blog/2009/05/22/sap-bw-indexing-scheme-oracle
See this PDF, it is having the details of Bitmap and B-tree indexing.
http://www.erada.com/articles/InfoCube_Modeling_Dimension_Design_Erada_BWInfoAlert.pdf
http://www.oracle.com/technology/pub/articles/sharma_indexes.html
Thanks
Reddy
| User | Count |
|---|---|
| 3 | |
| 3 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.