Hi Guys,
i have to do Bulk Insert from sybase db to oracle db,
and my table’s size are huge so first i must tuncate table wiht an procedure that dba’s created.(becasue i dont have DLL operation grants)
and i m aslo trying to alter indexes (with a procedure) so workflow be fast.
but i got error ORA-01502 : unusable index when trying to insert statement.
how can i ensures the performans for workflow to be fast?
Thx.
Denso (BOB member since 2010-07-22)
These are two questions.
How can you make sure you are fast? By disabling the indexes and recreating them afterwards.
What does the Oracle error mean? It means somebody has disable the Indexes but not rebuilt them afterwards.
Werner Daehn 
(BOB member since 2004-12-17)
i match the times wiht indexes and without indexes ,
for sure without indexes is faster,
i was droping and creating indexes when i have DLL operation grant but after all grants taking , i m just trying to unusable / reuild indexes with a procedure which dba gave to it me.
first i m making unusable indexes for better performans then bulk insert and rebuild them after insert.
Denso (BOB member since 2010-07-22)
Okay, and what is the problem? When does the index-unusable message appear?
And actually, doesn’t the index rebuild need the same permissions as creating the index?
Werner Daehn (BOB member since 2004-12-17)
the error is :
error message for operation : <ORA-01502: …
i guess only when i select Bulk Load : NONE and only if target table have an unique index it gives that error.
i try to load with “Bulk Load : API” and DI made indexes unusable defualt,
but the performans was worst than drop/create index with sql();
Denso (BOB member since 2010-07-22)
Let me summarize:
Option1: You use regular inserts to load a table but cannot as you brought the index into unusable state first. True, then an insert does no longer work.
Solution 1: drop index/create index
Solution 2: alter session set skip_unusable_indexes=true as session parameter.
Option 2: Use API bulkload, then DS does recreate the index but is slow
Solution: Drop/create index yourself. I am sure that the load is actually fast, you are just using a better index create statement like parallel nologging novalidate or something.
Werner Daehn (BOB member since 2004-12-17)