Re: Ad-hoc/Dynamic SQL Performance

On Feb 16, 2:38 pm, spamb..._at_milmac.com (Doug Miller) wrote:
> In article <1203138234.151..._at_bubbleator.drizzle.com>, DA Morgan <damor..._at_psoug.org> wrote:
> >Doug Miller wrote:
> >> In article <1203049592.136..._at_bubbleator.drizzle.com>, DA Morgan
> > <damor..._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?
>

No, One, Oracle doesn't split blocks, and it certainly wouldn't do so on an insert, unlike SQL Server with an identity column, clustered index and IDENTITY_INSERT ON. Two, updates are the culprits which fracture rows in Oracle due to insufficient free space, and if you know a table will not to be updated (such as the table in this example) then it's wasteful to reserve space for an operation which won't occur. Which is not to say ALL tables need these parameters set in this manner. Tables in a heavily used OLTP system benefit from a PCTFREE set to a non-zero value as this reduces the possibility of row migration.

You clearly don't understand the concepts of PCTFREE and PCTUSED.

> >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.- Hide quoted text -
>
> - Show quoted text -

David Fitzjarrell Received on Sat Feb 16 2008 - 16:12:41 CST