[
http://issues.apache.org/jira/browse/DERBY-1489?page=comments#action_12429936 ]
Tim Dudgeon commented on DERBY-1489:
------------------------------------
I've tested the basic drop column funtionality and it seems to be working. It
is a bit slow though.
Here are some apporximate benchmarks for this. I created a tables like these:
CREATE TABLE a_table cd_id
INT NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1)
cd_structure BLOB, cd_smiles VARCHAR(4000)
cd_fp1 INT,
cd_fp2 INT,
cd_fp3 INT,
cd_fp4 INT,
cd_fp5 INT,
cd_fp6 INT,
cd_fp7 INT,
cd_fp8 INT,
cd_fp9 INT,
cd_fp10 INT,
cd_fp11 INT,
cd_fp12 INT,
cd_fp13 INT,
cd_fp14 INT,
cd_fp15 INT,
cd_fp16 INT
)
This is typical column for me. The BLOB column contains a long (approx 5K)
string, all other columns are small.
Then I loaded approx 60,000 rows into one of these tables, added a column and
then dropped it. Then repeated the process in a different tables for 250,000
and 3,000,000 rows. I also did the first 2 in oracle for a comparison. The
oracle db already had lots of data in it so things are probably tipped in
Derby's favour. The times for dropping the column are shown in millis.
Table size Derby Oracle
60K 4886 1088
250K 32108 13786
3M 421521 ND
Presumably the time is taken to delete all the data (though the column being
dropped contains only nulls). But Derby is significantly slower than Oracle.
> Provide ALTER TABLE DROP COLUMN functionality
> ---------------------------------------------
>
> Key: DERBY-1489
> URL: http://issues.apache.org/jira/browse/DERBY-1489
> Project: Derby
> Issue Type: New Feature
> Components: Documentation, SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.1.0, 10.1.2.1,
> 10.1.3.0, 10.1.3.1
> Reporter: Bryan Pendleton
> Assigned To: Bryan Pendleton
> Attachments: dropColumn_2.diff
>
>
> Provide a way to drop a column from an existing table. Possible syntax would
> be:
> ALTER TABLE tablename DROP COLUMN columnname CASCADE / RESTRICT;
> Feature should properly handle columns which are used in constraints, views,
> triggers, indexes, etc.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
