Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Reply-To: <msegel@segel.com>
From: "Michael Segel" <msegel@segel.com>
To: "'Derby Discussion'" <derby-user@db.apache.org>
Subject: RE: Is it Possible to Turn off Referential Constraints for a DBUNIT
 Load?
Date: Mon, 16 Oct 2006 08:58:38 -0500
Organization: MSCC
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <fbb0d11d0610160444r77ad5c64r6f3e12ce6edb8379@mail.gmail.com>
thread-index: AcbxGZc1N5dV7/caRCS4YWUq1GuGEAAEI1Bg
Message-Id: <20061016140603.1DFC64BA8D@dbrack01.segel.com>


> -----Original Message-----
> From: Dan Scott [mailto:denials@gmail.com]
> Sent: Monday, October 16, 2006 6:45 AM
> To: Derby Discussion
> Cc: msegel@segel.com
> Subject: Re: Is it Possible to Turn off Referential Constraints for a
> DBUNIT Load?
> 
[SNIP]
> And to be fair, DB2 and Oracle offer the same sort of functionality
> via the SET CONSTRAINTS statement. In Oracle's case, the constraints
> are checked at the end of the transaction -- so if you load a million
> rows, you're not checking row-by-row.
> 
[mjs] 
Using Oracle as the example, the constraint checking occurs at the end of
the process. The key point is that the constraint checking occurs.

> The reason people typically want to temporarily disable constraints
> for a load is to increase the speed with which you can load data. It
> certainly makes a big difference if you're loading a data warehouse
> with a terabyte of data; that's probably not a space where Derby is
> going to come into play, but a common enough need for other databases.
> 
[mjs] 
Yup. Constraint checking certainly slows things down.

But even Oracle recognizes that you can't stop the checking, especially in a
terabyte DW. 

With respect to Derby, you're using constraints for a reason. Most likely
you're using it in conjunction with a data dependent application.

What do you think happens if you have bad data?
Can you say core dump?
Or in terms of Java... an unexpected exception is going to be thrown....

With respect to Chris,
He's looking for a "feature" that will allow him to use a tool to load data
and make his life easier.

With respect to XML, the self referencing nature of XML makes life a little
more difficult. If you can load a record at time, you should be able to put
it in to a tree and then choose the load orders.

In terms of a data warehouse, you'd load the fact tables first, then the
parent record and then its children.

Perhaps the key isn't in turning off constraints, but building a better
tool?

> Dan