Re: Questions regarding direct path loader

In article 32y_at_cbnews.cb.att.com, awy_at_cbnews.cb.att.com (andre.w.yoshida) writes:
-->We're considering using the Oracle direct path load using SQL*loader.
-->We would like to "append" to a table using this method but have some
-->concerns regarding simultaneous queries on previously loaded data:
-->
-->Our design assumptions are:
--> - Only one writer (loader) to a table at a time - the data in the table
--> is inserted using the loader, but only queries (selects) can be made
--> on the table by other applications.
--> - No indexing applied to the table being loaded
-->
-->1) If a query is in progress on the table, what impact does this have when the
--> direct path load is invoked?
-->
-->2) Conversely, if the direct path load is in progress, what impact does this
--> have when queries are invoked?
-->
-->It seems to be unclear as to the degree of concurrent table access (between
-->a direct path loader and multiple applications performing queries on
-->the same table - but not the rows that are being loaded) that is supported
-->using the direct path loader and any insights regarding contention
-->and locking, etc. would be appreciated.

when you use direct path you eliminate the overhead of the Oracle kernel by creating datablocks that are already in the Oracle database block format, instead of filling a bind array and passing it to the RDBMS with an INSERT.

When you load to a table with direct path it will lock that table until it is finished loading. So there should be no worries about others affecting you. (they will have to wait till you finish)

However you may only use direct path if (1) the table isn't clustered (2) there are no active transactions on it (3) SQL strings are not used in the control file. (4) there are no select statements on nthat table (if it's indexed)

PS One thing I found really annoying is the fact that it doesn't write anything to the log file until it's completey finished. so if you have a large load (we did 33million records with this method) the process could appear to be 'just sitting there doing nothing' for hours mwhen in fact it is processing.  

---
-----------------------------------------------------------------------------
David Crowson          | "Rude alert! Rude alert! An electrical fire
DBA (ORACLE V.4,5,6,7) | has knocked out my voice-recognition unicycle! Many 
Amoco Exploration      | Wurlitzers are missing from my database! Abandon shop!  
Ealing, London         | This is not a daffodil! Repeat: This is not a daffodil!"
dcrowson_at_amoco.com     | - Holly (Ship's computer on Red Dwarf)
"My views not Amoex's" |               =%^)