Home » SQL & PL/SQL » SQL & PL/SQL » function based index
function based index [message #228318] Mon, 02 April 2007 09:34 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
hi, I have the following select, which is taking longer then expected to execute:


SELECT fees_date, NVL (fees_amount, 0) amount
FROM fees_tbl
WHERE fees_year = '2007'
AND fees_numb IN (
SELECT fees_num
FROM fees_info
WHERE fees_year = '2007')

I am on Oracle 9i, Optimezer set to CHOOSE. So, I decide to create a function based index on fees_amount by doing the following:

1.
ALTER SYSTEM SET QUERY_REWRITE_ENABLED = TRUE;

2.
Creating index as follows:

create index fees_amt_idx on ssrfees_tbl
NVL (fees_amount, 0);

analyze index fees_amt_idx compute statistics;

3.
Modifying my select as follows:

SELECT /*+ index(pws_fees_amt_idx) */
fees_date, NVL (fees_amount, 0) amount
FROM fees_tbl
WHERE fees_year = '2007'
AND fees_numb IN (
SELECT fees_num
FROM fees_info
WHERE fees_year = '2007');

However, when I ran the explain plan, it is obvious that the index is not being used. So it does not good in terms of speeding up this query. Am I doing something wrong here? How do I force Oracle to use this index? Maybe I am missing other settings? Thank you!!!!!!!
Re: function based index [message #228319 is a reply to message #228318] Mon, 02 April 2007 09:40 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Why exactly do you think this would help ? For it to be of any use, NVL (fees_amount, 0) needs to be one of the where conditions on the query, not one of the values you are selecting.
Re: function based index [message #228320 is a reply to message #228319] Mon, 02 April 2007 09:43 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Oh, u right! No wonder Oracle is not using this index.. thanx
Re: function based index [message #228321 is a reply to message #228318] Mon, 02 April 2007 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Maybe I am missing other settings?
With Oracle string variable are enclosed by single quote marks!
You should ALWAYS use TO_DATE to convert strings into DATE datatypes.
Implicit data conversion is precluding use of index.
Re: function based index [message #228322 is a reply to message #228321] Mon, 02 April 2007 09:47 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
The year is stored as a VARCHAR in the database...
Re: function based index [message #228332 is a reply to message #228321] Mon, 02 April 2007 10:23 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Ok, I think I am confused. When Oracle evaluates indexes, does it look at what is selected or does it look at the where clause?

Re: function based index [message #228334 is a reply to message #228318] Mon, 02 April 2007 10:26 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Think about it this way. If I asked you to find a treasure chest using a map where x marks the spot, which piece of information do you think would be more useful in helping you to find it ? The fact that you are looking for a treasure chest or the x on the map that tells you where it is ?
Re: function based index [message #228337 is a reply to message #228334] Mon, 02 April 2007 10:32 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
where it is Smile so this means that creating indexes on SELECTED columns makes no sense...
Re: function based index [message #228340 is a reply to message #228318] Mon, 02 April 2007 10:59 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
sorry to bother all of you Smile but i just read this and it looks like adding indexes on SELECTED columns does help in some cases because if oracle sees an index on the columns that are needed by the query, it will not hit the table...

http://orafaq.com/node/1814

am i not understanding this correctly? i am just trying to get it clear in my head...thank you!
Re: function based index [message #228341 is a reply to message #228318] Mon, 02 April 2007 11:06 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
That is correct but you would need an index that covered all the columns from fees_tbl accessed by your query. So you would have to be a concatenated index on fees_year, fees_numb, fees_date and fees_amount. The NVL on fees_amount would probably not help much in this situation, as Oracle has already found the value of fees_amount from the index and it's not much of an overhead to apply the NVL afterwards.
Re: function based index [message #228343 is a reply to message #228341] Mon, 02 April 2007 11:11 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
so create indexes not just on selected columns but ALL involved in the query?
Re: function based index [message #228349 is a reply to message #228318] Mon, 02 April 2007 11:37 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
It's a possibility but the index would take up more space and might not work as well if you need to add other columns to it in the future. For your query, I would start by putting indexes on (fees_year, fees_numb) on both fees_tbl and fees_info. You could also rewrite it using where exists to make the index usage more clear-cut:

SELECT fees_date, NVL (fees_amount, 0) amount
FROM fees_tbl t
WHERE fees_year = '2007'
AND EXISTS
(SELECT 1
FROM fees_info i
WHERE i.fees_year = t.fees_year
and i.fees_num = t.fees_numb)
Re: function based index [message #228353 is a reply to message #228349] Mon, 02 April 2007 12:07 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
thank you!
Re: function based index [message #228357 is a reply to message #228343] Mon, 02 April 2007 12:38 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
lotusdeva wrote on Mon, 02 April 2007 12:11
so create indexes not just on selected columns but ALL involved in the query?


Ouch! No way. Creating an index and every column in the SELECT will have devastating results on INSERTs, UPDATEs and DELETEs.
Previous Topic: Connect by Without Prior
Next Topic: Help Required in Procedure
Goto Forum:
  


Current Time: Thu Dec 08 20:01:49 CST 2016

Total time taken to generate the page: 0.08162 seconds