Error while rebuilding a text index
zigzagdna
index (textidx1) online
using:
Alter index rebuild textidx1;
If I do a query so index is used, I get:
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
Error goes away after index is completely rebuilt.
Since I am rebuilding index online, this error should not occur,
otherwise what
Is the purpose of on line index rebuild? Can someone explain how to
get rid
o the error. I cannot stop my application while rebuilding text
indexes.
Mladen Gogala
Ah, Zigzag strikes again, with another case of RTFM. Why don't you do
yourself a service and go to oracle-l? Steve Adams will gladly protect
your feelings from the likes of me. You obviously don't know how to
read the literature, so let me help you. Here it goes, from the 11.2
literature. Pay attention to the last paragraph in the quote.
<quote>
ALTER INDEX REBUILD Syntax
Use ALTER INDEX REBUILD to rebuild an index, rebuild an index partition,
resume a failed operation, replace index metadata, add stopwords to an
index, or add sections and stop sections to an index.
ALTER INDEX REBUILD has its own sub-syntax. That is, its parameters have
their own syntax. For example, the ALTER INDEX REBUILD PARAMETERS command
can take either REPLACE or RESUME as an argument, and ALTER INDEX REBUILD
PARAMETERS ('REPLACE') has several arguments it can take.
Valid examples of ALTER INDEX REBUILD include the following statements:
ALTER INDEX REBUILD PARALLEL n
ALTER INDEX REBUILD PARAMETERS (SYNC memsize)
ALTER INDEX REBUILD PARAMETERS (REPLACE DATASTORE datastore_pref)
ALTER INDEX REBUILD PARAMETERS (REPLACE WORDLIST wordlist_pref)
This is the syntax for ALTER INDEX REBUILD:
ALTER INDEX [schema.]index REBUILD [PARTITION partname] [ONLINE]
[PARAMETERS
(paramstring)][PARALLEL N] ;
PARTITION partname
Rebuilds the index partition partname. Only one index partition can
be built at a time.
When you rebuild a partition you can specify only RESUME or REPLACE
in paramstring. These operations work only on the partname you specify.
With the REPLACE operation, you can only specify MEMORY and STORAGE
for each index partition.
Adding Partitions To add a partition to the base table, use the ALTER
TABLE SQL statement. When you add a partition to an indexed table, Oracle
Text automatically creates the metadata for the new index partition. The
new index partition has the same name as the new table partition. Change
the index partition name with ALTER INDEX RENAME.
Splitting or Merging Partitions Splitting or merging a table
partition with ALTER TABLE renders the index partition(s) invalid. You
must rebuild them with ALTER INDEX REBUILD.
[ONLINE]
ONLINE enables you to continue to perform updates, inserts, and
deletes on a base table. It does not enable you to query the base table.
Note:
You can specify REPLACE or RESUME when rebuilding an index or an
index partition ONLINE.
</quote>
The source is: Oracle® Text Reference 11g Release 2 (11.2) in the 1st
chapter called "Oracle Text SQL Statements and Operators". This wasn't so
hard, was it? The next step is to look in the documentation for yourself,
it isn't that hard. Lastly, I used the version 11.2 documentation although
I am pretty sure that your database isn't 11.2 because 11.2 has fewer
limitations than the previous releases. If it was possible in the version
11.2, it might be possible in the version 10.2.0.4. As it isn't possible
in the 11.2, it most certainly is not possible in any of the lower
versions. Please, don't ask other people to read the documentation for
you. You're almost grown up, you can do it yourself.
ddf
On Dec 9, 11:23 pm, zigzagdna <zigzag...@yahoo.com> wrote:
> I am using Oracle 10.2.0.3 on HP UNIX 11i. I am rebuilding a text
> index (textidx1) online
> using:
> Alter index rebuild textidx1;
Sorry, no, you're not rebuilding this index online as you never told
Oracle you wanted to do that by using the ONLINE directive in the
ALTER INDEX statement:
Alter index rebuild textidx1 online;
> If I do a query so index is used, I get:
> ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
> Error goes away after index is completely rebuilt.
And it should; ONLINE rebuilds only allow DML on the base table, not
queries OF the base table. The documentation clearly states this.
> Since I am rebuilding index online, this error should not occur,
You're not rebuilding online, and even if you were the error WILL
still occur because queries against the base table are not permitted
during a rebuild, online or not.
> otherwise what
> Is the purpose of on line index rebuild?
It allows OLTP applications to continue to insert/update/delete data
in the base table so people can continue working.
>Can someone explain how to
> get rid
> o the error.
Stop trying to query the base table while the rebuild is in progress.
> I cannot stop my application while rebuilding text
> indexes.
And you don't have to stop your application, you simply need to
learn how to actually do an online index rebuild and stop trying to
query the base table while the rebuild is occurring.
David Fitzjarrell
zigzagdna
I had already read this confusing document a while ago. Text indexes
seem different, when I enter:
alter index X_TEXT_ENTITY rebuild online;
I get syntax error (this is not the case with normal indexes).
alter index X_TEXT_ENTITY rebuild online
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX X_TEXT_ENTITY failed
DRG-10562: missing alter index parameter
If I use the syntax:
alter index X_TEXT_ENTITY rebuild online parameters ('RESUME');
There is no syntax error but it seems this statement does not do
anything by looking at last analyzed in user indexes.
Since my index did not really fail adding RESUME does not make sense
anyway,
So one cannot rebuild text indexes "ONLINE", i.e., while index is
being rebuilt one can do update/insert/delete on the table.
joel garry
I learn something new every day: http://www.oracle.com/technology/products/text/htdocs/Index_Maintenance.html
"This will de-fragment the $X index for fastest access. If you want to
be able to continue to run queries while this is happening, you should
append the ONLINE keyword. "
Sometimes I learn strange things.
jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2009/dec/10/workers-can-exchange-worthless-stock/
ddf
>
> - Show quoted text -
Possibly you should consider using REPLACE rather than RESUME since
you're not resuming a prior index rebuild. Or maybe you should read
the document Joel provided.
David Fitzjarrell
zigzagdna
> On Dec 9, 8:23 pm, zigzagdna <zigzag...@yahoo.com> wrote:
>
> > I am using Oracle 10.2.0.3 on HP UNIX 11i. I am rebuilding a text
> > index (textidx1) online
> > using:
> > Alter index rebuild textidx1;
> > If I do a query so index is used, I get:
> > ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
> > Error goes away after index is completely rebuilt.
> > Since I am rebuilding index online, this error should not occur,
> > otherwise what
> > Is the purpose of on line index rebuild? Can someone explain how to
> > get rid
> > o the error. I cannot stop my application while rebuilding text
> > indexes.
>
>
> "This will de-fragment the $X index for fastest access. If you want to
> be able to continue to run queries while this is happening, you should
> append the ONLINE keyword. "
>
> Sometimes I learn strange things.
>
> jg
> --
Joel:
Thanks a lot fpr the link you provided. It has lots of useful
information.
Mladen Gogala
> "This will de-fragment the $X index for fastest access. If you want to
> be able to continue to run queries while this is happening, you should
> append the ONLINE keyword. "
Unless the index is not normal.It doesn't apply to bitmap indexes, for
instance. Domain indexes also require special considerations.
joel garry
Of course, the quote has lost the, shall we say, context of the
paper. The X$ index is a b-tree index. I'm wondering about
reconciling this quote with your quote "ONLINE enables you to continue
to perform updates, inserts, and deletes on a base table. It does not
enable you to query the base table. " Since I don't work with this
stuff, it's all academic to me, but one never knows when one will be
blindsided by it. I may be misunderstanding the paper, docs or the
OP, but something doesn't add up.
jg
--
@home.com is bogus.
"...don't bend reality to your ideals - real life works the other way
around." - Noons