🚀 go-pugleaf

Jordi Bellver <bellver@tau.uab.es> wrote ...
 
> Can someone explain under What circunstances are index automatically
> generated ?
> I know that, when I create a table with PK, automatically is generated
> an index for this table,
> but... has the same result a Fk ? or a NOT NULL constraint ?

Only on a Primary Key constraint and a Unique constraint.

Make sure you either (a) specify the storage parameters for the index via
the "USING INDEX" clause, or (b) set the parameters in the index tablespace
the way you want and make it your DEFAULT tablespace.
 
> And related with index and tunning, I think that default Storage
> parameters and
> increase parameters are in general good, not allways.

Not always!  In fact, you should change them.  My current project consists
of dozens of small servers all over the country, with no local DBA and poor
communications links.  Future data growth is not really known and the
servers are non-dedicated (ie, often acting as NT / NetWare file servers as
well).  So for easiest "hands-off" management of storage, I created
auto-extending data files, and storage parameters like the following:

initial 50 / 100 / 200k, depending on object type / size
next 50 / 100 / 200k, ditto
pctincrease 0, for consistent growth
minextents 1
maxextents unlimited (need 7.3 for this)
pctfree 10
pctused 40

So I'll wind up with potentially lots of small extents for some objects,
but at least they're consistently sized in each tablespace, and they'll
never run out of space (which would require DBA intervention, not easily
available).  And they won't need to add datafiles.  Performance is not the
major issue since the data is not overly large - so far it's been the
developer's SQL that's caused most of the problems.  And the developers (at
yet another remote site) can simply put the objects they create in the
proper tablespace and they'll have reasonable parameters.

>     For a example, if in table A I've lots of inserts and updates, what
> are the best
> parameters ? (PCTFREE, MINEXTENTS, PCTINCREASE...)


>     And if in table B are only 1 insert and lots of selects ? (by a PK
> with its own index)

Use a PCTFREE of 1 or 0 to utilize the maximum amount of space per block.

Chris
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Chris Hamilton -- toneczar@erols.com
City of Washington Pipe Band
http://www.serve.com/cowpb/chamilton.html