Oracle Performance and Engineering

Well, stress does so many things to you.

Early 2004, I was consulted about a particular design of role-based scoped data access in a web-based application (i.e., a complex role based data access where Virtual Private Database model was rejected as a design since the logic defining the rules was complex and slow enough to be executed for each single access!).

To make it simple, let's say if you are an employee of a design consultancy you can see all data belonging to your company, plus the data of the other company projects that've been assigned to you. An outsourcer, who outsourced a work to your company, though can login to the same application can see only his or her project out of thousands your company may be working upon. The access-rules (i.e., the SQL queries that define what data you can see / modify) were very complex because this web application was serving to multitude of suppliers-vendors-manufacturers-consultants-employees and more different "roles" - all accessing the SAME database!

However, the crux of the design I was consulted with (I was not invited for design!) was - every main table will have a temp table having the (scoped) primary_keys of the main table. Whenever a query is fired against the 'main' table - it should first check the 'temp' table's "staleness" (i.e., if any row is not supposed to be seen now - or new rows are supposed to be seen by that particular role). Every single role will have its own "temp" table.

If temp table is stale then
the 'temp' table is refreshed and
query is served.
else
the query is served;

I was, stupidly, trying to write single SQLs to achieve this! A SQL that takes the form of

with stale_n_refresh_check as
(select check_stale(:p_handle_id, 'MAIN_ITEM_MASTERS') col from dual)
select b.* from
TEMP_ITEM_MASTERS_<:p_handle_id> a,
MAIN_ITEM_MASTERS b ,
temp1 d
where
a.object_id = b.ITEM_ID
and d.col =1 ;

I wanted to make sure that the check_stale function is evaluated first since it's in the WITH clause. Since check_stale on execution *also* refreshes the TEMP_ITEM_MASTERS_XXXX -- the queries will *always* have fresh data.

Well, I was stupid. Very stupid.

I totally forgot about the database principle that a query cannot modify data, even if it does by any hack like above - it won't be able to see it! Of course, that would break the whole C of ACID properties of transaction management.

But as I said, stress does so many things to you. I tried ORDERED_PREDICATES - so the with sub-factored clause is executed first and TEMP_ITEM_MASTERS_ is only read within a query after being modified by the function! Yeah, I was so stupid. This is database for God's sake.

But the questions are,
(a) can a transaction read committed data that's committed by itself during the transaction? If so, then
(b) can a query - by some means - read the data modified by itself?

So - more secondary questions.

-- How can a transaction go on and still commit data within the scope of itself?
-- How can a query modify data? (Well, it cannot. But what if you are asked to write SELECT to modify data? It's more like those trick interview questions!)

C.R Talk --
--------

Really liked this knife rack.