Primary Key
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 143
- Joined: Thu Nov 04, 2004 6:53 am
Primary Key
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
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
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.
Also check the settings for your $APT_ORACLE_LOAD_OPTIONS to see what your actual values are.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Charter Member
- Posts: 143
- Joined: Thu Nov 04, 2004 6:53 am
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.
How can I get rid of the rows in the table now? This there any way other than droping and creating the table again.
ArndW wrote: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.
Can you disable that index and do an appropriate SELECT on the table?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Charter Member
- Posts: 143
- Joined: Thu Nov 04, 2004 6:53 am
What about "skip_unusable_indexes true", that should allow the delete operations to run.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Hi,vcannadevula wrote:Index is in UNUSABLE state . I am not able to disable the index.
ArndW wrote:Can you disable that index and do an appropriate SELECT on the table?
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.
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
You could hint it to use a different index or use the NOINDEX hint:
to facilitate removal of the problem rows before you rebuild the index.
Code: Select all
/*+ INDEX_NOINDEX(table_name index_name) */
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Charter Member
- Posts: 143
- Joined: Thu Nov 04, 2004 6:53 am
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.
chulett wrote:You could hint it to use a different index or use the NOINDEX hint:
to facilitate removal of the problem rows before you rebuild the index.Code: Select all
/*+ INDEX_NOINDEX(table_name index_name) */
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.vcannadevula wrote:But still I don't know how database is accepting different rows with same ID
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers