OraFAQ Forum: SQL & PL/SQL » function based index on date column

Home » SQL & PL/SQL » SQL & PL/SQL » function based index on date column (11g, 11.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

function based index on date column [message #661180]

Fri, 10 March 2017 04:53

annu-agi
Messages: 239
Registered: July 2005
Location: Karachi

Senior Member

DEar experts

see the data below

Code: [Select all] [Show/ hide]

     code       trans_Date    
	1	15-Jun-14 11:35:13 AM
	2	16-Jun-14 11:35:13 AM
	3	17-Jun-14 11:35:13 AM
	4	18-Jun-14 11:35:13 AM
	5	19-Jun-14 11:35:13 AM

i need to query without bothering with time stamps , and query is like
[code]
select * from check_date where to_Date(trans_Date)='15-jun-2014' when i create index on this column

Code: [Select all] [Show/ hide]

create Index dc_1 On date_check to_Date(trans_Date);

it is by default created like
TO_DATE(TO_CHAR("TRANS_DATE",'DD-MON-RR'),'dd-mon-yyyy');

Code: [Select all] [Show/ hide]

Recommendation: 
~~~~~~~~~~~~~~ 
Avoid function on the predicate TRANS_DATE or create a function based index on the column TRANS_DATE.

as follows the above recommendations

Now the question is

1- what is the best way to query such data eliminating time stamp in date field, with <>=> and or < operators
2- how can i index date column with to_date(trans_Date) function
3- what is the syntax to obtain data with dc_1 index without giving time segments

regards

anwer

Report message to a moderator

Re: function based index on date column [message #661182 is a reply to message #661180]

Fri, 10 March 2017 05:13

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Well you're using the wrong function for the job, that's what trunc is for.
To_date doesn't accept a date parameter, so oracle has to implicitly to_char the date first - and it's obviously making implicit conversion explicit in the FBI.
You should never use to_date for this purpose because if the user changes their sessions nls_date_format you can end up getting the wrong result or an error.
Trunc is explicitly designed for this task and changing nls_date_format won't affect what it does.

The way to do it without using a function on the column is:

Code: [Select all] [Show/ hide]

date_col >= trunc(date_parameter)
AND date_col < trunc(date_parameter) + 1

Report message to a moderator

Re: function based index on date column [message #661185 is a reply to message #661182]

Fri, 10 March 2017 05:48

annu-agi
Messages: 239
Registered: July 2005
Location: Karachi

Senior Member

Thanks for the reply

what about the SECOND part of my question which is regarding creating index , i have read several forums that recommend MUST AVOID TRUNC ON DATE COLUMNS ... It might effect performance, also trunc function avoid all indexes and function based indexes. I have checked and found query performs full scan rather using any indexes or perform range index.

Please put some light on it

Report message to a moderator

Re: function based index on date column [message #661197 is a reply to message #661185]

Fri, 10 March 2017 06:52

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

trunc is no different to to_date in that regard.
If you wrap a column with a function call (any function, what so ever) then oracle will not use that column for index look-ups unless you have a function-based index that exactly matches the function call in the query.
So if you have:
trunc(date_col) = date_paramater
then oracle will not use a normal index on date_col, but will happily use a function based index on trunc(date_col).

If you use the code format from my previous post then this problem goes away as no function is being applied to the date column.

Is the advice to use trunc what caused you to try to_date instead? Cause if it is you missed the point, any function causes that problem, it's just that trunc is the correct function for the task.

Report message to a moderator

Re: function based index on date column [message #661199 is a reply to message #661197]

Fri, 10 March 2017 07:05

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

The specific reason why your to_date index was behaving the way it was is because the optional 2nd parameter to to_Date is the current sessions nls_date_format.
When oracle creates a function based index it computes the result of the function and stores that in the index. But in this case the result is session specific, so it needs to actually add the missing parameter using the settings of the session that created the index.

For most oracle functions with optional parameters the defaults for those parameters are hard-coded, but date and number conversion functions (to_char, to_number, to_date) are obvious exceptions.

Report message to a moderator

Re: function based index on date column [message #661214 is a reply to message #661185]

Fri, 10 March 2017 10:54

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

i have read several forums that recommend MUST AVOID TRUNC ON DATE COLUMNS ...

Which ones? Post links?

Report message to a moderator

Re: function based index on date column [message #661218 is a reply to message #661214]

Fri, 10 March 2017 23:46

annu-agi
Messages: 239
Registered: July 2005
Location: Karachi

Senior Member

for "i have read several forums that recommend MUST AVOID TRUNC ON DATE COLUMNS ..."

Code: [Select all] [Show/ hide]

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9531667400346397777
https://hoopercharles.wordpress.com/2010/03/08/impact-of-the-trunc-function-on-an-indexed-date-column/
http://www.dba-oracle.com/oracle_tips_index_scan_fbi_sql.htm

Report message to a moderator

Re: function based index on date column [message #661219 is a reply to message #661218]

Sat, 11 March 2017 00:24

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

First 2 links are what cookiemonster also said and there is nothing new in this, this is what we have continuously said during the last 30 years and they do not say you can't use it in a FBI.
Third link, all I have to say is this site b......t, NEVER uses it unless you're searching for a statement syntax or you know the page is a copy of another site.

Report message to a moderator

Re: function based index on date column [message #661275 is a reply to message #661219]

Mon, 13 March 2017 20:50

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Create index my_table_f1 on my_table(trunc(my_date_col)) ;

This is a function based index so if your code uses

And trunc(my_date_col) = trunc(sysdate)

It Will use the index. The trick is that the function index matches the function that your using in your where clause

[Updated on: Mon, 13 March 2017 20:51]

Report message to a moderator

Re: function based index on date column [message #661433 is a reply to message #661275]

Fri, 17 March 2017 04:48

annu-agi
Messages: 239
Registered: July 2005
Location: Karachi

Senior Member

it works

Thanks all of you

Report message to a moderator

Previous Topic:	oracle transaction
Next Topic:	query dead slow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 03 23:56:31 CDT 2025