When renaming a column will this rewrite the rows of a table?

Alter table X rename C1 to C2

asked 19 Oct '10, 09:51

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

edited 14 Jan '11, 14:25

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819

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.

(19 Oct '10, 10:47) Volker Barth

I don't expect it, but I want to be sure

(19 Oct '10, 11:32) Martin
Comment Text Removed
1

@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 :)

(19 Oct '10, 12:29) Breck Carter

@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?

(20 Oct '10, 20:24) 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).

permanent link

answered 19 Oct '10, 12:03

Mark%20Culp's gravatar image

Mark Culp
24.9k10139297
accept rate: 41%

edited 20 Oct '10, 16:29

@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.)

(19 Oct '10, 12:09) Volker Barth
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).

(20 Oct '10, 16:27) Mark Culp

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".

permanent link

answered 19 Oct '10, 12:25

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 20 Oct '10, 18:27

Any explanation of what has happened?

(19 Oct '10, 13:11) Volker Barth

Sounds to me like a sequential table scan...

(19 Oct '10, 13:17) Martin

@Volker: I have no idea. @Martin: I have no idea.

(19 Oct '10, 14:30) Breck Carter

@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:)

(19 Oct '10, 14:37) Volker Barth
Comment Text Removed

@Volker: Not in Version 10 or later... but you get points for humor :)

(19 Oct '10, 17:58) Breck Carter
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.

(20 Oct '10, 16:27) Mark Culp

@Mark: Yes.....

(20 Oct '10, 18:24) Breck Carter
More comments hidden
showing 4 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×275
×90
×8
×2

question asked: 19 Oct '10, 09:51

question was seen: 4,078 times

last updated: 14 Jan '11, 14:25