Some MORE Testing on Intra-Block Row Chaining

(note : See update on 31-Oct for a single-rows test on a 1,000 column table)

Following my first round of tests on Intra-Block Row Chaining (where a table has more than 255 columns), here are some results that are more suprising :

SQL> set autotrace on
SQL> select count(*) from many_columns_table;

COUNT(*)
----------
50640


Execution Plan
----------------------------------------------------------
Plan hash value: 3712319054

---------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     1 |  3019   (1)| 00:00:37 |
|   1 |  SORT AGGREGATE    |                    |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 |  3019   (1)| 00:00:37 |
---------------------------------------------------------------------------------


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

SQL> select count(col_vc1) from many_columns_table;

COUNT(COL_VC1)
--------------
   50640


Execution Plan
----------------------------------------------------------
Plan hash value: 3712319054

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     1 |     3 |  3019   (1)| 00:00:37 |
|   1 |  SORT AGGREGATE    |                    |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 |   148K|  3019   (1)| 00:00:37 |
-----------------------------------------------------------------------------------------


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

SQL>  

So a COUNT(col_vc1) is comparable to a COUNT(*).

Next we see :

SQL> select count(col_vc93) from many_columns_table;

COUNT(COL_VC93)
---------------
    50640


Execution Plan
----------------------------------------------------------
Plan hash value: 3712319054

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     1 |     3 |  3025   (1)| 00:00:37 |
|   1 |  SORT AGGREGATE    |                    |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 |   148K|  3025   (1)| 00:00:37 |
-----------------------------------------------------------------------------------------


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

SQL> select count(col_vc94) from many_columns_table;

COUNT(COL_VC94)
---------------
    50640


Execution Plan
----------------------------------------------------------
Plan hash value: 3712319054

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     1 |     3 |  3025   (1)| 00:00:37 |
|   1 |  SORT AGGREGATE    |                    |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 |   148K|  3025   (1)| 00:00:37 |
-----------------------------------------------------------------------------------------


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

SQL> 

Notice how the COUNT(col_vc94) seems to have done more than 5 times as many 'consistent gets' as the COUNT(col_vc93) ? (If you note from my previous tests, col_vc94 is the 96th column in the 350 column table (which makes it the 254th column if we count backwards).

SQL> select count(distinct(col_vc1)) from many_columns_table;

COUNT(DISTINCT(COL_VC1))
------------------------
             30892


Execution Plan
----------------------------------------------------------
Plan hash value: 1283832531

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     1 |     3 |  3019   (1)| 00:00:37 |
|   1 |  SORT GROUP BY     |                    |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 |   148K|  3019   (1)| 00:00:37 |
-----------------------------------------------------------------------------------------


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

SQL> select count(distinct(col_vc93)) from many_columns_table;

COUNT(DISTINCT(COL_VC93))
-------------------------
              30965


Execution Plan
----------------------------------------------------------
Plan hash value: 1283832531

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     1 |     3 |  3025   (1)| 00:00:37 |
|   1 |  SORT GROUP BY     |                    |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 |   148K|  3025   (1)| 00:00:37 |
-----------------------------------------------------------------------------------------


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

SQL> select count(distinct(col_vc94)) from many_columns_table;

COUNT(DISTINCT(COL_VC94))
-------------------------
              31029


Execution Plan
----------------------------------------------------------
Plan hash value: 1283832531

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     1 |     3 |  3025   (1)| 00:00:37 |
|   1 |  SORT GROUP BY     |                    |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 |   148K|  3025   (1)| 00:00:37 |
-----------------------------------------------------------------------------------------


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

SQL> select count(distinct(col_vc348)) from many_columns_table;

COUNT(DISTINCT(COL_VC348))
--------------------------
               30955


Execution Plan
----------------------------------------------------------
Plan hash value: 1283832531

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     1 |     3 |  3042   (1)| 00:00:37 |
|   1 |  SORT GROUP BY     |                    |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 |   148K|  3042   (1)| 00:00:37 |
-----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
   24  recursive calls
    0  db block gets
61013  consistent gets
    0  physical reads
    0  redo size
  535  bytes sent via SQL*Net to client
  492  bytes received via SQL*Net from client
    2  SQL*Net roundtrips to/from client
    1  sorts (memory)
    0  sorts (disk)
    1  rows processed

SQL>  

Again, we see more than 5x 'consistent gets' with col_vc94 and col_vc348 as with col_vc1 and col_vc93.

The execution plan is a FullTableScan, yet the 'consistent gets' are so much higher.

What is my concern ? Whether the 'consistent gets' are *really* that high or not, they are likely significantly influencing "performance measures" -- e.g. whether AWR uses these (higher) figures or whether my own monitoring scripts use these (higher) figures, the fact would be that they may not be real indicators of performance ?

Soon, I will test with 400+, 500+ and 800+ columns !

.
.

UPDATE : 31-Oct-09
Here are some results with a 1,000 column table (in a 16KB tablespace). The table has been created with 1 single row :

SQL> analyze table many_columns_table compute statistics;

Table analyzed.

SQL> select blocks, num_rows, avg_row_len, chain_cnt from user_tables where table_name = 'MANY_COLUMNS_TABLE';

BLOCKS   NUM_ROWS AVG_ROW_LEN  CHAIN_CNT
---------- ---------- ----------- ----------
     5          1        4013          0

SQL> analyze table many_columns_table delete statistics;

Table analyzed.

SQL>
SQL> exec dbms_stats.gather_table_stats('','MANY_COLUMNS_TABLE',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks, num_rows, avg_row_len from user_tables where table_name = 'MANY_COLUMNS_TABLE';

BLOCKS   NUM_ROWS AVG_ROW_LEN                                                            
---------- ---------- -----------
     5          1        4004

SQL>

SQL> create index many_cols_ndx_1 on many_columns_table(col_number) tablespace test_16k;

Index created.

SQL>
SQL> select col_vc1, col_vc3,col_vc5, col_vc93 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
697   8QY   RTE   OOF


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("COL_NUMBER"=1050)


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

SQL>
SQL> select col_vc1, col_vc3,col_vc5, col_vc235 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
697   8QY   RTE   NSP


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("COL_NUMBER"=1050)


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

SQL>
SQL> select col_vc1, col_vc3,col_vc5, col_vc489 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
697   8QY   RTE   U85


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("COL_NUMBER"=1050)


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

SQL>
SQL> select col_vc1, col_vc3,col_vc5, col_vc744 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
697   8QY   RTE   Z7R


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("COL_NUMBER"=1050)


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

SQL>
SQL> select col_vc1, col_vc3,col_vc5, col_vc998 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
697   8QY   RTE   JN8


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("COL_NUMBER"=1050)


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

SQL> 

I had as many as 3 additional 'consistent gets' !

For the same row, depending on which column I select, I see 1 or 2 or 3 or 4 consistent gets from the table.

.
.
.

Some Testing on Intra-Block Row Chaining

Per MetaLink Note#1062906.6, Intra-Block Row Chaining occurs when a Table definition has more than 255 columns because each Row Piece can handle only 255 columns.

I have been running some (not conclusive ?) tests with dummy tables of 348 or so columns. In my tests, I find that if I access the 96th column or higher, then the counter for 'consistent gets' shows 2 gets for the table block. However, if I access the 95th column or lower, I have only 1 'consistent gets'.
These tests have been with columns of 3bytes/5bytes and 15bytes each. With 8KB blocks and 16KB blocks. With the row being accessed via an Index and by a ROWID lookup. An Indexed lookup has 4 'consistent gets', which include 3 for the index, for the 95th column or lower but 5 'consistent gets' for the 96th column or higher.

In all my test runs, I see 2 table block gets for the 96th column and 1 get for the 95th column.

Could this be related to the table definition ? (One number and date column followed by 346 varchar2 columns) ? I don't know for sure.

This is the output for a run where the varchar columns are defined as varchar2(5) with 3 characters stored (other tests have been with a definition of varchar2(15) and 13 characters stored) :
Column COL_VC93 is the 95th column in the table. Column COL_VC94 is the 96th column.

SQL>
SQL> create tablespace test_8K  blocksize 8192
2  datafile '/oracle_fs/Databases/ORT24FS/test_8k.dbf' size 1000M autoextend on next 100M maxsize 2000M
3  extent management local autoallocate segment space management auto;

Tablespace created.

SQL>
SQL> drop table many_columns_table purge;
drop table many_columns_table purge
       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> create table many_columns_table (
2  col_number  number,
3  col_date  date,
4  col_vc1  varchar2(5),
5  col_vc2   varchar2(5),
6  col_vc3  varchar2(5),
7  col_vc4  varchar2(5),
8  col_vc5  varchar2(5),
9  col_vc6  varchar2(5),
10  col_vc7     varchar2(5),
11  col_vc8     varchar2(5),
12  col_vc9     varchar2(5),
13  col_vc10     varchar2(5),
14  col_vc11     varchar2(5),
15  col_vc12     varchar2(5),
16  col_vc13     varchar2(5),
17  col_vc14     varchar2(5),
18  col_vc15     varchar2(5),
.....
344  col_vc341    varchar2(5),
345  col_vc342    varchar2(5),
346  col_vc343    varchar2(5),
347  col_vc344    varchar2(5),
348  col_vc345    varchar2(5),
349  col_vc346    varchar2(5),
350  col_vc347    varchar2(5),
351  col_vc348    varchar2(5)
352  )
353  tablespace test_8k
354  /

Table created.

SQL>
SQL>
SQL> insert into many_columns_table
2  select  object_id, created,
3  dbms_random.string('X',3),
4  dbms_random.string('X',3),
5  dbms_random.string('X',3),
6  dbms_random.string('X',3),
7  dbms_random.string('X',3),
8  dbms_random.string('X',3),
9  dbms_random.string('X',3),
10  dbms_random.string('X',3),
11  dbms_random.string('X',3),
12  dbms_random.string('X',3),
13  dbms_random.string('X',3),
14  dbms_random.string('X',3),
15  dbms_random.string('X',3),
16  dbms_random.string('X',3),
17  dbms_random.string('X',3),
18  dbms_random.string('X',3),
...
341  dbms_random.string('X',3),
342  dbms_random.string('X',3),
343  dbms_random.string('X',3),
344  dbms_random.string('X',3),
345  dbms_random.string('X',3),
346  dbms_random.string('X',3),
347  dbms_random.string('X',3),
348  dbms_random.string('X',3),
349  dbms_random.string('X',3),
350  dbms_random.string('X',3)
351  from dba_objects ;

50640 rows created.

SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats('','MANY_COLUMNS_TABLE',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks, num_rows, avg_row_len*num_rows*1.1/8192 from user_tables where table_name = 'MANY_COLUMNS_TABLE';

BLOCKS   NUM_ROWS AVG_ROW_LEN*NUM_ROWS*1.1/8192
---------- ---------- -----------------------------
 11117      50640                    9546.92578

SQL> select blocks, bytes/1048576 from user_segments where segment_type = 'TABLE' and segment_name = 'MANY_COLUMNS_TABLE';

BLOCKS BYTES/1048576
---------- -------------
 11264            88

SQL>
SQL> select count(*) from many_columns_table;

COUNT(*)
----------
 50640

SQL> create index many_cols_ndx_1 on many_columns_table(col_number);

Index created.

SQL>
SQL> connect hemant/hemant
Connected.
SQL> set autotrace on
SQL> select col_vc1, col_vc3,col_vc5, col_vc340 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL   DW0   WUN   9ZP


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    21 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("COL_NUMBER"=1050)


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

SQL> select col_vc1, col_vc3,col_vc5, col_vc340 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL   DW0   WUN   9ZP


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    21 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("COL_NUMBER"=1050)


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

SQL> select col_vc1, col_vc3,col_vc5, col_vc93 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL   DW0   WUN   J7N


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    21 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("COL_NUMBER"=1050)


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

SQL> select col_vc1, col_vc3,col_vc5, col_vc93 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL   DW0   WUN   J7N


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    21 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_NUMBER"=1050)


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

SQL> select col_vc1, col_vc3,col_vc5, col_vc94 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL   DW0   WUN   VXM


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    21 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("COL_NUMBER"=1050)


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

SQL> select col_vc1, col_vc3,col_vc5, col_vc94 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL   DW0   WUN   VXM


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    21 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("COL_NUMBER"=1050)


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

SQL> set autotrace off
SQL>
SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
2  and s.sid=(select sid from v$mystat where rownum = 1)
3  and (n.name = 'consistent gets' or n.name like 'table fetch%');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                         339
table fetch by rowid                                                     23
table fetch continued row                                                 0

SQL>
SQL> select rowid from many_columns_table where col_number = 1050;

ROWID
------------------
AAANT0AAHAAAACvAAH

SQL>
SQL>
SQL>
SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
2  and s.sid=(select sid from v$mystat where rownum = 1)
3  and (n.name = 'consistent gets' or n.name like 'table fetch%');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                         344
table fetch by rowid                                                     23
table fetch continued row                                                 0

SQL> select col_vc93 from many_columns_table where rowid='AAANT0AAHAAAACvAAH';

COL_V
-----
J7N

SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
2  and s.sid=(select sid from v$mystat where rownum = 1)
3  and (n.name = 'consistent gets' or n.name like 'table fetch%');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                         345
table fetch by rowid                                                     24
table fetch continued row                                                 0

SQL> select col_vc94 from many_columns_table where rowid='AAANT0AAHAAAACvAAH';

COL_V
-----
VXM

SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
2  and s.sid=(select sid from v$mystat where rownum = 1)
3  and (n.name = 'consistent gets' or n.name like 'table fetch%');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                         347
table fetch by rowid                                                     25
table fetch continued row                                                 0

SQL> select col_vc94 from many_columns_table where rowid='AAANT0AAHAAAACvAAH';

COL_V
-----
VXM

SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
2  and s.sid=(select sid from v$mystat where rownum = 1)
3  and (n.name = 'consistent gets' or n.name like 'table fetch%');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                         349
table fetch by rowid                                                     26
table fetch continued row                                                 0

SQL> analyze table many_columns_table compute statistics;

Table analyzed.

SQL> select blocks, num_rows, chain_cnt from user_tables where table_name  ='MANY_COLUMNS_TABLE';

BLOCKS   NUM_ROWS  CHAIN_CNT
---------- ---------- ----------
 11117      50640          0

SQL> 

I have run the same test on 16K blocks. I confirm that the Chain_Count is 0 so the rows I fetch are not chained across blocks.


.

UPDATE : For more discussions on this issue follow the Oracle-L thread

.

.
Second UPDATE : More tests at Some MORE Testing on Intra-Block Row Chaining.
.
.
.

Indexes Growing Larger After Rebuilds

Here's a note by Richard Foote about Indexes that can grow *larger* when rebuilt.