Home » SQL & PL/SQL » SQL & PL/SQL » how to use index on function based value
how to use index on function based value [message #225295] Mon, 19 March 2007 10:37 Go to next message
dusoo
Messages: 41
Registered: March 2007
Member
Hi there, im having some problems with one of my queries where im trying to retrieve few lines from huge table. Problem is that,that explain plan is telling me that he's not using the index, but goin through the full table scan, that takes some time. Problem is probably caused by using my own function. Creating function based index does not solve this, since im not using

where my_function(column) = 'value'

but

where column = my_function('value')

And using index hint did not solve this as well, cos he was going through full scan index, that took the same as full table scan. Function is pretty simple - substring of a value = '0023' = '23'

thanks for any help !!

d.
Re: how to use index on function based value [message #225301 is a reply to message #225295] Mon, 19 March 2007 11:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The fact that the index is not used has nothing to do with your function. The function is not applied to the indexed column, but to a constant.
Did you analyze your table?
Is it actually better to use the index?
etc..
Re: how to use index on function based value [message #225308 is a reply to message #225295] Mon, 19 March 2007 11:46 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
First, do yourself and everybody else a favor, and just post the actual query. Second, my money is on "column" being a NUMBER type and your function returning a VARCHAR2. But that's just my guess since you haven't posted nearly enough information to make an educated guess.
Re: how to use index on function based value [message #225585 is a reply to message #225295] Tue, 20 March 2007 07:40 Go to previous messageGo to next message
dusoo
Messages: 41
Registered: March 2007
Member
Hi,
so im posting the query and the explain plan for u to see, plus the body of the function. Thanks!

query without function:
SELECT ID, dtime, usernumber, access, stat, act
FROM ma_general g
WHERE dtime > '18.03.2007'
AND usernumber = '123456789'

explain plan:
./fa/2257/0/

query with function:
SELECT ID, dtime, usernumber, access, stat, act
FROM ma_general g
WHERE dtime > '18.03.2007'
AND usernumber = a_view.formatnumber('123456789')

explain plan:
below PARTITION RANGE ITERATOR (exp plan image from above)
is
TABLE ACCESS FULL MA_GENERAL [Analyzed]
Blocks: 208868 Est. Rows 1 of 26 674 485 Cost 201

Function is simple =>
FUNCTION formatnumber(i_phnum VARCHAR2) RETURN VARCHAR2 IS
phNum VARCHAR2(50) := trunc(i_phnum);
BEGIN
CASE
WHEN phNum LIKE '9________' THEN RETURN '0'||phNum;
WHEN phNum LIKE '4219________' THEN RETURN '0'||substr(phNum, 4);
WHEN phNum LIKE '04219________' THEN RETURN '0'||substr(phNum, 5);
WHEN phNum LIKE '004219________' THEN RETURN '0'||substr(phNum, 6);
WHEN phNum LIKE '+4219________' THEN RETURN '0'||substr(phNum, 5);
ELSE RETURN phNum;
END CASE;
EXCEPTION
WHEN OTHERS THEN
RETURN phNum;
END;
Re: how to use index on function based value [message #225587 is a reply to message #225585] Tue, 20 March 2007 07:44 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
dusoo wrote on Tue, 20 March 2007 08:40
Hi,
so im posting the query and the explain plan for u to see, plus the body of the function. Thanks!

query without function:
SELECT ID, dtime, usernumber, access, stat, act
FROM ma_general g
WHERE dtime > '18.03.2007'
AND usernumber = '123456789'



Is dtime a DATE column? If so, this is a poorly formed query as you are comparing a STRING to a DATE. How many apples does it take to Tuberculosis?
Re: how to use index on function based value [message #225606 is a reply to message #225295] Tue, 20 March 2007 08:24 Go to previous messageGo to next message
dusoo
Messages: 41
Registered: March 2007
Member
okok,
but there is no problem about the date, but about the function, ive just quickly created the query to demonstrate what i ment.
If u have any ideas, write Smile
thanks
Re: how to use index on function based value [message #225608 is a reply to message #225587] Tue, 20 March 2007 08:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
DTIME must be a date column, otherwise that sort makes no sense ('20.02.2007' would be greater than '19.03.2007'

So @joy_division is right - you should be doing a to_date on the right hand side of that line:
WHERE dtime > to_date('18.03.2007','dd.mm.yyyy')


It looks like you've got a Bitmap index on usernumber - try dropping that and putting a normal index on usernumber and dtime
Re: how to use index on function based value [message #225618 is a reply to message #225295] Tue, 20 March 2007 09:46 Go to previous messageGo to next message
dusoo
Messages: 41
Registered: March 2007
Member
Hi there,

thanks @JRowbottom for your advice. After i've changed the index from bitmap to normal, it began to use it also with function. Not sure why in this case normal index is much better then bitmap one...
anyway thanks !
Re: how to use index on function based value [message #225640 is a reply to message #225618] Tue, 20 March 2007 12:24 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
When you try to tune a query, do not replace any variables with literals.
When you execute your query at runtime, I doubt if there will be a literal value '123456789'
It makes huge difference for the explain plan!
Previous Topic: Sending E mail to a Distribution List using UTL.SMTP
Next Topic: Backup before Update
Goto Forum:
  


Current Time: Tue Dec 06 04:41:15 CST 2016

Total time taken to generate the page: 0.09834 seconds