| Does trunc slow down performance [message #371473] |
Wed, 25 October 2000 18:42  |
CC
Messages: 2
Registered: October 2000
|
Junior Member |
|
|
If I use trunc in the where clause of a date column does it slow down the query (assuming that the column is an indexed column).
e.g.
select name
from emp
where trunc(hiredate) = '10-OCT-00';
|
|
|
|
| Re: Does trunc slow down performance [message #371480 is a reply to message #371473] |
Thu, 26 October 2000 12:59  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
|
No, the trunc function is one of the few functions which can still make use of an index on that column. to_char, || etc all knock out the index (unless you are using a function based index like "CREATE INDEX Idx_1 ON Emp(Sal*1.1);" which is a new 8i feature)
|
|
|
|
|
|
| Re: Does trunc slow down performance [message #371486 is a reply to message #371473] |
Fri, 27 October 2000 15:01 |
Hari
Messages: 59
Registered: August 2000
|
Member |
|
|
select name
from emp
where trunc(hiredate) = '10-OCT-00';
use this WAY
select name from emp
where hiredate between trunc('10-0ct-00') and trunc('10-oct-00') + 0.99999;
so that the index is used and u get all therecords for that day ... hope this helps..
|
|
|
|
| Re: Does trunc slow down performance [message #371487 is a reply to message #371473] |
Fri, 27 October 2000 15:02 |
Hari
Messages: 59
Registered: August 2000
|
Member |
|
|
select name
from emp
where trunc(hiredate) = '10-OCT-00';
use this WAY
select name from emp
where hiredate between trunc('10-oct-00') and trunc('10-oct-00') + 0.99999;
so that the index is used and u get all therecords for that day ... hope this helps..
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
