Home » SQL & PL/SQL » SQL & PL/SQL » function based index on date column (11g, 11.2.0.3.0)
function based index on date column [message #661180] Fri, 10 March 2017 04:53 Go to next message
annu-agi
Messages: 228
Registered: July 2005
Location: Karachi
Senior Member

DEar experts


see the data below

     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
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');


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






Re: function based index on date column [message #661182 is a reply to message #661180] Fri, 10 March 2017 05:13 Go to previous messageGo to next message
cookiemonster
Messages: 13264
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:
date_col >= trunc(date_parameter)
AND date_col < trunc(date_parameter) + 1
Re: function based index on date column [message #661185 is a reply to message #661182] Fri, 10 March 2017 05:48 Go to previous messageGo to next message
annu-agi
Messages: 228
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

Re: function based index on date column [message #661197 is a reply to message #661185] Fri, 10 March 2017 06:52 Go to previous messageGo to next message
cookiemonster
Messages: 13264
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.
Re: function based index on date column [message #661199 is a reply to message #661197] Fri, 10 March 2017 07:05 Go to previous messageGo to next message
cookiemonster
Messages: 13264
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.
Re: function based index on date column [message #661214 is a reply to message #661185] Fri, 10 March 2017 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65831
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
i have read several forums that recommend MUST AVOID TRUNC ON DATE COLUMNS ...
Which ones? Post links?

Re: function based index on date column [message #661218 is a reply to message #661214] Fri, 10 March 2017 23:46 Go to previous messageGo to next message
annu-agi
Messages: 228
Registered: July 2005
Location: Karachi
Senior Member

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

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
Re: function based index on date column [message #661219 is a reply to message #661218] Sat, 11 March 2017 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 65831
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: function based index on date column [message #661275 is a reply to message #661219] Mon, 13 March 2017 20:50 Go to previous messageGo to next message
Bill B
Messages: 1794
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 Go to previous message
annu-agi
Messages: 228
Registered: July 2005
Location: Karachi
Senior Member

it works

Thanks all of you
Previous Topic: oracle transaction
Next Topic: query dead slow
Goto Forum:
  


Current Time: Mon Oct 15 06:06:09 CDT 2018