db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tim Dudgeon (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1489) Provide ALTER TABLE DROP COLUMN functionality
Date Wed, 23 Aug 2006 08:34:14 GMT
    [ 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

        

Mime
View raw message