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
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
(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