Re: Creating Oracle Text Indexes
Date: Mon, 9 Mar 2009 09:04:57 -0700 (PDT)
Message-ID: <ef0f5f2a-e37e-4b76-835d-c689911c568f_at_s28g2000vbp.googlegroups.com>
On Mar 9, 6:34 pm, Jeremy <jeremy0..._at_gmail.com> wrote:
> In article <adff2a9a-8424-4dba-a8cc-
> 43323e82c..._at_a12g2000yqm.googlegroups.com>,
> vladimir.zakharyc..._at_gmail.com says...>
>
> > Did you consider running ctx_ddl.optimize_index() at REBUILD or FULL
> > level regularly (especially if your data is volatile and index
> > fragmentation/garbage percentage grows quickly?) Rebuilding the index
> > by dropping and creating it every so often makes it unavailable for
> > the duration of "rebuild", which might not please your users, and
> > consumes a lot of resources (mostly CPU, because Text needs to put
> > every document being indexed through external filter program and then
> > parse and process resulting XML); ctx_ddl.optimize_index
> > (index_name,'REBUILD') is online and is much cheaper.
>
> We are on Standard Edition and so online index DDL operations are not
> supported (at least to my knowledge). We can make the index unavailable
> overnight so that is not an issue.
>
> On this rebuild we used the setting "memory 64m" in the parameters
> clause. I would be very interested to see any information which
> discusses what optimal values of the "memory" parameter might be?
>
> --
> jeremy
Jeremy,
CTX_DDL.OPTIMIZE_INDEX('INDEX_NAME','REBUILD') is online regardless the Oracle edition, it's just the way it works, so you definitely should give it a try. REBUILD optimization builds a new $I table while still keeping current available for querying, and then switches old and new tables, so you should have enough space in the index tablespace to accommodate about twice the size of $I + $X for the duration of the optimization.
As of memory, the docs say that the larger amount you give the lower is index fragmentation and the faster it builds. If you can afford giving, say, 360M of RAM for the index build, this might be sufficient to fit the whole index in memory and fragmentation right after the build should be minimal (at least in theory.) I didn't conduct extensive tests on how much MEMORY parameter really affects fragmentation and indexing speed though, so can't say for sure.
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Mon Mar 09 2009 - 11:04:57 CDT