db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel Noll <dan...@nuix.com>
Subject Moving/copying BLOBs from one table to another
Date Mon, 09 Feb 2009 04:19:59 GMT
Hi all.

This is probably a weird question.  I was considering implementing a 
database something like this...

CREATE TABLE Binaries {
    id INTEGER NOT NULL PRIMARY KEY,
    data BLOB(1G) NOT NULL
}

CREATE TABLE BinariesHistory {
    id INTEGER NOT NULL,
    data BLOB(1G) NOT NULL,
    version INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP NOT NULL,
    PRIMARY KEY (integer, version)
}

In this fashion querying Binaries for an item will be quicker than doing 
it for BinariesHistory as the table size will be fairly large and 
Binaries has only a single id to look stuff up whereas with 
BinariesHistory you would need to return all items and select the one 
where version = MAX(version).

My concern is not knowing what happens to the BLOB.  If I insert into 
BinariesHistory and then do a second query which copies part of the row 
I just inserted into Binaries, will Derby create a second copy of the 
BLOB (undesirable) or will it create a reference to the *same* BLOB?

Daniel


-- 
Daniel Noll                            Forensic and eDiscovery Software
Senior Developer                              The world's most advanced
Nuix                                                email data analysis
http://nuix.com/                                and eDiscovery software

Mime
View raw message