Database Reference
In-Depth Information
SQL> INSERT INTO persons (name) VALUES ('Michelle');
SQL> SELECT name
2 FROM persons
3 WHERE name_upper = 'MICHELLE';
NAME
----------
Michelle
As you will see in Chapter 13, virtual columns can also be indexed.
The main issue of virtual columns, compared to extended statistics, is that they change the behavior of some SQL
statements (for examples, of
SELECT *
statements and
INSERT
statements without the column list). In other words,
because extended statistics are based on hidden columns, they are completely transparent to the application.
It's important to recognize that, independently of how virtual columns are defined (either explicitly by the user or
implicitly through extended statistics), object statistics and histograms are normally gathered on them. This way, the
query optimizer gets additional statistics about the data.
SQL pLaN DIreCtIVeS
SQL plan directives are a new concept introduced in version 12.1. Their purpose is to help the query optimizer
cope with misestimates. To do so, they store in the data dictionary information about the expressions that cause
misestimates. because they aren't associated to a specific SQL statement, not only can several of them be used
for a single SQL statement, but, in addition, a single SQL plan directive can be applied to multiple SQL statements.
in some cases, SQL plan directives instruct the database engine to automatically create extended statistics
(specifically, column groups). When extended statistics can't be created, they instruct the query optimizer to use
dynamic sampling.
SQL plan directives are enabled when the
optimizer_adaptive_features
initialization parameter is set to
TRUE
(this is the default value). When activated, the database engine automatically maintains (for example, creates and
purges) SQL plan directives. Some management operations can also be executed manually through the
dbms_spd
package.
information about the available SQL plan directives is provided through the
dba_sql_plan_directives
and
dba_sql_plan_dir_objects
views (
cdb
versions of these views also exist).
Index Statistics
Before describing index statistics, let's briefly review the structure of an index based on Figure
8-5
. The block at the top
is called the
root block
. This is the block where every lookup starts from. The root block references the
branch blocks
.
Note that the root block is also considered a branch block. Each branch block in turn references either another level
of branch blocks or, as in Figure
8-5
, the
leaf blocks
. The leaf blocks store the keys (in this case, some numeric values
between 6 and 89) and the rowids that reference the data. For a given index, there are always the same number of
branch blocks between the root block and every leaf block. In other words, the index is always balanced. Note that to
support efficient lookups over ranges of values (for example, all values between 25 and 45), the leaf blocks are chained.
