Home » RDBMS Server » Server Utilities » DISABLE INDEXES SQL LOADER
DISABLE INDEXES SQL LOADER [message #196528] Thu, 05 October 2006 17:01 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #196714 is a reply to message #196561] Fri, 06 October 2006 13:42 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Index rebuild means that the indes will be destroyed??


Im with conoventional path
Re: DISABLE INDEXES SQL LOADER [message #196720 is a reply to message #196714] Fri, 06 October 2006 15:07 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
REBUILD clause will re-create an existing index; if it was marked unusable, rebuilding will mark it usable.
Re: DISABLE INDEXES SQL LOADER [message #196757 is a reply to message #196714] Sat, 07 October 2006 01:11 Go to previous messageGo to next message
pareshjavkar
Messages: 7
Registered: October 2006
Location: Mumbai
Junior Member
Hi,

Index Rebuild means, it will create Indexes at some temporary location, drop the old index and then move the new index onto the tablespace specified.
Re: DISABLE INDEXES SQL LOADER [message #196762 is a reply to message #196757] Sat, 07 October 2006 01:56 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Rebuild = drop old one and create new.
Re: DISABLE INDEXES SQL LOADER [message #197015 is a reply to message #196762] Mon, 09 October 2006 09:55 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Are there any way to disable constraint when i insert via SQL Loader or i have only one choice .... like a trigger??

Thanks for your answers
Alex
Re: DISABLE INDEXES SQL LOADER [message #197057 is a reply to message #197015] Mon, 09 October 2006 15:53 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You could use ALTER TABLE statement to disable (and later enable) any constraint:

ALTER TABLE table_name MODIFY CONSTRAINT constraint_name DISABLE;
Re: DISABLE INDEXES SQL LOADER [message #198094 is a reply to message #197057] Sat, 14 October 2006 15:42 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

My problem is that i have a batch file that runs many loads for many tables (and i dont know how to do an alter table after o before to the loads)...

I would like that in my run the enable and disable constraint will be modified without user intervention...

Can you any solution for this??

Thanks
Alex
Re: DISABLE INDEXES SQL LOADER [message #198352 is a reply to message #198094] Mon, 16 October 2006 13:47 Go to previous messageGo to next message
joy_division
Messages: 4642
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 #198853 is a reply to message #198352] Wed, 18 October 2006 18:53 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

IF I DO :

alter index idx_test unusable;

I CAN´T ANY DATA:
THE MESSAGE ERROR SAY THAT THE INDEX STATE IS UNUSUABLE;

AND IF I DESABLE A CONSTRAINT ... IT´S THE SAME THAT DISABLE AN INDEX??

THANK YOU
ALEX
Re: DISABLE INDEXES SQL LOADER [message #198871 is a reply to message #198853] Wed, 18 October 2006 21:43 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #198991 is a reply to message #198871] Thu, 19 October 2006 09:19 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

I understand that the direct path is for files delimited by positions and they don´t check foreign keys... and my files are delimited by | and i need to check FK... By the way my files have very large volume as you say...

maybe i´m wrong...

That´s why y heard that i need to disable my indexes... but if i try...

alter index idx_test unusable;

when i rebuild it ... it has a long long time...


Thanks
Alex
Re: DISABLE INDEXES SQL LOADER [message #199053 is a reply to message #198991] Thu, 19 October 2006 20:38 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #201306 is a reply to message #199053] Fri, 03 November 2006 07:51 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

I´m a little confused... what is really the problem in a slow load?? The indexes or the constraints...

With direct path the referencial integrity is ignored and i need it...

and no i dont have the partitioning option?

another suggestion?

Thanks Ross
Alex

[Updated on: Fri, 03 November 2006 07:54]

Report message to a moderator

Re: DISABLE INDEXES SQL LOADER [message #201493 is a reply to message #201306] Sat, 04 November 2006 19:30 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #201535 is a reply to message #201493] Sun, 05 November 2006 13:13 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

do i need only specify

direct = true

in my control files right??

Thanks for the explanation...

Greetings
Alex
Re: DISABLE INDEXES SQL LOADER [message #201549 is a reply to message #201535] Sun, 05 November 2006 18:38 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
right
Re: DISABLE INDEXES SQL LOADER [message #202209 is a reply to message #201549] Wed, 08 November 2006 14:20 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Thanks Ross:

Now I used direct path for load and if i have data duplicated for the primary key the values are inserted then when the index try to rebuilt it's in unusuable state and i cant delete this records duplicated ...

My table is a principal table of my system and there are many stored procedures running in another tables then i can't disable all constraints and then enable....


Any quick solution for this...

Greetings
Alex
Re: DISABLE INDEXES SQL LOADER [message #202250 is a reply to message #202209] Wed, 08 November 2006 20:22 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: DISABLE INDEXES SQL LOADER [message #202395 is a reply to message #202250] Thu, 09 November 2006 08:46 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

thanks again Ross


but I disable all fk´s for this table, i dropped the index, delete the records and rebuit the index and finally enable the fk's...

Anyway I learned something new thanks to you...

Honesty
Alex
Re: DISABLE INDEXES SQL LOADER [message #202496 is a reply to message #202395] Fri, 10 November 2006 01:07 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Glad you're happy. But the point of direct-path load via SQL*Loader is that you don't have to drop the indexes.

Ross Leishman
Previous Topic: Varchar data type
Next Topic: SQLLDR: Loading Ref Columns
Goto Forum:
  


Current Time: Thu Dec 08 04:21:43 CST 2016

Total time taken to generate the page: 0.08865 seconds