Performance Question

[dunc0029]

Which, in your experience, will be faster?

table1 has 500 fields ( field1, field2, .... )
table2 has a subset fields from table1 ( field1, field2 )

Same # of records.

select field1, field2 from table1
select * from table2

Thanks in advance!

 

[Farab]

As far as Oracle is concerned,
select field1, field2 from table1
, is going to be more expensive (slower). The reason is even though you only want two fields Oracle will get the whole row anyway. Only selecting 2 fields will save network resources if it's a client running the query, because only the data for those fields will be returned.

Indexes will make a difference. If table1 has an index of field1, field2 then query #1 will be just as good if not faster than query #2. (The data will be read from the index, not the data rows)

Then again, why would you have a second table that is only a subset of another, but that's a different story.

 

[dunc0029]

Thanks a lot - you were kinda of settling a bet! I just thought of a great point on this. Basically, this is how an index works - one field and a pointer to the record on disk. Indexes are faster because they are smaller files to access a record directly.

Last Friday, I had someone trying to convince my boss that it wouldn't matter. We had split a table for performance reasons and this person claimed it would not affect performance.