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.