Precedence of function over table(s) in SQL

Hi All,

I have a query like -

with temp1 as (select ae_check_stale(22, 'MAIN_ITEM_MASTERS') col from xdual)
select b.* from

   TEMP_ITEM_MASTERS_1154 a,
   MAIN_ITEM_MASTERS b ,
   temp1 d
where
a.object_id = b.ITEM_ID
and d.col =1 ;

Where :

MAIN_ITEM_MASTERS is the main table.

TEMP_ITEM_MASTERS is the "temp" cached table which stores the primary keys from MAIN_ITEM_MASTERS based on some business rules.

AE_CHECK_STALE is a function (contains AUTONOMOUS TRANSACTION) - which might or might not *update* records in TEMP_ITEM_MASTERS. It always returns 1.

My objective is to write a query so that things happen in following sequence -

(1) Execute a function, which might update a table taking part in the
query (AUTONOMOUS_TRANSACTION)

(2) Then only execute a table join (or, the main part of the query)

Currently, with the query above, (1) and (2) happen at the same time. i.e., the join is performed with the un-updated data from TEMP_IETM_MASTERS, though the records are indeed updated in TEMP_ITEM_MASTERS. We see updates from the last time when next time the query is run.

So, the question is -- is there a way to make sure the function is run consistently the first thing in the query and only after it's run *then* we join the MAIN_ and TEMP_ tables? We need to use the TEMP_ table in plan only after the function is run.

Otherwise, the first run of the query while refreshing would always bring wrong data.

I tried with an ORDERED, ORDERED_PREDICATE (after WHERE clause - so that the function(x) = 1 is evaluated the first thing) and NO_MERGE hints. But each to no avail.

• my query with ORDERED hint looked like --

with temp1 as (select ae_check_stale(22, 'MAIN_ITEM_MASTERS') col from xdual)
select /*+ ORDERED */ b.* from
TEMP_ITEM_MASTERS_1154 a,
MAIN_ITEM_MASTERS b ,
temp1 d
where
a.object_id = b.ITEM_ID
and d.col =1 ;

But even then, it shows the un-updated data from TEMP_ITEM_MASTERS even when the function refreshes the table in the same query.

Execution plan looks like --

Execution Plan

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1693 Card=699 Byte
          s=76191)

   1    0   FILTER
   2    1     MERGE JOIN (CARTESIAN) (Cost=1693 Card=699 Bytes=76191)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'MAIN_ITEM_MASTERS'


(TA

          BLE) (Cost=2 Card=1 Bytes=96)

   4    3         NESTED LOOPS (Cost=1428 Card=699 Bytes=76191)
   5    4           TABLE ACCESS (FULL) OF 'TEMP_ITEM_MASTERS_1154'


(TA

          BLE) (Cost=27 Card=699 Bytes=9087)

   6    4           INDEX (RANGE SCAN) OF 'MAIN_ITEM_MASTERS_PK'


(INDEX)

           (Cost=1 Card=744743)

   7    2       BUFFER (SORT) (Cost=1691 Card=1)
   8    7         INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_35101' (INDEX
           (UNIQUE)) (Cost=0 Card=1)

We have XDUAL table -- exactly like DUAL, except it's INDEX_ORGANIZED and has
the index 'SYS_IOT_TOP_35101' on it.

Query with ORDERED_PREDICATE looked like -

select * from
(select b.* from

 MAIN_ITEM_MASTERS b,
 TEMP_ITEM_MASTERS_1154 a
where /*+ ORDERED_PREDICATES */
a.object_id = b.ITEM_ID)
where /*+ ORDERED_PREDICATES */ APP.ae_check_stale(22, 'MAIN_ITEM_MASTERS')= 1; But every time - the function(temp_item_masters) and data fetch from temp_item_masters happen at the same time --- or at least, function(temp_item_masters) is *not* happening the *first* thing in the access path.

Is there a way to achieve this?

Thanks,

~nM Received on Thu Apr 08 2004 - 00:58:24 CDT