Timeline for How to manage constants within an Oracle Database
Current License: CC BY-SA 3.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 1, 2013 at 23:34 | vote | accept | Ewanw | ||
| Jun 29, 2013 at 16:17 | answer | added | David Aldridge | timeline score: 3 | |
| May 30, 2013 at 23:28 | comment | added | Ewanw | Yasir, According the explain plan they are not. I will do some performance testing and rebuild statistics and confirm Ste, That was covered in the question below, I'm not so concerned with why they aren't as I am with having reasonable performance Colin, I did try adding deterministic to my functions, it didn't appear to change anything. I will re-test and confirm | |
| May 30, 2013 at 10:03 | answer | added | Vincent Malgrat | timeline score: 1 | |
| May 30, 2013 at 9:04 | comment | added | Colin 't Hart |
You need to specify deterministic on your function definition otherwise Oracle has no way of knowing that your function cannot return different results for each invocation -- eg if the body of the function depends on some other objects whose value could change. See docs.oracle.com/cd/E11882_01/appdev.112/e10472/…
|
|
| May 30, 2013 at 8:00 | comment | added | Ste | @Ewanw do you want to understand why index is not being used? | |
| May 30, 2013 at 8:00 | answer | added | MTIhai | timeline score: 0 | |
| May 30, 2013 at 7:24 | comment | added | Yasir Arsanukayev | Are you sure the indexes are not used? Maybe your table is not sufficiently large and optimizers decides to use full table scan over indexes? IIRC this is the case when the table is small enough, and full table scan time is comparable to that of index scan. I can see TABLE ACCESS BY INDEX ROWID and INDEX RANGE SCAN in my explain plan even though there's only a few records in the table, and my query does contain a function call as in your query. | |
| May 30, 2013 at 5:36 | history | asked | Ewanw | CC BY-SA 3.0 |