Re: Use command line argument in script

WHERE trunc(date) < trunc(sysdate) - $days

trunc(date) is redundant and makes the query take longer.

It is redundant as the purpose is to delete entries by the day. Truncating the date will still be the same day and not be less than the truncated sysdate. And if it is less, it would have been less even without being truncated.

It makes it take longer because it negates the use of any index and the function itself takes time. (Unless there is a function-based index there, which, silly as it may be, would make this the correct way to write the statement.)

FWIW, "FROM" is optional in a DELETE statement.

Comment on Re: Use command line argument in script

Replies are listed 'Best First'.
Re^2: Use command line argument in script by homer4all (Acolyte) on Dec 19, 2014 at 17:24 UTC
Yes that makes sense, will remove trunc(date) and replace with date. 'date' contains datetime data so thought without trunc(date) it won't work. Basically trying to accept parameter days at runtime, use it in DELETE Delete all data from table which are older than -days parameter.	[reply]
Re^3: Use command line argument in script by chacham (Prior) on Dec 19, 2014 at 17:37 UTC
Excellent. Got to think about the where clauses sometimes. They're not always obvious from the get go. As for using dynamic SQL, that is a security risk. You should really uses a parametrized query. Variables on the end of a statement are particularly bad.	[reply]