Primary Key

[vcannadevula]

Hi All,

I have 2 jobs developed in Parallel Extender that are loading data into same table.In both the jobs I am using surrogate key generator to create id. I ran both the jobs with out specifying start value. So it took default value and now I see 2 records with each ID in the database , I didn't get any errors or warnings while running the job. Now when I am trying to delete the records from table it says

ORA-01502: index 'WIDS.XPKCLG_TYP' or partition of such index is in unusable state

I am unable to do anything with the table.It is giving me the same error for everything.
I would like to know if parallel Extender disables the constraints before loading data into table automatically ( I Didn't specify in oracle stage to disable the constraints)
Please let me know how to handle this.

Thank You

[ArndW]

If you chose a "table load" (i.e. bulk load) then you will have an "index mode" option in the Oracle stage, set to either "maintenance" or "rebuild". I think that in the log file there would have been a warning from the stage stating something like "unable to build index '....' on table"; at least that is what I get when I create duplicates.

Also check the settings for your $APT_ORACLE_LOAD_OPTIONS to see what your actual values are.

[vcannadevula]

Hi Arnd,

How can I get rid of the rows in the table now? This there any way other than droping and creating the table again.

If you chose a "table load" (i.e. bulk load) then you will have an "index mode" option in the Oracle stage, set to either "maintenance" or "rebuild". I think that in the log file there would have been a warning from the stage stating something like "unable to build index '....' on table"; at least that is what I get when I create duplicates.

Also check the settings for your $APT_ORACLE_LOAD_OPTIONS to see what your actual values are.

[ArndW]

Can you disable that index and do an appropriate SELECT on the table?

[vcannadevula]

Index is in UNUSABLE state . I am not able to disable the index.

Can you disable that index and do an appropriate SELECT on the table?

[ArndW]

What about "skip_unusable_indexes true", that should allow the delete operations to run.

[thebird]

Index is in UNUSABLE state . I am not able to disable the index.

Can you disable that index and do an appropriate SELECT on the table?

Hi,

The index will have to be rebuilt .

While doing a load into the Oracle table, if there occurs an error or a constraint violation then the index goes to the unusable state. This will have to be got back to the usable state for the job to run successfully again.

Regards,

The Bird.

[ArndW]

TheBird,

the rebuilding was not his question, though. I think that part is clear - an unusable index needs to be rebuilt. But it was the use of DELETE/SELECT that he is asking about on a table with an unusable index.

the Oracle setting that I gave will work at Oracle 10.x; I am not sure if it existed at previous versions.

[chulett]

You could hint it to use a different index or use the NOINDEX hint:

Code: Select all

/*+ INDEX_NOINDEX(table_name index_name) */

to facilitate removal of the problem rows before you rebuild the index.

[vcannadevula]

I truncated the table finally . But still I don't know how database is accepting different rows with same ID.BTW our database in Oracle 10g.

You could hint it to use a different index or use the NOINDEX hint:

Code: Select all

/*+ INDEX_NOINDEX(table_name index_name) */

to facilitate removal of the problem rows before you rebuild the index.

[chulett]

But still I don't know how database is accepting different rows with same ID

Direct path loads are fast because indexes and such are disabled during the load, so it's up to you to not load 'duplicates'. The database will accept them just fine, it's when it tries to re-enable the PK constraint (for example) that it fails and that leaves the affected index marked as UNUSABLE.