Databases Reference
In-Depth Information
You can collect statistics for this hidden virtual column by using the following syntax:
SQL> exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'EMPLOYEES',
estimate_percent=>null,cascade=>true,method_opt=>'FOR ALL HIDDEN COLUMNS SIZE
1');
PL/SQL procedure successfully completed.
SQL>
You can check once again to confirm that the database has collected statistics for the hidden virtual
column.
SQL> select column_name,num_distinct, hidden_column,virtual_column
2* from dba_tab_cols where table_name='EMPLOYEES';
COLUMN_NAME NUM_DISTINCT HID VIR
---------------- -------------- --- ---
SYS_NC00012$ 31 YES YES
DEPARTMENT_ID 11 NO NO
MANAGER_ID 18 NO NO
…
12 rows selected.
SQL>
Alternately, you can directly collect statistics on the function expression, as shown in the following
example:
SQL> execute dbms_stats.gather_table_stats (ownname=> USER, -
> tabname=>'EMPLOYEES',-
> method_opt=>'FOR ALL COLUMNS FOR COLUMNS-
> (12 * salary * commission_pct)');
PL/SQL procedure successfully completed.
SQL>
Indexes on Virtual Columns
Before we discuss creating an index on a virtual column, it's a good idea to clarify exactly what a virtual
column is. A virtual column represents data in a table just as a normal table, but it's not stored on disk
(or elsewhere!). Instead, the database computes the values for the virtual column on the fly by
computing a set of expressions or functions. It is important to understand that the value of a virtual
column is computed only when that value is queried. That is, virtual columns are evaluated when a
WHERE
clause refers to the column in a
SELECT
statement.
