2

I have a table in my oracle db with 100 columns. 50 columns in this table are not used by the program accessing this table. (i.e. the select queries only select the relevant columns and NOT using '*')

My question is this : If I recreate the same table with only the columns I need will it improve queries performance using the same query I used with the original table (remember that only the relevant columns are selected)

It is well worth mentioning the the program makes these queries a reasonable amount of times per second!

P.S. : This is an existing project I am working on and the table design was made a long time ago for other products as well (thats why we have unused columns now)

Darida
  • 196
  • 1
  • 8
giorashc
  • 13,691
  • 3
  • 35
  • 71

1 Answers1

3

So the effect of this will be that the average row will be smaller, if the extra columns have got data that will no longer be in the table. Therefore the table can be smaller, and not only will it use less space on disk it will use less memory space in the SGA, and caching will be more efficient.

Therefore, if you access the table via a full table scan then it will be faster to read the segment, but if you use index-based access mechanisms then the only performance improvement is likely to be through an improved chance of fetching the block from cache.

[Edited]

This SO thread suggests "it always pulls a tuple...". Hence, you are likely to see some performance improvement, not sure major or minor, as already mentioned.

Community
  • 1
  • 1
David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • The data will not be loaded to the memory/SGA anyway, because we never queried that data at all. Therefore, memory/cache/SGA all are out of question. I like to emphasize again, "remember that only the relevant columns are selected", as stated by OP. – Adeel Ansari May 22 '12 at 09:42
  • 1
    The data is fetched from disk into the Oracle System Global Area's (SGA) buffer cache. The smallest unit in which it is read from disk is the block, which is generally either 4kb, 8kb or maybe 16kb. So, whether you read one column or all columns the entire block might need to be physically read from the Oracle data files into the SGA. – David Aldridge May 22 '12 at 09:47
  • I have deleted my answer, buddy. And now going to complement yours if you mind you can alter it later. Thanks for the info, I didn't know that. – Adeel Ansari May 22 '12 at 09:57
  • I think that the other thread really means that it always pulls at least a tuple (and I commented to mention two special cases in Oracle), but in any case the minimum amount of data read is the block. In the case of a chained or migrated row in Oracle multiple blocks might have to be read to retrieve a single row. – David Aldridge May 22 '12 at 10:11