bitmap index query is slow in DW

[mike9]

Tamil,


I didn't realy understand what you meant but based on the output of the explain plan it seems to me that the column QUOTE_CREATED_DATE is a date column and I guess that the generated query uses, based on the session nls settings, the correct date format.


"quote_created_date between '01-OCT-2005' and '31-OCT-2005' "

becomes

5 - access("A"."QUOTE_CREATED_DATE">=TO_DATE('2005-10-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "A"."QUOTE_CREATED_DATE"<=TO_DATE('2005-10-31 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))

[pranavgovind]

All,

The query i gave in this fourms is developed by me. Not cognos. cognos is generating query with timestamp..

Here is query generated by cognos. Here quote_created_date is date column and the value is timestamp data type. Due to different data type, the index is not referring... We already created the ticket to cognos. We are working with cogos now. I will certainly post on this forums once i get a feed back from COGNOS.


SELECT count(*)
FROM quote a, enterprise_sales_det_fact b, orders c
WHERE quote_created_date between TIMESTAMP '2005-10-01 00:00:00.000' and TIMESTAMP '2005-10-31 23:59:59.999'
and a.quote_dim_sid = b.quote_dim_sid
AND b.order_dim_sid = c.order_dim_sid

Tamil,


I didn't realy understand what you meant but based on the output of the explain plan it seems to me that the column QUOTE_CREATED_DATE is a date column and I guess that the generated query uses, based on the session nls settings, the correct date format.


"quote_created_date between '01-OCT-2005' and '31-OCT-2005' "

becomes

5 - access("A"."QUOTE_CREATED_DATE">=TO_DATE('2005-10-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "A"."QUOTE_CREATED_DATE"<=TO_DATE('2005-10-31 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))

[pranavgovind]

Mike, Thanks for the information. I will look at range partitions to improve the performance. I will let you know if i need any help on implementing range partitions. thank you very much!!!

You need FKs to enable star transformation. Also it provides some important information to the Oracle optimizer. So yes I recommand you to keep the FKs.

But, as I already said, have also a look at range partitioning. It is an easy (or even the easiest) way to improve the performance of queries in a DWH.