Precedence of function over table(s) in SQL

Giganews Newsgroups
Subject: Precedence of function over table(s) in SQL
Posted by:  Nilendu (nilen…@nilendu.com)
Date: 7 Apr 2004

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

Replies