Index on a column

[tupac_rd]

Hello Gurus,

I have a question about how the indexing works in FM. We have a date dimension, with the index on the primary key of the table, a column called Date Key which always has data as for eg. 20100607, and it is a numeric. So if we filter on this column, the queries are faster because of the index. Now, we have a calculation based on the indexed column called Year Month text - to_char ( substr ( [Import View].[Date Dim].[Date Key] ,1,6)). So, if we filter on this new calculated column, will the index still be used, since the calculated column is using the indexed column.

Thanks
2pac

[blom0344]

Actually this has little to do with Cognos itself. The database optimizer will determine acces paths and the use of indices. Which leads to the question which database you are using..

The best way to find out is to generate explain plans for the queries involving the primary key and the query involving the derived column. A friendly DBA comes in handy , though on SQL Server things are really easy.

A 2nd approach is to use a special testtool like:
http://www.datamanipulation.net/SQLQueryStress/

which will give you some insight into the performance of queries. The output itself may not tell much , but it does allow you to compare results. If the query involving the derived column is very costly, this will show..

[tupac_rd]

We are using Oracle. I will try out your suggestions. Meanwhile if you have any ideas or other suggestions specifically for Oracle please let me know.

[blom0344]

AFAIK the latest  Oracle versions allows you to index a pseudo column (calculated column) which may be better yet..

In your example you:

1. Change datatype through the cast from numeric to char
2. Change an object with a unique key/index to a non-unique one (daily grain is changed to monthly)

My bet would be that this too much for the optimizer and you will lose the index access 

[tupac_rd]

Thanks Blom, I am trying to get this column added to the database and get an index created .......