Re: Ad-hoc/Dynamic SQL Performance

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.

-- 
Regards,
        Doug Miller (alphageek at milmac dot com)

It's time to throw all their damned tea in the harbor again.