It can also lead to some peculiar side effects.
Test setup
Let's begin by creating a test table with the on commit refresh materialized zone map:
SQL> create table t pctfree 95 clustering by linear order (n) as
2 select level n, rpad('x', 200, 'x') v
3 from dual
4 connect by level <= 5000;
Table created
SQL> create materialized zonemap zm$t refresh fast on commit on t (n);
Done
SQL> select * from zm$t order by 1;
ZONE_ID$ MIN_1_N MAX_1_N ZONE_LEVEL$ ZONE_STATE$ ZONE_ROWS$
---------- ---------- ---------- ----------- ----------- ----------
3241022750 1 748 0 0 748
3241022750 749 1756 0 0 1008
3241022750 1757 2764 0 0 1008
3241022750 2765 3772 0 0 1008
3241022750 3773 4780 0 0 1008
3241022750 4781 5000 0 0 220
6 rows selected
Zone Staleness (first session)
When a session updates a row in the table it will also mark the corresponding zone in the zone map as stale by setting ZONE_STATE$=1:
SQL> update t set n=0 where n=1; 1 row updated SQL> select * from zm$t order by 1; ZONE_ID$ MIN_1_N MAX_1_N ZONE_LEVEL$ ZONE_STATE$ ZONE_ROWS$ ---------- ---------- ---------- ----------- ----------- ---------- 3241022750 1 748 0 1 748 <=== Zone marked as stale 3241022750 749 1756 0 0 1008 3241022750 1757 2764 0 0 1008 3241022750 2765 3772 0 0 1008 3241022750 3773 4780 0 0 1008 3241022750 4781 5000 0 0 220 6 rows selectedA stale zone is always scanned when performing Zone Map pruning regardless of the predicates involved. This makes sense otherwise we won't be able to select the updated row back while performing Zone Map pruning at the same time because the value is now out of range. With the zone marked as stale we have no such problem:
SQL> select n from t where n=0;
N
----------
0
A zone will be marked as fresh when the session commits by setting ZONE_STATE$=0. Note that our session did not commit yet.
Second session
So what happens if some other session updates a row belonging to the stale zone map and then commits? Does the zone gets invalidated by being considered fresh again? Let's find out what happens:
SQL> update t set n=2 where n=2; 1 row updated SQL> commit; Commit complete SQL> select * from zm$t order by 1; ZONE_ID$ MIN_1_N MAX_1_N ZONE_LEVEL$ ZONE_STATE$ ZONE_ROWS$ ---------- ---------- ---------- ----------- ----------- ---------- 3241022750 1 748 0 0 748 <=== Zone is no longer marked as stale 3241022750 749 1756 0 0 1008 3241022750 1757 2764 0 0 1008 3241022750 2765 3772 0 0 1008 3241022750 3773 4780 0 0 1008 3241022750 4781 5000 0 0 220 6 rows selectedIndeed the zone is no longer marked as stale! Now let's go back to our first session...
First session
So what happens if we try to select the same updated row in the first session?
SQL> select n from t where n=0; no rows selectedWe've lost the ability to see the updated row thanks to the second session invalidating the zone! Worse yet we still won't be able to see the row even after this session commits meaning any query in the database which relies on this Zone Map to do the pruning will be unable to see the data either:
SQL> commit;
Commit complete.
SQL> select n from t where n=0;
no rows selected
SQL> select /*+ no_zonemap(t scan) */ n from t where n=0;
N
----------
0
The only way to select the row is to disable Zone Map pruning.
The end result is we have a perfectly "valid" Zone Map as long as Oracle is concerned which is out of sync with the actual table data:
SQL> select zonemap_name, invalid, stale, unusable, compile_state from dba_zonemaps; ZONEMAP_NAME INVALID STALE UNUSABLE COMPILE_STATE ------------ ------- ------- -------- ------------------- ZM$T NO NO NO VALID
The fact that Oracle documentation claims that on commit refresh zone maps stay transactionally fresh suggests that this behavior is clearly a bug resulted from incorrect handling of concurrent transactions modifying the data belonging to the same zone.
Posts
