Resequence column order in a table
Radhakrishnan A, December 11, 2001 - 1:39 am UTC
Thanks tom. I find it is useful for my problem.
Degrade Performance
Prince, December 11, 2001 - 9:43 am UTC
Although your answer is right. But I think the view will degrade performance if the size of the table is very large. So there should be some other way, if Oracle is smart..
December 11, 2001 - 2:29 pm UTC
The view will not degrade query performance. It REALLY TRULY does not have anything to do with the size of the table either (that part of your comment is totally inaccurate -- tell me, how could a VIEW affect the performance of a query as the table gets larger????? As long as the plans are the same - and they will be -- it cannot)..
So, I think we are sort of smart in this way. Benchmark it and see. The only thing the view does is add a TINY extra step to the parse, one that you will not notice or detect (and certainly isn't in any way affected by the SIZE of the table).
So, this is the correct way to do it, will be the way to do it, does not affect query plans AT ALL (and that is what would affect performance, a changed plan).
Just yet another case of fear of the unknown, or believing in myths (the myth being "I've heard views are evil").
Not that the order of columns should even be RELEVANT, not that your applications should even count on their order being in some fixed way (by the way).... This is EXACTLY what views are designed for. Use them (please -- you pay for these tools, just use them).
order
j, December 11, 2001 - 4:44 pm UTC
I thought the order of columns(tables not views) had a direct impact on selects,index performance ie: more queried columns to the top, indexed columns at the top. We resequence all the time ( the old fashioned way ,create newtab as select <new order> from tab - drop tab rename newtab tab - reconstraint). Lots of tables, lots of resequencing. Have we been mistaken or has something changed that we missed taking advantage of?
December 11, 2001 - 7:32 pm UTC
If and only if the row is chained (too big to fit on a block) would this matter -- otherwise all of the data is there on the block, we read the block -- we have everything we need. Makes no difference really if its the last column or the first (unless its chained, then you might notice a difference).
Indexed columns -- that really makes no difference, they are copied into the index, the table isn't relevant (or even necessary) in a index range/unique/whatever scan.
You have most likely been wasting CPU cycles doing this. One way to find out for sure -- benchmark yourself. Save history (eg: it takes X seconds to do Y units of work). Save it every day. After major changes -- compare. Data like that is the only way to have evidence that what you are doing has any sort of impact.
try little tests like:
create table t
as
select 1 c1, all_objects.*, 1 c2
from all_objects
/
alter session set sql_trace=true;
set termout off
select c1 from t;
select c1 from t;
select c1 from t;
select c2 from t;
select c2 from t;
select c2 from t;
set termout on
the tkprof report shows us:
select c1
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3453 0.31 0.37 0 4158 36 51741
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3459 0.31 0.37 0 4158 36 51741
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 63
Rows Row Source Operation
------- ---------------------------------------------------
17247 TABLE ACCESS FULL T
********************************************************************************
select c2
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.01 0 0 0 0
Fetch 3453 0.32 0.32 0 4158 36 51741
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3459 0.32 0.33 0 4158 36 51741
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 63
Rows Row Source Operation
------- ---------------------------------------------------
17247 TABLE ACCESS FULL T
due to the granularity of the clock -- sometimes the first query will be marginally faster, sometimes the second -- they are the same for all intents and purposes.
A reader, December 11, 2001 - 6:28 pm UTC
j from MA your method is really outdated, the only reason I have heard you have to resequence the column order is to put the nullable columns at the end to save space but that was it, I have heard because I have never have to do it except twice to maintain some kind of consistency with other tables which wasnt really necessary but since I was asked to do it...
December 11, 2001 - 7:36 pm UTC
True -- thats a valid point.
You could strive to put columns you believe will most likely believe to be NULL at the "end" of the table. Trailing null columns in a row take zero bytes of storage. Interior null columns (those with non-null columns after them) take 1 byte.
it would not be worth your while to reorg the order of the columns in order to save a byte.
It would howerver keep the developers on their toes! You would find the developers that used "select *" or insert into t values - with no column list -- very fast!!
The Myth
A reader, December 12, 2001 - 1:47 am UTC
Tom...
Another Book question... :-)
Why Dont u write a Book about
"Oracle Myths and Superstitions" That will sell like a Hot Cake and i hope it will be a small book compared to Expert One-on-One...
Regards,
Ganesh R
December 12, 2001 - 8:15 am UTC
are you kidding -- it would be BIGGER then expert one on one...
believe it or not, expert one on one would have been bigger had i followed my outline, we ended up cutting it at the end...
Ganesh is right.....
Adrian, December 12, 2001 - 3:29 am UTC
Tom
Ganesh is right - reading Prince's comments above just makes you realise how many truly ignorant "Oracle developers" there are out there. They just do not understand what Oracle is all about - they hear a SQL-Server trained DBA make a statement and it lives with them forever. So how about that book as Ganesh suggests? Or even a regular feature in the Oracle Magazine on the myths and misconceptions. The possibilities are endless:-
o views DO NOT degrade performance
o it's really OK to have lots of extents (please don't give that table a 1gig INITIAL...!)
o count(*) and count(0) are the same
o implicit cursors are OK to use
Oh we could go on forever.......
Adrian
order of columns in a table
Asim Naveed, June 22, 2003 - 3:33 pm UTC
Hi,
Does the order of columns in a table have any significance,
i know that putting null likely columns in the end is good.
But I am asking this question from the application
developers point of view. for e.g
if i use SELECT * FROM t in my application, does it
guarantees that the order of columns returned by this
statement is always same? (assume that i havent changed
my table). But it could happend that my database is reloaded
from a backup media.
Like oracle doesnt guarantee the order of rows returned
by SELECT statement until you use ORDER BY. Does the same
is true that oracle doesnt guarantee the order of columns
returned by the SELECT * FROM t, until you explicitly
write SELECT col1, col2, ... FROM t.
Thanks.
June 22, 2003 - 4:29 pm UTC
you should NEVER use select * from t in your applications -- the only place that construct is "sometimes safe" is in STATIC sql in PLSQL.
And even then, it is a performance issue if you really only needed 5 columns, not all 50.
In all other cases -- I would consider it a BUG.
PL/SQL, using STATIC sql, is the only safe environment -- and even then, only if you do it right. for example if you have:
cursor c is select * from t;
begin
open c;
fetch c into x, y, z;
that is a BUG to me. If you have;
begin
for x in ( select * from t )
loop
....
you are "safe", protected from columns being added, datatypes getting larger or even changing in type, order of columns changing and so on.
Inserts -- same thing, ALWAYS list the columns in the insert
insert into t ( c1, c2, c3 ) values ....
Never
insert into t values ( .... ); <<<==== bug waiting for you...
Not so safe after all
Henrie, May 29, 2007 - 9:46 am UTC
Tom, you said:
begin
for x in ( select * from t )
loop
....
you are "safe", protected from columns being added, datatypes getting larger or even changing in
type, order of columns changing and so on.
--
Nowadays it is possible to delete columns, so this one being safe has become unsafe also....
So leaving no (sometimes) safe option left one could say:
Never use select * from t in your applications.
May 30, 2007 - 10:53 am UTC
as long as your code didn't access that deleted column - you would still be safe
and in fact, you would be safe anyway because the code would go invalid and refuse to compile....
but - i agree, select * is not something you want to do in general. you should select what you need.
nice I like alphabetical order for my columns
Kevin, May 31, 2007 - 4:50 pm UTC
there are lots of advantages to alphabetical order of columns in a table.
my most favorite reason is it makes some people mad when they see it. Then I get to ask them lots of questions about why they don't like it, and what order would they use, and why do they think their order is any better. They usually have no response, or reponses based on non-practice, or the best they got is "other people don't do it like that".
also, during development it is easy to match up long lists of columns. Inserts are of particular note. Got a 100 column table, then a insert/select will have two long 100 column name lists which need to by synchronized. Alphabetical is an easy way to spot an out of place column.
Alphabetical order even impresses some people: oooh... what neat, clean code you write. Never underestimate the WOW factor.
As for the Myths: Oracle has been around a while. I wrote my first Oracle sql statement in 1985. Most myths are just outdated rules that are not true anymore and people just don't know it yet.
There was a time when for example when number of extents did make something of a difference in active oltp systems (long long ago in database version far far away). Oracle evevn passed out the advice on how to limit the number of extents and the need for extent management.
Then as I recall there was a paper presented many years ago at an Oracle conference in which someone benchmarked explicit and implicit cursors and decided that explicit cursors were better cause you could skip the second fetch. Of course I can't find the Oracle presentation in my library and I never did the benchmark myself so who knows if my memory is right these days.
Then there is the old debate on does order of columns in an index affect its performance. For many years some people said yes real load which only served to mess things up for the weak minded cause they missed the whole point of leading index scans. Then again, a guy showed up at an Oracle conference with a reproducable benchmark and said, nope, no meaningful difference, make the column order what satisfies the most queries, which is what people should have been doing all along anyway.
And how about those true but not useful for most people rules like: does the order of tests in the where clause affect performance. Or: should I use binary_integer for my plsql number types? In both cases maybe, but I'd wager there are 100 other places you should be looking into instead that would offer way better performance improvments than worring about these details. Each is a neat discussion which for most people has little value. Indeed, Tom has another post somewhere that says "hey, that binary_integer could even take you the other way" (a cool read as always).
so, when you see someone spouting an old rule of thumb, don't think they are dumb. They might just be an old Oracle dog who has not seen the new data yet. Show them a benchmark for it and they will call you friend, and having an Old Oracle Salty as a friend can be very valuable when you make your next big screwup and have to undo the damage fast but aren't sure how. Beside, we got some good stories to tell from the "early years".
Kevin