Oracle 11g - Statisticly speaking!

OK, it's time for another Oracle Database 11g New Feature! Today some new statistics related features.

In Oracle database 11g you now have two new kinds of statistics that you can collect. Collectively these are known as extended statistics. The two kinds of extended statistics you can collect are:

1. Multi-column statistics
2. Expression statistics

Prior to Oracle Database 11g Oracle had no way of understanding the relationship of data within multiple columns of a where clause. Oracle Database 11g adds multicolumn statistics to the mix to try to solve this problem. Now the optimizer can generate more intelligent cost based plans when you have multiple columns in your where clause, based on the combined selectivity of both columns. Multi-column statistics are not generated automatically, when you generate statistics. You have to define the columns you want to generate the statistics on when analyzing the table. Here is an example of generating multi-column statistics on the table DUDE for columns DUDENO and DUDES_JOB. Note the "for columns" syntax that defines the columns to build the multi-column statistics on:

exec dbms_stats.gather_table_stats(null,'DUDE',
method_opt=>'for all columns size skewonly
for columns (DUDENO,DUDES_JOB)');

Expression statistics allow Oracle to collect selectivity information based on the application of a function on a column. This has direct relationship to the use of function based indexes. Again, you collect expression statistics with dbms_stats when you collect table statistics as seen in this example:

begin
dbms_stats.gather_table_stats(null, 'DUDE',
method_opt=>'for all columns size skewonly
for columns (lower(dude_name))');

Now the optimizer can rationally make execution plan choices with regards to the selectivity of the data in the dude_name with the lower function applied.

There is even more in my new book, Oracle Database 11g New Features. It's available for pre-sales on Amazon and should be out in November!

http://www.amazon.com/Oracle-Database-11g-New-Features/dp/0071496610/ref=sr_1_2/102-1704151-7971347?ie=UTF8&s=books&qid=1188535426&sr=1-2

There are more new statistics related features in 11g including publish/subscribe and restore of old statistics!! I'll talk about that in another post.