Skip site navigation (1) Skip section navigation (2)

Re: Equivalent praxis to CLUSTERED INDEX?

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Adi Alurkar <adi(at)sf(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
Date: 2004-08-27 17:27:07
Message-ID: 200408271727.i7RHR7Z13191@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
But what is the advantage of non-full pages in Oracle?

---------------------------------------------------------------------------

Adi Alurkar wrote:
> Greetings,
> 
> I am not sure if this applies only to clustering but for storage in 
> general,
> 
> IIRC  Oracle has 2 parameters that can be set at table creation :
> from Oracle docs
> 
> PCTFREE integer :
> Specify the percentage of space in each data block of the table, object 
> table OID index, or partition reserved for future updates to the 
> table's rows. The value of PCTFREE must be a value from 0 to 99. A 
> value of 0 allows the entire block to be filled by inserts of new rows. 
> The default value is 10. This value reserves 10% of each block for 
> updates to existing rows and allows inserts of new rows to fill a 
> maximum of 90% of each block.
> PCTFREE has the same function in the PARTITION description and in the 
> statements that create and alter clusters, indexes, materialized views, 
> and materialized view logs. The combination of PCTFREE and PCTUSED 
> determines whether new rows will be inserted into existing data blocks 
> or into new blocks.
> 
> PCTUSED integer
> Specify the minimum percentage of used space that Oracle maintains for 
> each data block of the table, object table OID index, or 
> index-organized table overflow data segment. A block becomes a 
> candidate for row insertion when its used space falls below PCTUSED. 
> PCTUSED is specified as a positive integer from 0 to 99 and defaults to 
> 40.
> PCTUSED has the same function in the PARTITION description and in the 
> statements that create and alter clusters, materialized views, and 
> materialized view logs.
> PCTUSED is not a valid table storage characteristic for an 
> index-organized table (ORGANIZATION INDEX).
> The sum of PCTFREE and PCTUSED must be equal to or less than 100. You 
> can use PCTFREE and PCTUSED together to utilize space within a table 
> more efficiently.
> 
> PostgreSQL could take some hints from the above.
> 
> On Aug 27, 2004, at 1:26 AM, Gaetano Mendola wrote:
> 
> > Greg Stark wrote:
> >
> >> The discussions before talked about a mechanism to try to place new
> > > tuples as close as possible to the proper index position.
> >
> > Means this that an index shall have a "fill factor" property, similar 
> > to
> > Informix one ?
> >
> > From the manual:
> >
> >
> > The FILLFACTOR option takes effect only when you build an index on a 
> > table
> > that contains more than 5,000 rows and uses more than 100 table pages, 
> > when
> > you create an index on a fragmented table, or when you create a 
> > fragmented
> > index on a nonfragmented table.
> > Use the FILLFACTOR option to provide for expansion of an index at a 
> > later
> > date or to create compacted indexes.
> > When the index is created, the database server initially fills only 
> > that
> > percentage of the nodes specified with the FILLFACTOR value.
> >
> > # Providing a Low Percentage Value
> > If you provide a low percentage value, such as 50, you allow room for 
> > growth
> > in your index. The nodes of the index initially fill to a certain 
> > percentage and
> > contain space for inserts. The amount of available space depends on the
> > number of keys in each page as well as the percentage value.
> > For example, with a 50-percent FILLFACTOR value, the page would be half
> > full and could accommodate doubling in size. A low percentage value can
> > result in faster inserts and can be used for indexes that you expect 
> > to grow.
> >
> >
> > # Providing a High Percentage Value
> > If you provide a high percentage value, such as 99, your indexes are
> > compacted, and any new index inserts result in splitting nodes. The
> > maximum density is achieved with 100 percent. With a 100-percent
> > FILLFACTOR value, the index has no room available for growth; any
> > additions to the index result in splitting the nodes.
> > A 99-percent FILLFACTOR value allows room for at least one insertion 
> > per
> > node. A high percentage value can result in faster selects and can be 
> > used for
> > indexes that you do not expect to grow or for mostly read-only indexes.
> >
> >
> >
> >
> > Regards
> > Gaetano Mendola
> >
> >
> >
> >
> >
> > ---------------------------(end of 
> > broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings
> >
> >
> --
> Adi Alurkar (DBA sf.NET) <adi(at)vasoftware(dot)com>
> 1024D/79730470 A491 5724 74DE 956D 06CB  D844 6DF1 B972 7973 0470
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-performance by date

Next:From: Jeremy DunnDate: 2004-08-27 17:39:35
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
Previous:From: Josh BerkusDate: 2004-08-27 17:14:29
Subject: Re: Query performance issue with 8.0.0beta1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group