How Does Using Expression Statistics Differ from Using a Function-Based Index?
(Doc ID 1489505.1)
Last updated on MARCH 05, 2020
Applies to:Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 22.214.171.124 and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.
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.)
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document