Oracle Scratchpad

January 6, 2012

I wish

Filed under: CBO,Execution plans,Oracle,Wishlist — Jonathan Lewis @ 5:38 pm GMT Jan 6,2012

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 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.

December 29, 2011

I wish

Filed under: CBO,Execution plans,Oracle,Wishlist — Jonathan Lewis @ 10:02 pm GMT Dec 29,2011

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:

December 16, 2011

I Wish

Filed under: Oracle,Statistics,Wishlist — Jonathan Lewis @ 6:31 pm GMT Dec 16,2011

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) .

December 13, 2011

I wish

Filed under: CBO,Indexing,Oracle,Statistics,Wishlist — Jonathan Lewis @ 6:08 pm GMT Dec 13,2011

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.

June 18, 2010

I wish (2)

Filed under: Oracle,SQL Server,Wishlist — Jonathan Lewis @ 6:13 am GMT Jun 18,2010

Continuing my little list of things in SQL Server that I’d like to see in Oracle:

June 1, 2010

I wish .. (1)

Filed under: Oracle,SQL Server,Wishlist — Jonathan Lewis @ 7:45 pm GMT Jun 1,2010

I pointed out some time ago a few of the things in SQL Server that I would like to see in Oracle. Here’s a couple more:

February 4, 2010

SQL Server

Filed under: CBO,Execution plans,Hints,Infrastructure,Oracle,SQL Server,Statistics,Wishlist — Jonathan Lewis @ 7:07 pm GMT Feb 4,2010

A few days ago I did a presentation on SQL Server. This probably sounds a little strange given my status as an Oracle specialist – but the nice people at Microsoft asked me if I would contribute to one of their seminars so I downloaded and installed the 180 day free trial copy of the Enterprise version, then downloaded the “Books Online” manuals and started to play.

It was an interesting experience – and I think the audience (and organisers) found my presentation useful. The title was “What the Enterprise needs in an RDBMS” – and that’s something I do know about – and the presentation was about whether or not you could find everything you needed in SQL Server 2008, where you’d have to look in the manuals, and supplementary questions you’d have to ask.


Blog at