X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 660A55E46E7 for ; Fri, 27 Aug 2004 14:27:38 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 85386-05 for ; Fri, 27 Aug 2004 17:27:32 +0000 (GMT) Received: from candle.pha.pa.us (candle.pha.pa.us [207.106.42.251]) by svr1.postgresql.org (Postfix) with ESMTP id 3A53C5E46CD for ; Fri, 27 Aug 2004 14:27:31 -0300 (ADT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id i7RHR7Z13191; Fri, 27 Aug 2004 13:27:07 -0400 (EDT) From: Bruce Momjian Message-Id: <200408271727.i7RHR7Z13191@candle.pha.pa.us> Subject: Re: Equivalent praxis to CLUSTERED INDEX? In-Reply-To: <4661B1AE-F849-11D8-8B8C-000A95C4BD7A@sf.net> To: Adi Alurkar Date: Fri, 27 Aug 2004 13:27:07 -0400 (EDT) Cc: pgsql-performance@postgresql.org X-Mailer: ELM [version 2.4ME+ PL108 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/394 X-Sequence-Number: 8030 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) > 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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073