Mark Rittman triggered meto investigate this: If you are Filtering against a date column in OBIEE you will see in the log that a timestamp conversion is added to your query:
select distinct T2826.DAYTIME as c1
from
DAYTIME2 T2826
where ( T2826.DAYTIME = TIMESTAMP '1968-03-14 00:00:00' )
order by c1
This timestamp conversion implies that a index on the DATE column is skipped, resulting in a full tablescan. How can we force the usage of an index? Here is an alternative:
1: Create a FUNCTIONAL INDEX on your date column Fi:
create index dt3 on daytime2(cast ("DAYTIME" as timestamp));
2: Create a view on your table casting the date column as TIMESTAMP:
create view v_daytime2
as
(select cast(daytime as timestamp) as daytime from daytime2)
Map this view to your RPD and you will, check the explain plan, see that Oracle uses your index.
If you have any better / other solutions please let me know!
Till Next Time
7 comments:
Hi John,
I assume you are talking about a date field in a dimension. (Use of a dedicated time dimension would not create this issue).
An option could be to convert the date into a string with the format YYYY-MM-DD e.g. 2009-03-03. Then a conventional index can be used. Of course the user need some instruction about how to use the 'date' in the queries. e.g. between '2009-02' AND '2009-05' retrieves all 'dates' from 01.02. until 31.05.09. The con is however, that you cannot do date calculations on it.
Regards
Peter
I agree with Peter, convert the date, or better still use Julian date numbers which cna be used in calculating differences.
HI John (and Pete, Adrian)
These sound good. One other issue we had though, with this adding of a TIMESTAMP conversion into the SQL, was that it was stopping partition elimination happening at the database end, which considerably slowed down the queries. If we could just find a way of stopping OBIEE adding the unneccessary TIMESTAMP into the SQL in the first place, it'd save a lot of bother. If I can find a way, I'll report back on it,
regards
Mark
If you aren't concerned about time, just the date, you can set the column type in the physical layer to DATE instead of DATETIME. This will cause BI to filter using TO_DATE instead of TIMESTAMP. The explain plan will show that the Date index gets used.
I would strongly recommend this over converting to text.
@John
You are passing a date and the timestamp is being added.To avoid timestamp as said convert the date into a string or other way is example trunc(daytime) eliminates the timestamp internally and the performance will not hinder.
Kranthi.
OBIEE Date, Timestamp and ORACLE Indexes...and partitions: http://ananthsridharan.blogspot.com/2010/03/obiee-timestamp-issue.html
Dear Peter,
you mentiont in your comment to convert the date to string.
can you please tell me how to do that.
thank you very much
regards,
Lukas
Post a Comment