The Constraint Optimization series:
In the previous parts of this series I showed that Oracle does a nice optimization – that may save plenty of time – when we add in a (more...)
About a year ago I wrote the post Subtleties – Part 1 (SQL and PL/SQL). I wrote there:
Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement.
One example is the COLLECT aggregate function with the DISTINCT option.
And I showed an example that was executed in 18.104.22.168 and in (more...)
The Constraint Optimization series:
In the previous parts of this series I showed that Oracle does a nice optimization – that may save plenty of time – when we add in a single ALTER TABLE statement a new (nullable with no default value) column (more...)
In a recent post I showed that Oracle does a nice optimization when we add a new (nullable with no default value) column with an inline (a.k.a. “column-level”) check constraint in a single ALTER TABLE statement. This optimization does not apply for out-of-line (“table-level”) check constraints.
So, what about foreign key constraints?
Clearly, when adding a new nullable with no default value column to a table which contains records, then, by definition, the (more...)
In the previous post I showed that Oracle does a nice optimization when we add a new (nullable with no default value) column with an inline check constraint in a single ALTER TABLE statement.
However, there is one case where this optimization allows for data integrity violation instead of forbidding it (which makes it a bug, in this specific case, rather than an optimization). It happens when the check constraint is “column IS NOT NULL”.
I have a table T with many records and I want to add a new column C to this table with some check constraint on C.
Does it matter if I use the following statement
ALTER TABLE T ADD (C NUMBER, CONSTRAINT C_CHK CHECK (C>0));
or this one
ALTER TABLE T ADD (C NUMBER CONSTRAINT C_CHK CHECK (C>0));
Note that the only difference is the comma that appears in the first option and not (more...)
The last version of Oracle in which CHOOSE was officially supported as an OPTIMIZER_MODE parameter value was 9.2.
This is what the documentation of Oracle 9.2 says about it:
The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available.
If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal (more...)
Suppose that every time we add records into the T1 table we have to do some additional stuff.
One option to implement this is by using an AFTER INSERT trigger that will perform this additional stuff, but I really dislike this option (mainly because the code becomes hidden in a way, and there may be a negative impact on performance).
I prefer writing a procedure that inserts the records into T1 and performs this additional (more...)
We all know that adding a new index may change the execution plans of existing queries – sometimes improving the performance, other times causing a performance degradation (and sometimes not making any difference).
But is it true even if the new index is not used by the new execution plan? And even if the new index is invisible?
I’ve recently discovered that the answer is yes for a specific scenario that involves a descending and (more...)
Here’s my contribution to the OTN Appreciation Day.
Edition-Based Redefinition (EBR) is a really great feature, or more accurately a set of features, that was introduced in Oracle 11.2 and allows for online application upgrades using hot rollover. If you know me then you know that I talk (ok, preach) about it a lot, and from a long and successful experience.
It is impossible to learn EBR in 5 minutes, but perhaps you’ll understand (more...)