Hello experts. Using oracle 9. Can I call a function based index on a view?
If not I need some optimization help. I have a select query inwhich I am using a UNION ALL. Now my issue becomes this. In my where clause I say the following.
DateField >= Function((trunc(sysdate)-1)) and DateField <= Function(trunc(sysdate)-(1/24/60/60));
If I look at the explain path it is doing full table scans and the cost for the select is 1041990, the cardinality is 155844 and the Bytes are 559120197.
What the function is doing is taking an Date value and converting it to an epoch value like (1189526400). It is doing this because my dates in the database are stored as epoch time. My query always need to take the date from the prior day at 12:00:00 AM to end of prior day 11:59:59 PM and this has to happen dynamically rather then me manually entering start and end date. This requirement cannot change. Your help is appreciated. Thanks.
I suspect the reason its not using the index is because it doesnt know what values are being returned from the function at the time it does the execution plan thus it may assume your selecting the majority of rows.
If you replace the function call with the actual values you will probably find it does use the index. Alternatively putting in a index hint should also do the job. All this assumes your stats are upto date of course.
Maybe I missed something. I do have a function that converts timestamp to an oracle date. However, the function is being used on a view(the datetime field in the view). Can I use a function based index on a view?
How would I apply your logic
create index my_table_i1 on my_table(function(datefield));
Does my_table get replaced by the view name. I tried that and it says "ORA-01702: a view is not appropriate here".
Thanks again. I just found out that I will in no way have access to be able to create any sort of index. However, at least now I know where to begin and if I need to ask the administrators to create this sort of index for me they can. I like to thank everyone for there help.