On Asking, Chaining, and Decoding

Our technologist answers questions about questions, chained rows, and decoding.

November/December 2002

How do you handle the questions on Ask Tom (asktom.oracle.com)? How many questions do you get, and how do you pick the ones for the magazine?

That's a good question. From January 2000 through July 2002, I received and answered more than 16,000 questions; that's an average of 18 a day, every day of the year. When I'm taking questions, I typically set up the application to allow 10 questions to be entered. After that, the system automatically displays the "Sorry I have a large backlog" message that you see frequently. I don't like to have more than 10 or so questions in the queue, and with this system I am able to answer the questions in a timely fashion. I answer almost every question myself, asking for help from gurus in particular technologies from time to time (I always refer to them in the answer when I do that). Some days, when I am traveling or on site with a customer, or when I'm taking a day off, I take no questions. Other days, I might take 40 or so. It all depends on what else is going on.

In order to select the questions for Oracle Magazine, I actually let you do the picking. My rating system finds the most highly rated questions, giving precedence to articles with the most actual ratings. For example, when I was preparing to write this column, the following section on chained rows had 10 "reviews" for an average rating of 5 stars in each (a perfect score!). That's why I picked that particular question (and the others, which also had many reviews and high reader ratings).

Once we select the articles for printing, we "pretty them up" by editing them—technically and grammatically. Then they get printed in the magazine.

I have a table with 25-percent chained rows. I am trying to determine the impact of this chaining and rebuild the table if needed. To determine whether I need to reorganize this table, I did the following test:

SQL> select table_name, num_rows, chain_cnt
   from dba_tables
where owner = 'DB_USER' and table_name = 'MYTABLE';
TABLE_NAME      NUM_ROWS     CHAIN_CNT
------------    ----------   ----------
MYTABLE         1061399      277139
SQL> set autotrace traceonly stat;
SQL> select /*+ full */ CNY#, PAYMENTDATE from DB_USER.MYTABLE ;
1064428 rows selected.
SQL> select a.name, b.value
  from  v$statname a, v$mystat b
  where a.statistic#=b.statistic#
    and name = 'table fetch continued row';
NAME                          VALUE
---------------------------   ---------------
table fetch continued row     99

There's a lot to be learned from this question about the manner in which data is processed.

I'll take a guess that almost 100 percent of your chained rows are actually migrated, which is a special kind of chaining.

My other guess would be that CNY#, PAYMENTDATE are near the "front" of your table (because they come first in the CREATE TABLE statement). To see why I'm making these guesses, you need a little background first.

You migrate a row when an update to that row would cause it not to fit on the block anymore (with all of the other data that exists there currently). A migration means that the entire row will move and just leave behind the "forwarding address." So the original block just has the ROWID of the new block and the entire row of data is moved.

Then there is the true chained row (although a migrated row is a specialized case of a chained row). In the case of a true chained row, part of the data of a row is on one block and part of it is on another block. Therefore, instead of just having a forwarding address ROWID on one block and the data on another (that's a migrated row), you actually have data on two or more blocks.

A full scan on a table actually ignores the forwarding addresses (the head rowpiece for a row). As the scan continues, it'll eventually get to that row, so it can ignore the forwarding address and just process the actual data row when it gets there. Hence, in a full scan, migrated rows don't cause any extra work. Sure, the forwarding address is consuming a couple of bytes on the block, so it is overhead, but frankly, it's meaningless.

When we INDEX READ into a table, a migrated row will cause additional input/output (I/O) operation. That is because the index will tell us "go to file X, block Y, slot Z to find this row." But when it gets there, a message says "go to file A, block B, slot C to find this row." You have to do another I/O operation (logical or physical) to find the row.

True chained rows affect things differently. Here, it depends on the data you need. Suppose you had a row with two columns that was spread over two blocks, and you executed the following query:

select column1 from t;

If column1 is the "first" column, the query would not cause any table fetch continued row . The query would not actually have to get column2 ; it would not follow the chained row all the way out. On the other hand, if you ask for:

select column2 from t;

you would, in fact, see a table fetch continued row.

This behavior is so predictable that setting up an illustrative example is easy. In the following example, I am using an 8K block size, so if you use something different, you'll have to adjust the column sizes appropriately (or add more columns, as the case may be).

SQL> create table t ( x int primary key, a char(2000),
 b char(2000), c char(2000), d char(2000), e char(2000) );

That is our table. The char(2000)s will let us easily cause rows to migrate or chain. I used five columns— a, b, c, d , and e —so that the total row size can grow to about 10K (bigger than my 8K block), ensuring that I can truly chain a row. (You don't need to see a, b, c, d , and e ; you just fetch them. Also, they are really wide, so I'll suppress their display.)

Now I insert values into the table:

SQL> insert into t(x) values ( 1 );
SQL> insert into t(x) values ( 2 );
SQL> insert into t(x) values ( 3 );
1 row created.
SQL> commit;
Commit complete.

The following queries show the result of the insert:

SQL> select * from t;
     X
----------
     1
     2
     3
SQL> select a.name, b.value
 2 from v$statname a, v$mystat b
 3 where a.statistic# = b.statistic#
 4 and lower(a.name) like '%' || lower('&1')||'%'
 5 /
NAME                         VALUE
---------------------------  ---------------
table fetch continued row    0

That result is expected. The rows came out in the order I put them in (this query used a full table scan, and it processed the data as it found it). Also expected is that the table fetch continued row (TFCR from now on) is zero. This data is so small right now that I know all three rows fit on a single block. There's no chaining.

Now I want to demonstrate the migration issue and how it affects (or rather doesn't affect) the full scan. I'll do some updates in a specific way:

SQL> update t set a = 'x', b = 'x', c = 'x' where x = 3;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t set a = 'x', b = 'x', c = 'x' where x = 2;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t set a = 'x', b = 'x', c = 'x' where x = 1;
1 row updated.
SQL> commit;
Commit complete.

Note the order of the updates. I did the last row first and the first row last, so note the following results:

SQL> select * from t;
     X
----------
     3
     2
     1
SQL> select a.name, b.value
 2 from v$statname a, v$mystat b
 3 where a.statistic# = b.statistic#
 4 and lower(a.name) like '%' || lower('&1')||'%'
 5 /
NAME                         VALUE
---------------------------  ---------------
table fetch continued row    0

Interestingly, the rows came out "backwards." The backwards query result is because I updated row 3 first. The row data in row 3 did not have to migrate, but it filled up block 1. I then updated row 2. With row 3 hogging all of the space in block 1, row 2 had to migrate to block 2. I then updated row 1, and it migrated to block 3. So I migrated rows 2 and 1, leaving row 3 where it started (you can analyze the table to confirm this yourself).

The full table scan found row 3 on block 1 first, row 2 on block 2 second, and row 1 on block 3 third. It ignored the head rowid piece on block 1 for rows 1 and 2 and just found the rows as it scanned the table. That is why the TFCR still equals zero.

So, let's see how a migrated row affects the TFCR:

SQL> select * from t where x = 3;
     X
----------
     3
SQL> select a.name, b.value
 2 from v$statname a, v$mystat b
 3 where a.statistic# = b.statistic#
 4 and lower(a.name) like '%' || lower('&1')||'%'
 5 /
NAME                         VALUE
---------------------------  ---------------
table fetch continued row    0

I'm using the rule-based optimizer (RBO), and RBO is notorious for being index-happy. Hence, this was an index range scan and table access by ROWID. I didn't increment the TFCR yet, since row 3 isn't migrated.

The following queries and results show that row 1 is migrated, and there it is:

SQL> select * from t where x = 1;
     X
----------
     1
SQL> select a.name, b.value
 2 from v$statname a, v$mystat b
 3 where a.statistic# = b.statistic#
 4 and lower(a.name) like '%' || lower('&1')||'%'
 5 /
NAME                         VALUE
---------------------------  ---------------
table fetch continued row    1

Using the index, I forced a TFCR. Now let's see the effect of a chained row:

SQL> update t set d = 'x', e = 'x' where x = 3;
1 row updated.
SQL> commit;
Commit complete.

Now row 3 no longer fits on block 1. With columns d and e set, the row size is about 10K. It is truly chained:

SQL> select x, a from t where x = 3;
     X
----------
     3
SQL> select a.name, b.value
 2 from v$statname a, v$mystat b
 3 where a.statistic# = b.statistic#
 4 and lower(a.name) like '%' || lower('&1')||'%'
 5 /
NAME                         VALUE
---------------------------  ---------------
table fetch continued row    1

If you fetch something from the "head" of the table, it will not cause a TFCR. Column a is on the head rowpiece block. There is no extra I/O required to get it.

The following queries columns d and e :

SQL> select x, d, e from t where x = 3;
     X
----------
     3
SQL> select a.name, b.value
 2 from v$statname a, v$mystat b
 3 where a.statistic# = b.statistic#
 4 and lower(a.name) like '%' || lower('&1')||'%'
 5 /
NAME                         VALUE
---------------------------  ---------------
table fetch continued row    2

Going after columns d and e via the index does increment the TFCR. I had to put the row back together from its head to its tail to get that data.

Interestingly, the full scan is now affected as well:

SQL> select * from t;
     X
----------
     3
     2
     1
SQL> select a.name, b.value
 2 from v$statname a, v$mystat b
 3 where a.statistic# = b.statistic#
 4 and lower(a.name) like '%' || lower('&1')||'%'
 5 /
NAME                         VALUE
---------------------------  ---------------
table fetch continued row    3

The TFCR incremented here because of row 3. I had to assemble row 3 in its entirety to get the trailing columns. Rows 1 and 2, even though they are chained—migrated, really—don't add to the TFCR because I did a full table scan.

Continue to look at the effects of my queries on TFCR:

SQL> select x, a from t;
     X
----------
     3
     2
     1
SQL> select a.name, b.value
 2 from v$statname a, v$mystat b
 3 where a.statistic# = b.statistic#
 4 and lower(a.name) like '%' || lower('&1')||'%'
 5 /
NAME                         VALUE
---------------------------  ---------------
table fetch continued row    3

There is no TFCR, since I didn't have to assemble row 3; I just needed the first two columns. In the following, however, I need row 3:

SQL> select x, d, e from t;
     X
----------
     3
     2
     1
SQL> select a.name, b.value
 2 from v$statname a, v$mystat b
 3 where a.statistic# = b.statistic#
 4 and lower(a.name) like '%' || lower('&1')||'%'
 5 /
NAME                         VALUE
---------------------------  ---------------
table fetch continued row    4

By asking for columns d and e , I did have to assemble row 3.

There you go. You most likely have only migrated rows, but even if they are truly chained, the columns you are selecting are at the front of the table.

Migrated rows affect OLTP systems that use indexed reads to read singleton rows. In the worst case, you can add an extra I/O operation to all reads, which would be really bad. Truly chained rows affect everyone.

So, how can you decide if you have migrated or truly chained rows? The answer is in the following:

SQL> select count(e) from t;
COUNT(E)
----------
      1
SQL> select a.name, b.value
 2 from v$statname a, v$mystat b
 3 where a.statistic# = b.statistic#
 4 and lower(a.name) like '%' || lower('&1')||'%'
 5 /
NAME                         VALUE
---------------------------  ---------------
table fetch continued row    5

Counting the last column in that table will force me to construct the entire row. You can see that the count did add 1 TFCR.

In the following, I count chained rows:

SQL> analyze table t compute statistics;
Table analyzed.
SQL> select chain_cnt from user_tables
 2 where table_name = 'T';
        CHAIN_CNT
----------
        3

Three rows are chained. And since the count (3) encountered only one TFCR (for the chained row), but the chain count is 3, that means that the other 2 rows are migrated.

Please take a look at the following:

SQL> create table foo (f1 number);
Table created.
SQL> insert into foo values (-3350);
1 row created.
SQL> insert into foo values (-2200);
1 row created.
SQL> select min(f1) from foo;
        MIN(F1)
----------
        -3350
SQL> select max(f1) from foo;
        MAX(F1)
----------
        -2200
So far, so good.
SQL> select min(decode(f1, -1001, null, f1)) from foo;
MIN(DECODE(F1,-1001,NULL,F1))
-----------------------------------------
-2200
SQL> select max(decode(f1, -1001, null, f1)) from foo;
MAX(DECODE(F1,-1001,NULL,F1))
-----------------------------------------
-3350

It appears that the query is taking the absolute value of field f1 . It appears that MIN and MAX are not selecting the correct numbers when used with DECODE. Is this a bug or a feature?

It's definitely a feature. The following example will help you see why:

SQL> create table t ( x int );
Table created.
SQL> insert into t select rownum - 10
 2 from all_users where rownum < 20;
14 rows created.
SQL> select x, decode( x, -1000, null, x )
 2 from t order by 1;
  X          DECODE(X,-1000,NULL,X)
-----------  -------------------------------
  -9            -9
  -8            -8
  -7            -7
  -6            -6
  -5            -5
  -4            -4
  -3            -3
  -2            -2
  -1            -1
   0             0
   1             1
   2             2
   3             3
   4             4
14 rows selected.

This result should provide a clue: select x creates a right-justified result (it's a number) and select decode( x, -1000, NULL, ... ) creates a left-justified result (it is apparently a string).

Now I sort by the second field instead:

SQL> select x, decode( x, -1000, null, x )
 2 from t order by 2;
  X          DECODE(X,-1000,NULL,X)
-----------  -------------------------------
  -1            -1
  -2            -2
  -3            -3
  -4            -4
  -5            -5
  -6            -6
  -7            -7
  -8            -8
  -9            -9
   0             0
   1             1
   2             2
   3             3
   4             4
14 rows selected.

Notice how it sorts as a string would. That is because DECODE is returning a string! What happens is that DECODE is peeking at the first returnable value— NULL —in this case, to decide the return datatype for this DECODE call ( DECODE is massively "overloaded"). Well, according to DECODE, NULL defaults to a return datatype of VARCHAR2 , so that is what DECODE decides it will return.

How do you fix it? Be explicit.

SQL> select x, decode( x, -1000, to_number(null), x )
 2 from t order by 2;
  X          DECODE(X,1000,TO_NUMBER(NULL),X)
-----------  -------------------------------
  -9            -9
  -8            -8
  -7            -7
  -6            -6
  -5            -5
  -4            -4
  -3            -3
  -2            -2
  -1            -1
   0             0
   1             1
   2             2
   3             3
   4             4
14 rows selected.

Now they are numbers again, sorted as numbers would be sorted.