Archive for August, 2009

NoCOUG’s “First international SQL challenge”

Just a short note to tell my friends that I have been bestowed the August Order of the Wooden Pretzel, that is, that I won the NoCOUG’s “First international SQL challenge” with this solution. I’m especially happy to see that, after (way too) many years since graduation, I...
Read more

fast refresh of single-table materialized views – algorithm summary

Today we are going to investigate how Oracle fast refreshes materialized views (MVs) of a single master table, containing no aggregate but, at most, filter predicates and additional column definitions: create materialized view test_mv build immediate refresh fast on demand with rowid -- with...
Read more

Xplan 2.0

A lot of new features have been added in version 2.0 of xplan, the sqlplus script I use to investigate about SQL statements performance (I spoke about version 1.0 in this post). Here’s a brief description. wait profile (from ASH) For each statement, its wait profile is calculated fetching...
Read more

fast refresh of join-only materialized views – algorithm summary

This post investigates how Oracle fast refreshes materialized views containing only joins of master tables: create materialized view test_mv build immediate refresh fast on demand as select test_t1.*, test_t1.rowid as test_t1_rowid, test_t2.*, test_t2.rowid as test_t2_rowid, ...
Read more