Re: Oracle Text Search Across Multiple Columns and Different Tables

Bellas wrote:
> Hi,
>
> This is how I created my materialized view:
> CREATE MATERIALIZED VIEW test_search_view AS
> SELECT film_id as id, 'F' as media_type, film_title as name,
> film_synopsis AS description FROM film
> UNION ALL
> SELECT jobid as id, 'J' as media_type, jobname as name, thework AS
> description FROM job
> UNION ALL
> SELECT news_id as id, 'N' as media_type, headline as name,
> to_clob(content) AS description FROM news
>
> I'm not sure what happens to the index if the data changes in one of
> the tables. Do you know if I would need to rebuild the index?
>
> Thanks,
>
> Bellas

Well, unless you have materialized view logs on each master table, the resulting MV is not eligible for fast refresh, so each refresh will be complete (that is, all current data will be purged and the defining query re-executed to populate the MV anew.) The effect on the Text index can be disastrous unless you immediately optimize it with rebuild option or simply rebuild it from scratch. Read about materialized views in the docs and make sure your MV is fast-refreshable. In this case, Text index maintenance will be reduced to regular CTX_DDL.OPTIMIZE_INDEX() calls in fast mode and, occasionally, in full or rebuild mode.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Wed Jun 21 2006 - 11:50:52 CDT