No. Maybe. See Mark's expanded answer, re: computed columns.
That's the short answer, the long answer is "it might not rewrite the rows but it sure does something expensive".
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:
- The Windows Vista Task Manager showed
dbsrv11.exe pegged one of the 4
processors at 100%.
- The Resource Monitor - Resource
Overview - Disk graph went up to 50
MB/sec, and the Disk details pane
showed the database I/O at
3,100,000,000 for Read (B/min) and 0
for Write (B/min)
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
32.5k●539●724●1050
accept rate:
20%
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?