The forum will be down for maintenance at some point from Friday, November 16 at 19:00 EDT until Sunday, November 18 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

Running 16.0.0.2704, I have added some tables with a column with a COMPUTE clause based on a connection-specific variable, such as

create variable varInfo varchar(10);

create table T_Test
(
   pk_Test     int default autoincrement primary key,
   Info        varchar(10)  not null compute (varInfo),
   ...
);

Before filling the table via LOAD TABLE from various files, I set the varInfo variable to a file-specific value. After the load, I drop the compute via

alter table T_Test alter Info drop compute;

According to the docs, the DROP COMPUTE clause has the following characteristics:

DROP COMPUTE – Removes the COMPUTE attribute for the specified column. This statement does not change any existing values in the table.

I'm testing currently with several table schema, some of them with compressed blobs.

The loading of my test files takes about 7-9 minutes on a local engine with only one connection.

However, the ALTER TABLE DROP COMPUTE statement takes way longer, namely more than 20 minutes, particularly with tables with compressed blobs. I'm surprised as I would expect a short timespan when only the table schema has to be adapted, but no data, and there are no other connections that could interfere...

What effect am I missing?

asked 16 Oct, 05:36

Volker%20Barth's gravatar image

Volker Barth
32.4k328476692
accept rate: 32%

edited 16 Oct, 05:37


Barring a bug where we think we need to rewrite all of the rows, I don't know what the DROP could be doing. I think we store the values for COMPUTE columns (rather than recompute them on demand every time).

What if you used ... Info varchar(10) not null default 'whatever' ...

and then dropped altered the column to remove the default?

permanent link

answered 17 Oct, 12:33

John%20Smirnios's gravatar image

John Smirnios
9.5k381121
accept rate: 39%

Well, I had tested that yesterday, too, but because the value is file-specific, I had to alter the default via a dynamic SQL statement for each loaded file... - and that worked even worse, I guess because that issued another checkpoint.

Finally, I chose a simpler schema without any computed columns and use a load into a temporary table and then use an INSERT SELECT from the temporary table to build the "computed expressions". Now altering/updating the permanent table is waaayyy faster.

The disadvantage is that INSERT SELECT apparently fills the trans log... There's another question coming up, dealing with how to use LOAD TABLE from a column/OpenString expression...

(17 Oct, 14:40) Volker Barth

> because the value is file-specific

Your sample code doesn't show anything "file-specific"... is the sample code representative of the real code?

(19 Oct, 15:59) Breck Carter
Replies hidden

Well, I set the value for the varInfo variable to a particular value for each group of files that are then used via LOAD TABLE. Basically the value is a kind of tag for the date the files have been delivered. (It's not the file creation date, however).

(04 Nov, 17:41) Volker Barth
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:

×233
×12
×7

question asked: 16 Oct, 05:36

question was seen: 78 times

last updated: 04 Nov, 17:43