Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Angry Unanswered: FBI or what else can I do

    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.
    Code:
     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.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Do you have an index on DateField?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Apr 2006
    Posts
    140
    Hello beilstwh,

    Thanks for the reply. Yes I do.

  4. #4
    Join Date
    Sep 2007
    Posts
    2
    Is your INDEX on the date field TRUNC(DateField) or just on DateField?

  5. #5
    Join Date
    Apr 2006
    Posts
    140

    Red face

    Sorry Valkyr,

    I am not understanding. Could you explain that please?

    My index is on the datefield......

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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.

    Alan
    Last edited by AlanP; 09-17-07 at 13:32.

  7. #7
    Join Date
    Apr 2006
    Posts
    140
    AlanP,

    Yes your are correct in saying "If you replace the function call with the actual values you will probably find it does use the index." Well it speeds it up definitely:

    Sorry now what do you mean putting "an index hint should also do the job". How do I do this with my exisiting code? Your help is appreciated.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What I would do is create a function that would convert your timestamp to an oracle date and then create a function based index on that.

    create index my_table_i1 on my_table(function(datefield));

    Then your query would be simply

    where function(DateField) between trunc(sysdate)-1 and trunc(sysdate)-(1/24/60/60)

    and the function based index would very quickly find your rows.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Apr 2006
    Posts
    140

    Red face

    Beilstw,

    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
    Code:
    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".

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    No, you make the function based index on the underlaying table and the optimizer will use it when the view is used.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Apr 2006
    Posts
    140

    Red face

    Beilstw,

    O.k I see what your saying. Learn something new everyday

    I am still having an issue though. My function is taking an epoch value and converting it to a truedate with Newtime Adjustment. So when I issue the following

    Code:
    create index Date_Time_Resolved on F1234(Function_Convert(g5627356, 'MDT'));
    I get an error saying function_convert invalid identifier? I made sure the function is correct and working as I have used it in the past. Sorry for all the questions.

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    IS "G5627356" the name of the column holding the epoch value and if you login into the schema holding the f1234 table, and issue the command "desc function_convert", does it show the arguments?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  13. #13
    Join Date
    Apr 2006
    Posts
    140
    Beilstwh,

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •