How Does Using Expression Statistics Differ from Using a Function-Based Index? (Doc ID 1489505.1)

Last updated on NOVEMBER 07, 2023

Applies to:

Goal

Function-based indexes have existed for several releases of Oracle Database.  They're very useful when a column appears frequently as either an argument to a function or within an arithmetic expression within the predicate (or WHERE clause).  However, it was not until Release 11g that Oracle introduced extended statistics, of which there are two types: multicolumn statistics and expression statistics.  Expression statistics allow you to collect statistics on a column involved in a function call or arithmetic expression, even in the absence of a function-based index.  You may be asking yourself, "If I already have a function-based index, then do I really need expression statistics, too?  Isn't that overkill?"  The answer is "Yes, provided that you know how to collect the appropriate statistics on the function-based index."

Walking through an example will pull all of this information into focus.  (Note: for purposes of these examples, we'll run with dynamic sampling disabled.)

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.