Re: Constraints create large indexes

From: Julio Negueruela <julio.negueruela_at_si.unirioja.es>
Date: Thu, 20 May 1999 14:01:56 +0200
Message-ID: <3743F9B4.E5A2DBC7@si.unirioja.es>

        Braddon Hurley escribió:
> =

> I have been creating check constraints for referential integrity. Each =

of these creates an internal index, with each of these indices consuming =
10M of disk space for even the smallest tables. Does anybody know how I m=
ight be able to reduce this amount at the time that the index is created?=

 Maybe it is a parameter somewhere?
> =

> Brad

Take a look at:

alter table <table>

   constraint <name>
     check (<condition>) =

         using index tablespace <tbsp_name>
                pctfree x
                pctused x
                storage (initial x k
                         next x k
                         pctincrease x k)

Play with this parameters in order to try to reduce the index size. For example, if you know there won't be a lot of updates or inserts you can decrease the pctfree and increase the pctused. Avoid a pctincrease greater than zero. Estimate the size of your index so you can be put it into a segment,...

Regards.
-- =

Julio Negueruela
DBA Servicio Informático

Universidad de La Rioja      -      Spain
Telf: 941-299179     Fax: 941- 299180

mailto:julio.negueruela_at_si.unirioja.es Received on Thu May 20 1999 - 07:01:56 CDT