Table Locks and Foreign Keys

Brief Description:

After reading “Expert Indexing in Oracle Database 11g ” book, I decided to share the following information with you. Which, I think, is very very useful.

It is better to index foreign key columns to avoid locking issues. Note, that locking issues are avoided when index type is B-tree, not Bitmap.

The Scenario:

Note: This is the quote from the following book:

Expert Indexing in Oracle Database 11g
Maximum Performance for Your Database

Darl Kuhn
Sam R. Alapati
Bill Padfield

“Here’s a simple example that demonstrates the locking issue when foreign key columns are not indexed.

First, create two tables (DEPT and EMP) and associate them with a foreign key constraint.

create table emp(emp_id number primary key, dept_id number);

create table dept(dept_id number primary key);

alter table emp add constraint emp_fk1 foreign key (dept_id) references dept(dept_id);

Now insert some data.

insert into dept values(10);
insert into dept values(20);
insert into dept values(30);
insert into emp values(1,10);
insert into emp values(2,20);
insert into emp values(3,10);
commit;

Open two terminal sessions. From one, delete one record from the child table (don’t commit).

delete from emp where dept_id = 10;

Now attempt to delete from the parent table some data not impacted by the child table delete.

delete from dept where dept_id = 30;

The delete from the parent table hangs until the child table transaction is committed. Without a regular B-tree index on the foreign key column in the child table, any time you attempt to insert or delete in the child table, it places a table-wide lock on the parent table, which prevents deletes or updates in the parent table until the child table transaction completes.

Now run the prior experiment, except this time additionally create an index on the foreign key column of
the child table.

create index emp_fk1 on emp(dept_id);

You should be able to independently run the prior two delete statements. When you have a B-tree index on
the foreign key columns, if deleting from the child table, Oracle will not excessively lock all rows in the
parent table.”

Function-based index

There are several types of indices: B-tree, Bitmap, Function-based, IOT.

I will discuss what function-based index is and in what cases is it used.

Let’s start by creating a demo example.

–Create a test table

create table tesTable(
col1 number,
col2 VARCHAR2(50)
);

–Insert some values

insert into testable
values(1,‘Giorgi’);

commit;

insert into testable
values(2,‘Mariami’);

commit;

–Gather table statistics

begin
dbms_stats.gather_table_stats(null,‘TESTABLE’);
end;

–Check if the statistics is OK

select column_name,num_distinct,hidden_column
from dba_tab_cols
where table_name=‘TESTABLE’;

–Output

COLUMN_NAME NUM_DISTINCT HIDDEN_COLUMN
COL1 2 NO
COL2 2 NO

 

Let’s see the explain plan for the following sql statement:

explain plan for select *
from testable
where UPPER(col2)=‘MARIAMI’;

select * from table(dbms_xplan.display);

——————————————————————————
| Id  | Operation   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————
|   0 | SELECT STATEMENT  |          |     1 |    40 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTABLE |     1 |    40 |     2   (0)| 00:00:01 |
——————————————————————————

If we have a big amount of data in this table, for example 1000000 rows what will happen?

Oracle will convert each row value into UPPER case and then compare it to our string. And this will happen 1000000  times Surprised smile

To make faster our query, there exists function-based index.

–Create function-based index

create index testable_func_indx on testable(upper(col2));

–Gather table statistics

begin
dbms_stats.gather_table_stats
(null,‘TESTABLE’);
end;

–Check if the statistics is OK

select column_name,num_distinct,hidden_column
from dba_tab_cols
where table_name=‘TESTABLE’;

–Output

COLUMN_NAME NUM_DISTINCT HIDDEN_COLUMN
COL1 2 NO
COL2 2 NO
SYS_NC00003$ 2 YES

 

As you can see there appeared hidden column by system generated name. Oracle behind the scenes creates a hidden virtual column on the parent table in order to capture the data characteristics of the function so that the CBO can make an accurate determination of the selectivity associated with using the function.

–See explain plan

explain plan for select *
from testable
where upper(col2)=‘MARIAMI’;

select * from table(dbms_xplan.display);

————————————————————————————————–
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————————–
|   0 | SELECT STATEMENT            |                    |     1 |    40 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTABLE   |     1 |    40 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN | TESTABLE_FUNC_INDX |     1 |       |     1   (0)| 00:00:01 |
————————————————————————————————–

You see there is INDEX RANGE SCAN. It will be faster then FULL TABLE SCAN if there is a big amount of data.

If we want to use user-defined function instead of Oracle built-in function, such as UPPER, we should do the following:

create or replace function my_fun(x varchar2) RETURN VARCHAR2
deterministic
is
begin
return upper
(x);
end;

You must indicate that the function is deterministic,which means that for same input values the output will be the same. So you must not use random function or something like that .

explain plan for select *
from testable
where upper(col2)=‘MARIAMI’;

select * from table(dbms_xplan.display);

————————————————————————————————–
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————————–
|   0 | SELECT STATEMENT            |                    |     1 |    40 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTABLE   |     1 |    40 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN | TESTABLE_FUNC_INDX |     1 |       |     1   (0)| 00:00:01 |
————————————————————————————————–