change / baypass sql

From: Ujang Jaenudin <ujang.jaenudin_at_gmail.com>
Date: Wed, 10 Oct 2007 05:39:35 +0700
Message-ID: <3edcb66e0710091539u5f393f5alae3376acfdffbf4b@mail.gmail.com>

all,

is there anyway to change or baypass sql statement in oracle? i tried using outline, but no luck.

CREATE OR REPLACE OUTLINE ORIGINALSQL for category us ON update emp2 set last_name='XCXX'
where last_name = :lastnm;

CREATE OR REPLACE OUTLINE HINTSQL for category us ON select sysdate
from dual;

UPDATE OUTLN.OL$HINTS
SET OL_NAME=DECODE(OL_NAME,'HINTSQL','ORIGINALSQL','ORIGINALSQL','HINTSQL') WHERE OL_NAME IN ('ORIGINALSQL','HINTSQL'); commit;

alter system set use_stored_outline=US;

alter system flush shared_pool;

update emp2 set last_name='XCXX'
where last_name = :lastnm;

Execution Plan

Plan hash value: 2492025900

|   0 | UPDATE STATEMENT   |      |     3 |    21 |     5   (0)| 00:00:01 |
|   1 |  UPDATE            | EMP2 |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP2 |     3 |    21 |     5   (0)| 00:00:01 |


---------------------------------------------------------------------------

Predicate Information (identified by operation id):

2 - filter("LAST_NAME"=:LASTNM)

Note

outline "ORIGINALSQL" used for this statement

but the row successfully updated.... :(

-- 
regards
ujang
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 09 2007 - 17:39:35 CDT