Re: Sotirng data in row versus storing the same in column

On Thu, 16 May 2002, arijit_at_lucent.com wrote:

[...]

> In column format - I will be using the normal RDBMS format - with
> the only exception that I will need to put additional columns in
> extended tables having an identifier between the same rows in the
> extended table. Regards, Arijit

Do you think this will be faster or something? Lets say you have 100 columns, with 99 indexes. This way will have an index for every column, but every column will only have one row (?). In Oracle, at least, 99 blocks would be used to store these 99 single values along with their rowids. If you stored it in relational form, row form as you say, a very small number of blocks (two in my example below) would be used to store this one index.

Lets say each column takes 4 bytes. Lets say a block is 2K. Lets say the rowid is 10 bytes. Then, 2000/40 = 50. You would have to have more than 50 rows in your index before one extra block would be used in your relational index (Not totally true with headers and other stuff but close enough for argument). In your newfangled way, 99 2K blocks would be used to store your 99 indexes. (To tell you the truth, with 100 rows, an index would even make sense. Oracle could scan much faster than reading both index and then table)

Now, remember that the database will read an entire block to get at the data in that block. It will have to do almost exactly the same work to read the two, 100 value index as reading any of your 99 one value indexes. Do you really think you are speeding things up? You do realize that any dml on this newfangled table would have to maintain all these 99 indexes don't you?

Now, lets say you have 1 million values. Are you going to have multitudes of tables all with one row but 100's of columns, all indexed? Do you really think this is speeding things up? Are all of these tables going to be linked together by the same id? Whew!

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.