Thanks. I had intended to rebuild the table due to the 600 columns reordering the columns from most populated to least populated when i noticed this issue while attempting to query the table to figure out which columns were used. I cannot drop columns that are not used since the application dynamically references them on occassion. I have done the column reorg before and have seen up to 80% reduction in storage due to the unused columns being at the end of the row and oracle not writing nulls when they are at the end of the table. I used the latest stats run and and some client input on which columns were used and kicked off a rebuild. I didn't know that the database did single block reads for chained rows. I started an analyze to count the chained rows but it would have run for days so after a half hour i killed it and checked the size of the chained_rows table which was about 50mb even though the table scan was less than .2% complete indicating significant chaining which i believe was the root cause. After rebuilding the table the size was reduced by 67.8%, and the index builds took between 1 and 11 minutes each, performing full scans using scattered reads, direct path reads. Previously full scans were taking over 4 hours. Thanks to everyone for thier input. Ken On Wed, Aug 18, 2010 at 10:14 AM, Mark W. Farnham <mwf@xxxxxxxx> wrote: > <snipped to fit> > > To repeat myself, this is probably due to having 600 columns. Tanel > suggested examining checking the increase in continued reads, which will > provide you evidence. > > > > As a practical manner, you can also check: > > > > select c1,c2,…,cn from tab > > > > where c# is the column name for column position left to right with n large > enough so that you have an unindexed column set (so that you still get a > full table scan). > > > > Then, if you don’t really need all the columns from the table, and none of > the columns you need for a particular table are beyond column 254 > > or reach beyond row column values not stored within the first block piece > of an individual row, you could restore multiblock reading for a particular > query. You may provide a view that retrieves columns that most often fit in > the first row piece. Of course updates may move things around and Oracle > posts no guarantee that column values are stored left to right in the block > when row chaining is required. > > > > (I wrote 254 rather than 255, because if I remember correctly when a row is > continued because of being over 255 columns you only get 254 in the first > block and the row continuation location is in the array slot for the > 255thcolumn. I could be wrong about that. Since you already have 600 columns, > it > is moot. You’ve got three row pieces per row unless you have a pile of > trailing null column values, and even then I’m not sure and I’d have to test > it.) > > > > After you do the entity relationship requirements for a logical schema > design, you also need to do physical schema design if performance > requirements are among your considerations. One of the bits of physical > design for entities that require more columns than the RDBMS in question > stores conveniently in the first block (for block oriented storage models) > is putting the most commonly required column values left most in the table > definition and putting most likely to be unreferenced and null right most if > a single row is unlikely to fit in the first storage block. While this is no > guarantee, since updates may force a column value into a later rowpiece > block, it is useful statistically. For short rows (in columns or actual sum > of vsize of columns) this only costs you the extra storage from having null > values non-last in a row. When there is mnemonic value to having certain > columns in a certain order and grouped together in the table definition you > may face a trade-off, but that is rarely a practical consideration and I > would tend to group columns favoring any such mnemonic value. > > > > For a particular case, you can determine whether a rebuild rearranging the > column order might be worth the effort. But you need to know the details of > the functional use of the object. Sometimes it may even be worthwhile to > divide a logically cohesive entity’s row definition into multiple physical > table pieces linked by a common unique id column set. Existing select * > queries can be serviced by a view while the rest of the queries are > repaired, and you can make sure you follow all the rules so that it is an > updatable view for existing dml queries. I’m always a bit skeptical about > the logical schema design when a table has this many columns, but of course > that ship may have sailed before you arrived and it is not my intention to > address the question of whether an entity really needs 600 columns, but > rather, to help you with remediation possibilites. > > > > If > > > > select c1, c2, …, cn (etc) restores multiblock read, please be sure to > compare the total read time required versus the single block read time for > the same using complete rows before embarking on remediation efforts. > Depending on the hierarchy of your cache structures down to the “spinning > rust” your mileage may vary. Of course if you mostly only need column values > that are stored most often in the first row piece, the big value of not > reading the other row piece blocks is avoiding the LIO work. Right Kevin? So > if you reimplement on exadata and convert your queries to only use the > columns you need, that would be another way to go. Quite a bit more > expensive experiment though. > > > > Good luck. > > mwf > > <snip> > > >