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.

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.