Re: Ad-hoc/Dynamic SQL Performance
From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 17 Feb 2008 12:46:42 -0800
Message-ID: <1203281176.230546@bubbleator.drizzle.com>
>
> That's not necessarily a bad thing, though. The less free space there is
> within blocks, the more likely it is that a record insert will cause a block
> split, no?
>
>
> Please elaborate.
Date: Sun, 17 Feb 2008 12:46:42 -0800
Message-ID: <1203281176.230546@bubbleator.drizzle.com>
Doug Miller wrote:
> In article <1203138234.151805_at_bubbleator.drizzle.com>, DA Morgan <damorgan_at_psoug.org> wrote:
>> Doug Miller wrote: >>> In article <1203049592.136561_at_bubbleator.drizzle.com>, DA Morgan >> <damorgan_at_psoug.org> wrote: >>> One thing I don't understand: why is this >>>> SQL> CREATE TABLE test1(field1 NUMBER(10) PRIMARY KEY); >>> inferior to this? >>>> SQL> CREATE TABLE test2(field1 NUMBER(10)) >>>> 2 PCTUSED 99 >>>> 3 PCTFREE 0; >>>> >>>> SQL> ALTER TABLE test2 >>>> 2 ADD CONSTRAINT pk_test2 >>>> 3 PRIMARY KEY (field1) >>>> 4 USING INDEX >>>> 5 PCTFREE 0; >> You are wasting space: Writing more blocks than required.
>
> That's not necessarily a bad thing, though. The less free space there is
> within blocks, the more likely it is that a record insert will cause a block
> split, no?
>
>> And violating more than a few "best practice" guidelines.
>
> Please elaborate.
Data blocks don't split. Index blocks based on incrementing a surrogate key could be an issue with a small sample that is not something you are going to solve with the default tablespace values provided by Oracle.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Sun Feb 17 2008 - 14:46:42 CST