Wednesday, August 1, 2012

Virtual Columns 11G

Before 11g virtual columns where created when you created a function based index
They were also hidden. On virtual columns statistics can be created witch is usefull but
you had to create an index for it.

As of 11g you can define virtual columns and are visible in the table definition.

SQL>create table aap as select rownum a1,
         'AHH' a2
         FROM DUAL CONNECT BY LEVEL <= 100000;
SQL>CREATE INDEX aap_i ON aap(a2);
SQL> insert into aap values(100001,'GIN');
1 row created.
SQL> insert into aap values(100002,'Gin');
1 row created.
SQL> insert into aap values(100003,'GiN');
1 row created.
SQL> commit;
SQL>begin
         dbms_stats.gather_table_stats(ownname=>'GDA001',
         tabname=> 'AAP',
         cascade=> true,
         estimate_percent=>null,
         method_opt=> 'FOR ALL COLUMNS SIZE 1');
         end;
          /
SQL> SELECT * FROM AAP WHERE UPPER(a2)='GIN';
        A1 A2
---------- ---
    100001 GIN
    100002 Gin
    100003 GiN

Execution Plan
----------------------------------------------------------
Plan hash value: 3829027638

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |  9000 |    54   (6)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| AAP  |  1000 |  9000 |    54   (6)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(UPPER("A2")='GIN')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        216  consistent gets
          0  physical reads
          0  redo size
        664  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

As you see the index is not used because of the UPPER function.
Let create the function based index.

SQL> create index f_aap_a2_i on aap(UPPER(a2)) compute statistics;
Index created.
        A1 A2
---------- ---
    100001 GIN
    100002 Gin
    100003 GiN

Execution Plan
----------------------------------------------------------
Plan hash value: 2746435280

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |  1000 |  9000 |    50   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| AAP        |  1000 |  9000 |    50   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | F_AAP_A2_I |   400 |       |   105   (0)| 00:00:02 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("A2")='GIN')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        664  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

As you see the index is now used.

SQL> select table_name, column_name, num_distinct,
          density, virtual_column, hidden_column
          from dba_tab_cols
         where table_name = 'AAP';
TABLE COLUMN_NAME     NUM_DISTINCT    DENSITY VIR HID
-----         ---------------               ------------              ---------- --- ---
AAP     SYS_NC00003$                       2                         .5        NO NO
AAP   A2                                               4                       .25         NO  NO
AAP   A1                                      100003                9.9997E-06 NO  NO

As you see there is a virtual column that is hidden.
and no statistics also

SQL> begin
dbms_stats.gather_table_stats(ownname=>'GDA001',
tabname=> 'AAP',
cascade=> true,
estimate_percent=>null,
method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 3');
end;
/

SQL> select table_name, column_name, num_distinct,
          density, virtual_column, hidden_column
          from dba_tab_cols
         where table_name = 'AAP';

TABLE COLUMN_NAME     NUM_DISTINCT    DENSITY VIR HID
-----     ---------------                   ------------             ---------- --- ---
AAP   SYS_NC00003$                         2                            .5 YES YES
AAP   A2                                               4                          .25 NO  NO
AAP   A1                                      100003            9.9997E-06 NO  NO
The virtual statistics are now in place.
Lets run the statement again

SQL>  SELECT * FROM AAP WHERE UPPER(a2)='GIN';
        A1 A2
---------- ---
    100001 GIN
    100002 Gin
    100003 GiN

Execution Plan
----------------------------------------------------------
Plan hash value: 2746435280
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     3 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| AAP        |     3 |    39 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | F_AAP_A2_I |     3 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("A2")='GIN')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        664  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
Al you now see also the statiscts are now much more accurate. there are 100000 rows with AHH and only
3 with GIN. The rows are now 3 before that is was 100000 and with the index 400.

Now the above was the method before 11g.
This is how you can do it in 11g

SQL> drop index F_AAP_A2_I;
Index dropped.
SQL> alter table aap add (upper_naam as (UPPER(a2)));
Table altered.
SQL>begin
         dbms_stats.gather_table_stats(ownname=>'GDA001',
         tabname=> 'AAP',
         cascade=> true,
         estimate_percent=>null,
         method_opt=> 'FOR ALL COLUMNS SIZE 1');
         end;
          /
SQL> select table_name, column_name, num_distinct,
          density, virtual_column, hidden_column
          from dba_tab_cols
         where table_name = 'AAP';
 
TABLE COLUMN_NAME     NUM_DISTINCT    DENSITY VIR HID
-----         ---------------               ------------              ---------- --- ---
AAP   UPPER_NAAM                          2                         .5        YES NO
AAP   A2                                               4                       .25         NO  NO
AAP   A1                                      100003                9.9997E-06 NO  NO

As you see the virtual column upper_naam is not hidding more.

SQL> select table_name, column_name
          from  dba_tab_columns
         where  table_name = 'AAP';
TABLE COLUMN_NAME
----- ---------------
AAP   A1
AAP   A2
AAP   UPPER_NAAM

SQL> SELECT * FROM AAP WHERE UPPER(a2)='GIN';
        A1 A2  UPP
---------- --- ---
    100001 GIN GIN
    100002 Gin GIN
    100003 GiN GIN

Execution Plan
----------------------------------------------------------
Plan hash value: 3829027638
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 50002 |   634K| 56(9)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| AAP  | 50002 |   634K|    56   (9)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("AAP"."UPPER_NAAM"='GIN')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        216  consistent gets
          0  physical reads
          0  redo size
        744  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
SQL> create index upper_naam_i on aap(upper(a2));
create index upper_naam_i on aap(upper(upper(a2))
                                       *
ERROR at line 1:
ORA-54018: A virtual column exists for this expression
So you can't create the index because there is virtual column

SQL> create index upper_naam_i on aap(upper_naam);
Index created.
But we can create a normal index on the virtual column

SQL> SELECT index_name, index_type FROM user_indexes WHERE index_name='UPPER_NAAM_I';
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
UPPER_NAAM_I                   FUNCTION-BASED NORMAL

As you see the index type is function based

SQL> SELECT index_name, column_expression
          FROM user_ind_expressions
         WHERE index_name = 'UPPER_NAAM_I'; 

INDEX_NAME                     COLUMN_EXP
------------------------------ ----------
UPPER_NAAM_I                   UPPER("A2")
                              
SQL>  select * from aap where upper_naam =  'GIN';
        A1 A2  UPP
---------- --- ---
    100001 GIN GIN
    100002 Gin GIN
    100003 GiN GIN

Execution Plan
----------------------------------------------------------
Plan hash value: 2505477614
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     3 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| AAP          |     3 |    39 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | UPPER_NAAM_I |     3 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("UPPER_NAAM"='GIN')

Statistics
----------------------------------------------------------
         28  recursive calls
        144  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
        744  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          3  rows processed

And there is the index on the virtual column

Thats it

No comments:

Post a Comment