RE: Reorg to put columns in order

Steve Adams had an article on his website about the cost of retrieving columns to the right years ago. I think he had recommended placing the most retrieved columns at the start of the row. If the article is still posted it would be interesting to compare the two. Another thing to add to my endless list.

• Mark D Powell -- Phone (313) 592-5148

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Gints Plivna Sent: Tuesday, October 23, 2007 10:40 AM To: ric.van.dyke_at_hotsos.com
Cc: Jacob.van.Zanen_at_rabobank.com; oracle-l_at_freelists.org Subject: Re: Reorg to put columns in order

OK according to presentation by Joze Senegacnik he gave in OOW 2006: "It is interesting that the cost of LIO depends of the position of the column retrieved."
He shows in his presentation's example that query elapsed time increases for the 250th column compared to 1st column. 250th column of course is just an example. The actual example forces full scan on the table and the growth is ~50% for 250th column compared to 1st column. Also CBO increases CPU cost as the column number grows. Unfortunately it seems that these presenations are not available for download any more, so you may ask details to the author himself :) He is (at least WAS) member of this list.

However the main question remains - do these arguments justify the cost of reorganization? (somehow I doubt that these arguments even appeared in the real discussion :)

Gints Plivna
http://www.gplivna.eu

2007/10/23, Ric Van Dyke <ric.van.dyke_at_hotsos.com>:
>
>
>
>
> Sorry that I'm coming to this discussion late but I do want to point
out that column order actually does mater. However it's not in the way these folks are thinking. When the data is stored in the block, each rows data is stored in the column order of the table definition. Why this is important is that trailing NULL columns will actually take up NO SPACE in the row, however NULL columns between non-null columns will take up a byte of storage. OK so one byte isn't a big deal, unless you are talking about millions of rows with maybe several null columns within each row. Now it may actually add up to something.
>
>
>
> The reality is that column order does mater from a space management
point of view, if you have columns that are nearly always null, then they should be at the end of the table definition. If not you are "wasting" some space within each row. Is it enough to make a difference? That will be your call.
>
>
>
>
> -----------------------
>
> Ric Van Dyke
>
> Hotsos Enterprises
>
> -----------------------
>
>
>
> ________________________________

>
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zanen van, J
> (Jacob)
> Sent: Monday, October 22, 2007 2:00 AM
> To: oracle-l_at_freelists.org
> Subject: Reorg to put columns in order
>
>
>
> Hi All,
>
>
>
> I'm having a bit of an issue with our designers/developers for our so
called "datawarehouse"
>
> Columns are added to tables every so often based on requirements from
the business. They however insist on doing a reorg to get the columns in a certain order so when they pull them up in BO the columns show up in an order that makes sense to them. I have raised my doubts about this procedure as it takes a 2 second job and squeezes it in a 7 hour job.
>
> I can see from a chained/migrated row point of view that there might
be a valid reason for it but since they have always done it this I see no way for me to find out how bad this is going to be. I suspect for most of these tables it won't make much difference as they are not filled with data for historical records AFAIK.
>
> My solution for them was to create a view on top of the table but they
rejected the idea and insist this is the way to go.
>
> Anybody have any insight for me.
>
>
>
> Thnx
>
>
>
> Jack
> ________________________________

>
> This email, including any attachments, may be confidential or
privileged, and is sent for the personal attention of the intended recipient. If you have received this email in error, please delete it immediately. The views expressed are not necessarily those of the Rabobank Group. The Group is not liable for the effects of any virus which may be contained in this email.
>
> If this email contains marketing material and you do not wish to
receive such material by email in future, please reply to this email and place the words "Remove My Details - Electronic Messages" in the Subject Header.
>
> The Rabobank Group
>
> Australia: 1800 025 484
> New Zealand: 0800 500 933
> ________________________________

>
>
>

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l