view counter

Feed items

OuterJoin with Filter Predicate

I recently came across a query where an outer join was being used. Such an OuterJoin allows us to create a "dummy" row for the columns from a table where the join fails so that we can still present the values from the other table in the join.
However, the query was subsequently modified by the developer to add a filter predicate on the table that the OuterJoin was defined on.
Remember that an OuterJoin returns columns with NULL values for the table that is Outer Joined.

I/O for OutOfLine LOBs

When you define a LOB (CLOB or BLOB) as a column for a table, you have the choice of specifying whether it is Inline (stored with the row) or OutOfLine (stored in a separate segment). (Note : An Inline LOB still has a LOB Segment because an entry exceeding 4000bytes will automatically be stored OutOfLine).
The data in the OutOfLine LOB, being stored in a separate segment (which is of type 'LOBSEGMENT') is accessed via an Index (which is of type 'LOBINDEX').

Oracle Enterprise Cloud Summit in Singapore

The Oracle cloud event in Singapore was a mild success. It's not clear how many have adopted or tested clouds in this region -- not many responded to the SMS poll immediately.

Published with Blogger-droid v1.6.7

Cardinality Estimates in Dynamic Partition Pruning

Dynamic Partition Pruning occurs when the Optimizer cannot determine which Partition(s) of a table will a query have to execute against, although the query does not need to do a Full Table Scan of all the Partitions.
Typically this happens with queries that use Bind Variables. However, it can also occur with SubQueries against Partition Key Columns and where Dimensions are joined to a Fact.

Primary Key and Index

The default expectation is that defining a Primary Key constraint creates a Unique Index on the exactly the same columns (i.e. in the same order as well) as specified in the constraint.

However, this is not necessarily so.
1. You can have a Primary Key constraint with a non-Unique Index.
2. The index can be created ahead of the constraint. (Thus, the constraint "uses" the index as it finds the index already present)
3. The columns in the index need not be in the same order as the PK definition.

Here are two example tables showing :

Most Popular Posts - Feb 11

Blogger's "Stats" feature shows that the 5 most popular posts in the past 30 days have been :

ITIL v3 Foundation

I have received confirmation that I passed the ITIL v3 Foundation Examination.


Index Block Splits --- with REVERSE KEY Index

An earlier post "Index Block Splits : 90-10" demonstrated 90-10 block splits for an index on a monotonously increasing sequence -- as is defined in a Unique Index.

Qualifying Column/Object names to set the right scope

Earlier today, I had a curious query that I knew was returning the wrong results.
(Fortunately, I could look at the output and say outright "this is wrong". Had I not been observant enough and not known the underlying data, I would have allowed that "wrong" query to return the wrong results and accepted them as "correct").

More than possible Oracle code and optimization bugs, the biggest reason for "wrong results" is improper referencing of objects. My query today was similar -- and it had been written by me !

Cardinality Feedback in 11.2

The (undocumented) CARDINALITY Hint has been available since --10g-- 9i (correction by Timur). This allowed the Developer / DBA to "hint" to Oracle the expected number of rows that will be returned from a table that is part of a query.

view counter