Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Fasten huge index rebuild - How To ?

Re: Fasten huge index rebuild - How To ?

From: Connor McDonald <mcdonald.connor_at_gmail.com>
Date: Fri, 20 May 2005 08:50:16 +0800
Message-ID: <5e30486205051917506226b200@mail.gmail.com>


We got good results when we tweaked the memory within the text index parameters to hundreds of megs (in our case it was about a 50G index).  I remember a gotcha something along of the lines that if even if you asked for (say) 500M of memory, then if this exceeded the "global" ctx memory parameter (set with ctx_adm), then it would be silently adjusted down...

Plus of course cranking up sort_area_size / sort_area_retained_size parameters, partitions in parallel etc etc

hth
connor

On 5/19/05, Prem Khanna J <premjhere_at_gmail.com> wrote:
> Hi All ,

>=20

> One of my collegue has got a problem on index rebuilds.
>=20
> *************************************************************************=
**=3D
> **
> We have a table of 35 partitions with a CLOB column amount to about
> 350G of data. Based on the timing for the domain index creation on
> the CLOB column in development database with smaller data volume using
> parallel degree of 8, I extrapolated (development database is on a
> much smaller box and I used linear extrapolation) that it will take
> ~53 hours to create the same index in production database, which is
> not feasible to do even in a weekend. Are you aware of any "divide
> and conquer" or any methods we can explore to create the index in much
> less time so that it is feasible to accomplish in a weekend? From
> your experiences on other applications, What do other
> people/application do if they have a huge index and it gets corrupt
> and needs to be rebuilt and the rebuild time normally takes days?
> Index online rebuild will not help us because the domain index needs
> to be finished completely so that our BREAK generation job can access
> the index by key word search.
>=20

> DB blocksize is 8k. Will use of larger tablespace or sections reduce
> rebuild time? What about future guards against index loss? Any other
> thoughts?
> *************************************************************************=
**=3D
> **
>=20

> Any idea and suggestions will be greatly appreciated. Thanks a lot!
>=20

> Regards,
> Prem J
> --
> http://www.freelists.org/webpage/oracle-l
>=20

--=20
Connor McDonald
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D
email: connor_mcdonald_at_yahoo.com
web: http://www.oracledba.co.uk

"Semper in excremento, sole profundum qui variat"

--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 19 2005 - 20:54:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US