user5716448 wrote:I can't imagine any performance gain at all, unless you have chained rows. And unless you have individual rows whose length exceeds the size of a block, you shouldn't have chained rows. (yes, you can have chained rows but that is addressed in a different manner). So, if the smallest unit of I/O is a block, and an entire row is on that block, how are cpu cycles saved by having "more frequently used columns" at a lower offset in the block (and remember, that is a lower offset only in relation to the other columns of that particular row) ?
Using oracle 18.104.22.168 and it has been suggested to us that more efficient to have most freqeuntly used columns at start of table as use less cpu cycles.
Currently the keys of the table are listed first but as existing live tables wonder about implicatuions if we tried to rearraneg ordr of columsn in table
1) How big is the performance gain really?
2) Can you easily rearrange order of columns in table or do you have to drop and recreate the table?
user5716448 wrote:Ask the "perf team" to quit thinking and produce a reproducible demonstration. At the very least, ask them to produce a technical explanation of why they "believe" this.
Thanks for replies.
It is a data warehouse and have a large number of columns that are often NULL since it is a dimension with 3 levels in the hierarchy - these are at start of table after the key columns.
Order of columns
Table has pk at start (dimension_key)
all columns for lowest level of dimension (30 columns) these can be null
then 20 columns which relate to next level in the dimension - again can be null
then 10 columns for highest level of dimension - these always populated.
Oracle Warehouse builder tool creates the tables based upon the dimension definition so has ordered the columsn in this way.
Perf team think that because columns at the end of table often accessed that more cpu is being consumed by having these at end rather than start.
Saying twice the cpu used accessing these columsn at end of table compared to accessing columns at start of table.And why does it take more cpu to caclulate the offset to column 512 than it does to calculate the offset to column 1?
Any way to avoid this without having to reorg tables and fact twice cpu used what impact would this have on overall query speeds e.g. would there be a danger in having the more populated columns at start of table.There's nothing to avoid except people who make performance claims based on .... fantasy.
Oracel warehouse builder has determined the oredr and I'm assuming it puts the key columns first and the null columns first in table order for a reason to make it more efficeint?
Large dimension table 8 million rows
Large fact table expect have 3 billion rows when finished - 1.5 billion at moment.
Edited by: user5716448 on 13-Apr-2013 05:29
1) How big is the performance gain really?It is going to absolutely depend on your specific circumstances so test it.
2) Can you easily rearrange order of columns in table or do you have to drop and recreate the table?Drop and recreate.
rp0428 wrote:That's the type of compression trick that Oracle uses in a few places, but not for null columns. The only special case for null columns is that Oracle doesn't record them when they're at the end of the row; otherwise a null column is represented by a single byte (0xff).
Each column's data is preceded by a length byte so to get to column 2 you have to begin at the start of the row and add the length of column 1. Then to get to column three you need to add the length of column 2, and so on. There is an exception for consecutive null columns where Oracle encodes how many there are and can them skip all of them at once.
Jan-Marten Spit wrote:Got it. Seems that I used to know that ...
"At the very least, ask them to produce a technical explanation of why they "believe" this."
"Is it going to more expensive to calculate an offset to a location closer to the start of the row than to a location further into the row?"
"And why does it take more cpu to caclulate the offset to column 512 than it does to calculate the offset to column 1?"
because Oracle does not maintain a per-column ofsset relative to the start of the row.
oracle calculates the start of column X by adding the widths of columns 0 to X-1.
as i stated elsewhere in this thread, the CBO CPU cost actually includes this penalty.
eg, the CPU cost of filtering 1E6 rows on column 200 will be higher than filtering 1E6 rows on column 1, and the same holds for the actual CPU usage.SQL.
now i would agree that in most cases the effect is practically negligable. but i have also seen cases where it (reordering the columns in a table) reduced CPU usage significantly. in order to do that, you must be so lucky that the developers created column-position independent