| how to use index on function based value [message #225295] |
Mon, 19 March 2007 10:37  |
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 #225308 is a reply to message #225295] |
Mon, 19 March 2007 11:46  |
scottwmackey
Messages: 515
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 |
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:
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 |
joy_division
Messages: 4963
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 #225608 is a reply to message #225587] |
Tue, 20 March 2007 08:49 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
