Database Reference
In-Depth Information
########################################################################
COLUMN USAGE REPORT FOR CHRIS.T
...............................
1. VAL1 : EQ
2. VAL2 : EQ
3. VAL3 : EQ
4. (VAL1, VAL2) : FILTER
5. (VAL1, VAL3) : FILTER
6. (VAL2, VAL3) : GROUP_BY
########################################################################
3.
Create extensions using the
create_extended_stats
procedure of the
dbms_stats
package. Note that if the definition of the extensions themselves isn't passed as a
parameter, the definition is taken from the information stored during the recording.
Therefore, only the schema and table names are required. Notice how, in the following
example, three extensions are created with a single call to
create_extended_stats
:
SQL> SELECT dbms_stats.create_extended_stats(ownname => user, tabname => 't')
2 FROM dual;
DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME=>USER,TABNAME=>'T')
-------------------------------------------------------------------------------
###############################################################################
EXTENSIONS FOR CHRIS.T
......................
1. (VAL1, VAL2) : SYS_STU4K1K3JNH1Z9#_L_V93K3DT4 created
2. (VAL1, VAL3) : SYS_STUS574STTDWYBF6PGQN#XHGGJ created
3. (VAL2, VAL3) : SYS_STUPS77EFBJCOTDFMHM8CHP7Q1 created
###############################################################################
4.
After creating the extensions, regather the object statistics of the modified table.
In version 12.1, extensions can also be automatically created by the database engine. In fact, for SQL statements
that take advantage of statistics feedback, the query optimizer can create a SQL plan directive whose aim is to instruct
the database engine to create an extension. As a result, future reoptimization due to statistics feedback can be
avoided. A full example is available in the
seed_col_usage.sql
script. There are two essential things to be aware of.
First, extensions can and will be automatically created. Second, the extensions are created only when object statistics
are gathered. In other words, the interval between the creation of the SQL plan directive and the creation of the
extension depends on the frequency of the object statistics gathering.
It's interesting to note that extended statistics are based on another feature, introduced in version 11.1, called
virtual columns
. A virtual column is a column that doesn't store data but simply generates its content with an
expression based on other columns. This is helpful in case an application makes frequent usage of given expressions.
A typical example is applying the
upper
function to a
VARCHAR2
column or the
trunc
function to a
DATE
column. If these
expressions are frequently used, it makes sense to define them directly in the table as shown in the following example:
SQL> CREATE TABLE persons (
2 name VARCHAR2(100),