A few days ago I published an example of the optimizer failing to handle an updateable join view because it didn’t recognise that a particular type of aggregate subquery would guarantee key-preservation. Here’s another example where the human eye can see key-preservation, but the optimizer can’t (even in 220.127.116.11). As usual we’ll start with some sample data – in this case two tables since I want to update from one table to the other.
create table t1 ( id1 number, id2 number, val number, constraint t1_pk primary key (id1, id2) ); insert into t1 values (1,1,99); commit; create table t2 ( id1 number, id2 number, id3 number, val number, constraint t2_pk primary key (id1, id2, id3) ); insert into t2 values (1,1,1,200); insert into t2 values (1,1,2,200); commit;
Here’s a simple update statement that identifies a few rows in a table then updates a column where a matching value can be derived from another table – it’s an example of an update by correlated subquery:
update t1 set small_vc = ( select max(small_vc) from t2 where t2.id = t1.id ) where mod(id,100) = 0 and exists ( select null from t2 where t2.id = t1.id ) ;
A couple of days ago I wrote about some things I’d like to see in index statistics, which means changing dbms_stats.gather_index_stats(). Here’s an idea for dbms_stats.gather_table_stats(). I’d like to see the number of chained and migrated rows counted separately in dba_tables when we run the packaged procedure. The optimizer will use information about chained or migrated rows, but the information is only collected if you use the analyze command (and even then the two figures are summed into the single value chain_cnt) .
Here are a few thoughts on dbms_stats – in particular the procedure gather_index_stats.
The procedure counts the number of used leaf blocks and the number of distinct keys using a count distinct operation, which means you get an expensive aggregation operation when you gather stats on a large index. It would be nice efficiency feature if Oracle changed the code to use the new Approximate NDV mechanism for these counts.