| DISABLE INDEXES SQL LOADER [message #196528] |
Thu, 05 October 2006 17:01  |
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
|
Senior Member |
|
|
Hi
I´m loading data with SQL Loader, i heard that i need to disable my indexes to load faster but i dont know how disable them in my control file...
Or SQL Loader disable the indexes??
The syntax REENABLE DISABLE CONSTRAINTS apply for this??
Thanks to all for your answers.
Greetings
Alex
|
|
|
|
| Re: DISABLE INDEXES SQL LOADER [message #196533 is a reply to message #196528] |
Thu, 05 October 2006 17:39  |
Nirmala
Messages: 43
Registered: October 2004
|
Member |
|
|
you can not disableindexes in the control file. You will have to disable the index in sqlplus. You can us the below command to disable the index.
alter index idx_test unusable;
Once the load is complete you can recreate the index using the following command.
alter index idx_test rebuild;
|
|
|
|
| Re: DISABLE INDEXES SQL LOADER [message #196561 is a reply to message #196533] |
Thu, 05 October 2006 22:47 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If you are using direct path load (DIRECT=TRUE) then you do not need to disable indexes - SQL*Loader defers index maintenance until the end of the load by default. You can alter this default functionality to maintain indexes on the fly if you don't have enough space but its less efficient unless your source data is sorted.
If you are using conventional path, see @Nirmala's comment above.
Ross Leishman
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: DISABLE INDEXES SQL LOADER [message #198352 is a reply to message #198094] |
Mon, 16 October 2006 13:47 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
|
Why not put Littlefoot's solution in the same batch file? First you disable the constraints via a Sql*Plus script, then you SQL*Load, then you enable them again via another Sql*Plus script.
|
|
|
|
|
|
| Re: DISABLE INDEXES SQL LOADER [message #198871 is a reply to message #198853] |
Wed, 18 October 2006 21:43 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If you are loading sufficiently large volumes to make index rebuild faster than index maintenance, you should be using DIRECT=TRUE - forget about conventional path. Direct path (by default) performs index maintenance after the load, and only on the new rows (doesn't have to rebuild the index for old rows).
If volumes are not large enough to warrant direct path, then why worry about the performance of index maintenance?
Ross Leishman
|
|
|
|
|
|
| Re: DISABLE INDEXES SQL LOADER [message #199053 is a reply to message #198991] |
Thu, 19 October 2006 20:38 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If the volumes are high, DONT use conventional path.
You can use direct path with delimited files - no problems.
Referential Integrity constraints can be invalidated by the load and then re-enabled automatically afterwards with the REENABLE DISABLED CONSTRAINTS clause. However it does have to re-check the entire table.
The fastest way to load such a table is via Partition Exchange. Is your table partitioned?
Ross Leishman
|
|
|
|
|
|
| Re: DISABLE INDEXES SQL LOADER [message #201493 is a reply to message #201306] |
Sat, 04 November 2006 19:30 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| superoscarin@hotmail.com wrote on Sat, 04 November 2006 00:51 | With direct path the referencial integrity is ignored and i need it...
|
No, it is not ignored, it just has to validate the ENTIRE table after a load (assuming you use REENABLE DISABLED CONSTRAINTS. Not so much different from rebuilding an entire index when you think about it.
Tell you what, YOU benchmark the following:
- Conventional load with indexes enabled
versus
- Conventional load with indexes dropped, plus
- Time taken to re-enable indexes
versus
- Direct path load with indexes enabled
If you have more than 1 Million rows, I guarantee you that the third will be faster, but clearly the only way for you to believe it is to do it yourself. And - surprise, surprise - it's also easy to implement. I wonder if thats why Oracle invented it?
Ross Leishman
[Updated on: Sat, 04 November 2006 19:32] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: DISABLE INDEXES SQL LOADER [message #202250 is a reply to message #202209] |
Wed, 08 November 2006 20:22 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Yes, there is a solution.
If there are rows you want to overwrite, or not load at all, then you should be using Externally Organised Tables instead of SQL*Loader.
With an EOT pointing to your raw data file, you can either delete the pre-existing rows before you INSERT /*+APPEND*/, or you can use MERGE /*+APPEND*/, although there is a bug in the MERGE /*+APPEND*/ in 10gR1 if you are merging into a table with an MV Log.
Unfortunately, the /*+APPEND*/ hint is silently ignored if you have referential integrity enabled, and conventional path is used instead of direct path. To deal with this, you can do the DELETE in SQL using an EOT, and then run the load through SQL*Loader, which will automaticaly disable and enable constraints. Alternatively, disable constraints manually before you INSERT/MERGE.
The best way though, is to partition the table, use and EOTs, CTAS, and Exchange Partition. You have already said that the table is not partitioned, so I won't go into this any further.
Ross Leishman
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
