True but most Oracle implementations world wide are still on versions 8 and 9 so it's a worthwhile discussion. Simply avoiding the existing preferred method for high performance loading because of a future change is a bad architecture decision.palaniappan wrote:just wanted to highlight that the support to SQL Loader is to be discontinued
Oracle OCI Load (Automatic Mode) Error
Moderators: chulett, rschirm, roy
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
rleishman
- Premium Member
- Posts: 252
- Joined: Mon Sep 19, 2005 10:28 pm
- Location: Melbourne, Australia
- Contact:
Also worth noting that if the OCI Load stage ceases to "support" SQL*Loader then we are hardly any worse off:
- You can only use delimited format
- You cannot choose a non-printable delimiter (eg. TAB)
- It does not produce proper CSV format (quotes are not doubled)
- And if your data contains quotes, you cannot choose another enclosure character
Honestly, I won't be mourning the loss. The only safe way to deploy SQL*Loader at the moment is to roll your own CTL file and write the data file with the SEQ stage.
Also worth noting that External tables are not the God-send that Oracle would have us believe for large volume loading. Whilst INSERT /*+APPEND*/ can give us direct-path load performance, there are still two major drawbacks:
1. Foreign keys must be disabled. SQL*Loader in Direct Path mode defers FK checking. This is not equivalent to disabling the RI before the INSERT /*+APPEND*/, because the re-enable has to re-validate the entire table.
2. Indexes are maintained on the fly. Not only is this slower, but the index blocks are written to the Rollback Segments. If the load is big enough, you can blow your rollback segments with INSERT /*+APPEND*/. SQL*Loader in Direct Path defers index maintenance until after the load, and does not seem to be subject to the same Rollback problem (I have no proof, but have never heard of Direct Path load breaking the rollback segments).
For my money, External Tables are only good for MERGE loading (upserts). OCI Load in Automatic mode is superior for INSERTs in speed, simplicity, and robustness. SQL*Loader (Direct) - in isolation - is superior again in speed, but may be inferior when serialised behind a slow transformation (see my earlier note).
- You can only use delimited format
- You cannot choose a non-printable delimiter (eg. TAB)
- It does not produce proper CSV format (quotes are not doubled)
- And if your data contains quotes, you cannot choose another enclosure character
Honestly, I won't be mourning the loss. The only safe way to deploy SQL*Loader at the moment is to roll your own CTL file and write the data file with the SEQ stage.
Also worth noting that External tables are not the God-send that Oracle would have us believe for large volume loading. Whilst INSERT /*+APPEND*/ can give us direct-path load performance, there are still two major drawbacks:
1. Foreign keys must be disabled. SQL*Loader in Direct Path mode defers FK checking. This is not equivalent to disabling the RI before the INSERT /*+APPEND*/, because the re-enable has to re-validate the entire table.
2. Indexes are maintained on the fly. Not only is this slower, but the index blocks are written to the Rollback Segments. If the load is big enough, you can blow your rollback segments with INSERT /*+APPEND*/. SQL*Loader in Direct Path defers index maintenance until after the load, and does not seem to be subject to the same Rollback problem (I have no proof, but have never heard of Direct Path load breaking the rollback segments).
For my money, External Tables are only good for MERGE loading (upserts). OCI Load in Automatic mode is superior for INSERTs in speed, simplicity, and robustness. SQL*Loader (Direct) - in isolation - is superior again in speed, but may be inferior when serialised behind a slow transformation (see my earlier note).
Ross Leishman