In 11.1.0.6, there appears to be a bug regarding the interaction of Virtual Columns with the ‘row shipping’ enhancement.
Thanks to Miladin Modrakovic for the heads-up on row shipping.
Wide Table Select Row Shipping
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 5 09:26:33 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning option
SQL> create table t2_vc
2 ( col0 varchar2(30) generated always as ('SAMPLE'),
3 col1 number,
4 col2 number,
5 col3 number,
6 col4 number
7 );
Table created.
SQL> insert into t2_vc (col1) values (1);
1 row created.
SQL> select * from t2_vc;
COL0 COL1 COL2 COL3 COL4
----- ---------- ---------- ---------- ----------
1
Note COL0 is incorrectly returned as NULL.
Turn on row shipping notes in the explain plan
SQL> alter session set "_row_shipping_explain" = true; Session altered. SQL> explain plan for select * from t2_vc; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3599290104 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| T2_VC | 1 | 69 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Note ----- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- - dynamic sampling used for this statement - row shipping is enabled for this statement 13 rows selected.
Virtual columns fail with row shipping enabled. Try again without row shipping:
SQL> alter session set "_enable_row_shipping" = false; Session altered. SQL> select * from t2_vc; COL0 COL1 COL2 COL3 COL4 ----- ---------- ---------- ---------- ---------- SAMPLE 1
COL0 is correctly returned.
SQL> explain plan for select * from t2_vc; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3599290104 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| T2_VC | 1 | 69 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Note ----- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- - dynamic sampling used for this statement 12 rows selected.
Virtual columns now work correctly.
Test the threshold (default is 80%). If we select more than 4 columns from our 5 column table, the threshold is exceeded and row shipping is enabled. Select only 4 columns and row shipping is disabled and everything is fine.
SQL> alter session set "_enable_row_shipping" = true;
Session altered.
SQL> select col0,col1,col2,col3,col4 from t2_vc;
COL0 COL1 COL2 COL3 COL4
----- ---------- ---------- ---------- ----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3599290104
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T2_VC | 1 | 69 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
- row shipping is enabled for this statement
SQL> select col0,col1,col2,col3 from t2_vc;
COL0 COL1 COL2 COL3
----- ---------- ---------- ----------
SAMPLE 1
Execution Plan
----------------------------------------------------------
Plan hash value: 3599290104
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 56 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T2_VC | 1 | 56 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Interestingly, the threshold calculation also seems a bit broken, at least it does the way I interpret it. Even though I’m selecting all columns, if I set the threshold to 81% row shipping is disabled for the virtual column table.
See the following attachment for an example of the threshold seemingly behaving in an unexpected way when virtual columns are involved.