let us see.
would we rather
a) use a 500 column table which will be chained, but probably onto the same block and that can be put back together by simply traversing a row piece pointer found at the end of the first chain...
b) use two tables such that we would end up still with the logical equivalent of a chained row, but one that would require an index unique scan to find the second bit?
In the case of A, if you don't need the second set of columns, we'll never traverse to them. They would be typically on the same block as the original row. But if you did need them - we'd just dereference a row piece pointer.
In the case of B, if you don't need the second set of columns, we'll never traverse to them. They would be typically on the same block as the original row. But if you did need them - we'd
walk a index to find a rowid to find the rowchained rows are not in themselves a performance problem - IF THEY ARE UNAVOIDABLE.
Chained rows can be a performance problem - when they can be solved, avoided. If you have a table with more than 255 attributes - you will have chained rows, period. If you have a table whose row doesn't fit on a single block, you will have chained rows - period. Chained rows are OK if your design demands them.
Migrated rows, a special case of chained rows, rows that have moved because they no long fit on the block they were inserted into - they can be a performance issue and they can be 'fixed' permanently by thinking about your storage related parameters.
However, you have simply a chained row that cannot be avoided.
ps$tkyte%ORA11GR1> create cluster btree_cluster ( id number ) size 4096
2 /
Cluster created.
ops$tkyte%ORA11GR1> create index btree_cluster_idx on cluster btree_cluster
2 /
Index created.
ops$tkyte%ORA11GR1> create table t1 ( id number primary key, data char(2000) )
2 cluster btree_cluster(id)
3 /
Table created.
ops$tkyte%ORA11GR1> create table t2 ( id number primary key, data char(2000) )
2 cluster btree_cluster(id)
3 /
Table created.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> @at
ops$tkyte%ORA11GR1> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA11GR1> set autotrace traceonly explain
ops$tkyte%ORA11GR1> select * from t1, t2 where t1.id = t2.id and t1.id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3173799388
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Co
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4030 |
| 1 | NESTED LOOPS | | 1 | 4030 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 2015 |
|* 3 | INDEX UNIQUE SCAN | SYS_C0014438 | 1 | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 2015 |
|* 5 | INDEX UNIQUE SCAN | SYS_C0014437 | 1 | |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."ID"=1)
5 - access("T1"."ID"=1)
that would be less efficient to retrieve the data - two index scans instead of just one.