I know I haven’t been very good about posting on the blog or replying to questions lately (and a big thank you to anyone who has answered some of the recent questions correctly), but tonight is a Friday night, and I have a few moments to spare, so here’s a question prompted by a recent comment on OTN.
I have a table declared as follows (and the ellipsis means repeat the column definitions according to the simplest and most obvious pattern to give you 1,000 columns all of type number(1)):
create table t1 ( col000 number(1), col001 number(1), col002 number(1), ... col997 number(1), col998 number(1), col999 number(1), constraint t1_pk primary key (col000) ) ;
I have one row in the table.
How many row pieces might that row consist of ?
Update (Saturday Morning)
We have some good responses so far, and a general conclusion that the row might be stored as 1, 2, 3, or 4 row pieces depending on the number of trailing null columns. This is true, but isn’t a complete list of the possibilities – so I’m looking for a little more.
A few highlights in the responses – it was definitely a good move from Jithin Sarath to do a block dump and view the results; and at one point he also created the row with just the first and last values set – which is also an interesting thing to dump.
VishalDesai also took an interesting approach, in effect following an important related topic. If you have to read a single row that is chained from multiple row-pieces, what do the workload stats look like. (There’s an interesting oddity there as well – but there’s also a lot of variation across versions and the results are dependent on access path too.)
My reference: c_wide_table_2.sql
For the rather staggering answer, please read through the comment trail.
Update April 2017
See also this note for further details, and follow the pingback for an observation about improvements (fixes) in 12.2