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

IGNORE_ROW_ON_DUPKEY_INDEX HINT

In oracle 11gr2 there is a new hint call IGNORE_ROW_ON_DUPKEY_INDEX.
It does wat it says ignore duplicate rows violation.

SQL> create table aap(id number constraint aap_pk_idx primary key
           using index(create index aap_pk_idx on aap(id)), a1 varchar2(3));
Table created.

SQL> insert into aap select rownum, 'AHH' from dual connect by level <= 10;
10 rows created.
SQL> commit;
Commit complete

SQL> insert into aap select rownum, 'AHH' from dual connect by level <= 12;
     insert into aap select rownum, 'AHH' from dual connect by level <= 12
      *
ERROR at line 1:
ORA-00001: unique constraint (GDA001.AAP_PK_IDX) violated
Oke now that is what was expected. Lets use the hint.

SQL>insert /*+ ignore_row_on_dupkey_index(aap,aap_pk_idx) */
         into aap select rownum, 'AHH'
         from dual connect by level <= 12;
into aap select rownum, 'AHH'
     *
ERROR at line 2:
ORA-38913: Index specified in the index hint is invalid
Why doesn't the hint gives this messages Well

SQL> select index_name, uniqueness, table_name
           from dba_indexes
          where index_name='AAP_PK_IDX'; 
INDEX_NAME                     UNIQUENES TABLE_NAME
------------------------------ --------- ------------------------------
AAP_PK_IDX                     NONUNIQUE AAP

The index in non unique and for the hint to work is has to be.

SQL>create table aap(id number constraint aap_pk_idx primary key
          using index(create unique index aap_pk_idx on aap(id)), a1 varchar2(3));
.table created

SQL> select index_name, uniqueness, table_name
           from dba_indexes
          where index_name='AAP_PK_IDX'; 
INDEX_NAME                     UNIQUENES TABLE_NAME
------------------------------ --------- ------------------------------
AAP_PK_IDX                      UNIQUE         AAP

SQL> insert into aap select rownum, 'AHH' from dual connect by level <= 10;
SQL commit;

SQL> insert /*+ ignore_row_on_dupkey_index(aap,aap_pk_idx) */
     into aap select rownum, 'AHH'
     from dual connect by level <= 12;
2 rows created.

So with this hint duplicate are ignored.

SQL> select * from aap;
        ID A1
---------- ---
         1 AHH
         2 AHH
         3 AHH
         4 AHH
         5 AHH
         6 AHH
         7 AHH
         8 AHH
         9 AHH
        10 AHH
        11 AHH
        ID A1
---------- ---
        12 AHH
12 rows selected.
And there is 1 thing. The hint doesn't work with the update statement.

SQL> update /*+ ignore_row_on_dupkey_index(aap,aap_pk_idx) */
          aap set id = 13 where id = 5;
   update /*+ ignore_row_on_dupkey_index(aap,aap_pk_idx) */
*
ERROR at line 1:
ORA-38917: IGNORE_ROW_ON_DUPKEY_INDEX hint disallowed for this operation

Thats it.

On demand segments Oracle 11gr2

Before 11gr2 when you created a table, index and so on there was ALWAYS allocated 1 initail extent
Example

SQL> create table aap (a number, a2 varchar2(100))
         storage (initial 10m) tablespace users;
Table created.

SQL> select blocks, bytes/1024/1024, segment_name,
          segment_type
          from dba_segments
         where segment_name = 'AAP';
    BLOCKS BYTES/1024/1024 SEGMENT_NAME   SEGMENT_TYPE
     --------          ---------------    -------------------       ----------------
      1280             10                                 AAP                     TABLE
You see there is 1 segment from 10Mb

Now the same but now in Oracle 11gr2

SQL> create table aap (a number, a2 varchar2(100))
         storage (initial 10m) tablespace users;
Table created.

SQL> select blocks, bytes/1024/1024, segment_name, segment_type
          from dba_segments where segment_name = 'AAP';
 no rows selected

As you see no initial extent is created.Only when you insert 1 or more rows the first extent is created.

SQL> insert into aap values (1234,'AAP');
 1 row created.

SQL> select blocks, bytes/1024/1024, segment_name, segment_type from dba_segments where   segment_name = 'AAP';

     BLOCKS BYTES/1024/1024 SEGMENT_NAME   SEGMENT_TYPE
     ------- -      -------------          ----------------------     --------------
      1280              10                                   AAP                TABLE

As you see when the first row is inserted then then the fist extent is created.
It als means that the if you have an index on the table this also get its first extent.
So there is a slide overhead when the first rows are inserted.

SQL> create table noaap (a number, a2 varchar2(100))
           storage (initial 10m) tablespace users;  2
Table created.

SQL> create index noaap_1 on noaap(a);
Index created.
SQL> create index noaap_2 on noaap(a2);
Index created.

SQL> select blocks, bytes/1024/1024, segment_name,
         segment_type
         from dba_segments
         where segment_name like'NOAAP%';
 
no rows selected
SQL> insert into noaap values (1234,'AAP');
 1 row created.

SQL> select blocks, bytes/1024/1024, segment_name,
         segment_type
         from dba_segments
         where segment_name like'NOAAP%';
    BLOCKS BYTES/1024/1024 SEGMENT_NAME    SEGMENT_TYPE
----------        ---------------          ---------------             ---------------
         8                  .0625                    NOAAP_2                  INDEX
      1280                10                        NOAAP                      TABLE
         8                 .0625                     NOAAP_1                  INDEX
Now what is the advantage.
large applications can created a lot of segments that are not used.(SAP is one of  those one's).
Tables can have many indexes that may never be used. Which can result in wasted storage.
If you don't want this then set the following parameter on false

SQL> alter system set deferred_segment_creation = FALSE;

Now does this mean when you truncate the table you don't have any extent anymore.

SQL> truncate table aap;
SQL> select blocks, bytes/1024/1024, segment_name, segment_type
           from dba_segments where   segment_name = 'AAP';

     BLOCKS BYTES/1024/1024 SEGMENT_NAME   SEGMENT_TYPE
     ------- -      -------------          ----------------------     --------------
      1280              10                                   AAP                TABLE

The answer is no. The initial extent is kept.

That it