Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Drawbacks for direct path

Re: Drawbacks for direct path

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 14 Dec 2004 09:08:22 +1100
Message-ID: <41be12d3$0$5287$afc38c87@news.optusnet.com.au>


zeb wrote:
> Hi,
>
> It seems direct path for export and SQL Loader is always
> quicker than conventional path ...
>
> What are the drawbacks for direct path ?
> Why not to use always direct path ?
> When it is better to use diret path and
> when it is better to use conventional path ?
>
> Thanks for your lights ...
>
> Oracle 8i, 9i
> HP-UX 11.0 AIX 5L
>
>

Direct path export requires that the data in the database is in the same character set as the character set being used by the session doing the exporting. So if you have a WE8ISO8859P1 character set database, for example, but have connected via a Windows command shell (and hence, possibly, using MSWIN1252) the implicit character set conversion that arrangement requires would prevent you from doing a direct path export.

Direct path SQL Loader loads bypass the usual insert mechanism (that's why they're fast, of course) -but it also means that constraint checking and trigger firing won't happen properly. Now, for example, if you are loading into a table that usually gets its primary key generated by an insert trigger grabbing the next sequence number, that is going to be problematic. You will find that the type of constraints that are not checked but which are violated by the load will end up disabled, novalidate. Re-enabling the constraint will then (a) require a full table scan and (b) lots of table locking; and (c) you'll have to hunt down the violating records that were loaded before attempting to re-enable the constraint, of course.

Whether all of that is worth the speed boost the direct path method gives is open to debate. Usually it is. But your circumstances might be different.

Regards
HJR Received on Mon Dec 13 2004 - 16:08:22 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US