I wish (2)

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

1 Index statistics: when SQL Server collects index statistics, it also collects stats not only on the full index definition, but also on every “prefix” of the index, i.e. if you have a 4-column index, you will get stats for the first two columns and first three columns, as well as the full index stats.

Of course, you can emulate this in 11g with extended statistics – and you might argue that it’s better to make it a deliberate choice. Given the new ‘approximate NDV’ feature in 11.2 it might not be an expensive option to implement.

Here’s a related observation –  one that I haven’t tested to its limits, though, so you might was to check how well it works. The “approximate NDV” isn’t used for calculating the number of distinct keys in an index, but there have been occasions when I have noticed that dbms_stats.gather_index_stats(), and related calls,  will use the number of distinct values of the underlying column as the number of distinct keys in a single column index – and that’s a massive performance benefit. As a follow-up, then, if you create extended stats on the full combination of columns of a multi-column index, Oracle sometimes seems to use the same shortcut for a multi-column index  – but I haven’t investigated when this works and when it doesn’t. There may be a timing element involved.

2 Deprecated Features: one of the “events” that you can track with SQL Server’s Profiler is “Deprecated Events”. This allows you to collect details of when deprecated features are used in your code and plan ahead to changing the code rather than having a rush on the next upgrade. I’m not sure how well it would work on a large, busy system – the compromise between reporting the event too often and failing to report it might be a hard to manage – but it would be  nice to have an occasional warning in the alert log with (say) some of the client,  module or procedure information from v$session.

Update Feb 2013

According to the final section (Effect of auto sample size on index stats gathering) of this note from the Optimizer Group, a call to gather index stats should use the NDV on a suitably defined column group to avoid the cost of taking a large sample from the index – provided the table stats are sufficiently up to date.