Database Reference
In-Depth Information
As a side note, if you believe that a child table is getting locked via an unindexed foreign key and you would
like to prove it (or just prevent it in general), you can issue the following:
ALTER TABLE <child table name> DISABLE TABLE LOCK;
Now, any
UPDATE
or
DELETE
to the parent table that would cause the table lock will receive the following:
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for <child table name>
This is useful in tracking down the piece of code that is doing what you believe should not be done
(no
UPDATE
s or
DELETE
s of the parent primary key), as the end users will immediately report this error back to you.
Why Isn't My Index Getting Used?
There are many possible causes of this. In this section, we'll take a look at some of the most common.
Case 1
We're using a B*Tree index, and our predicate does not use the leading edge of an index. In this case, we might have
a table
T
with an index on
T(x,y)
. We query
SELECT * FROM T WHERE Y =
5
. The optimizer will tend not to use
the index since our predicate did not involve the column
X
—it might have to inspect each and every index entry in
this case (we'll discuss an index skip scan shortly where this is not true). It will typically opt for a full table scan of
T
instead. That does not preclude the index from being used. If the query was
SELECT X,Y FROM T WHERE Y =
5
, the
optimizer would notice that it did not have to go to the table to get either
X
or
Y
(they are in the index) and may very
well opt for a fast full scan of the index itself, as the index is typically much smaller than the underlying table. Note
also that this access path is only available with the CBO.
Another case whereby the index on
T(x,y)
could be used with the CBO is during an index skip scan. The skip
scan works well if—and only if—the leading edge of the index (
X
in the previous example) has very few distinct values
and the optimizer understands that. For example, consider an index on
(GENDER, EMPNO)
where
GENDER
has the
values
M
and
F
, and
EMPNO
is unique. A query such as
select * from t where empno = 5;
might consider using that index on
T
to satisfy the query in a
skip scan
method, meaning the query will be processed
conceptually like this:
select * from t where GENDER='M' and empno = 5
UNION ALL
select * from t where GENDER='F' and empno = 5;
It will skip throughout the index, pretending it is two indexes: one for
M
s and one for
F
s. We can see this in a query
plan easily. We'll set up a table with a bivalued column and index it:
EODA@ORA12CR1> create table t
2 as
3 select decode(mod(rownum,2), 0, 'M', 'F' ) gender, all_objects.*
4 from all_objects
5 /
Table created.
