This is where the Bulk processing capabilities of the Oracle database come in.
Instead of fetching or updating a single row at a time since Oracle 8i it is possible to use the bulk features in the Oracle database.
It’s very possible that some text is has been copied-and-pasted (but I’ve since forgotton).
Also, the scripts, which are available at the bottom, were not originally written by me, but were altered.
alter table t enable novalidate constraint uniq_a; alter table t enable novalidate constraint uniq_a * ERROR at line 1: ORA-02299: cannot validate (TUSKERDEV.
UNIQ_A) - duplicate keys found Here is what Oracle 9i (Release 2) Document says : Constraint States You can enable or disable integrity constraints at the table level using the CREATE TABLE or ALTER TABLE statement.
This means that every time your PL/SQL code needs data from the database, or writes to it control is passed from the PL/SQL runtime engine to the SQL. Not too much time individually, which is why you don’t see any slowdown of the program when you run a SQL statement that returns a single row, but when you run SQL statements in a loop then you actually perform a lot of SQL statements, which involves a lot of context switching.
This process involves a lot of work which has to be done by the two engines, like writing the SQL statement in an area the SQL engine can access and having the SQL engine write the result in an area the PL/SQL engine can access. You actually want to reduce these context switches to a single one and bring back the entire resultset in one pass.
As former Search Oracle expert Karen Morton says, an OUTER JOIN extends the result of a simple join by returning all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
Community member cpflames explains that the keyword OUTER is optional, but he says it really should be mandatory.
In an ALTER TABLE statement, ENABLE NOVALIDATE resumes constraint checking on disabled constraints without first validating all data in the table. The constraint is not checked and is not necessarily true.
DISABLE VALIDATE disables the constraint, drops the index on the constraint, and disallows any modification of the constrained columns.
Note that the guidelines and scripts assume a default Oracle 10g Release 2 (10.2) installation with default initialization parameters, etc.