Oracle 11.2.0.4 running on Linux
My data warehouse has a 5TB interval-list partitioned table with over 350 columns. Full partition scans of the table are becoming increasingly costly, due to the time spent performing single-block sequential reads, presumably to fetch the chained rows. I know that the number of columns in the table (>255) is usually the cause of chained rows. However, changing the structure of the table is not an option at this point.
What is strange is that the table in the test environment has far more chained rows than the same table in the production environment, despite both being loaded with the same data. I'd like to understand why the table in one environment is more prone to chained rows than another, so I can reduce the occurrence of chained rows.
I tried to recreate the chained rows by copying data to a new table as follows :
- Identify a partition of table T1 containing 80,000 rows
- Count chained rows in partition of T1 – 3,000 chained rows found (ANALYZE TABLE T1 SUBPARTITION(x) LIST CHAINED ROWS)
- Copy contents of partition to table T2 (CREATE TABLE T2 AS SELECT * FROM T1 SUBPARTITION (x))
- Count chained rows in T2 – 80 chained rows found
- Truncate original partition of T1, and reload data from T2 (INSERT INTO T1 SELECT * FROM T2)
- Count chained rows in T1 – 3,000 chained rows found
What’s puzzling me is that the original table T1 has such a large number of chained rows, even when reloaded, whereas the new table T2 has only a small number of chained rows, despite containing the same data. I’ve compared the various storage attributes for the two tables, and can see no difference. I even created T2 as a partitioned table, but again no difference.
What other factors might affect the number of chained rows in T1?