When renaming a column will this rewrite the rows of a table?
asked 19 Oct '10, 09:51 Martin Volker Barth |
Renaming a column is simply a matter of updating the catalog table SYSCOLUMN to have the new name. The row data in the table is not affected by the change.... unless you have computed columns in the table, in which case the table is scanned and each computed column value is recomputed and updated if its value has changed (i.e. if the computed column did not change then the row is not rewritten). answered 19 Oct '10, 12:03 Mark Culp @Mark: Are my assumptions about the performance issues (due to recompliation of stored procs and dropping of cached plans) correct? (Confine my comment on the question.) 1
Yes, renaming a column will cause triggers and stored procedures to be reloaded. I would also presume that the cache would be dropped in order to maintain correctness (but I did not confirm this). |
The following example shows what happens with a large table that has computed columns that do not change in value. Here's an example of renaming a column and renaming it back, in a fairly large table using 32-bit SQL Anywhere 11.0.1.2472 on a consumer-grade PC with a 2.66 GHz Core2 Quad Q9450 processor and a 500G drive running 64-bit Windows Vista Ultimate: ALTER TABLE DBA.rroad_group_2_property_pivot RENAME blocker_row_identifier TO BlahBlahBlah; Execution time: 293.281 seconds ALTER TABLE DBA.rroad_group_2_property_pivot RENAME BlahBlahBlah TO blocker_row_identifier; Execution time: 286.469 seconds Here's what I mean by "fairly large"... trust me, SQL Anywhere takes a lot longer than 5 minutes to rewrite 22M rows in a 14G table... -- DBA.rroad_group_2_property_pivot (table_id 735) in Foxhound on bcarter-pc - Oct 19 2010 7:58:23AM - Print - Foxhound © 2010 RisingRoad CREATE TABLE DBA.rroad_group_2_property_pivot ( -- 22,967,920 rows, 14G total = 13.5G table + 56k ext + 542M index, 655 bytes per row ... blocker_row_identifier VARCHAR ( 32 ) NULL, ... Here's some more evidence that something expensive is going on, but it's NOT rewriting the rows:
How big was the cache? Only 2G, only 1/7th the size of the table, so that doesn't explain the "0 writes". answered 19 Oct '10, 12:25 Breck Carter Any explanation of what has happened? Sounds to me like a sequential table scan... @Volker: I have no idea. @Martin: I have no idea. @Breck: Well, if you had changed the column name to/from "C2" (as in Martin's sample), then I would suspect that a C2-compliant audit has taken place under cover:) Comment Text Removed
@Volker: Not in Version 10 or later... but you get points for humor :) 2
@Breck: Do you have any computed columns in your table? If you do then renaming a column will cause all computed columns to be recomputed. @Mark: Yes..... More comments hidden
|
Why would you expect a rewrite? IMHO, that should only change the system catalog and might force all stored procs etc. to be reloaded and parsed and all cached query plans to be dropped.
I don't expect it, but I want to be sure
@Volker: IMO one should always be afraid, very afraid, of ALTER TABLE. First of all, what it does and does not do and HOW FAST it does the things it will do, depends to a huge extent on what version of SQL Anywhere we are talking about. In fact, ALTER TABLE never ceases to surprise me... see my anecdotal answer for evidence of that :)
@Martin: I'm o glad that you have asked - particularly as I wasn't aware of the "computed columns re-computation" side-effect (s. Mark's comment). @Breck: You are more right than you have thought, right?