Adding new column with it's position
Pavel Karady
it would be fine if there was possibility in ASA to add a new column to any
desired position.
example:
ALTER TABLE DBA.table ADD new_column INTEGER AFTER column1;
ALTER TABLE DBA.table ADD new_column INTEGER BEFORE column2;
Currently, I have to:
1. create temporary table with the new column as the first one
2. transfer data from "old" table to the "new_column" table with generation
of new column value
3. using a generalized script, transfer all user permissions to the new
table
4. drop old table, rename new table to the old one
5. recreate foreign keys, indexes
6. recreate triggers
7. recreate publication information
etc....
If there is any way how to do it now (ASA 9.0.2.3320), please let me know.
The function would be probably time-consuming due to huge movement of pages
inside the db file, but that absolutely wouldn't matter...
Pavel
Breck Carter [Team iAnywhere]
physical column order, because Codd said so!"
The answer to *that*, of course, is "Rubbish!"... there are excellent
reasons to care deeply what the physical column order is.
So I'm on your side :)
Breck
On 10 Jul 2006 08:26:54 -0700, "Pavel Karady"
<pavel_ns.ns_karady@ns_kogerusa.com> wrote:
--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck....@risingroad.com
Greg Fenton
>
> The answer to *that*, of course, is "Rubbish!"... there are excellent
> reasons to care deeply what the physical column order is.
Agreed.
>
> So I'm on your side :)
Hmmm...I'm not so quick to jump on this bandwagon. I think the
brainiacs would be best spent on solving more-frequently-used features.
The operation quite likely would be Very Expensive and quite possibly
cause more problems than simply creating a new table (same schema with
the additional column in the proper place), copying the data from the
old table and dropping the old table.
Updating the table to have the new column likely would lead to all sorts
of ugly internal issues such as fragmentation. Think about it, the
engine would have to go through an entire table adding in space for this
new column in the *middle* of the row...yuck.
g.f
--
Greg Fenton
Random Dude
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
Breck Carter [Team iAnywhere]
feature. That is more often than folks have asked for many other
features that have been implemented, including many the really big
ones coming in V10.
Remember, one person actually going public and asking for a feature
represents tens or perhaps hundreds of shy people wishing they had it
and cursing the darkness.
EVERY SINGLE TIME I do an ALTER TABLE to add a column, I wish the
column was placed somewhere else rather than the end of the row... the
end of the row is NEVER where I would like it. EVER.
For example, when adding an INTEGER column that is going to be
frequently used, physically placing that after a bunch of infrequently
used VARCHAR and LONG BINARY columns makes no sense whatsoever. Ask
the brainiacs what *that* does to performance :)
Breck Purveyor Of Fine Rants Since 1993
On 11 Jul 2006 19:32:59 -0700, Greg Fenton
<greg.fent...@googles-mail-site.com> wrote:
>Breck Carter [Team iAnywhere] wrote:
>>
>> The answer to *that*, of course, is "Rubbish!"... there are excellent
>> reasons to care deeply what the physical column order is.
>
>Agreed.
>
>>
>> So I'm on your side :)
>
>Hmmm...I'm not so quick to jump on this bandwagon. I think the
>brainiacs would be best spent on solving more-frequently-used features.
>
>The operation quite likely would be Very Expensive and quite possibly
>cause more problems than simply creating a new table (same schema with
>the additional column in the proper place), copying the data from the
>old table and dropping the old table.
>
>Updating the table to have the new column likely would lead to all sorts
>of ugly internal issues such as fragmentation. Think about it, the
>engine would have to go through an entire table adding in space for this
>new column in the *middle* of the row...yuck.
>
>g.f
--
David Kerber
site.com says...
...
> Updating the table to have the new column likely would lead to all sorts
> of ugly internal issues such as fragmentation. Think about it, the
> engine would have to go through an entire table adding in space for this
> new column in the *middle* of the row...yuck.
I'm sure it would be a very expensive operation in database cpu cycles,
but would it be as expensive in database administrator hours as
unloading, editing the reload.sql and reloading? I doubt it. Maybe the
choice should be left up to the operator as to which way to go,
depending on his/her needs.
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
Nick Elson
(and can imagine there may be others I haven't thought of) but
believe those are becoming less critical.
Just so everyone know, we all here *assume* Breck's point
> Remember, one person actually going public and asking for a feature
> represents tens or perhaps hundreds of shy people wishing they had it
> and cursing the darkness.
is 'the case' not only on the newsgroups but with every contact. While
many of our customers are obviously 'direct influencerss' (OEMs and
partners to start), we also understand anyone can be an influencer of
others as well as just being the verbal representative of a (possibly
silent)
majority. Especially those who post on this newsgroup.
But having said that, this thread still seems to be asking about a
'nice to have'
feature and not necessarily a 'must have'.
As such you might want to flesh out some of 'your' requirements
and under what conditions that a rebuild would not be a normal
action. [For me, at least that way I would not need to just/actually
'imagine' them.]
But definitely thanks posting.
[Of course I write that (fool well) knowing that column order
significance will be significantly diminished in SQL Anywhere 10.]
In Vers. 10, no longer will column order be critical to primary keys
or foreign key specifications.
"Breck Carter [Team iAnywhere]" <NOSPAM_...@risingroad.com> wrote in
message news:h3m9b29kbdln1lr12...@4ax.com...
Greg Fenton
>
> I'm sure it would be a very expensive operation in database cpu cycles,
> but would it be as expensive in database administrator hours as
> unloading, editing the reload.sql and reloading?
CPU/disk/memory cycles. But I guess I was thinking more of the amount
of energy needed for iAnywhere to implement this *correctly* (whatever
that might mean).
Nick said it better than I did: describe the problem in concrete terms
and let product management/engineering determine the "right" way to
implement a solution.
This most certainly is a "nice to have". I don't believe any other
vendor has a similar process, and the (stated/assumed) outcome is
achievable today.
Breck Carter [Team iAnywhere]
<greg.fent...@googles-mail-site.com> wrote:
>This most certainly is a "nice to have".
That's YOUR opinion, and with all due respect, you are not working in
The Black Hole Of Programming where features like this are very much
appreciated.
I can think of several features that were regarded as trivial
nice-to-have features until they were delivered into the hands of
people who actually used them. DEFAULT TIMESTAMP comes to mind, "just
another g-d Transact SQL Compatibility kludge". Then there is UPDATE
ON EXISTING, IF VAREXISTS, and so on and so on. Coming in V10 is LOAD
TABLE ROW DELIMITED BY.
And guess what? No other DBMS has any of those features either (well,
not that I can think of before my first coffee.)
It can be enormously difficult and error prone to manually rearrange
the physical column order in an existing table. In reality, folks
suffer with the stupid orders that have grown up over the years, and
if such a feature was made available it would be heavily used.
Breck
Martin Baur
> It can be enormously difficult and error prone to manually rearrange
> the physical column order in an existing table. In reality, folks
> suffer with the stupid orders that have grown up over the years, and
> if such a feature was made available it would be heavily used.
Breck,
while I have nothing at all against such a feature (in fact, I did wish it some time in the past as well), where is the benefit from having it?
I deal with my databases using Central or other tools. The wish I had that day has never urged me again that much.
Is it only for performance reasons? At least, I can imange only this for now. But maybe you can provide some more strong arguemnts ...
Thanks,
Martin
Pavel Karady
would be fine".
Martin, how many nation-wide companies do you think deal with their ASA
databases using Sybase Central? ... I mean changing the database structure
when upgrading to a next release of a vendor software, not tasks like
checking the number of connected users.
The reality is that having a 170 kb script to create new primary key columns
for 12 tables, which can involve no unload/reload because what all the
clients want is 'to run a script' instead of 'to have someone trained who
has read all the manuals how to perform manual table structure change using
unload/reload processes and respective wizards, who will probably make a
thousand of errors before he succeeds and wouldn't be able to repeat it on
demand', is too much to maintain, if you can imagine having the statement
described before.
The whole script could have 12 lines. Imagine the $$$ and three days of a
developer's time saved. Imagine the ease of maintainability. Let us say a
trigger for one of those tables changes - then without a proper SCM the
script becomes buggy instantly. And that are just few drops from the ocean.
The feature is clearly a DEMAND.
Thanks to all who seconded.
Pavel
"Martin Baur" <ti...@mindpower.com> wrote in message
news:MPG.1f23acdf6...@forums.sybase.com...
Nick Elson
Your argument was always understood. We understand it holds
iff you must change column order.
What we need are the reasons for the
'need to change column order';
as such. Without that as a core requirment we have no
business case.
That is where any discussion needs to start (from my perspective
anyway). Other than a specific case of primary keys (for which a
uniqueness constraint might suffice as a proxy) most of the cases
I can imagine would fall into the 'nice to have' category.
Let us know what we are missing here.
"Pavel Karady" <pavel_ns.ns_karady@ns_kogerusa.com> wrote in message
news:44baf2c0@forums-2-dub...
Mike Gould
It is my understanding that the most frequently accessed columns should be
at the beginning of the table for performance reasons. If a column(s) being
added are going to fall into that category then performance could be a
reason. That being said, I've restructured my tables a couple of times over
the years and I just didn't find it that big of a deal. Now we have 50+
location's worth of data in our database with almost 5 years worth of data
and our tables are well normalized. We only have 1.2 Gig worth of data so
doing a complete unload /convert/reload just isn't that big of a deal for me
personally, however I've worked with several terabytes worth of data
(billions of rows ) on a mainframe database and I could see that it might be
nice to not have to unload/reload to add a column.
Michael Gould
"Nick Elson" <no_span_...@sybase.com> wrote in message
news:44bba3c0$1@forums-1-dub...
Dan Konigsbach
> [...]
> What we need are the reasons for the
>
> 'need to change column order';
>
> as such. Without that as a core requirment we have no
> business case.
How about adding a short field that will be used heavily in WHERE
conditions to a table that already has a lot of columns and/or fairly
large varchars?
Martin Baur
> The feature is clearly a DEMAND.
>
> Thanks to all who seconded.
> Pavel
You did not answer my point. As Nick Elison put it, WHY is a need to change colum order ... not IF I need to, HOW can I make it best.
Rows are structures stored in pages. Once a page is loaded, offsets from its memory address access any column in the same amount of time. It does not matter how far the offset is to the CPU as long
as it is the same assembly code. Address maths is something a CPU does quite well these days.
MY point was: If I want to manipulate data, see reports in a convenient way, then I want the tool (Central, others) to display the order in the way I want.
I was the one who made Central having the fatilty to order the columns be creation order ... Sybase forgot that one alhtough they could sort the display by all other columns in Central. They've just
missed that one. No big deal of course, because it only affected the sorting of the grid. So they did it. And I got the info I needed, the sorted view of the history how the columns where created.
This way, I can have my prefered view and I don't care about how it's stored.
So, my question still is: WHAT for should one need to PHYSICALLY insert a column in a specific position except for possible performance things (where Mike Gould put some words to)?
If for handling, then use a tool that provides any possible view of your columns ...
Regards
Martin
Dan Konigsbach
> [...]
> its memory address access any column in the same amount of time. It does
> not matter how far the offset is to the CPU as long
> as it is the same assembly code. Address maths is something a CPU does
> quite well these days.
Not quite. You're thinking of some other DBMS.
In ASA,
1) CHAR fields are varying length, even if not declared VARCHAR. Fields
after a CHAR are not at a fixed offset.
2) Once a row is created in a page, it is not moved to a different page
when a page becomes full (due to updates to rows in the page, making their
CHAR fields longer). Instead, rows get split into overflow rows. It is
much, much faster to access a field in the initial page instead of the
overflow page.
Supporting evidence: The following is taken from the Sybase whitepaper
"Improving Performance in SQL Anywhere".
" Tip 1: Be wary of wide tables
"[...] When the number of columns in a table causes the size of individual
rows to exceed the database page size, each row is split across two or more
database pages. The more pages a row takes up, the longer it takes to read
each row. [...]"
(Actually, the same problem can happen with tables with few columns. It's
more a question of how much a row grows after the page it lives in is filled
with neighbors, and how much free space was originally left behind.)
" Tip 8: First come, first served
"Columns in a row are accessed in a sequential manner in the order of their
creation. For example, in order to access columns at the end of a row,
Adaptive Server Anywhere has to skip over any columns that appear earlier in
the row. Primary key columns are always stored at the beginning of rows. For
this reason, it is important to create tables such that small and/or
frequently accessed columns are placed before seldom accessed columns in the
table."
" Tip 14: Diminish database fragmentation
[...]
" Table fragmentation
"Table fragmentation occurs when rows are not stored contiguously, or when
rows are split between multiple pages. Performance decreases because these
rows require additional page accesses.
"Adaptive Server Anywhere reserves extra room on each page to allow rows to
grow slightly. When an update to a row causes it to grow beyond the space
available on the current page reserve, the row is split and the initial row
location contains a pointer to another page where the continuous row is
stored. For example, filling empty rows with UPDATE statements or inserting
new columns into a table can lead to severe row splitting. As more rows are
stored on separate pages, more time is required to access the additional
pages.
[...]"
Mike Gould
In many other database systems, page size can be set at the table level
instead of
Breck Carter [Team iAnywhere]
or column name order. Neither of those orders make sense when you
ALTER a table to add a new column, if it is a column that logically
"should" appear at or near the top of the list, or "grouped together"
with other columns.
When creating new tables, designers often spend a lot of time
arranging the order of columns in a way that makes sense to other
human beings. The ALTER TABLE command behavior is hugely irritating.
Now if YOU don't care, that's fine, you get a vote. But I care a lot,
and so do a lot of other people... take a poll of database designers,
see what they say, I'll bet a huge majority will say adding a column
at the end of the list is not a happy ending.
Breck
--
Nick Elson
addition; especially when one one considers what the
"reorganize table" operation brings to this discussion.
"Dan Konigsbach" <dkoni...@dominator.com> wrote in message
news:44bc3522@forums-2-dub...
Dan Konigsbach
>I am not exactly certain if this is a vote 'for' or 'against' such an
> addition; especially when one one considers what the
> "reorganize table" operation brings to this discussion.
>
It is a vote 'for' the the change Pavel Karady proposes, because
1) I am showing that this argument against his proposal is based on
incorrect information, and
2) Reorganize table brings nothing to this issue.
Please go back and read "Tip 8: First come, first served" in the Sybase
Breck Carter [Team iAnywhere]
On 18 Jul 2006 10:54:45 -0700, "Dan Konigsbach"
<dkoni...@dominator.com> wrote:
>Reorganize table brings nothing to this issue.
...seems just a bit on the bold side <g>.
AFAIK it is possible to have a row that is horribly split across
multiple pages without being wider than one page (e.g., 4K). In that
case REORGANIZE TABLE should be able to gather the bits and put them
on a single page, and improve access to columns at the end of the row.
And for extremely wide rows (that won't fit on a page), I believe
REORGANIZE TABLE will reduce the number of splits if possible... not
entirely, of course, and frequent access to the last column (the ALTER
TABLE victim) would still suffer performance-wise... just not as much.
=====
What is your vote on the "Human Interface" side of the argument?
Dan Konigsbach
you describe, so I don't have much practical experience with it, but I
certainly can see the value of it, and respect the suggestion.
I may have been a bit bold, but in my response I was focusing on the order
of columns. REORGANIZE TABLE helps reduce splits, but it doesn't address
the column order. Even though others seem to belittle column order, it's a
really, really a valid concern:
- human interface
- more frequently accessed columns earlier in table
- add to front of primary key
- force table in different copies of a database, which may have gone through
different schema histories, to have identical column order, so that
unload/reload data files can be used across all.
Frankly, I'd go even further than Pavel. I'd like to see REORGANIZE TABLE
be able to let you list the column order you want, with the ability to say
"any columns I didn't list come, in arbitrary order, after the ones I've
listed".
e.g.
REORGANIZE TABLE mine.mydata ( name, rank serialno, mealpreference,
... )
But, maybe I'm just dreaming. (And I've got another issue at the top of my
wish list.)
- Dan
"Breck Carter [Team iAnywhere]" <NOSPAM_...@risingroad.com> wrote in
message news:mgaqb253ic3lmo7p1...@4ax.com...
Nick Elson
moment and continue with the perceived requirements.
Pavel any others from your perspective? (any one else too?)
Any clarification for your requirements?
Let me list Dan's list of 4 here again
1 - human interface
2 - more frequently accessed columns earlier in table
3 - add to front of primary key
4 - force table in different copies of a database, which may
have gone through different schema histories, to have
identical column order, so that unload/reload data files
can be used across all.
For those who are concerned about this, you will want to also
review what version 10 will bring to the table. It directly affects
#3 and impacts #4. I am reserving all of my comments until
others have had a chance to add to this list.
"Dan Konigsbach" <dkoni...@dominator.com> wrote in message
news:44bd40ee@forums-1-dub...
Dan Konigsbach
Unfortunately, I haven't been able to get into the ASA10 Beta program
(sniff, sniff, whimper).
Are you allowed to share what ASA 10 adds for these kind of issues? (Or are
beta participants under some kind of Billy Crystal/Winston University-type
of NDA?)
Thanks,
Dan
"Nick Elson" <no_span_...@sybase.com> wrote in message
news:44be5fb4$1@forums-1-dub...
Breck Carter [Team iAnywhere]
<dkoni...@dominator.com> wrote:
>I haven't been able to get into the ASA10 Beta program
It should be easy... did you provide a real email address? Do you have
a Dominatrix-Style firewall?
Glenn Paulley
In my mind, there is absolutely no question that the ability to place a
new column in any position in a row is exceedingly useful, particularly
with SQL Anywhere since column order can make a significant difference in
performance, as has been discussed by others in this thread.
In fact, at Techwave every year I remind customers of the need to take
column order into account when doing physical database design, because it
can be exceedingly inefficient to add a heavily-used attribute to the end
of a row (particularly if, for example, the attribute is a foreign key
and hence widely utilized in join queries).
Given that advice, it is somewhat unfortunate that SQL Anywhere supports
"merely" the ANSI SQL Standard (adding a column adds it to the end of the
row).
There are a few things in the Jasper release that mitigate this
deficiency to some extent:
- primary keys no longer have to be at the beginning of rows; they can be
anywhere in the row.
- wide columns (>130 bytes) can be compressed (compression/decompression
occurs automatically)
- one can specifiy limits for how much data of a BLOB column is in-lined
within the row, on a per-column basis.
That said, I still believe that the ability to explicitly add a column to
a particular position is useful, and my team and I have discussed this at
length (prompted by this thread) over the past week.
The bad news (TM) is that while this feature is easy to describe, it
isn't straightforward to implement. We are currently debating the
tradeoffs of modifying the server's internals to perform the ALTER "in
situ", versus a different approach that does not require such fundamental
changes. We will continue to discuss this issue further and I hope to
develop an action plan on this particular item sometime in the fall.
I would like to thank all of the contributors to this thread for their
input, and I welcome additional suggestions (or votes) on this matter or
any other. *All* of my staff monitor this thread and we take the
suggestions (and debates) that occur in these forums quite seriously.
Glenn
--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering
Register now for TechWave 2006 August 6-10 in Las Vegas. New! 2-day
and 4-day options for SQL Anywhere technical training. Sessions will
focus on data management, data movement, and SQL Anywhere 10. Visit
www.sybase.com/techwave
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
David Kerber
says...
> So we have 4 bullets now. I'd like to stop the debates for a
> moment and continue with the perceived requirements.
>
> Pavel any others from your perspective? (any one else too?)
> Any clarification for your requirements?
>
>
> Let me list Dan's list of 4 here again
>
> 1 - human interface
> 2 - more frequently accessed columns earlier in table
> 3 - add to front of primary key
> 4 - force table in different copies of a database, which may
> have gone through different schema histories, to have
> identical column order, so that unload/reload data files
> can be used across all.
>
> For those who are concerned about this, you will want to also
> review what version 10 will bring to the table. It directly affects
Is there any way we can do that without signing up for the beta? I'd be
glad to do a NDA just to see this info. I hadn't thought this issue
through as thoroughly as Dan did, but his list looks good to me. #4
would be of particularly handy use for me.
....
Breck Carter [Team iAnywhere]
for the Beta... do the latter, install it, then just read the "What's
New" section in the Help, very thoroughly done.
PLUS, there are a bunch of downloadable articles, webcasts, etcetera,
that are available when you sign up.
Just make sure your spam filter doesn't block the return email when
you sign up.
Breck
On 19 Jul 2006 13:10:36 -0700, David Kerber
<ns_dkerber@ns_WarrenRogersAssociates.com> wrote:
--
Dan Konigsbach
filter.
My reference to a "Billy Crystal/Winston University-type of NDA" was only a
feeble attempt at humor (with a way-too-obscure reference). Sorry if it
caused anyone concern.
- Dan
"Breck Carter [Team iAnywhere]" <NOSPAM_...@risingroad.com> wrote in
message news:bf5tb29vf450os1ak...@4ax.com...
Breck Carter [Team iAnywhere]
<dkoni...@dominator.com> wrote:
>way-too-obscure reference
http://snltranscripts.jt.org/83/83owinston.phtml
Mike Nicewarner
being able to add columns to the end of tables really is a problem. It
isn't that we want columns in a particular order because of aesthetics,
but to optimize performance, as has been mentioned before in this
thread. It is cumbersome to have to unload and drop the table every
time I need to add a new column in the middle of the table.
--
Mike Nicewarner [TeamSybase]
http://www.datamodel.org
mike[at]datamodel[dot]org (can you figure what to change?)
Sybase product enhancement requests:
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement
Breck Carter [Team iAnywhere] wrote:
Pavel Karady
column to the end of the table - the lazy one.
I cannot imagine that any DBA in the world, who is not lazy, would add a
column which will serve as the new single-column primary key to the end of
the table *knowing* that this WILL cause (probably huge) performance issues
(let's say the previous designers created this table with 40 columns...).
Such a DBA would choose the list of actions described in the post that
opened this thread instead of using the correct ALTER TABLE ... ADD ...
statement.
Thanks to Glenn for giving the column positioning a chance to become real.
Nick, I think you can "release" your comments. Here's the list:
1 - human interface
2 - more frequently accessed columns earlier in table
3 - add to front of primary key
4 - force table in different copies of a database, which may
have gone through different schema histories, to have
identical column order, so that unload/reload data files
can be used across all
Pavel
P.S. I like Dan's REORGANIZE TABLE [owner.]table_name [(column_name, ...)]
... idea. No changes for ALTER TABLE statement required that way.
"Mike Nicewarner" <"mike[at]datamodel[dot]org"> wrote in message
news:44c16e02$1@forums-1-dub...
Paul Horan[TeamSybase]
much, much deeper problems. If you're adding a new primary key to an
existing table, you've got bigger problems than the lack of a syntactical
convention for automatically adding it to the front of the row...
My 2 cents.
Paul Horan[TeamSybase]
"Pavel Karady" <pavel_ns.ns_karady@ns_kogerusa.com> wrote in message
news:44c50ed2$1@forums-1-dub...
Breck Carter [Team iAnywhere]
<paul.horanATcynergysystems.com> wrote:
>deeper problems
Some of us don't get everything perfect, first time... I know, we
don't deserve to keep our jobs :)
Breck Admits To The Deepest Of Problems
Mike Nicewarner
correct initially, but what about that old legacy system that someone
else built, or we bought, that has a terrible database? We are forced
to use it, but there are flaws that can only be fixed by changing the PK
of some of the tables. It is a rare situation, but it does happen.
Also, to be fair, with DB2 I would have to drop and recreate the tables
to rearrange the columns, so ASA isn't any worse than DB2.
--
Mike Nicewarner [TeamSybase]
http://www.datamodel.org
mike[at]datamodel[dot]org (can you figure what to change?)
Sybase product enhancement requests:
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement
Breck Carter [Team iAnywhere]
<"mike[at]datamodel[dot]org"> wrote:
>ASA isn't any worse than DB2
Oh, THAT'S a relief <bg>
Greg Fenton
>>ASA isn't any worse than DB2
>
> Oh, THAT'S a relief <bg>
>
Breck, shouldn't there have been a pointer to sqlanywhere.ca with this
one? <g>
g.f
--
Greg Fenton
Some Random Dude
Breck Carter [Team iAnywhere]
On 30 Jul 2006 10:57:50 -0700, Greg Fenton
<greg.fent...@googles-mail-site.com> wrote:
>Breck Carter [Team iAnywhere] wrote:
>>>ASA isn't any worse than DB2
>>
>> Oh, THAT'S a relief <bg>
>>
>
>Breck, shouldn't there have been a pointer to sqlanywhere.ca with this
>one? <g>
>
>g.f
--
Robert Paresi
-Robert
"Breck Carter [Team iAnywhere]" <NOSPAM_...@risingroad.com> wrote in
message news:2b67b2topp5nj5i6t...@4ax.com...
> You're going to get responses that read "You should not care about the
> physical column order, because Codd said so!"
>
> The answer to *that*, of course, is "Rubbish!"... there are excellent
> reasons to care deeply what the physical column order is.
>
> So I'm on your side :)
>
> Breck
>
> On 10 Jul 2006 08:26:54 -0700, "Pavel Karady"
> <pavel_ns.ns_karady@ns_kogerusa.com> wrote:
>
>>Greetings experts,
>>
>>it would be fine if there was possibility in ASA to add a new column to
>>any
>>desired position.
>>
>>example:
>>ALTER TABLE DBA.table ADD new_column INTEGER AFTER column1;
>>ALTER TABLE DBA.table ADD new_column INTEGER BEFORE column2;
>>
>>Currently, I have to:
>>1. create temporary table with the new column as the first one
>>2. transfer data from "old" table to the "new_column" table with
>>generation
>>of new column value
>>3. using a generalized script, transfer all user permissions to the new
>>table
>>4. drop old table, rename new table to the old one
>>5. recreate foreign keys, indexes
>>6. recreate triggers
>>7. recreate publication information
>>etc....
>>
>>If there is any way how to do it now (ASA 9.0.2.3320), please let me know.
>>The function would be probably time-consuming due to huge movement of
>>pages
>>inside the db file, but that absolutely wouldn't matter...
>>
>>Pavel
Breck Carter [Team iAnywhere]
(realized?) that data can be copied AND rearranged in a different
column order with a single INSERT SELECT that does NOT list the
columns explicitly:
INSERT t2 WITH AUTO NAME SELECT * FROM t1;
More proof that "iAnywere Does Things The Way They Should Be Done".
Breck
PS I still want to use ALTER to do this in-place, no backing down
there :)
Volker Barth
Just a thought:
If ALTER TABLE is expanded with respect to column rearranging, this should
be in a way that does not disturb SQL Remote.
I don't know if column order is important for SQL Remote now (column name
and type are, AFAIK). If so, this would be an important issue IMHO.
I generally make sure that column order is the same in published tables both
for consolidated and remotes.
If an alter table (or reorganize table with column rearranging) could lead
to possible problems in this respect, that should be made clear - say, as a
hint "Do re-arrange columns locally and remote the same way before any data
changes...".
Volker
"Breck Carter [Team iAnywhere]" <NOSPAM_...@risingroad.com> schrieb im
Newsbeitrag news:i32pe250duskqnhjv...@4ax.com...
Breck Carter [Team iAnywhere]
run *any* ALTER TABLE on a remote database table that is participating
in synchronization. You have to drop the table from the publication,
or do the ALTER within the sp_hook_dbmlsync_schema_upgrade procedure.
In other words, this ALTER TABLE would fall into the same category.
With SQL Remote I'm not sure column order matters since it ships
transaction log entries... but that might just be...
Breck Having A Senior Moment
On 24 Aug 2006 04:46:54 -0700, "Volker Barth"
Rob Waywell
will be able to confirm that dbremote specifies the column names when
executing operations. One reason for this is that dbremote supports the
publication of a subset of the columns from a table so regardless of whether
the operation is an insert or an update we have to specify the columns by
name.
--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional
Sybase's iAnywhere Solutions
Please respond ONLY to newsgroup
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
"Breck Carter [Team iAnywhere]" <NOSPAM_...@risingroad.com> wrote in
message news:gk8re21lm9sfsfqca...@4ax.com...
Volker Barth
if I understand you correctly, column order is not relevant for SQL Remote.
So this would be no problem for an ALTER TABLE extension.
(Besides the fact that altering published tables in a SQL Remote / MobiLink
setup must always be done with care and testing^3 ;-).
Thanks for the clarification!
Volker
"Rob Waywell" <rwaywell_no...@ianywhere.com> wrote in
news:44ede66c$1@forums-1-dub...
Rob Waywell
Especially the part about being very thorough in your planning and testing
before rolling out schema changes to replicated/synchronized databases. :-)
--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional
Sybase's iAnywhere Solutions
Please respond ONLY to newsgroup
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
"Volker Barth" <No_VBar...@GLOBAL-FINANZ.de> wrote in message
news:44eea5b4$1@forums-2-dub...