Re: SQL query

Gerard H. Pille wrote:

> Do not use functions in your where clause, Oracle is terrible at this,
> just check your cpu when this query is running. Store the values in a
> context variable and retrieve it with sys_context, performance will be
> up to par.

I agree and I'll go one step further ... what is the value in SELECTing SYSDATE at all?

Build a package header with a variable name current_date or whatever. Set its value in the package with a call using an AFTER LOGON trigger. Then just use the variable from then on. For example:

CREATE OR REPLACE PACKAGE tp IS
  current_date DATE;
  PROCEDURE set_current_date;
END tp;
/

CREATE OR REPLACE PACKAGE BODY tp IS

PROCEDURE set_current_date IS
BEGIN
   current_date := TRUNC(SYSDATE);
END set_current_date;

END tp;
/

CREATE TABLE t (
testcol DATE);

• executed at logon using an AFTER LOGON trigger exec tp.set_current_date;

DECLARE
  x DATE := tp.current_date;
BEGIN
   INSERT INTO t VALUES (x);
END;
/

SELECT * FROM t;

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)