|
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:
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? |
|
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? 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 '18, 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 '18, 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 '18, 17:41)
Volker Barth
|
