1

let me explain this....

I had ran 1 session of sqlldr while I run indexes into a table for 6 fields... now the indexes are completed but the sqlldr running very slow it loads one file after each 30 minutes...

now when I do any alterations to set the indexes off it won't let me do it and though an error of ( ORA-000054 and ORA-000604) even if I run another sqlldr....

any suggestion will be highly appreciated.... bellow is a sample of my control files header...

OPTIONS(DIRECT=TRUE,ROWS=10000,BINDSIZE=209700000,readsize=209700000) 
load data 
append 
into table name 
FIELDS TERMINATED by '!' 
OPTIONALLY ENCLOSED by '"' 
trailing nullcols 

When I run the following query

select * from dba_dml_locks

session_id,owner,name,mode_held,mode_requested,last_convert,blocking_others 
123,username,tablename,exclusive,none,1228,not blocking 

any suggestion ... this is my daily scheduler to load around 8000+ files around 20GB so any suggestion

1

3 Answers 3

0

Since no other sessions are accessing your table now, the lock shows as NOT BLOCKING. As soon as an other session tries to manage that table, it suddenly feels it is BLOCKED. The index is used so you can not manage it as long as other sessions are using it.

Having indexes on havily loaded tables is for sure like putting a brake on the loader process. You could help yourself by checking out the docs. See Conventional and Direct Path Loads

Maybe partitioning can help you. Than you can prepare a single table, load that, finish it's indexing and perform partition exchange. Just don't create global indexes on such a table.

2
  • Hi thanks for replay...I already have partition daywise...it it always like this in oracle that slow down the loading when you have indexes... this will be huge problem since big companies with terabyte of data will face the same issue ... I wonder how they manage their loading as well as indexing...
    – hi4ppl
    Feb 11, 2015 at 8:08
  • 2
    Why not load into a standalone table, then create indexes and perform partition exchange?
    – Raj
    Feb 11, 2015 at 12:20
0

Indexes are going to slow down insert, update, and delete
As the indexes get fragmented it gets even slower

Few thing you can do

  • Load in index order or as close as you can to slow down fragmentation
  • Create the index will fill factor less then 100 to slow down fragmentation
  • Disable index, load, and then enable (build) index
1
  • I would go for the third one(if I had same kind of issues). It is very efficient and easy to do. Oct 14, 2016 at 10:13
0

You're using direct path inserts, so the column data required for the indexes is being loaded to temporary segments and then scanned and merged into the existing indexes.

This is not necessarily slower than dropping all your indexes, inserting, and rebuilding them -- the load part will be faster, but the overall time required may well be much slower and your tables will be unindexed for a while.

You can reduce the cost of the merge by having each load insert into a (sub)partition that is empty, and by performing as few separate loads as possible.

You also don't want to be running 8,000 loads, when you could be running one, so merging the files (and sorting on any data for which you want the index to have a reduced clustering factor) will be beneficial. Of course the files don't actually need to be merged -- you could create a fifo node, pipe them all into it, and read from it to perform the load.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.