| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Automatic index generation & little tunning
Jordi Bellver <bellver_at_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
 |
 |