Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Gerard.

Asked: June 09, 2000 - 10:45 am UTC

Last updated: July 21, 2022 - 10:53 am UTC

Version: 8.0.5.2.1 (64bit)

Viewed 100K+ times! This question is

You Asked

I have read several books which have repeatedly mentioned creating indexes on foreign keys. I know one advantage is that it eliminates table-level locks and, I have seen the benefit since I have encountered a similar problem. However, I would like to know if you would recommend doing this for ALL foreign key constraints or, for only those which create the locking problem? How badly would it impact the database performance considering an OLTP system?

and Tom said...

See the tail of this answer for a script to detect unindexed foreign keys and pointers into the documentation as to the above referred to locking issue (which is real -- table locks can arise if you delete a parent record or update the primary key of a parent record and have unindexed foreign keys).

In addition to the table lock issue that might hit you, an unindexed foreign key is bad in the following cases as well:

o When you have an on delete cascade and have not indexed the child table. For example EMP is child of DEPT. Delete deptno = 10 should cascade to EMP. If deptno in emp is not indexed, you will get a full table scan of EMP. This full scan is probably undesirable and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.

o When you query from the PARENT to the CHILD. Consider the EMP, DEPT example again. It is very common to query the EMP table in the context of a deptno. If you frequently query:

select * from dept, emp
where emp.deptno = dept.deptno and dept.deptno = :X;

to generate a report or something, you'll find not having the index in place will slow down the queries.


So, when do you NOT need to index a foriegn key. In general when the following conditions are met:

o you do NOT delete from the parent table. (especially with delete cascade -- it is a double whammy)

o you do NOT update the parent tables unique/primary key value.

o you do NOT join from the PARENT to the CHILD (like DEPT->EMP).

If you satisfy all three above, feel free to skip the index, it is not needed. If you do any of the above, be aware of the consequences.

As for the effect of an index on a foriegn key index on an OLTP system -- it depends. If you frequently update the foreign key (in my experience, you do not update the fkey frequently, if at all, in many applications), the overhead is during the insert and might not be noticed. If you update it frequently, it might be worse. Its like any other index -- you just have more reasons (the above list) to consider adding that index then normal....


column columns format a20 word_wrapped
column table_name format a30 word_wrapped

select decode( b.table_name, NULL, '****', 'ok' ) Status,
a.table_name, a.columns, b.columns
from
( select a.table_name, a.constraint_name,
max(decode(position, 1, substr(column_name,1,30),NULL)) ||
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name ) a,
( select table_name, index_name,
max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
from user_ind_columns
group by table_name, index_name ) b
where a.table_name = b.table_name (+)
and b.columns (+) like a.columns || '%'
/

Rating

  (195 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Helena Markova, March 28, 2001 - 4:21 am UTC


Indexes on foreign keys

Thasin Sardar, May 09, 2001 - 10:12 am UTC


Great info!

A reader, May 25, 2001 - 12:15 pm UTC


A reader, July 12, 2001 - 9:58 am UTC


A reader, July 12, 2001 - 10:04 am UTC


Martin Gamperl, July 17, 2001 - 5:58 am UTC


shibu, August 09, 2001 - 2:16 am UTC

very informative

an, October 25, 2001 - 4:00 pm UTC

excellent!

And now.... A moment for the newbies!

Paul, January 25, 2002 - 9:01 am UTC

Tom,

If we want to index our forgien key columns, what is the best index to build against them? I mean does the index need to be seperate and special on that peticular column to be effective? Or is it better to index the column as part of a larger indexing stragey? Or should I go back to selling shoes? :)

Tom Kyte
January 25, 2002 - 1:49 pm UTC

Any index that contains the foreign key on the leading edge will do -- eg:


create table emp ( empno int primary key, ... deptno references DEPT );

create index on dept(deptno,empno); -- this is OK
create index on dept(empno,deptno); -- this will not prevent the fkey lock
create index on dept(deptno); -- this is OK

Reader

Reader, January 26, 2002 - 8:54 am UTC

Tom,

In your script
http://asktom.oracle.com/~tkyte/unindex/index.html,

why each decode returns 2 rows

SQL> set feedback on
SQL> /

DECODE(COLUMN_POSITION,1,','||SU
--------------------------------
, COL1
null

2 rows selected.

SQL> l
  1  select decode(column_position,1,', '||substr(column_name,1,30),NULL) from
  2* dba_ind_columns where  index_name = 'IDX'
SQL> 
 

Tom Kyte
January 26, 2002 - 6:58 pm UTC

You are not running the query as I have it. The query you have above is part of an inline view that I AGGREGATE over to get rid of the multiple rows.

Revisit that URL </code> http://asktom.oracle.com/~tkyte/unindex/index.html <code>and run the query as I have it coded.

Another twist - what if the child tables are empty?

Doug, May 01, 2002 - 5:16 pm UTC

Tom.. due to an app upgrade, we have some new child tables that aren't even used. They have unindexed foreign keys but the tables are empty. Do all these rules still apply?


Tom Kyte
May 01, 2002 - 9:22 pm UTC

Yes, they still apply.

BUT -- do you care if that empty child table gets a share lock on it to prevent updates (doubtful, if the child table is empty)...

So, they apply -- but do you care?

Lock on child Table

kiro, May 22, 2002 - 4:41 am UTC

Hi Tom
I have tried to reproduce lock on child table on 9.0.1 but I could not. Is it fix or not. See example.
test> create table b (id int primary key);

Table created.

test> create table b1 (id references b);

Table created.

test> insert into b values(1);

1 row created.

test> insert into b values(2);

1 row created.

test> commit;

Commit complete.

test> update b set id = 10 where id=1;

1 row updated.

--- Here I suppose that table b1 is locked

From another session I tried to insert into child table b1 and I did it.

test1> insert into b1 values(2);

1 row created.

test1> commit;

Commit complete.

test1> select * from b1;

ID
----------
2

Again frist session

test> commit;

Commit complete.

test> select * from b;

ID
----------
10
2

Please comment this.
Thank you.

Tom Kyte
May 22, 2002 - 8:07 am UTC

set echo on

drop table t1 cascade constraints;
drop table t2 cascade constraints;


create table t1 ( x int primary key );
create table t2 ( y references t1 );


insert into t1 values ( 1 );
insert into t1 values ( 2 );
commit;

set echo off
PROMPT goto another window, execute:
PROMPT insert into t2 values ( 1 );
PROMPT then come back here and hit enter
set echo on
pause

set echo off
PROMPT the following update will BLOCK as it
PROMPT tries to lock the child (cannot because
PROMPT of the other session).
update t1 set x = 3 where x = 2;


will show it.

The update/delete of the parent will still attemp to lock the child but only for an instant. If the child has outstanding transactions against it, the update/delete will have to wait. Other transactions will become blocked by the update/delete itself (potentially). Hence the problem still exists, just in a lesser form.

The child table lock is taken for the duration of the parent update/delete NOT the entire transaction in 9i. Lessens the problem but does not remove it.

Thanks.

kiro, May 23, 2002 - 2:27 am UTC


Delete Cascade Locking

Mark, August 16, 2002 - 3:04 pm UTC

Tom,
If I declare delete cascade, does it lock the child tables or only the child records.In another words when I delete the master record what kind of lock is applied to the child tables? row level or table level? because I have lot of inserts going on at the same time and I don't want to lock the Child or master table.I have indexes on all the foreign key columns.

Thanks,
Mark

Tom Kyte
August 16, 2002 - 3:51 pm UTC

If you have indexes on the fkeys, this question is MOOT for you -- the locking at the table level happens ONLY on unindexed foreign keys.

Since the server cannot know beforehand if there are any child records or not -- the lack of an index on the fkey will cause the child table to be locked upon delete of a parent record regardless of whether a row exists or not in the child.

This really isn't to do with "on delete cascade" -- it is to do with

a) having a foreign key
b) not having an index on that fkey
c) updating the parents primary key or deleting from parent.



How the index prevent table-level locking?

Praveen, November 06, 2002 - 11:53 pm UTC

Hi Tom,

You said that an index on foriegn key prevents the table-level locking of the child table when the parent record is deleted or primary key of a parent record is modified.

Q. How does the existance of FK-index prevent table-level locking of child table?

Thanks and regards

Praveen

Tom Kyte
November 07, 2002 - 7:33 am UTC

follow the links -- on the unindex link is a link to that pesky server concepts manual where so much of the internals of Oracle that are relevant to you and me are explained in nice detail. In fact, the entire "how this works when you have an index vs not having an index" is right there!




Why not hard wire this into Oracle

Bala, January 13, 2003 - 1:29 am UTC

If there are 99 reasons for indexing the foreign key why not hard wire this into oracle?

Tom Kyte
January 13, 2003 - 8:01 am UTC

because of the last reason not to.



Do I need to index?

Sri, February 13, 2003 - 1:21 pm UTC

Dear Tom,

Please see the following scripts: -

Create Table BranchMaster
(BranchCode Varchar2(10) Primary Key,
BranchName Varchar2(100) Unique Not Null
)
/
Create Table ItemMaster
(ItemCode Varchar2(10) Primary Key,
ItemName Varchar2(100) Unique Not Null
)
/
Create Table ShippingMaster
(ShippingCode Varchar2(10) Primary Key,
ShippingDate Date,
BranchCode Varchar2(10) References BranchMaster(BranchCode),
ShipDate Date
)
/
Create Table ShippingDetail
(ShippingCode Varchar2(10) References ShippingMaster(ShippingCode),
SlNo Number(2),
ItemCode Varchar2(10) References ItemMaster(ItemCode),
Quantity Number(12,2),
Primary Key (ShippingCode, SlNo)
)
/

Here, as you can see, BranchMaster and ItemMaster are not restrictly Master tables. They are sort of Code tables to store code in transactions instead of long descriptions. As you can see the BranchCode in the ShippingMaster and ItemCode in the ShippingDetail are NOT indexed.

Now, I have an application that uses the above tables. The daily transactions are mainly on the ShippingMaster and ShippingDetail tables. As and when the transactions are happening, I tried to create another table: -

Create Table BillingMaster
(BillCode Varchar2(10) Primary Key,
BillDate Date,
BranchCode Varchar2(10) References BranchMaster(BranchCode)
)
/

I could not create this and Oracle gives an error message after some time.

Create Table BillingMaster
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object GFL.BRANCHMASTER

1. Why is this happening. I am just refering the table.

2. I queried the v$access table and it shows that all the users of my application (Shipping screen) are having an entry in the table with Object BranchMaster. Is this abnormal and can I relate the error to this? Otherwise, what might be the problem?

3. With the above tables in place, should I index the columns referring the Code tables (BranchMaster & ItemMaster). I will NOT be modifying or deleting any records in these Code tables. I might add a new record once in a while.

I am using Oracle 9.0.1.1.1. Kindly advice. Thanks a lot.


Tom Kyte
February 13, 2003 - 5:14 pm UTC

1) you need to have the parent for a moment in order to put the constraint in place.  You can simulate this issue easily:

ops$tkyte@ORA817DEV> create table p ( x int primary key );

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into p values ( 1 );

1 row created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
  2          pragma autonomous_transaction;
  3  begin
  4          execute immediate 'create table c2 ( x references p )';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at line 4


ops$tkyte@ORA817DEV> commit;

Commit complete.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
  2          pragma autonomous_transaction;
  3  begin
  4          execute immediate 'create table c2 ( x references p )';
  5  end;
  6  /

PL/SQL procedure successfully completed.



2) it is normal - they are in fact accessing it after all.

3) if you DELETE from the parent tables OR update the parent tables primary keys -- yes.  else, it is upto you. 

Doubts regarding v$access

Sri, February 14, 2003 - 3:22 pm UTC

Dear Tom,

This is in continuation with my just previous question.

Regarding v$access, the oracle 9i Database Reference says "This view displays objects in the database that are currently locked and the sessions that are accessing them".

1. What type of lock will it put on the object?
2. If it is a table, would it lock only the row that the user is accessing or the whole table?
3. When I run a report with a select statement, why should Oracle put a lock, I am only reading it and it is not Select... For Update.
5. With this lock in place, will I be able to delete a row from that table?
4. What are the implications of this locking in a multi-user environment?

Kindly Comment. Thank you.


Tom Kyte
February 14, 2003 - 8:44 pm UTC

1) it is a lock that prevents ddl -- you cannot have someone physically changing that which you are currently actively using. like you cannot compile a procedure I am running.

2) it is the DEFINITION of the table, not the rows therein.

3) it is the DEFINITION of the table that is being protected -- not the rows therein.

4) hey, I got a freebie!

5) yes.

4) bummer, it came back. the implications of locking in a multi-user environment. well, very simply:

o without locking -- you would not be able to have multi users
o with locking -- you can

so i guess its a good thing

on delete cascade and unique keys

Madhuri Bondre, March 31, 2003 - 6:58 am UTC

i have a parent table with unique constraint (contains few null columns) this unique key is refered by another table
(child table)
when i delete the parent key with few null columns
it does not delete the child table althogh relationship is on delete cascade.

is it a feature ??

if i want to delete the child rows for maching null columns also how can i do it ?

thanks in advance



Tom Kyte
March 31, 2003 - 9:16 am UTC

feature.  In fact, you probably didn't realize that you can put anything in the child table you want when you use a composite column fkey constraint and one of the columns is NULL.  

The documentation is a little hazy on this:

http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c24integ.htm#2251

it describes the three cases ANSI allows us to persue -- however, it never actually says "and this is the one we do do".  We do "match none".  consider:

ops$tkyte@ORA920> create table t1
  2  ( x int, y int,
  3   constraint t1_unq unique(x,y) );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t2
  2  ( x int, y int,
  3    constraint t2_fk_t1 foreign key(x,y) references t1(x,y) ON DELETE CASCADE );

Table created.

ops$tkyte@ORA920> insert into t1 values ( 1, null );
ops$tkyte@ORA920> insert into t1 values ( 1, 1 );

<b>so, the parent table has two rows -- ONLY ONE of which can possibly be a parent key in a parent child relationship.</b>


<b>Now, this child record is NOT associated with the parent at all</b>
ops$tkyte@ORA920> insert into t2 values ( 1, null );
1 row created.

<b>As evidenced by this insert, (2,null) goes right in</b>

ops$tkyte@ORA920> insert into t2 values ( 2, null );
1 row created.


<b>but note that there is in fact a foreign key</b>

ops$tkyte@ORA920> insert into t2 values ( 2, 2 );
insert into t2 values ( 2, 2 )
*
ERROR at line 1:
ORA-02291: integrity constraint (OPS$TKYTE.T2_FK_T1) violated - parent key not found


ops$tkyte@ORA920> insert into t2 values ( 1, 1 );
1 row created.


<b>NULLS can really mess you up in a case like this (and many others)... You might consider re-evaluating your use of null in a composite foreign key relationship like this.</b> 

What's behind the .......and many others...

pasko, March 31, 2003 - 9:47 am UTC

Hi Tom,

Thanks for Great Demo showing the danger of having Nullable Composite foreign keys...

1.What are other side effects do you refer to ...


2.I remember to have read from some Book that nullable foreign keys should in general be avoided , regardless of wether they are composite or not ...
--you have already demonstraated to us about the Composite ones...what are the Dangers of using nullable simple foreign keys ....


3.Do we have same issues when we have simple Nullable foreign keys in a single Table referencing two separate tables ?

For instance :

create table parent1 ( id1 number primary key ,... ) ;
create table parent2 ( id2 number primary key ,... ) ;

create table ref_table (
idr number primary key ,
ref_p1 number ... references parent1( id1 ) ,
ref_p2 number ... references parent2( id2 ) ,
---other columns here
) ;



Thanks in advance.





Tom Kyte
March 31, 2003 - 9:59 am UTC

1) queries gone "wrong" -- they are done right but give the "apparently wrong" answer to the end user:

select * from t where x != 5;

they want everything not equal to 5 -- well, that should be:


where x != 5 or x is null;


queries going slower then then should,

select * from t where x NOT IN ( select nullable_column from t2 )

will run much slower (under CBO -- under RBO both run slow) then

select * from t where x not in ( select NOT_nullable_column from t2 )

2) nullable single column fkeys are perfectly OK. When the fkey is null -- it points to nothing. If it has a value, it must point to something.

3) no, they are separate keys all together.

Thanks Tom....and Response was really quick ..:)-

pasko, March 31, 2003 - 10:10 am UTC


Functional unique index as foreign key

Madhuri Bondre, April 16, 2003 - 1:45 am UTC

Can I use functional unique index as foreign key reference for another table in 8i ?

Since i want to avoid reference of null columns to child tables. If I use docode in unique index, will it help me?

will it be feasible ?

if yes then please guide.

thanks in advance

Tom Kyte
April 16, 2003 - 10:08 am UTC

No, you need a unique constraint/primary key.

I do not understand what is meant by:

Since i want to avoid reference of null columns to child tables...

Please elaborate on your concern (perhaps there is nothing to be concerned about). concern by example works best



docode=decode

madhuri bondre, April 16, 2003 - 6:40 am UTC

i am sorry

docode = decode

unique functional index with example

Madhuri Bondre, April 17, 2003 - 3:11 am UTC

here with showing with an example as you said

I have following tables

1. sales_order_item

CREATE TABLE sales_order_item
(
so_no number ( 6) not null,
item_cd varchar2( 6) not null,
qty number ( 6) not null,
rate number (11,3) not null,
.
.
.
.
CONSTRAINT pky_sales_order_item
PRIMARY KEY (so_no,
item_cd)
);

2. production_plan_item

CREATE TABLE production_plan_item
(
plan_no number ( 6) not null,
item_cd VARCHAR2 ( 6) not null,
so_no number ( 6),
qty number ( 6) not null,
.
.
.
.
CONSTRAINT unq_production_plan_item
UNIQUE (plan_no,
item_cd,
so_no)
),
CONSTRAINT fky_prod_plan_item__so_item
FOREIGN KEY (so_no,
item_cd)
REFERENCES sales_order_item
(so_no,
item_cd)
);


3. prodction_plan_item_batch

CREATE TABLE production_plan_item_batch
(
plan_no number ( 6) not null,
item_cd VARCHAR2 ( 6) not null,
so_no number ( 6),
batch number ( 4) not null,
qty number ( 6) not null,
CONSTRAINT unq_production_plan_item_batch
UNIQUE (plan_no,
item_cd,
so_no,
batch),
CONSTRAINT fky_prod_plan_item_bt__prod_plan_item
FOREIGN KEY (plan_no,
item_cd,
so_no)
REFERENCES prod_plan_item
(plan_no,
item_cd,
so_no)
ON DELETE CASCADE
);


Now, my problem is

1. on delete cascade will not work, with respect to fky_prod_plan_item_dt__prod_plan_item
when so_no is null
2. as per user requirement even null record is also going to appear only once.
means avoiding the constraint unq_production_plan_item on production_plan_item
and creating new unique functional index as

CREATE UNIQUE INDEX unq_production_plan_item
ON production_plan_item
(plan_no,
item_cd,
nvl(so_no,0));

how can I replace the following constraint using the unique functional index

CONSTRAINT fky_prod_plan_item_bt__prod_plan_item
FOREIGN KEY (plan_no,
item_cd,
so_no)
REFERENCES prod_plan_item
(plan_no,
item_cd,
so_no)
ON DELETE CASCADE

how to use nvl(so_no,0) in above constraint to use unique index unq_production_plan_item ?

alter table white_item_bin
add constraint fky_white_item_bin
foreign key (item_cd,
item_srno,
warehouse_cd,
nvl(so_yr,0),
nvl(so_no,0))
references white_item_bin
(item_cd,
item_srno,
warehouse_cd,
nvl(so_yr,0),
nvl(so_no,0)
ON DELETE CASCADE);

when I try this, it shows error as

ERROR at line 7:
ORA-00907: missing right parenthesis

is there any way out to do this ?

I want to use unique functional index instead of unique constraint.




Tom Kyte
April 17, 2003 - 10:21 am UTC

1) it is not that it doesn't work -- it is that when there is NULL in a foreign key -- it does not get related to ANY parent key. It is an orphan from birth. There is no key it can be related to in parent since NULL = NULL is not true and NULL != NULL is not true.


2) you should default that column to 0 -- it obviously does not support missing values (nulls). This is an issue with the data model more then anything.

One more related question

Alla, April 17, 2003 - 7:45 am UTC

Tom;

What do we do when we have a table that is resolving a many-to-many relationship and have a composite primary key from two tables (in terms of indexing foreign keys)?

Example:

emp (emp_no PK, emp_name)
project (project_id PK, project_name)

Employee can work on multiple projects:
emp_project (emp_no, project_id, no_of_hours).

PK in emp_project would be (emp_no, project_id).

If I understand you correctly, PK will take care of the index for one of the foreign keys (emp_no), since the first column there is emp_no and I'll have to index only project_id to take care of the second foreign key

Please let me know what you think

Thanks in advance

Alla

Tom Kyte
April 17, 2003 - 10:46 am UTC

association objects, as this table is known as, generally need to "go both ways"

you would have an index on emp_no, project_id and an index on prject_id, emp_no.

might be time to look at an IOT:

ops$tkyte@ORA920> create table emp_project
  2  ( empno int, projectid int, primary key(empno,projectid) )
  3  organization index
  4  /

Table created.

ops$tkyte@ORA920> create unique index emp_project_idx on emp_project(projectid,empno)
  2  /

Index created.

that'll just have two indexes and NO tables.  Normally -- you want the entire associate object's row in the index to avoid table access by rowid steps in plans -- since you only use this row to traverse from object to object.


 

More about IOT

Alla, April 17, 2003 - 11:33 am UTC

Thanks for you reply, Tom!

One more question though - how can I use an IOT when I need to keep some more information in this table as well - no of hours an employee worked on the project, not to mention audit fields, like create_ts, update_ts etc?

Thanks

Alla

Tom Kyte
April 17, 2003 - 11:40 am UTC

that all works. If the data is small enough, you'll keep it right in the index structure, if not, you'll keep it in an overflow segment.

If you have my book "Expert one on one Oracle" -- I cover this in lots more detail -- but it is all available in the Oracle docs as well (overflow segments, controlling what is stored in the index, what gets spilled over and so on)

unique index and unique constraint

Madhuri Bondre, April 18, 2003 - 1:15 am UTC

hi tom,

Thanks for you reply.

as you said make so_no default 0, won't solve my problem

since it has fky reference with so_item. and there is no record in so_item with 0 no.

is there any difference between unique constraint and unique index ?

if I can use unique index to create fky reference, then why can't I use unique functional index to create fky reference
as asked in example ?

does it compare value of the column or value of function with parent table ?

if I want to compare with value of the function how can I do that ?

am I doing something wrong ?

please help me.



Tom Kyte
April 18, 2003 - 11:48 am UTC

then -- you have even BIGGER problem....


You need a parent record there for that one. You quite simple DO NOT HAVE ONE. Period....


A unique constraint is a CONSTRAINT.
A unique index is an INDEX.

This is like asking "is there any difference between a house and a car?" They are totally different beasts, nothing in common really. Just like Cars have seats, windows, carpeting -- houses normally have seats, windows, carpeting, but they are totally different.


I think your model is wrong - you have partial fkeys with no parents. You need to have all NON-NULL columns in your fkeys for it to be a parent child relationship. The second you let NULLS in there -- no go.

ora-00600

Madhuri Bondre, April 19, 2003 - 1:19 am UTC

hi tom,

thanks for your reply.

Sorry to bother you again in this thread.

with reference to above example

I have created an unique functional index on parent table. converting null columns to not null.

ex.
CREATE UNIQUE INDEX unq_production_plan_item
ON production_plan_item
(plan_no,
item_cd,
nvl(so_no,0));

which has converted internally nvl(so_no,0) to sys_nc00051$

then I used that column to create fky reference for child table

as

alter table .... add
CONSTRAINT fky_prod_plan_item_bt__prod_plan_item
FOREIGN KEY (plan_no,
item_cd,
so_no)
REFERENCES prod_plan_item
(plan_no,
item_cd,
sys_nc00051$)
ON DELETE CASCADE;

it gives me following error.

ERROR at line 1:
ORA-00600: internal error code, arguments: [kkdlgvx2], [], [], [], [], [], [],

and in ora-00600 it tells me to call customer support.

I have to use "functional unique index" to create foreign key reference, since I can't use neither "unique constraint" nor "unique index" of parent table for the above task.

I think, I have cleared my requirement.

pl.help me.

once again sorry to bother you.


Tom Kyte
April 19, 2003 - 12:04 pm UTC

a FBI does not convert columns to NOT NULL.

SYS_NC00051$ does not exist, as the famous movies says "ignore that man behind the curtain".

Don't bother calling support -- they'll just say "well, don't do that".

In order to accomplish what you want to accomplish -- I'll say it again -- so_no would have to be NOT NULL for real

I hear your requirement
I've told you "isn't going to happen"

don't know what else to say.... so_no APPARENTLY should not be NULLABLE in that parent table -- you've allowed it to be so. Therefore you cannot get there from here.

thanks

Madhuri Bondre, April 21, 2003 - 12:49 am UTC

Thanks for your reply


Question about script to detect unindexed foreign keys

Arun Mathur, May 13, 2003 - 11:44 am UTC

Tom,

I'm reading Chapter 3 from your book "Expert One-On-One Oracle", and am getting lots of valuable information. I tried running the script you provided, but got the following error:

nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
*
ERROR at line 3:
ORA-00979: not a GROUP BY expression

I'm almost certain that it's something on my end, especially since your SQL session is provided as an example, but I can't figure out where I went wrong. Below is the script I took from p.110:

select table_name,constraint_name,
cname1||nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) ||
nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) ||
nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) ||
nvl2(cname8,','||cname8,null)
columns
from (select b.table_name,
b.constraint_name,
max(decode(position,1,column_name,null)) cname1,
max(decode(position,2,column_name,null)) cname2,
max(decode(position,3,column_name,null)) cname3,
max(decode(position,4,column_name,null)) cname4,
max(decode(position,5,column_name,null)) cname5,
max(decode(position,6,column_name,null)) cname6,
max(decode(position,7,column_name,null)) cname7,
max(decode(position,8,column_name,null)) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name and
b.constraint_type='R'
group by b.table_name,b.constraint_name
) cons
where col_cnt > ALL
(select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1,cname2,cname3,cname4,
cname5,cname6,cname7,cname8)
and i.column_position <= cons.col_cnt
group by i.index_name
)
/


Thanks in advance, as always.



Tom Kyte
May 13, 2003 - 4:34 pm UTC

stick:

where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
and rownum > 0

group by b.table_name, b.constraint_name
) cons

in there and let me know if that doesn't fix it.

group by error in 9i

John Hurley, May 13, 2003 - 1:13 pm UTC

The error noted below (by someone)

nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
*
ERROR at line 3:
ORA-00979: not a GROUP BY expression

Appears to start in oracle 9.0.1 and 9.2.0.1 but is fixed (thankfully) after applying the patchset to 9.2.0.2.3


The additional clause you provided fixed the script

Arun Mathur, May 14, 2003 - 2:06 pm UTC

Thanks for your help. Also, before reading John Hurley's response that mentions a bug, I downloaded your unindex script and used it without any problems. John, thanks for your response as well.

Regards,
Arun


Re: Lock on child Table

A reader, July 02, 2003 - 9:00 am UTC

Tom,
We are trying to find out why a certain deadlock is occuring in an application(EJB based application) time and again on the same table. I would appreciate your input on the following

For deadlocks due to unindexed FK's
a) the SQL's behind the blocker and blockee are update/delete's(or deletes/updates), no inserts? Yes/No - If No can you please explain
Reason: Even with the child table locked, we can still do an insert - right!!
b) deadlock don't effect insert statements(wrt unindexed FK's)?
Parent table P has 2 col's as PK's(col1, col2), these 2 col's are going to the child table as fk's, an index is present on the child table for the FK. ie. index present as Index1 = (col1, col2).
c) Do I need to have an aditional index in the child table Index2 = (col2, col1) for the deadlock not to occur? Yes/No


Thank you

Tom Kyte
July 02, 2003 - 9:24 am UTC

if (you UPDATE a parent primary key OR you DELETE a row in the parent table)
AND (child tables fkey is not indexed)
then
child table becomes locked -- no other session can insert/update/delete in
the child table
end if;

(slightly modified in 9i but still an issue)


Index your foreign keys if you EVER

o update parents primary key
o delete from parent


the order of columns is not relevant, they need be on the leading edge is all. That is, if the fkey is on C1, C2 an index on :

c1,c2,c3
c2,c1,c3

is OK, but

c3,c1,c2
c3,c2,c1

is not.


Re: Lock on child Table

A reader, July 02, 2003 - 10:12 am UTC

Thank you

This helps a lot

>>child table becomes locked -- no other session can >>INSERT/update/delete in
>> the child table


Re: Lock on child Table

A reader, July 02, 2003 - 10:55 am UTC

Tom,

For a deadlock that is occuring, our DBA provided the following information. The table wts has a composite fk and is indexed. We are not sure why the deadlock is occuring. We are planning on requesting the trace file information on the oracle database to find out the time the deadlock has occured. Is there any other information we get from the DB that can help us detect why the deadlock is occuring?

Thank you


WAITING_SESSION
--------------------------------------------------------------------------------
LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1
-------------------------- -------------- -------------- ----------------------
LOCK_ID2
----------------------
110
None

92
Transaction Share Exclusive 2359306
2008



PROCESS LOGON SID SERIAL ACT SRV USER_NAME UGA
------- -------------------- ---- ------ --- --- ---------------- ---------
COMPUTER
----------------------------------------------------------------
55582 01 Jul 2003 09:51:47 92 3653 ACT DED GEN_USER 340096
houston.aabb.com
48166 01 Jul 2003 09:58:23 110 6118 INA DED GEN_USER 274688
houston.aabb.com



SID SQL
---------- ----------------------------------------------------------------
92 insert into WTS (P_ID, CT_
92 ID, W_ID) VALUES (:1, :2, :3)

Tom Kyte
July 02, 2003 - 10:58 am UTC

not really knowing what I'm looking at (looks like the output of utllockt -- sesson 110 is blocking sesson 92, session 92 is attempting to insert into wts. perhaps wts has a unique constraint and session 110 already inserted a row with that value into wts and session 92 is blocked for that reason

Re: Lock on child Table

A reader, July 02, 2003 - 11:29 am UTC

That meaans we have not got into a deadlock situation - right? this is just one session blocking the other - Is that correct?

Tom Kyte
July 02, 2003 - 11:46 am UTC

you were not deadlocked at that point, no.

it'll be highly unlikely to actually "observe" a deadlock using v$ tables as they very quickly "un deadlock" themselves.

Re: Lock on child Table

A reader, July 02, 2003 - 12:00 pm UTC

The only wat to trace a deadlock is through the oracle trace files. Is that correct?

Tom Kyte
July 02, 2003 - 12:21 pm UTC

yes

Re: Lock on child Table

A reader, July 02, 2003 - 12:03 pm UTC

The only way to trace a deadlock is through the oracle trace files. Is that
correct?
>>they very quickly "un deadlock" themselves.
How does this happen. We will have to manually kill one of the dead locked sessions? won't we?

Tom Kyte
July 02, 2003 - 12:22 pm UTC

one of the two sessions gets an ORA-60 "deadlock detected". it is never necessary to kill a deadlock, they are self resolving.

Re: Lock on child Table

A reader, July 02, 2003 - 1:17 pm UTC

Tom,
1) Are all deadlocks self resolving?
Deadlocks with error messages ORA-60 and ORA-04020?

2) what are the reasons for the sessions to get blocked? Missing commits?, Will unIndexed FKs get us into blocking sessions?

3) From your earlier posting
>>you were not deadlocked at that point, no.
If the seesions 92 and 110 were deadlocked, what would be different in the output below?

WAITING_SESSION
--------------------------------------------------------------------------------
LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1
-------------------------- -------------- -------------- ----------------------
LOCK_ID2
----------------------
110
None

92
Transaction Share Exclusive 2359306
2008



PROCESS LOGON SID SERIAL ACT SRV USER_NAME UGA
------- -------------------- ---- ------ --- --- ---------------- ---------
COMPUTER
----------------------------------------------------------------
55582 01 Jul 2003 09:51:47 92 3653 ACT DED GEN_USER 340096
houston.aabb.com
48166 01 Jul 2003 09:58:23 110 6118 INA DED GEN_USER 274688
houston.aabb.com



SID SQL
---------- ----------------------------------------------------------------
92 insert into WTS (P_ID, CT_
92 ID, W_ID) VALUES (:1, :2, :3)

Thank you for your timely help

Tom Kyte
July 02, 2003 - 2:08 pm UTC

1) yes.

2) there is no such thing as a "missing commit" -- if you have a missing commit, you have a totally non-functional application.

unindex fks get you into block situations easily.

3) there would be no output actually - the deadlock is self resolving. It is an event that "happens" and is corrected.

you will find a trace file on the server.

Re: Lock on child Table

A reader, July 02, 2003 - 2:29 pm UTC

Tom,
I would really appreciate it if you can point me to the documentation that mentions that deadlocks are self resolving. I would like to give that as a reference to our DBA.

Thank you again

Tom Kyte
July 02, 2003 - 2:36 pm UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#6037 <code>


right there in that handy dandy server concepts guide!

Re: Lock on child Table

A reader, July 02, 2003 - 3:35 pm UTC

Thank you

PARENT to the CHILD & Driving Table

Robert, July 03, 2003 - 11:10 am UTC

Quote >>
When you query from the PARENT to the CHILD. Consider the EMP, DEPT
example again. It is very common to query the EMP table in the context of a
deptno. If you frequently query:

select * from dept, emp
where emp.deptno = dept.deptno and dept.deptno = :X;
<< End

Tom, you made the same point on this one in your book.

But I need a re-fresher on this. I'm not clear on exactly what is a "PARENT to the CHILD sql". (confused by the notion of "driving table")
--- is it because of the ORDER of the tables "from dept, emp" in your example ? or the order in the WHERE clause ?

Please clarify the idea of Parent->Child, Child->Parent in light of the notion of "driving table"

In the sample you gave above,
What's a Child->Parent query with EMP & DEPT ?

Tom Kyte
July 03, 2003 - 11:26 am UTC

The parent table is pretty clear -- it is the table WITHOUT the fkey.

dept has primary key deptno
emp has fkey deptno -> dept(deptno)

dept is the parent
emp is the child.


Here, the query predicate is phrased against DEPT - the optimizer will most likely choose DEPT as the driving table in this query under "normal" circumstances (eg: dept has more then 4 measely rows!) the query plan will be:

index access dept
then index access emp

the DEPT table is the "driving" table in the query.

parent/child -- doesn't have anything to do with it -- in the query:

select * from dept, emp
where emp.deptno = dept.deptno and emp.ename = 'KING'

EMP would most likely be the driving table in the query plan.

A reader, July 11, 2003 - 5:02 pm UTC


cascade deletes on parent record

Menon, July 16, 2003 - 1:28 pm UTC

if i have a fk cascade delete relation (with appropriate
indexes) and if my parent record refers to multiple
child tables with lots of records - when I delete
the parent record and it is deleting child records -
does the insert, update and selects (without for update
clause) on the child table get affected. I think that Oracle just acquires a TM lock for the delete transaction
to be over. This means that we cant issue DDL on the
parent and associated child tables but this in no way
blocks other inserts that may be happenning on
the child tables for example. Is this correct?

Thank you!
Menon:)


Tom Kyte
July 16, 2003 - 4:14 pm UTC

if you have indexed the foreign keys in the child tables -- then simple row level locking of the affected rows are all you need to be concerned about -- concurrent dml can take place.

thanx!

A reader, July 16, 2003 - 4:17 pm UTC

that confirms what I was thinking (after having
read your book)

Regards
Menon:)

please review this .....

Reader, August 25, 2003 - 8:37 pm UTC

Tom, in the emp table, If I have a composite primary key on empno and deptno columns, will this composite pk index take care of locking issue with regard to updating deptno in the dept table? I am using emp.deptno in the composite pk as well as it refers to dept.deptno column as a referential integrity. Thanks.

Tom Kyte
August 26, 2003 - 8:34 am UTC

well, it would not make sense to have such a key (empno is the primary key) but -- no, it would not be a good "fkey index"


deptno would have to be on the leading edge of the index -- the primary key would have to be (DEPTNO, EMPNO) and the index would have to be in that specific order.

since you probably need EMPNO on the leading edge for querying -- you need another index on deptno if you ever

o update DEPT.DEPTNO
o delete from DEPT

in order to avoid locking contention on the child (EMP) table.

good

nitin, August 26, 2003 - 1:07 pm UTC

good note

very good

A reader, August 28, 2003 - 8:11 am UTC


Lock on child Table May 22, 2002

A reader, August 28, 2003 - 8:39 am UTC

Lock on child Table May 22, 2002
Reviewer: kiro from Bulgaria

Hi Tom
I have tried to reproduce lock on child table on 9.0.1 but I could not. Is it
fix or not. See example.
test> create table b (id int primary key);

Table created.

test> create table b1 (id references b);

Table created.

test> insert into b values(1);

1 row created.

test> insert into b values(2);

1 row created.

test> commit;

Commit complete.

test> update b set id = 10 where id=1;

1 row updated.

--- Here I suppose that table b1 is locked

From another session I tried to insert into child table b1 and I did it.

test1> insert into b1 values(2);

1 row created.

test1> commit;

Commit complete.

test1> select * from b1;

ID
----------
2

Again frist session

test> commit;

Commit complete.

test> select * from b;

ID
----------
10
2

Please comment this.
Thank you.

----------------
I call same SQL in my system (817+win2000server), but I cannot insert b1 anymore, I really don't understand the explanation by TOM.

Tom Kyte
August 29, 2003 - 7:32 am UTC

things change. in 9i, the lock is tell there -- it is just taken for a shorter duration.

to see it:

-----------------------------------------------------------
drop table c;
drop table p;

set echo on

create table p ( x int primary key );
create table c ( x references p );

insert into p values ( 1 );
insert into p values ( 2 );
insert into c values ( 2 );
commit;

insert into c values ( 2 );

set echo off
prompt in another session execute:
prompt delete from p where x = 1;;
prompt then come back here and hit enter, that session will block
pause

prompt now, in another session execute
prompt insert into c values ( 2);;
prompt that too will block.
pause

-----------------------------------------------------------




in 9i, the full table lock on C is taken by an update or delete to P when the fkey is unindexed just long enough for P to check out C and then released. It does not have to wait for a commit on P to release the lock. that is the enhancement from 8i to 9i

ORA-04021 - timeout occurred while waiting to lock object

Kiran Shah, December 31, 2003 - 4:29 am UTC

Tom,
I hope I am putting my question in proper thread...
Because while searching this site for 'timeout occurred while waiting to lock object' I am getting this page...

Now, about our problem...We are using Oracle 9i DB and when we use ALTER command we get following error :
ORA-04021 - timeout occurred while waiting to lock object abc.table1.

This error occurs for every table. However when we shutdown the database and again re-start it and immediately do this ALTER command, then this works fine.

Pl. give guideline to overcome this problem.

Tom Kyte
December 31, 2003 - 9:46 am UTC

what kind of alters -- give us a cut and paste of the full error stack and tell us about the object and whats going on in the database at that time.

Sorry for not giving full info...

Kiran Shah, January 01, 2004 - 2:13 am UTC

Tom,

We are using ALTER TABLE TABLE1 DROP COLUMN COLUMN1
command in SQL Plus.

At this juncture it seems to just hang. After about 10 minutes it gives following message. Right now we are developing our application. So no user is actually connected with database. And this particular table is also not being used any where while this command is given. Following is cut & paste of SQL Plus window...

  1  alter table randpregister
  2* drop column detailsofrandp
SQL> /
alter table randpregister
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object CCIS.RANDPREGISTER

 

Tom Kyte
January 01, 2004 - 9:27 am UTC

ok, show us the nicely formatted output of:

alter table randpregister drop column detailsofrandp;

select *
from v$access
where owner = 'CCIS' and object = 'RANDPREGISTER' and type = 'TABLE';

select *
from v$db_object_cache
where owner = 'CCIS' and name = 'RANDPREGISTER' and type = 'TABLE';




and while the alter is blocked -- run this query and see what it says:

select /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
/


and check out support note <Note:169139.1> on metalink.oracle.com

Query output....

Kiran Shah, January 03, 2004 - 2:07 am UTC

Tom,
We run "alter table randpregister drop column detailsofrandp" in one Sqlplus session and simultaneously open another session of pl/sql and run three queries.

Following is the result we got while running 3 queries given by you. 

SQL> select *
  2    from v$access
  3   where owner = 'CCIS' and object = 'RANDPREGISTER' and type = 'TABLE';

no rows selected

SQL> 
SQL> select substr(owner,1,5) owner,substr(name,1,15) name,substr(db_link,1,10) db_link,
  2  namespace,type,sharable_mem,loads,executions,locks,pins,kept
  3  from v$db_object_cache
  4  where owner = 'CCIS' and name = 'RANDPREGISTER' and type = 'TABLE' ;

Result of above query : (Edited...)
OWNER : CCIS 
NAME  : RANDPREGISTER
DB_LINK  :
NAMESPACE : TABLE/PROCEDURE
TYPE : TABLE                                  
SHARABLE_MEM  : 19814
LOADS : 1 
EXECUTIONS : 0
LOCKS  : 1
PINS : 2 
KEPT : NO

SQL> 
SQL> select /*+ ordered */ w1.sid  waiting_session,
  2      h1.sid  holding_session,
  3      w.kgllktype lock_or_pin,
  4          w.kgllkhdl address,
  5      decode(h.kgllkmod,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
  6         'Unknown') mode_held,
  7      decode(w.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
  8         'Unknown') mode_requested
  9    from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
 10   where
 11    (((h.kgllkmod != 0) and (h.kgllkmod != 1)
 12       and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
 13     and
 14       (((w.kgllkmod = 0) or (w.kgllkmod= 1))
 15       and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
 16    and  w.kgllktype     =  h.kgllktype
 17    and  w.kgllkhdl     =  h.kgllkhdl
 18    and  w.kgllkuse     =   w1.saddr
 19    and  h.kgllkuse     =   h1.saddr
 20  /

WAITING_SESSION HOLDING_SESSION LOCK ADDRESS  MODE_HELD MODE_REQU                                   
--------------- --------------- ---- -------- --------- ---------                                   
             11               8 Pin  70A97624 Share     Exclusive                                   
             11               8 Pin  70A97624 Share     Exclusive                                   

SQL> 
SQL> 
SQL> spool off

I hope this is as per your requirement. 

Thanks for your support... 

Tom Kyte
January 03, 2004 - 9:13 am UTC

so, now all you have to do is look at sid=8 (or whatever sid pops up next time) and see what they are doing.

They are the one holding the pin on the object that is preventing you from modifying it right now.

There IS a session using that object, contrary to what you believe and there is the "evidence".

o.k.

Kiran Shah, January 05, 2004 - 1:41 am UTC

ok. Tom, now onwards I will look which session is holding particular object. This is really new thing, I learnt now. Thanks for your guidelines...


Timeout time

Andres, February 06, 2004 - 11:05 am UTC

Kiran Shah wrote above:

/---/
After about 10 minutes it gives 
following message. 
/---/
  1  alter table randpregister
  2* drop column detailsofrandp
SQL> /
alter table randpregister
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object CCIS.RANDPREGISTER

---------

can this "about 10 minutes" be set to something shorter, eg 10 seconds? There is a system parameter DISTRIBUTED_LOCK_TIMEOUT, but this is for distributed row locking, right? Can't find a parameter as LOCK_TIMEOUT, for example.. (9.2.0.4) 

Tom Kyte
February 07, 2004 - 1:25 pm UTC

no, it cannot.

deadlock and FKs

A reader, February 07, 2004 - 3:48 am UTC

Hi

if we dont index FKs and update parent tables can this cause deadlocks?

How can I reproduce this?

Tom Kyte
February 07, 2004 - 2:54 pm UTC

if you update a parent tables primary key OR you delete from parent
then
if you have 8i and before
a full table lock is taken and held on the child table
for the duration of the transaction
else if you have 9i and above
a full table lock is taken on the child table
for the duration of the statement that does the update/delete (and
for as long as it blocks)
end if;
end

armed with that knowledge -- setting up a testcase is easily done. for 9i, all you need is some shared resource that two sessions might go after, and then one session that updates a parent primary key(or deletes) and another session that will update a totally unrelated child record. If both try to get the shared resource -- bam, deadlock.

drop table c;
drop table p;
drop table some_other_shared_resource;
set echo on


create table p ( x int primary key );
create table c ( a int primary key, x references p );
create table some_other_shared_resource ( x int );

insert into some_other_shared_resource values ( 1 );

insert into p values ( 1 );
insert into p values ( 2 );
insert into c values ( 1, 1 );
insert into c values ( 2, 1 );
insert into c values ( 3, 2 );
insert into c values ( 4, 2 );
commit;

prompt in another session update c set a = a where a = 3
pause

update some_other_shared_resource set x = 1;
prompt this will block, when it does go back to the other session
prompt and update some_other_shared_resource set x = 1;
update p set x = x where x=1;



last example does not work?

A reader, February 09, 2004 - 11:43 am UTC

Hi

trying your last example using emp, dept and x tables

session 1

update dept set deptno = 10 where deptno = 10;

1 row updated.

session 2
update x set a = 1 where a = 1;

1 row updated.

session 1

update x set a = 1 where a = 1;

blocked.........

session 2

update emp set deptno = 20 where deptno = 10;

3 rows updated.


I thought the last update would give deadlock?

Tom Kyte
February 09, 2004 - 12:10 pm UTC

i did not study your example (since it is incomplete -- no table creates)

how's about you use my example -- which definitely has primary/foreigng keys and no index on the fkeys (things emp/dept could well be lacking/having) and let us know....

sorry

A reader, February 09, 2004 - 12:09 pm UTC

Hi

I am sorry, it works, just that I must update Child first then Parent

Sql Query Timeout?

Amar Desai, February 17, 2004 - 3:34 am UTC

Hi Tom,

We are using Oracle 8i on Windows 2000/XP. Is it possible to restrict query, If execution of that query is going to take xx seconds/minutes? By setting any Server para or any other utility.

Some database have query timeout, So If query doesn't execute/fetch any records for say 300 seconds, Server will throw timeout error. Is this possible to implement in Oracle?

Please reply.

Thanks & Regards,

Amar Desai

Tom Kyte
February 17, 2004 - 8:19 am UTC

In 9i we can do this proactively (it'll estimate how long the query is to run for and based on that run it -- or not)

In all releases (10g and before) we have resource profiles which reactively cancel long running queries.

So yes, we have this. See

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/users.htm#1148 <code>

(the admin guide, a great place to start to discover things like this...)

Query timeout

Amar, February 19, 2004 - 2:28 am UTC

Thanks Tom. I found that Parse/Execution/fetch can limit using Resource Limit Parameters (LIMIT CPU_PER_CALL) of User Profiles.

If user execeeds limit of CPU time. Error appears "ORA-02393 exceeded call limit on CPU usage"

But how can we do this proactively in 9i? Could you please explain us?

Thanks & Regards,

Amar



Tom Kyte
February 19, 2004 - 10:58 am UTC

http://docs.oracle.com/docs/cd/B10501_01/server.920/a96521/part5.htm#435958

ops$tkyte@ORA920PC> @test
=================================================================
 
 
Begin by creating the resource plan.  It limits statements to
1 second in duraction for members of the plan...
 
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> begin
  2          dbms_resource_manager.create_pending_area;
  3
  4          dbms_resource_manager.create_plan
  5          ( 'est_max_time',
  6            comment => 'limit by estimated time demo' );
  7
  8          dbms_resource_manager.create_consumer_group
  9          ( consumer_group => 'demo',
 10            comment => 'yes it is a demo' );
 11
 12          dbms_resource_manager.create_plan_directive
 13          ( plan => 'est_max_time',
 14            group_or_subplan => 'demo',
 15            comment => 'its a demo',
 16            MAX_EST_EXEC_TIME => 1 );
 17
 18          dbms_resource_manager.create_plan_directive
 19          ( plan => 'est_max_time',
 20            group_or_subplan => 'OTHER_GROUPS',
 21            comment => 'its a demo' );
 22
 23          dbms_resource_manager.validate_pending_area;
 24
 25          dbms_resource_manager.submit_pending_area;
 26
 27  end;
 28  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> set echo off
Enter to continue
 
=================================================================
 
Lets create that poor end user who is limited to 1 second statements
and assign him to this plan
 
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> drop user low_priority cascade;
drop user low_priority cascade
          *
ERROR at line 1:
ORA-01918: user 'LOW_PRIORITY' does not exist
 
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create user low_priority identified by x;
 
User created.
 
ops$tkyte@ORA920PC> grant connect, resource to low_priority;
 
Grant succeeded.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> begin
  2          dbms_resource_manager_privs.grant_switch_consumer_group
  3          ( 'low_priority', 'demo', TRUE );
  4
  5          dbms_resource_manager.set_initial_consumer_group
  6          ( 'low_priority', 'demo' );
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> set echo off
Enter to continue
 
=================================================================
 
Now, we need to make the plan active
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> alter system set resource_manager_plan = est_max_time scope = memory;
 
System altered.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> connect low_priority/x
Connected.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> set echo off
Enter to continue
 
=================================================================
 
Now, we'll create a table, try to select from it and then see
what happens when we attempt a stupid query
 
 
low_priority@ORA920PC>
low_priority@ORA920PC> create table t as select * from all_objects;
 
Table created.
 
low_priority@ORA920PC>
low_priority@ORA920PC> analyze table t compute statistics;
 
Table analyzed.
 
low_priority@ORA920PC>
low_priority@ORA920PC> select count(*) from t;
 
  COUNT(*)
----------
     24673
 
low_priority@ORA920PC>
low_priority@ORA920PC> select count(*) from t,t;
select count(*) from t,t
                     *
ERROR at line 1:
ORA-07455: estimated execution time (15614 secs), exceeds limit (1 secs)
 
 
low_priority@ORA920PC>
low_priority@ORA920PC> set echo off
Enter to continue
 
=================================================================
 
Lastly, we'll put things back the way they were
 
 
low_priority@ORA920PC>
low_priority@ORA920PC> connect demo/demo
Connected.
low_priority@ORA920PC> set echo off
demo@ORA920PC>
demo@ORA920PC> alter system set resource_manager_plan = '' scope = memory;
 
System altered.
demo@ORA920PC>
demo@ORA920PC> begin
  2          dbms_resource_manager.create_pending_area;
  3      dbms_resource_manager.delete_plan( 'est_max_time' );
  4          dbms_resource_manager.delete_consumer_group( 'demo' );
  5          dbms_resource_manager.validate_pending_area;
  6          dbms_resource_manager.submit_pending_area;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
demo@ORA920PC>
demo@ORA920PC> drop user low_priority cascade;
 
User dropped.
 
 

index on foreign keys

Venkat, April 07, 2004 - 5:16 am UTC

Dear Tom,

We understand that indexing foreign keys will solve the table level locking issue especially during update/delete the primary key value from the parent table apart from improving the query performance in certain conditions.

Our application team has created a primary key constraint on all the foreign key columns instead of an index. Is this right in place of indexing?

Thanks,
Venkat

Tom Kyte
April 07, 2004 - 9:10 am UTC

all of your fkeys are also unique????????? that would be "very strange" as fkeys usually (not always but usually) imply "master -> detail", "parent -> child"



Your script does not seem to work

A reader, April 08, 2004 - 3:07 pm UTC

copy & paste from your link:

SQL> column columns format a20 word_wrapped
SQL> column table_name format a30 word_wrapped
SQL>
SQL> select decode( b.table_name, NULL, '****', 'ok' ) Status,
  2        a.table_name, a.columns, b.columns
  3  from
  4  ( select substr(a.table_name,1,30) table_name,
  5              substr(a.constraint_name,1,30) constraint_name,
  6          max(decode(position, 1,     substr(column_name,1,30),NULL)) ||
  7          max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
  8          max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
  9          max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
 10          max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
 11          max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
 12          max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
 13          max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
 14          max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
             max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
 15   16             max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
 17          max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
 18          max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
 19          max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
 20          max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
 21          max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
 22      from user_cons_columns a, user_constraints b
 23     where a.constraint_name = b.constraint_name
 24       and b.constraint_type = 'R'
 25     group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
 26  ( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
 27          max(decode(column_position, 1,     substr(column_name,1,30),NULL)) ||
             max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
             max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
             max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
 28   29   30   31           max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
 32          max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
 33          max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
 34          max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
 35          max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
 36          max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
 37          max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
 38          max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
 39          max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
 40          max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
 41          max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
 42          max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
 43      from user_ind_columns
 44     group by substr(table_name,1,30), substr(index_name,1,30) ) b
 45  where a.table_name = b.table_name (+)
 46    and b.columns (+) like a.columns || '%'
 47  /
             max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
                            *
ERROR at line 32:
ORA-01467: sort key too long
 

Tom Kyte
April 08, 2004 - 3:42 pm UTC

your block size is too small.

chomp out some columns in there, I assumed a max of 16, you'll have to assume "less" to accomidate your probable 2k block size.

from above

A reader, April 08, 2004 - 3:51 pm UTC

My 8k block size is not enough ?

SQL> sho parameter db_block_size


NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_block_size                        integer
8192
 

Tom Kyte
April 08, 2004 - 5:03 pm UTC

ahh -- I'll betcha $1 that cursor sharing = force/similar

hence bind variables kick in

hence it no longer knows "30" and assumes "something much larger"

from above

A reader, April 08, 2004 - 10:33 pm UTC

SQL> sho parameter cursor_sharing


NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing                       string
SIMILAR

:-O

I'm completely shocked, surprised, astonished, dumbfounded, flabbergasted, startled, bewildered, confounded, discomfitted, disconcerted, dismayed, nonplussed, perplexed, puzzled, fazed, rattled, rocked, bowled-over, floored, staggered, stunned, stupefied, left open-mouthed, taken aback,... (out of breath)

Are you a mind reader or what ?

You get your $1... I always thought you were exaggerating or that the side-effects of cursor-sharing were too subtle to be taken into account. 

Now I see it... *sigh*. You win. ;))))))))))))))

I had never "felt" any side-effects to it. After reading your threads and realizing the importance of binds, I set it to similar and left it that way... up till now ! Yes, man... It IS a band-aid. I'll immediately start reviewing all my applications. 

Thanks a lot !

PS: I hope this alerts others too ... 

Locking when there are unindex foreign keys

Neelam, April 21, 2004 - 7:09 pm UTC

I was going through the documentation
</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c22integ.htm#8565 <code>

and came along
"Inserts into the parent table do not require any locks on the child table."

But when I did the following I found that there is lock on child table.

Create table temp(a number primary key);

Create table temp1(b number references temp);

Insert into temp values(1);
Insert into temp values(2);

And when I check in OEM, it shows me ROWSHARE lock on Temp1 .

Isn't it contradicting the above statement regarding Insert or there is anything which I'm missing. Please explain.

Thanks a lot

Tom Kyte
April 21, 2004 - 9:27 pm UTC

blocking locks -- real lock locks.

no one is prevented from modifying any of the data in the child table.

Just a simple question

A reader, April 30, 2004 - 6:12 pm UTC

Hi Tom,
i took your advice and indexed my foreign keys. But i still have a query. As to what should be the approach for indexing the foreign keys. Should we index all of the foreign keys in just one index, in case a table has more than one foreign key columns. Or one index for each foreign key, or one index for a composite foreign key. What should be the approach?
Guide the way champion. :D

Tom Kyte
May 01, 2004 - 9:05 am UTC

the columns of the fkey must be on the leading edge of an index.

if you have a table t with a-> t2, b->t3, c ->t4, you'd need 3 indexes (one each with a, b, c on the leading edge)

if you have a table t with a-> t2, a,b->t3, a,b,c->t4, a single index on a,b,c would suffice.


so, look at the existing indexes, if the fkey columns are on the leading edge (and even if there are "extra columns" at the end) it can and will be used.

Index on Primary

Venkat, May 19, 2004 - 9:23 am UTC


What I meant to ask was?

There is an enhancement made in 9i release of Oracle for
Naming a Index while creating the table.

Create table NEW_EMP
( employee_id NUMBER
PRIMARY KEY USING INDEX
(CREATE INDEX emp_id_idx ON
NEW_EMP(employee_id)),
first_name VARCHAR2(20));

What is the specific advantage on using a seperate name for the index using the above option, over going for the same name as the primary key constraint?


Tom Kyte
May 19, 2004 - 10:58 am UTC

what "isn't" the advantage?

it is just a feature, if you want to name the index foo, the constraint bar -- you are allowed to.

some people must have wanted to do that.

foreign key indexes

Raaghid, June 05, 2004 - 7:12 am UTC

Great help.

After doing foreign key index, the performance improved like anything, while more than one user raising the same document (affecting same table).

Table is not getting locked while accessing parallely.

But in our development server, all updates are taking too much time (insert are faster). Is there any tuning required in the SGA sizing. (9i) (even if a single user using)

Development server (512 MB RAM, 151 MB allocated for SGA)



Tom Kyte
June 05, 2004 - 9:22 am UTC

define "taking too much time"

time taken

Raaghid, June 07, 2004 - 12:56 am UTC

New document generation (Insert) is taking 30 seconds
while updation takes about 420 seconds (7 minutes) - 14 times more.

What needs to be done and what steps need to be followed to solve this issue.

Please guide.

Tom Kyte
June 07, 2004 - 8:18 am UTC

you'd need to tell us what you mean by "document generation" and "update of a document" as these are processes that are 100% unique to you and your application.


I would start with sql_trace=true, timed_statistics=true -- run the processes and use TKPROF to analyze "where is my time being spent".




Thirumaran from India

Thirumaran, October 20, 2004 - 11:12 am UTC

Tom,

The information provided by you are very informative & usefull which had helped me manytimes to solve issues in my daily work.

The details you have mentioned about indexes are very clear to me ,but i have a different situtaion at work which i have mentioned below.

i have a table ACCT_T

ACCT_NUM_ID (Primary Key)
EMP_NUM_ID (Foreign Key)(EMP_T TABLE)
CUST_NUM_ID (foriegn key) (CUST_T TABLE)

here the EMP_NUM_ID & CUST_NUM_ID are indexed twice .
i.e
CREATE UNIQUE INDEX ACCT_T_FK1
ON ACCT_T
(
EMP_NUM_ID ASC
)

CREATE UNIQUE INDEX ACCT_T_FK2
ON ACCT_T
(
CUST_NUM_ID ASC
)

CREATE UNIQUE INDEX ACCT_UK
ON ACCT_T
(
EMP_NUM_ID ASC,
CUST_NUM_ID ASC
)

What is the use of creating seperate unique index on A column and then creating composite unique index on all columns.

As here is already a unique index on FKey column (seperately) and the same Fkey column is also composite index. how will the db use these indexes Or should i remove any one of these indexes

if a column is indexed twice (like one mentioned above) will it effect the query performance

Thanks in Advance
Thirumaran



Tom Kyte
October 20, 2004 - 11:49 am UTC

seems you have 3 constraints -- not three indexes.

but actually -- acct_uk is redundant

if emp_num_id is unique already -- a row with a emp_num_id is going to have a unique emp_num_id, cust_num_id by definition.

should you remove it? no idea, only you know. are you using that index to avoid a table access by index rowid?

why did you create the index in the first place? (you being your place of work, not you personally)

A reader, December 15, 2004 - 10:07 am UTC


Get Error

Ram Joshi, January 04, 2005 - 8:02 am UTC

Hi Tom,
 I'm facing an error as follows. Is it due to 9i???

SQL> select /*+ ordered */ w1.sid  waiting_session,
      h1.sid  holding_session,
      w.kgllktype lock_or_pin,
          w.kgllkhdl address,
      decode(h.kgllkmod,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
      'Unknown') mode_held, 
      decode(w.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
         'Unknown') mode_requested
    from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
   where
    (((h.kgllkmod != 0) and (h.kgllkmod != 1)
       and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
      and
        (((w.kgllkmod = 0) or (w.kgllkmod= 1))
        and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
     and  w.kgllktype     =  h.kgllktype
     and  w.kgllkhdl     =  h.kgllkhdl
     and  w.kgllkuse     =   w1.saddr
     and  h.kgllkuse     =   h1.saddr
   /
  from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
                      *
ERROR at line 9:
ORA-00980: synonym translation is no longer valid
 

Tom Kyte
January 04, 2005 - 8:43 am UTC

did you run catblock.sql which creates dba_kgllock?

connect / as sysdba
@?/rdbms/admin/catblock

Please Clarify

Siva, January 04, 2005 - 9:47 am UTC

Hello Tom,
Is there any possibility that Indexes slow down the
retrieval of data?
Please do reply.
Bye!

Tom Kyte
January 05, 2005 - 8:04 am UTC

sure, just like too much medicine will kill you.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6749454952894#6760861174154 <code>

Locking whole child table

Carol, January 21, 2005 - 1:12 pm UTC

Hi Tom... I am only updating a description field in a parent table and yet the whole child table is still being locked (using 8.1.7). Is there any way to fix this other than creating an index on the key in the child table? Don't understand it. Thanks.

Tom Kyte
January 21, 2005 - 7:34 pm UTC

can you "prove it". give us an example. for example:

tkyte@ORA8IW> create table p ( x int primary key, y int );
Table created.

tkyte@ORA8IW> create table c ( x references p, z int );
Table created.

tkyte@ORA8IW> insert into p values (1, null);
1 row created.

tkyte@ORA8IW> insert into c values (1, null);
1 row created.

tkyte@ORA8IW> commit;
Commit complete.


that is our parent child. now, in light of no indexes on child, we expect that any time we UPDATE the parent primary key or DELETE from parent -- the child table will be locked -- this demonstrates that fact:

tkyte@ORA8IW> update p set x = 1 where x = 1;
1 row updated.

tkyte@ORA8IW> declare
2 pragma autonomous_transaction;
3 begin
4 update c set z = 1;
5 commit;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

the subtransaction was blocked by the parent transaction -- that table was locked. When we DON'T update the primary key/delete from parent however:


tkyte@ORA8IW> rollback;
Rollback complete.

tkyte@ORA8IW> update p set Y = 1 where x = 1;
1 row updated.

tkyte@ORA8IW> declare
2 pragma autonomous_transaction;
3 begin
4 update c set z = 1;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.

tkyte@ORA8IW> commit;
Commit complete.


total success -- no problems


So, if you cannot reproduce your findings in sqlplus -- what I would guess is you have some generic API that is updating the tables and it in fact updates EVERY COLUMN -- regardless of whether you changed it or not (sql_trace=true and tkprof would bear this out)



just my two cents

Andres, January 23, 2005 - 6:52 pm UTC

/---/
o you do NOT update the parent tables unique/primary key value.
/---/

This is not as simple as it seems. I'd add something like "... or Oracle thinks the primary key might change".

Consider this example:
create table t1 ( x int primary key, col1 varchar2(10) );
create table t2 ( y references t1 );

create trigger tr1 before insert or update on t1 for each row
begin
if inserting then
select mysequence.nextval into :new.x from dual;
end if;
/* do some common checks here for both update and insert (including the :new.x value, so the "if inserting then" part can't just be put into a different insert trigger, because the order of the triggers fired would not be guaranteed)
*/
end;
/

And now, when one does

update t1 set col1 = 'someconst' where x=1;

(assuming the x=1 row exists), the table t2 gets locked.
Now, i do not update the primary key directly in the update statement, and nor does the trigger update the primary key, but i've still got the table lock.

It seems like Oracle
1) detects, that the trigger gets fired;
2) detects, that the trigger can assign to x;
and finds that enough to lock the child table, although the update of x never happens.

The solution to this was to put the common checks into a packaged procedure and split the before insert or update trigger into a before insert and a before update trigger. The insert trigger consists of the nextval select of the sequence and a call to the created procedure. The update trigger only calls the procedure.

Conclusion:
===========
beware assinging to the primary key using joint before "insert or update" trigger when there are unindexed foreign keys around :)

(by the way, it would have taken times longer to discover where the problem is if i hadn't read this thread about unindexed foreign keys before, so thank you for this thread!)

Indexes on Foreign Keys

Jeff, February 01, 2005 - 2:39 pm UTC

As always Tom's information is extremely helpful.

I was trying to go to the link on this page, but it seems to be "dead". Can it be resurrected?

"See </code> http://asktom.oracle.com/~tkyte/unindex/index.html <code>

for a script to detect unindexed foreign keys and pointers into the documentation as to the above referred to locking issue (which is real -- table locks can arise if you delete a parent record or update the primary key of a parent record and have unindexed foreign keys)."



Tom Kyte
February 01, 2005 - 3:45 pm UTC

c/osi/asktom/



Indexes on Foreign Keys

Jeff, February 01, 2005 - 4:09 pm UTC

Tom,

Thanks for the quick response... I am pretty new here, so I am not sure if I should know what the follow-up means (if so I apologize!) or if you did a copy/paste from somewhere and it didn't come out as a link to a website?

Followup:
c/osi/asktom/

I am trying to find foreign keys without indexes. We are having a problem with replication and we think it may have something to do with this (we get table locks when dropping users). I would like to see the script you had available to see if we are overlooking something.

Thanks!

Tom Kyte
February 02, 2005 - 4:42 am UTC

sorry, I was speaking in sqlplus ;)


change the word osi in the url to asktom.

To Jeff

A Reader, February 01, 2005 - 6:04 pm UTC

Jeff,
I think this is the URL you are looking for:

</code> http://asktom.oracle.com/~tkyte/unindex/index.html <code>

If you look at the bottom of the Ask Tom home page, you will see a link to articles ...



c/osi/asktom

Bob B, February 01, 2005 - 6:05 pm UTC

c/osi/asktom = change osi to asktom.

</code> http://asktom.oracle.com/~tkyte/unindex/index.html
does not work

http://asktom.oracle.com/~tkyte/unindex/index.html <code>
*DOES* work

Indexes on Foreign Keys (Bob B and Mr. A Reader)

Jeff, February 02, 2005 - 8:34 am UTC

OK now I am just embarassed. It was a long day yesterday.

Thanks!

Pls. change the url(s) in your db.

A reader, February 07, 2005 - 11:22 pm UTC

Hi Mr. Tom,

The osi is being referred at many places. Requesting you to change all the urls in your db so that it points to valueable information rather than dead loacation.

Thanks and regards.

Tom Kyte
February 08, 2005 - 1:27 am UTC

not going to happen "soon", urls are just stored in text. I'll have to write something.

Lock in the PARENT table when deleting a row in CHILD table.

DBStar, March 09, 2005 - 3:30 pm UTC

Tom, I can understand why there is a lock on the child table when deleting the rows in the parent table. However, I have the following trace which shows a lock created in the PARENT table when deleting A record in the CHILD table. Even after creating an index on the FK the share lock persist. The delete never ends for some reason.
Please advice where is the problem. Thanks a lot.
SQL> create table DEPT (deptno number constraint pk_dept primary key, dname 
varchar2(10));

Table created

SQL> create table EMP (deptno number(2) constraint fk_deptno references 
dept(deptno), ename varchar2(20));

Table created

SQL> insert into DEPT values (1, 'COSTCENTER');

1 row inserted

SQL> insert into EMP values (1, 'SCOTT');

1 row inserted

SQL> commit;

Commit complete

SQL> DELETE FROM emp WHERE deptno = 1;

1 row deleted

SQL>select  l.sid,s.serial#,s.username,s.terminal,
  2            decode(l.type,'RW','RW - Row Wait Enqueue',
  3   'TM','TM - DML Enqueue',
  4   'TX','TX - Trans Enqueue',
  5   'UL','UL - User',l.type||'System') res,
  6           substr(t.name,1,10) tab,u.name owner,
  7           l.id1,l.id2,
  8           decode(l.lmode,1,'No Lock',
  9                  2,'Row Share',
 10                   3,'Row Exclusive',
 11                  4,'Share',
 12                   5,'Shr Row Excl',
 13                  6,'Exclusive',null) lmode,
 14          decode(l.request,1,'No Lock',
 15                   2,'Row Share',
 16                  3,'Row Excl',
 17                  4,'Share',
 18                  5,'Shr Row Excl',
 19                  6,'Exclusive',null) request
 20   from v$lock l, v$session s,
 21   sys.user$ u,sys.obj$ t
 22   where l.sid = s.sid
 23   and s.type != 'BACKGROUND'
 24   and t.obj# = l.id1
 25   and u.user# = t.owner#
 26   /

       SID    SERIAL# USERNAME                       TERMINAL
---------- ---------- ------------------------------ ----------------
RES                   TAB        OWNER                                 ID1
--------------------- ---------- ------------------------------ ----------
       ID2 LMODE         REQUEST
---------- ------------- ------------
        11       6323 TEST                           CANNON
TM - DML Enqueue      DEPT       TEST                                67281
         0 Row Share

        11       6323 TEST                           CANNON
TM - DML Enqueue      EMP        TEST                                67283
         0 Row Exclusive

/******************************************************************
create index idx_deptno on emp (deptno);

test@fdb.cannon>insert into EMP values (1, 'SCOTT');

1 row created.

test@fdb.cannon>DELETE FROM emp WHERE deptno = 1;

1 row deleted.

test@fdb.cannon>

sys@fdb.cannon>select  l.sid,s.serial#,s.username,s.terminal,
  2            decode(l.type,'RW','RW - Row Wait Enqueue',
  3   'TM','TM - DML Enqueue',
  4   'TX','TX - Trans Enqueue',
  5   'UL','UL - User',l.type||'System') res,
  6           substr(t.name,1,10) tab,u.name owner,
  7           l.id1,l.id2,
  8           decode(l.lmode,1,'No Lock',
  9                  2,'Row Share',
 10                   3,'Row Exclusive',
 11                  4,'Share',
 12                   5,'Shr Row Excl',
 13                  6,'Exclusive',null) lmode,
 14          decode(l.request,1,'No Lock',
 15                   2,'Row Share',
 16                  3,'Row Excl',
 17                  4,'Share',
 18                  5,'Shr Row Excl',
 19                  6,'Exclusive',null) request
 20   from v$lock l, v$session s,
 21   sys.user$ u,sys.obj$ t
 22   where l.sid = s.sid
 23   and s.type != 'BACKGROUND'
 24   and t.obj# = l.id1
 25   and u.user# = t.owner#
 26   /

       SID    SERIAL# USERNAME                       TERMINAL
---------- ---------- ------------------------------ ----------------
RES                   TAB        OWNER                                 ID1
--------------------- ---------- ------------------------------ ----------
       ID2 LMODE         REQUEST
---------- ------------- ------------
        11          9 TEST                           CANNON
TM - DML Enqueue      DEPT       TEST                                67281
         0 Row Share

        11          9 TEST                           CANNON
TM - DML Enqueue      EMP        TEST                                67283
         0 Row Exclusive

 

Tom Kyte
March 09, 2005 - 3:50 pm UTC

it is there to prevent DDL -- to prevent someone from altering the parent table. No rows locked in there, nothing normal is prevented on the parent table.

reader

A reader, March 25, 2005 - 10:36 am UTC

Could you give us a methodical way to disable fk and 
truncate tables in an environment where there are cascading
referencial integrity constraints

SQL> create table dept (deptno number);
 
Table created.

SQL>  alter table dept add primary key (deptno);
 
Table altered.
 
SQL> create table emp ( empno int primary key,deptno references DEPT );
 
Table created.

SQL> truncate table emp;
 
Table truncated.

SQL> truncate table dept;
truncate table dept
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
 

Tom Kyte
March 25, 2005 - 6:30 pm UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5955584820279#35376868910093 <code>

(starting at the patch :) rest of code is above that section)

Strange access path

Sanjaya Balasuriya, March 29, 2005 - 3:37 pm UTC

Hi Tom,

I have 2 tables;

1.
CREATE TABLE uw_t_pol_funds
(pfd_seq_no VARCHAR2(15) NOT NULL,
pfd_pol_seq_no VARCHAR2(15) NOT NULL,
pfd_amount NUMBER(14,5) NOT NULL,
pfd_fun_code VARCHAR2(5) NOT NULL,
created_by VARCHAR2(20),
created_date DATE,
modified_by VARCHAR2(20),
modified_date DATE,
pfd_transaction_amount NUMBER(14,5),
pfd_active VARCHAR2(1) NOT NULL,
pfd_selected VARCHAR2(1),
pfd_previous_amount NUMBER(14,5),
pfd_calc_method VARCHAR2(1),
pfd_cancelled_amount NUMBER(14,5),
pfd_prev_seq_no VARCHAR2(15),
pfd_prev_sum_insured NUMBER(17,5),
pfd_prev_premium NUMBER(14,5),
pfdl_cancelled_amount NUMBER(14,5))
/

-- Indexes for UW_T_POL_FUNDS

CREATE INDEX pfd_pol_fk_i_new ON uw_t_pol_funds
(pfd_pol_seq_no ASC)
NOLOGGING
/
CREATE INDEX pfd_fun_fk_i_new ON uw_t_pol_funds
(pfd_fun_code ASC)
NOLOGGING
/
-- Constraints for UW_T_POL_FUNDS
ALTER TABLE uw_t_pol_funds
ADD CONSTRAINT pfd_pk_new PRIMARY KEY (pfd_pol_seq_no, pfd_seq_no)
USING INDEX
/

2.
CREATE TABLE uw_t_pol_com_breakup
(pcb_percentage NUMBER(9,6),
pcb_rate NUMBER(14,5),
pcb_amount NUMBER(14,5) NOT NULL,
pcb_pcm_pol_seq_no VARCHAR2(15) NOT NULL,
pcb_pcm_seq_no VARCHAR2(15) NOT NULL,
pcb_pfd_pol_seq_no VARCHAR2(15) NOT NULL,
pcb_pfd_seq_no VARCHAR2(15) NOT NULL,
created_by VARCHAR2(20),
created_date DATE,
modified_by VARCHAR2(20),
modified_date DATE,
pcb_seq_no VARCHAR2(15),
pcb_cancelled_amount NUMBER(14,5))
/

-- Indexes for UW_T_POL_COM_BREAKUP
CREATE INDEX pcb_pcm_fk_i ON uw_t_pol_com_breakup
(
pcb_pcm_pol_seq_no ASC,
pcb_pcm_seq_no ASC)
NOLOGGING
/
CREATE INDEX pcb_pfd_fk_i ON uw_t_pol_com_breakup
(pcb_pfd_pol_seq_no ASC,
pcb_pfd_seq_no ASC)
NOLOGGING
/
CREATE INDEX pcb_fk_pol_seq ON uw_t_pol_com_breakup
(pcb_pcm_pol_seq_no ASC)
NOLOGGING
/
CREATE INDEX indx_pcb_pcm_seq_no ON uw_t_pol_com_breakup
(pcb_pcm_seq_no ASC)
NOLOGGING
/
CREATE UNIQUE INDEX duw_pcb_seq_no_uk ON uw_t_pol_com_breakup
(pcb_seq_no ASC)
NOLOGGING
/
-- Constraints for UW_T_POL_COM_BREAKUP
ALTER TABLE uw_t_pol_com_breakup
ADD CONSTRAINT pcb_pk PRIMARY KEY (pcb_pcm_pol_seq_no, pcb_pcm_seq_no,
pcb_pfd_pol_seq_no, pcb_pfd_seq_no)
USING INDEX
/

And this is my query;

SELECT UW_T_POL_COM_BREAKUP.pcb_percentage, UW_T_POL_COM_BREAKUP.pcb_rate,
ROUND(NVL(UW_T_POL_COM_BREAKUP.pcb_amount,0),2), UW_T_POL_FUNDS.pfd_fun_code
FROM UW_T_POL_COM_BREAKUP, UW_T_POL_FUNDS
WHERE UW_T_POL_FUNDS.pfd_seq_no = UW_T_POL_COM_BREAKUP.pcb_pfd_seq_no
and UW_T_POL_FUNDS.pfd_pol_seq_no = UW_T_POL_COM_BREAKUP.pcb_pfd_pol_seq_no ,

Autotrace output of the query is;


302768 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=54705 Card=30276
8 Bytes=23615904)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'UW_T_POL_COM_BREAKUP' (C
ost=2 Card=1 Bytes=42)

2 1 NESTED LOOPS (Cost=54705 Card=302768 Bytes=23615904)
3 2 VIEW OF 'index$_join$_002' (Cost=1254 Card=213807 Byte
s=7697052)

4 3 HASH JOIN
5 4 INDEX (FAST FULL SCAN) OF 'PFD_FUN_FK_I_NEW' (NON-
UNIQUE) (Cost=9 Card=213807 Bytes=7697052)

6 4 INDEX (FAST FULL SCAN) OF 'PFD_PK_NEW' (UNIQUE) (C
ost=9 Card=213807 Bytes=7697052)

7 2 INDEX (RANGE SCAN) OF 'PCB_PFD_FK_I' (NON-UNIQUE)




Statistics
----------------------------------------------------------
97 recursive calls
0 db block gets
653928 consistent gets
2538 physical reads
0 redo size
5106665 bytes sent via SQL*Net to client
141563 bytes received via SQL*Net from client
20186 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
302768 rows processed

Can you please explain why "PFD_FUN_FK_I_NEW" is accessed instead of going only to "PFD_PK_NEW" ?
The columns "pfd_seq_no" and "pfd_pol_seq_no" are having 213807 and 98629 distinct values respectively. But "pfd_fun_code" has only 9 distinct values.

Thanks.

Tom Kyte
March 29, 2005 - 4:57 pm UTC


I'll guess "missing or invalidate statistics on indexes"

I can just about replicate this:

ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'uw_t_pol_funds', numrows=>1000000, numblks => 10000 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'UW_T_POL_COM_BREAKUP', numrows=>1000000, numblks => 10000 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> SELECT UW_T_POL_COM_BREAKUP.pcb_percentage, UW_T_POL_COM_BREAKUP.pcb_rate,
  2         ROUND(NVL(UW_T_POL_COM_BREAKUP.pcb_amount,0),2),
  3  UW_T_POL_FUNDS.pfd_fun_code
  4  FROM UW_T_POL_COM_BREAKUP, UW_T_POL_FUNDS
  5  WHERE UW_T_POL_FUNDS.pfd_seq_no = UW_T_POL_COM_BREAKUP.pcb_pfd_seq_no
  6    and UW_T_POL_FUNDS.pfd_pol_seq_no = UW_T_POL_COM_BREAKUP.pcb_pfd_pol_seq_no
  7  /
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4153 Card=1 Bytes=79)
   1    0   HASH JOIN (Cost=4153 Card=1 Bytes=79)
   2    1     VIEW OF 'index$_join$_002' (Cost=1068 Card=1000000 Bytes=22000000)
   3    2       HASH JOIN (Cost=4153 Card=1 Bytes=79)
   4    3         INDEX (FAST FULL SCAN) OF 'PFD_FUN_FK_I_NEW' (NON-UNIQUE) (Cost=33 Card=1000000 Bytes=220000
          00)
 
   5    3         INDEX (FAST FULL SCAN) OF 'PFD_PK_NEW' (UNIQUE) (Cost=33 Card=1000000 Bytes=22000000)
   6    1     TABLE ACCESS (FULL) OF 'UW_T_POL_COM_BREAKUP' (Cost=1519 Card=1000000 Bytes=57000000)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.set_index_stats( user, 'pfd_pol_fk_i_new', numrows => 1000000, numlblks => 10000 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec dbms_stats.set_index_stats( user, 'pfd_fun_fk_i_new', numrows => 1000000, numlblks => 10000 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec dbms_stats.set_index_stats( user, 'pfd_pk_new', numrows => 1000000, numlblks => 10000 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> SELECT UW_T_POL_COM_BREAKUP.pcb_percentage, UW_T_POL_COM_BREAKUP.pcb_rate,
  2         ROUND(NVL(UW_T_POL_COM_BREAKUP.pcb_amount,0),2),
  3  UW_T_POL_FUNDS.pfd_fun_code
  4  FROM UW_T_POL_COM_BREAKUP, UW_T_POL_FUNDS
  5  WHERE UW_T_POL_FUNDS.pfd_seq_no = UW_T_POL_COM_BREAKUP.pcb_pfd_seq_no
  6    and UW_T_POL_FUNDS.pfd_pol_seq_no = UW_T_POL_COM_BREAKUP.pcb_pfd_pol_seq_no
  7  /
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4604 Card=10000000000 Bytes=790000000000)
   1    0   HASH JOIN (Cost=4604 Card=10000000000 Bytes=790000000000)
   2    1     TABLE ACCESS (FULL) OF 'UW_T_POL_FUNDS' (Cost=1519 Card=1000000 Bytes=22000000)
   3    1     TABLE ACCESS (FULL) OF 'UW_T_POL_COM_BREAKUP' (Cost=1519 Card=1000000 Bytes=57000000)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off


I wouldn't want this using any indexes personally.  Unless the index can be used as a skinny table that is.


 

Still need more exaplanations

Sanjaya Balasuriya, April 06, 2005 - 6:06 am UTC

Hi Tom,

I still didn't get it all.
And got another case in the same sort.

Again with 2 tables;

1.
CREATE TABLE uw_t_plans
(pln_seq_no VARCHAR2(15) NOT NULL,
pln_code VARCHAR2(5) NOT NULL,
pln_description VARCHAR2(25),
pln_premium NUMBER(14,5) NOT NULL,
pln_quot_seq_no VARCHAR2(15),
pln_pol_seq_no VARCHAR2(15),
created_by VARCHAR2(20),
created_date DATE,
modified_by VARCHAR2(20),
modified_date DATE,
pln_event_limit NUMBER(17,5),
pln_annual_limit NUMBER(17,5),
pln_prd_code VARCHAR2(5),
pln_psc_seq_no VARCHAR2(15),
pln_psc_sec_code VARCHAR2(5),
pln_printable VARCHAR2(1),
pln_policy_no VARCHAR2(30),
pln_loading NUMBER(17,5))
/

-- Indexes for UW_T_PLANS

CREATE INDEX pln_policy_no_i ON uw_t_plans
(pln_policy_no ASC)
/
CREATE INDEX pln_prd_code_i ON uw_t_plans
(pln_prd_code ASC)
/


-- Constraints for UW_T_PLANS

ALTER TABLE uw_t_plans
ADD CONSTRAINT pln_pk PRIMARY KEY (pln_seq_no)
USING INDEX
/

2.

CREATE TABLE uw_t_pol_risks
(prs_seq_no VARCHAR2(15) NOT NULL,
prs_r_seq VARCHAR2(5) NOT NULL,
prs_risk_ref_no VARCHAR2(5),
prs_reference_field VARCHAR2(20),
prs_title VARCHAR2(5),
prs_name VARCHAR2(50),
prs_nic_no VARCHAR2(12),
prs_sum_insured NUMBER(17,5),
prs_premium NUMBER(14,5),
prs_relationship_type VARCHAR2(15),
prs_relationship_seq VARCHAR2(15),
prs_all_inclusive VARCHAR2(1),
prs_all_inclusive_pct NUMBER(9,6),
prs_plc_pol_seq_no VARCHAR2(15) NOT NULL,
prs_plc_seq_no VARCHAR2(15) NOT NULL,
prs_pln_seq_no VARCHAR2(15),
created_by VARCHAR2(20),
created_date DATE,
modified_by VARCHAR2(20),
modified_date DATE,
prs_event_limit NUMBER(14,5),
prs_annual_limit NUMBER(14,5),
prs_ret_occupation_code VARCHAR2(5),
prs_certificate_no VARCHAR2(30),
prs_no_certificates NUMBER(2),
prs_prev_seq_no VARCHAR2(15),
prs_prev_sum_insured NUMBER(17,5),
prs_prev_premium NUMBER(14,5),
prs_cl_claims_paid NUMBER(17,5),
prs_cl_claims_pending NUMBER(17,5),
prs_dependant_type VARCHAR2(15),
prs_dependant_seq VARCHAR2(15),
prs_to_be_charged VARCHAR2(1),
prs_charge_amount NUMBER(14,5),
prs_date_of_birth DATE,
prs_psc_sec_code VARCHAR2(5),
prs_policy_no VARCHAR2(30),
prs_loc_code VARCHAR2(20),
prs_date_endorsed DATE,
prs_transaction_date DATE,
prs_endorsed VARCHAR2(1),
prs_new_risk VARCHAR2(1),
prs_insert_endorse_no VARCHAR2(30),
prs_update_endorse_no VARCHAR2(30),
prs_date_deleted DATE,
prs_delete_endorse_no VARCHAR2(30))
/

-- Indexes for UW_T_POL_RISKS

CREATE INDEX indx_pol_risks ON uw_t_pol_risks
(prs_loc_code ASC,
prs_plc_pol_seq_no ASC,
prs_policy_no ASC,
prs_date_endorsed ASC,
prs_r_seq ASC,
prs_seq_no ASC)
/
CREATE INDEX nuk_prc_tit_fk ON uw_t_pol_risks
(prs_title ASC)
/
CREATE INDEX nuk_prs_occupation_code_fk ON uw_t_pol_risks
(prs_ret_occupation_code ASC)
/
CREATE INDEX nuk_prs_plc_fk ON uw_t_pol_risks
(prs_plc_pol_seq_no ASC,
prs_plc_seq_no ASC)
/
CREATE INDEX prs_pln_fk_i ON uw_t_pol_risks
(prs_pln_seq_no ASC)
/
CREATE INDEX prs_plc_fk_i ON uw_t_pol_risks
(prs_plc_seq_no ASC,
prs_plc_pol_seq_no ASC)
/
CREATE INDEX nui_prs_seq_no ON uw_t_pol_risks
(prs_seq_no ASC)
/
CREATE INDEX prs_fk_pol_seq ON uw_t_pol_risks
(prs_plc_pol_seq_no ASC)
/
CREATE INDEX indx_pol_risks_01 ON uw_t_pol_risks
(prs_relationship_seq ASC)
/


-- Constraints for UW_T_POL_RISKS

ALTER TABLE uw_t_pol_risks
ADD CONSTRAINT prs_pk PRIMARY KEY (prs_plc_pol_seq_no, prs_plc_seq_no,
prs_seq_no)
USING INDEX
/

Here is my query;

SELECT l.pln_seq_no, l.pln_code, l.pln_description, l.pln_event_limit,
l.pln_annual_limit, l.pln_printable, l.pln_policy_no,
l.pln_pol_seq_no, l.pln_premium
FROM uw_t_plans l
WHERE pln_seq_no IN (SELECT r.prs_pln_seq_no
FROM uw_t_pol_risks r
WHERE r.prs_plc_pol_seq_no LIKE '000MS0500155977'
AND r.prs_relationship_seq IS NULL)
ORDER BY l.pln_seq_no;

"autotrace" output for this query is;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=417 Card=1364 By
tes=125488)

1 0 NESTED LOOPS (SEMI) (Cost=417 Card=1364 Bytes=125488)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'UW_T_PLANS' (Cost=62 C
ard=1856 Bytes=126208)

3 2 INDEX (FULL SCAN) OF 'PLN_PK' (UNIQUE) (Cost=8 Card=18
56)

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'UW_T_POL_RISKS' (Cost=
417 Card=7 Bytes=168)

5 4 BITMAP CONVERSION (TO ROWIDS)
6 5 BITMAP AND
7 6 BITMAP CONVERSION (FROM ROWIDS)
8 7 INDEX (RANGE SCAN) OF 'PRS_FK_POL_SEQ' (NON-UNIQ
UE)

9 6 BITMAP CONVERSION (FROM ROWIDS)
10 9 INDEX (RANGE SCAN) OF 'PRS_PLN_FK_I' (NON-UNIQUE
)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3960 consistent gets
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
234 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

But if I remove the "order by" clause or add another column to the order by clause, results are much much better.

SELECT l.pln_seq_no, l.pln_code, l.pln_description, l.pln_event_limit,
l.pln_annual_limit, l.pln_printable, l.pln_policy_no,
l.pln_pol_seq_no, l.pln_premium
FROM uw_t_plans l
WHERE pln_seq_no IN (SELECT r.prs_pln_seq_no
FROM uw_t_pol_risks r
WHERE r.prs_plc_pol_seq_no LIKE '000MS0500155977'
AND r.prs_relationship_seq IS NULL)
ORDER BY l.pln_seq_no, l.pln_code;

"autotrace" output is;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=11 Card=9 Bytes=
828)

1 0 SORT (ORDER BY) (Cost=11 Card=9 Bytes=828)
2 1 NESTED LOOPS (Cost=10 Card=9 Bytes=828)
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'UW_T_POL_RISKS' (C
ost=6 Card=9 Bytes=216)

5 4 INDEX (RANGE SCAN) OF 'NUK_PRS_PLC_FK' (NON-UNIQUE
) (Cost=3 Card=45)

6 2 TABLE ACCESS (BY INDEX ROWID) OF 'UW_T_PLANS' (Cost=2
Card=1 Bytes=68)

7 6 INDEX (UNIQUE SCAN) OF 'PLN_PK' (UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
418 bytes sent via SQL*Net to client
234 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed

What's happening here ?
I tried deleting and re-gathering statistics on the tables also. But no luck.
My idea was that the data is sorted by the order by clause after the data is selected. But it seems to be all the rows are read despite the where clause.
Or Oracle is trying to find an index that can be used for data selection and sorting together ?

What are the techniques we can use to tune statements like this ? (we need the output ordered)

Thanks

Tom Kyte
April 06, 2005 - 9:09 am UTC

please, utilize this space for followups relevant to the original question.

A reader, April 13, 2005 - 3:35 am UTC


Creating an index on a table that is being processed

A reader, April 14, 2005 - 12:02 pm UTC

One of our update scripts have been running for almost 18 hours...the table only has 2 million rows. It does not have any indexes or pk constraints

As it is... right now, this table does not have any indexes or pk constraints. my questions,
1)Can I create an index while the table is being updated
2)Can I do an analyze while the table is being updated

Where can I start to tune?? The update statement has only one column in where clause(a basic simple update statment)

Tom Kyte
April 14, 2005 - 1:00 pm UTC

1) you will need to obtain a very short term lock on it to start the online index creation. So, the update would have to be of a type that commits every now and again.

2) yes.

but if the update is running, it won't matter WHAT you do index/stats wise, a plan cannot change midstream.

you are the best...

A reader, April 14, 2005 - 11:38 pm UTC

Thanks

sam, May 16, 2005 - 3:41 am UTC

Any index that contains the foreign key on the leading edge will do -- eg:

create table emp ( empno int primary key, ... deptno references DEPT );

create index on dept(deptno,empno); -- this is OK
create index on dept(empno,deptno); -- this will not prevent the fkey lock
create index on dept(deptno); -- this is OK


Question is wheather this holds true in 9i rel2, I mean if I have a Uinque Key (a,b,c,d) and also a FK on a,b,c to other tables.
1.Do I need to create a index on b,c (Assuming I hv to create index on all FKs to avoid locking? is this have any relation with index skip scan .
2. I can very well skip a index on a .Right ??


Tom Kyte
May 16, 2005 - 8:01 am UTC

as long as the entire set of foreign key columns is on the leading edge of an index, you are covered.

if (a,b,c,d) are indexed and the fkey is (a,b,c) you are done. no more indexes needed.

this has nothing to do with a skip scan, which is an access path.


2) i didn't understand that.

Thanks a lot

A reader, May 16, 2005 - 10:46 am UTC


Excellent!

Jairo Ojeda, May 16, 2005 - 12:50 pm UTC


Foreign keys and indexes

MJ, June 14, 2005 - 6:59 pm UTC

Tom,

This thread is very useful one for me, but I didn't see the question I came to ask.

I am a newbie working with more seasoned Oracle users. I noticed an odd thing about many of our indexes on foreign keys:

Where
- deptno is the key to dept
- empno is the key to emp
- emp has a foreign key (dept)
- emp has an index (dept, empno)

I asked the more seasoned guy why empno is included in the index, and he says it helps when deleting rows from emp based on a value in a dept field.

I tried testing deletion from emp with and without empno in the index, and it seemed to me that the stats were saying that it is actually faster without the unique index.

Is there any reason to make the foreign key unique like that? Is the cost significant?

Thank you,

MJ

Tom Kyte
June 15, 2005 - 3:16 am UTC

that "reasoning" makes no sense to me.

emp should have an index on (dept) for the fkey, is that is the only consideration, empno isn't relevant in the index.

What is the effect of rownum>0

Yousef, June 26, 2005 - 6:37 am UTC

the following query
select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
and rownum>0
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
/

runs successfully but the following generate an error not grout function, what is the effect of rownum>0

select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
/



Tom Kyte
June 26, 2005 - 9:45 am UTC

it was a temporary workaround for a 'product issue' (don't need it in current releases)

it materialized the inline view, instead of merging it - it fixed a temporary issue.

Thanks

A reader, June 26, 2005 - 9:52 am UTC

Thank you so much

reader

A reader, July 06, 2005 - 2:52 pm UTC

</code> http://asktom.oracle.com/~tkyte/unindex/index.html <code>
does not seem to work

Tom Kyte
July 07, 2005 - 8:22 am UTC

change osi to asktom, osi was the old server name

Full Table Scan

A reader, July 19, 2005 - 1:21 pm UTC

Hi Tom,

I have the following tables


CREATE TABLE CLM_DETAILS
(
CLM_ID NUMBER);


CREATE TABLE CLM_CLAIMANT_DETAILS
(
CLAIMANT_ID NUMBER,
CLM_ID NUMBER);


CREATE TABLE CLM_SUBFILE_DETAILS
(
SUBFILE_ID NUMBER,
CLAIMANT_ID NUMBER);


ALTER TABLE CLM_DETAILS ADD (
CONSTRAINT CLM_DETAILS_PK PRIMARY KEY (CLM_ID));

ALTER TABLE CLM_CLAIMANT_DETAILS ADD (
CONSTRAINT CLM_CLAIMANT_DETAILS_PK PRIMARY KEY (CLAIMANT_ID));

ALTER TABLE CLM_SUBFILE_DETAILS ADD (
CONSTRAINT CLM_SUBFILE_DETAILS_PK PRIMARY KEY (SUBFILE_ID));

ALTER TABLE CLM_CLAIMANT_DETAILS ADD (
CONSTRAINT CLM_CLAIMANT_DETAILS_FK FOREIGN KEY (CLM_ID)
REFERENCES CLM_DETAILS (CLM_ID));

ALTER TABLE CLM_SUBFILE_DETAILS ADD (
CONSTRAINT CLM_SUBFILE_DETAILS_FK FOREIGN KEY (CLAIMANT_ID)
REFERENCES CLM_CLAIMANT_DETAILS (CLAIMANT_ID));


CREATE INDEX CLM_DETAILS_CLM_ID_FK ON CLM_CLAIMANT_DETAILS
(CLM_ID);

CREATE INDEX CLM_SUBFILE_CLAIMANT_ID_FK ON CLM_SUBFILE_DETAILS
(CLAIMANT_ID);

I am executing the following query

SELECT cl.clm_id AS cl_clm_id
,cm.claimant_id AS cm_claimant_id
,cm.clm_id AS cm_clm_id
,sf.subfile_id AS sf_subfile_id
,sf.claimant_id AS sf_claimant_id
FROM clm_details cl
,clm_claimant_details cm
,clm_subfile_details sf
WHERE sf.claimant_id = cm.claimant_id
AND cm.clm_id = cl.clm_id;

Which produce the execution plan as follows.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=1354217 By
tes=27084340)

1 0 HASH JOIN (Cost=10 Card=1354217 Bytes=27084340)
2 1 VIEW OF 'index$_join$_003' (VIEW) (Cost=3 Card=10000 Byt
es=80000)

3 2 HASH JOIN
4 3 INDEX (FAST FULL SCAN) OF 'CLM_SUBFILE_CLAIMANT_ID_F
K' (INDEX) (Cost=1 Card=10000 Bytes=80000)

5 3 INDEX (FAST FULL SCAN) OF 'CLM_SUBFILE_DETAILS_PK' (
INDEX (UNIQUE)) (Cost=1 Card=10000 Bytes=80000)

6 1 HASH JOIN (Cost=5 Card=118765 Bytes=1425180)
7 6 INDEX (FULL SCAN) OF 'CLM_DETAILS_PK' (INDEX (UNIQUE))
(Cost=1 Card=10000 Bytes=40000)

8 6 VIEW OF 'index$_join$_002' (VIEW) (Cost=3 Card=10000 B
ytes=80000)

9 8 HASH JOIN
10 9 INDEX (FAST FULL SCAN) OF 'CLM_CLAIMANT_DETAILS_PK
' (INDEX (UNIQUE)) (Cost=1 Card=10000 Bytes=80000)

11 9 INDEX (FAST FULL SCAN) OF 'CLM_DETAILS_CLM_ID_FK'
(INDEX) (Cost=1 Card=10000 Bytes=80000)


Do you think there is further possibility to improve the performance?

Second, every table has 300 columns (approx.) and if I use all columns from all tables with same join condition, it generates the following execution plan.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10050 Card=1354217
Bytes=1887778498)

1 0 HASH JOIN (Cost=10050 Card=1354217 Bytes=1887778498)
2 1 TABLE ACCESS (FULL) OF 'CLM_DETAILS' (TABLE) (Cost=3090
Card=10000 Bytes=7310000)

3 1 MERGE JOIN (Cost=3519 Card=114025 Bytes=75598575)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLM_CLAIMANT_DETAILS
' (TABLE) (Cost=29 Card=10000 Bytes=3680000)

5 4 INDEX (FULL SCAN) OF 'CLM_CLAIMANT_DETAILS_PK' (INDE
X (UNIQUE)) (Cost=1 Card=10000)

6 3 SORT (JOIN) (Cost=3490 Card=10000 Bytes=2950000)
7 6 TABLE ACCESS (FULL) OF 'CLM_SUBFILE_DETAILS' (TABLE)
(Cost=3090 Card=10000 Bytes=2950000)


Why full table scan, even though there are index on FK columns?



Tom Kyte
July 19, 2005 - 5:32 pm UTC

why full scan? becuase you want all of the rows.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6749454952894#6760861174154 <code>



Deleting of rows with primary keys

Bala, November 06, 2005 - 5:04 am UTC

Hi,
I was asked this question by my friend, Can u help ?
When a row is deleted, the primary key value of that record is not deleted & is stored somewhere in some system table, when we try to add a new record with the same primary key value, it is not allowed even though the record was deleted previously. But what we need is that the database should allow adding the record with that primary key if that does not exist at present.
Is it possible to use on delete cascade?


Tom Kyte
November 06, 2005 - 8:32 am UTC

er? that is not the way it works at all.

when you delete (and subsequently commit that transaction) that key is free to be reused by anyone.

Beware of unindexed recursive foreign keys too...

Kashif, November 09, 2005 - 11:12 am UTC

Hi all -

Recently one of my team-mates came across a table that had recursive foreign keys, i.e. columns in that table referenced the primary key of the same table. A simple delete on this table would take extremely long to complete. It took us a while to figure out that it was these unindexed foreign keys that were slowing down the deletes. Without the indexes the delete was taking around 38 minutes to complete, and with the index it completed in 9 seconds.

So the moral of the story is that even if there are no foreign keys from other tables to a table you might be looking at, there might still be foreign key/s from the same table to the table's primary key, and those foreign keys still need to be indexed.

Hope this helps someone out there who's scratching his/her heard trying to figure out why a simple delete on a single table is taking forever. :-)

Kashif

Tom Kyte
November 11, 2005 - 10:10 am UTC

A rose smells as sweet, by whatever name you call it.

a foreign key is a foreign key is a foreign key :)

index

sam, November 10, 2005 - 10:52 pm UTC

Tom:

if you have a query

select * from table where stock_number <> '99999' and
stock_type in ('P','B') and withdrawn is not null

would you create one single index on these 3 columns

or create three separate indexes: one on each column


Tom Kyte
November 12, 2005 - 8:05 am UTC

<> is not giong to be excessively index friendly - as will be "is not null"

but an index on (stock_type,stock_number,widthdrawn)

would allow us to find the rows in the table - the entire predicate can be checked against the index and we'll access the table by INDEX ROWID as little as possible.


That is, if a full scan isn't deemed to be the best approach.

index

mo, November 12, 2005 - 9:25 am UTC

Tom:

1. Is there a way to avoid the "<> '99999'" and "is not null" to speed up the query and use the index.

2. just curious creating three indexes does not help for this specific query at all? correct

Tom Kyte
November 12, 2005 - 11:01 am UTC

1) i said it would use the index, it would use the index to resolve the IN part and then looking at the index entries only find the rows of interest in the index and access the table as little as possible.

short of creating a function based index just for this query - you have what you have.

2) correct, unless they were bitmap indexes - in which case we'd need to discuss further, but unless this data is readonly or read mostly, you won't be using bitmaps anyway.

index on fk

rr, May 01, 2006 - 3:48 pm UTC

How to find index on foeign key?

Tom Kyte
May 02, 2006 - 3:32 am UTC

you need to look for any index that has the columns that comprise the foreign key columns "in the front"

eg: if you have a foreign key (a,b) in a table (two columns...)

then you want ANY b*tree index that starts with A,B

create index i on t(a,b,c,d,e,f)

for example would be an OK index.


</code> http://asktom.oracle.com/~tkyte/unindex/unindex.sql <code>
shows how to find the UNINDEXED foreign keys.

fk

rr, May 01, 2006 - 9:18 pm UTC

select index_name,table_name,index_type
from dba_indexes where table_name in (select TABLE_NAME from dba_constraints where CONSTRAINT_TYPE='R')

Does it make sense?

Tom Kyte
May 02, 2006 - 3:40 am UTC

for what - if you want to ask the question

what is the entire set of all indexes on tables that have a foreign key

then you are running a sensible query. If that is NOT the question you want to ask - then you are not running a sensible query.

fk index

rr, May 02, 2006 - 7:01 pm UTC

create or replace view fkinfo as
2 select decode( b.table_name, NULL, '****', 'ok' ) Status,
3 a.table_name, a.columns "tabcol", b.columns "indexcolumn"
4 from
5 ( select substr(a.table_name,1,30) table_name,
6 substr(a.constraint_name,1,30) constraint_name,
7 max(decode(position, 1, substr(column_name,1,30),NULL)) ||
8 max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
9 max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
10 max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
11 max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
12 max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
13 max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
14 max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
15 max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
16 max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
17 max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
18 max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
19 max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
20 max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
21 max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
22 max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
23 from user_cons_columns a, user_constraints b
24 where a.constraint_name = b.constraint_name
25 and b.constraint_type = 'R'
26 group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
27 ( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
28 max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
29 max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
30 max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
31 max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
32 max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
33 max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
34 max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
35 max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
36 max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
37 max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
38 max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
39 max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
40 max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
41 max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
42 max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
43 max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
44 from user_ind_columns
45 group by substr(table_name,1,30), substr(index_name,1,30) ) b
46 where a.table_name = b.table_name (+)
47 and b.columns (+) like a.columns || '%'
48 /

View created.

select tabcol from fkinfo;
select tabcol from fkinfo
*
ERROR at line 1:
ORA-00904: "TABCOL": invalid identifier


select indexcolumn from fkinfo;
select indexcolumn from fkinfo
*
ERROR at line 1:
ORA-00904: "INDEXCOLUMN": invalid identifier

Please suggest on this as I am trying to write


select 'create index '|| column || '_indx on '|| table_name || ' ('||column||') tablespace daqindx ; ' from fkinfo;

Tom Kyte
May 03, 2006 - 1:46 am UTC

you purposefully named those columns in lowercase.

select "tabcol" from ....


once you quote them the first time - you'll be quoting them forever.

Function Based Index as Foreign Key

John G. Galinski, September 20, 2006 - 9:58 am UTC

This is similar to the situation described in the posting "Functional unique index as foreign key", without the nulls.

I have two tables, a parent with active and inactive records where an index on the active records will be unique and a child where a foreign key value must exist in an active record of the parent.

create table p
(
domain varchar2(4) not null,
type varchar2(4) not null,
desc1 varchar2(16) not null,
status varchar2(1) not null
)
;
create unique index p_type_fdx
on p
( case when status = 'A'
then domain end,
case when status = 'A'
then type end);
create table c
(
domain varchar2(4) not null,
project varchar2(1) not null,
type varchar2(4) not null,
desc2 varchar2(32) not null
)
;

The composite foreign key should be domain and type.

I understand from your answer to Madhuri Bondre above that the referenced key must be a constraint and not simply a unique index, so how can I enforce the referential integrity?

Tom Kyte
September 20, 2006 - 3:23 pm UTC

you cannot, the fact is the function based index trick is a trick we can employ to help work around a data model problem....

meaning, we need to fix your data model to fix other more serious integrity constraints.

We really meant to have two project tables here - active ones and inactive ones.

Function Based Index as Foreign Key

John G. Galinski, September 20, 2006 - 6:32 pm UTC

Thank you Tom for your fast response. It may not be what I wanted to hear, but it tells me what I need to know.

Index Creation in create table.

Rahul, October 03, 2006 - 5:25 pm UTC

Tom,

   Just curious, can you not link a foreign key to an index name while you are creating the table? Because, when I try with "using index tablespace" clause, I am getting this error:

SQL> drop table edi_isa purge;

Table dropped.

SQL> CREATE   TABLE xxdan.edi_isa
  2     (  isaid NUMBER(10,0),
  3     fileid NUMBER(10,0),
  4     senderidqualifier VARCHAR2(20),
  5     senderid VARCHAR2(20),
  6     receiveridqualifier VARCHAR2(20),
  7     receiverid VARCHAR2(20),
  8     filedate VARCHAR2(20),
  9     filetime VARCHAR2(20),
 10     controlversion VARCHAR2(10),
 11     controlnumber VARCHAR2(10),
 12     acknowledgementrequested VARCHAR2(1),
 13     usageid VARCHAR2(20),
 14     inserteddate TIMESTAMP (6),
 15     updateddate TIMESTAMP (6),
 16      CONSTRAINT edi_isa_pk PRIMARY KEY (isaid) USING INDEX   TABLESPACE xxdanx  ENABLE,
 17      CONSTRAINT edi_isa_fk FOREIGN KEY (fileid) REFERENCES edi_files (fileid)
 18     ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED ENABLE
 19     )
 20    TABLESPACE xxdand;

Table created.

SQL> drop table edi_isa purge;

Table dropped.

SQL> CREATE   TABLE xxdan.edi_isa
  2     (  isaid NUMBER(10,0),
  3     fileid NUMBER(10,0),
  4     senderidqualifier VARCHAR2(20),
  5     senderid VARCHAR2(20),
  6     receiveridqualifier VARCHAR2(20),
  7     receiverid VARCHAR2(20),
  8     filedate VARCHAR2(20),
  9     filetime VARCHAR2(20),
 10     controlversion VARCHAR2(10),
 11     controlnumber VARCHAR2(10),
 12     acknowledgementrequested VARCHAR2(1),
 13     usageid VARCHAR2(20),
 14     inserteddate TIMESTAMP (6),
 15     updateddate TIMESTAMP (6),
 16      CONSTRAINT edi_isa_pk PRIMARY KEY (isaid) USING INDEX   TABLESPACE xxdanx  ENABLE,
 17      CONSTRAINT edi_isa_fk FOREIGN KEY (fileid) REFERENCES edi_files (fileid)
 18     ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED ENABLE using index tablespace xxdanx
 19     )
 20    TABLESPACE xxdand;
        ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED ENABLE using index tablespace xxdanx
                                                               *
ERROR at line 18:
ORA-00907: missing right parenthesis


SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL>
 

Tom Kyte
October 03, 2006 - 7:21 pm UTC

foreign keys are never enforced via an index, they do not create one. hence the using index does not make sense for them.

however if you ever:

a) delete from parent;
b) update parent set pk = new_value where....;

you need one

a) for performance (to avoid full scans on child for ever row updated!)
b) for locking issues (full table lock on child table)

reader

A reader, October 25, 2006 - 8:55 am UTC

version 9.2.0.7

0. create a unique index on a table
1. create a primary key on the table refering to columns on the unique index
2. alter table drop primary key;
The unique index is automatically dropped

3. It I issue "alter table drop primary key keep inex"
This case, primary key is dropped but unique index is kept

Is the behaviour of 2. normal

Tom Kyte
October 25, 2006 - 9:46 am UTC

it is "normal" in your release, it will change in the future:

ops$tkyte%ORA10GR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA10GR2> create unique index t_idx on t(x);

Index created.

ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(x);

Table altered.

ops$tkyte%ORA10GR2> set echo off
Wrote file /tmp/xtmpx.sql
Datatypes for Table t

                               Data                 Data
Column Name                    Type                 Length     Nullable
------------------------------ -------------------- ---------- --------
X                              NUMBER                          not null
Y                              NUMBER                          null


Indexes on t

Index                          Is
Name                           Unique COLUMNS
------------------------------ ------ --------------------------------
T_IDX                          Yes    X


Triggers on t
ops$tkyte%ORA10GR2> alter table t drop constraint t_pk;

Table altered.

ops$tkyte%ORA10GR2> set echo off
Wrote file /tmp/xtmpx.sql
Datatypes for Table t

                               Data                 Data
Column Name                    Type                 Length     Nullable
------------------------------ -------------------- ---------- --------
X                              NUMBER                          null
Y                              NUMBER                          null


Indexes on t

Index                          Is
Name                           Unique COLUMNS
------------------------------ ------ --------------------------------
T_IDX                          Yes    X


 

Indexes in DWH environments

Deepak, November 17, 2006 - 5:13 am UTC

Hi Tom,

Thanks for your nice script and explanation on the issue. It is awesome

I have a question.

In a datawarehousing environment we have too many (arround 1K) foreign key columns. Shall we go ahead and blindly create indexes on every foreign key column without taking into account the fact that creating that many indexes may take a huge amount of time? (Let's assume that we have the desired window to create the indexes).

Yes ofcourse there would definitely a huge gain in performance of the queries.

Please give your suggestion.

Tom Kyte
November 17, 2006 - 8:05 am UTC

you cannot have "too many", that is not possible, you have as many as you have - there is no such thing as "too many"

In a warehouse, you do not need to index foreign keys since you index foreign keys in order to prevent concurrency issues (the full table lock that would result if you delete from parent table or update parent tables primary key).

And in the warehouse, you do not do those things.

So, index only that which needs be indexed for query performance.

Order of tables in select statement

Joanna, November 21, 2006 - 7:34 pm UTC

This info was very helpful to me, thank you!

You mentioned in your answer that you would probably need an index on your foreign key if you did many queries like this:

select * from dept, emp
where emp.deptno = dept.deptno and dept.deptno = :X;

My question is - does the order of tables mentioned in the query matter? If I were to do this:

select * from emp, dept
where emp.deptno = dept.deptno and dept.deptno = :X;

Would this speed up/slow down this query at all?

Tom Kyte
November 22, 2006 - 4:49 pm UTC

nope, in this case the nope applies for both the CBO and RBO even...

both would know

a) unique index on dept(deptno)
b) probably index range scan for emp(deptno)

Hanging session during Alter table

Tariq Zia Lakho, January 11, 2007 - 1:36 am UTC

I am working on 9i (9.2.0.1.0).
I am facing One problem since long.
Whenever i want to Alter any table in current schema
(add / modify column or add constraint) current session hang.
I have run all the block query but there is not row in blocker and v$locked_object.

Please do the needful.

Hanging session during Alter table

Tariq Zia Lakho, January 13, 2007 - 2:30 am UTC

Dear Tom,
I am still waiting for your reply.

Nulls on foreign keys

Richard, February 15, 2007 - 11:07 am UTC

Hi Tom,

why is it possible insert a null value in a foreign key column if foreign key only stores values that exists in the pk and the pk can not store null values?

Thank you!
Tom Kyte
February 16, 2007 - 10:54 am UTC

because the rules of ANSI SQL say it is that way. A nullable foreign key is allowed to contain nulls. It is how you implement an optional relationship.

Nulls on foreign keys

Richard, February 16, 2007 - 11:15 am UTC


column order for the Unindexed Foreign Keys query

Donat Callens, April 02, 2007 - 8:07 am UTC

about the query used to display Unindexed Foreign Keys (available on http://asktom.oracle.com/tkyte/unindex/unindex.sql ):
it sorts the columns by their position order in the constraint and the index and then compare the concatenated strings. But an index with columns in a reversed order (or any other) should also be successful in providing performance benefits for the constraint. I have updated the query to reflect that by sorting the columns by their name instead. I have also removed some of the useless SUBSTR functions, added the owner by querying the ALL_ tables instead of the USER_ ones and allowed only the display of the unindexed foreign key constraints. I also used 'WITH ... AS' to ensure the "sort key too long" issue does not come up.
Please comment on this version of the query:
WITH cons_cols AS
 (SELECT owner,
   table_name,
   constraint_name,
   MAX(decode(row_num,   1,   SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   2,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   3,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   4,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   5,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   6,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   7,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   8,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   9,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   10,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   11,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   12,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   13,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   14,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   15,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   16,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) columns
  FROM
  (SELECT acc.owner,
    acc.table_name,
    acc.constraint_name,
    acc.column_name,
    row_number() over(PARTITION BY acc.owner,   acc.TABLE_NAME,   acc.constraint_name ORDER BY acc.column_name) row_num
   FROM all_cons_columns acc JOIN all_constraints ac ON(acc.constraint_name = ac.constraint_name AND ac.constraint_type = 'R'))
 GROUP BY owner,
   table_name,
   constraint_name),
 ind_cols AS
 (SELECT table_owner,
   table_name,
   index_name,
   MAX(decode(row_num,   1,   SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   2,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   3,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   4,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   5,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   6,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   7,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   8,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   9,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   10,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   11,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   12,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   13,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   14,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   15,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) ||
                MAX(decode(row_num,   16,   ', ' || SUBSTR(column_name,   1,   30),   NULL)) columns
  FROM
  (SELECT table_owner,
    TABLE_NAME,
    index_name,
    column_name,
    row_number() over(PARTITION BY table_owner,   table_name,   index_name ORDER BY column_name) row_num
   FROM all_ind_columns)
 GROUP BY table_owner,
   TABLE_NAME,
   index_name)
SELECT cons_cols.owner,
 cons_cols.table_name,
 cons_cols.constraint_name,
 cons_cols.columns
FROM cons_cols LEFT OUTER JOIN ind_cols ON(cons_cols.owner = ind_cols.table_owner AND cons_cols.table_name = ind_cols.table_name AND cons_cols.columns || '%' LIKE ind_cols.columns)
WHERE ind_cols.table_name IS NULL
 AND NOT owner LIKE 'SYS%'

Tom Kyte
April 04, 2007 - 9:28 am UTC

seems to get tons of false positives.

corrected my version of the query

Donat, April 10, 2007 - 5:40 am UTC

Well, we learn every day.

I feel quite abashed that I've discovered only today that 'XX%' like 'XXxxx' does not work! It should indeed be written as 'XXxxx' like 'XX%'.

I've corrected the query. There should be no false positives anymore. I replaced AND cons_cols.columns || '%' LIKE ind_cols.columns with AND ind_cols.columns LIKE cons_cols.columns || '%'.

Please comment on the usefulness of sorting by name instead of index column order and constraint column order like the query you provided.
WITH cons_cols AS 
        (SELECT owner, 
                table_name, 
                constraint_name, 
                MAX(decode(row_num, 1, SUBSTR(column_name, 1, 30), NULL)) || 
                MAX(decode(row_num, 2, ', ' || SUBSTR(column_name, 1, 30), NULL)) || 
                MAX(decode(row_num, 3, ', ' || SUBSTR(column_name, 1, 30), NULL)) || 
                MAX(decode(row_num, 4, ', ' || SUBSTR(column_name, 1, 30), NULL)) || 
                MAX(decode(row_num, 5, ', ' || SUBSTR(column_name, 1, 30), NULL)) || 
                MAX(decode(row_num, 6, ', ' || SUBSTR(column_name, 1, 30), NULL)) || 
                MAX(decode(row_num, 7, ', ' || SUBSTR(column_name, 1, 30), NULL)) || 
                MAX(decode(row_num, 8, ', ' || SUBSTR(column_name, 1, 30), NULL)) || 
                MAX(decode(row_num, 9, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 10, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 11, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 12, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 13, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 14, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 15, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 16, ', ' || SUBSTR(column_name, 1, 30), NULL)) columns 
        FROM 
                (SELECT acc.owner, 
                        acc.TABLE_NAME, 
                        acc.constraint_name, 
                        acc.column_name, 
                        row_number() over(PARTITION BY acc.owner, acc.TABLE_NAME, acc.constraint_name ORDER BY acc.column_name) row_num 
                FROM    all_cons_columns acc 
                JOIN all_constraints ac 
                ON(acc.constraint_name    =ac.constraint_name 
                    AND ac.constraint_type='R')
                ) 
        GROUP BY owner, 
                TABLE_NAME, 
                constraint_name
        )
        , 
        ind_cols AS 
        (SELECT table_owner, 
                table_name, 
                index_name, 
                MAX(decode(row_num, 1, SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 2, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 3, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 4, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 5, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 6, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 7, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 8, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 9, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 10, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 11, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 12, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 13, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 14, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 15, ', ' || SUBSTR(column_name, 1, 30), NULL)) ||
                MAX(decode(row_num, 16, ', ' || SUBSTR(column_name, 1, 30), NULL)) columns 
        FROM 
                (SELECT table_owner, 
                        table_name, 
                        index_name, 
                        column_name, 
                        row_number() over(PARTITION BY table_owner, TABLE_NAME, index_name ORDER BY column_name) row_num 
                FROM    all_ind_columns
                ) 
        GROUP BY table_owner, 
                TABLE_NAME, 
                index_name
        ) 
SELECT  cons_cols.constraint_name, 
        cons_cols.owner table_owner, 
        cons_cols.table_name , 
        cons_cols.columns 
FROM    cons_cols 
LEFT JOIN ind_cols 
ON(cons_cols.owner          =ind_cols.table_owner 
    AND cons_cols.table_name=ind_cols.table_name 
    AND ind_cols.columns LIKE cons_cols.columns || '%') 
WHERE   ind_cols.TABLE_NAME IS NULL 
    AND NOT cons_cols.owner LIKE 'SYS%' 
ORDER BY 1, 
        2, 
        3;

Tom Kyte
April 10, 2007 - 10:49 am UTC

create table p ( a number primary key);
create table c ( a references p, b number );
create index c_idx on c(b,a);


It does not work to sort by column name.

You have to order by index position.... we need an index with the columns on the LEADING EDGE.

You will not report this foreign key as unindexed.

Has this changed on 10g?

A reader, April 10, 2007 - 2:06 pm UTC

Tom,
Has the requirement to have index on foreign key columns changed or been modified in some way in 10gr2?

Thanks
Tom Kyte
April 10, 2007 - 3:59 pm UTC

no, same as in 9i

EXCELLENT

SANTO, April 11, 2007 - 11:37 am UTC

IT IS VERY NICE INFORMATION TO KNOW....

index stats and Oracle optimizer behavior

Charlie Zhu, May 03, 2007 - 6:26 pm UTC

Can I ask you a question about the index stats and Oracle optimizer behavior here?

Oracle 10.1.0.4, database stats gathered 1 month ago,
and it's a 10 years Order Item table.

How can I use DBMS_STATS.SET_INDEX_STATS() or DBMS_STATS.SET_COLUMN_STATS() to change the index stats,
to make the SQL optimizer to choose the right index I want.

We have an Order_Items table with 20 millions rows for 10 years,
and a SQL SELECT suddenly changed the execution plan to use the wrong index last Monday.

Here is the table, 2 indexes and the SQL SELECT,
It should used the index on ORDER_ID (FK) column, but now it changed to use index on STATUS column.

SELECT * FROM ABEPOITEMS
WHERE ABEPOID=:B2 AND STATUS = :B1
ORDER BY ABEPOITEMID

Order item table:

CREATE TABLE ABEPOITEMS
(
ABEPOITEMID NUMBER(8),
ABEPOID NUMBER(8),
STATUS CHAR(2 BYTE),
STATUSUPDDT DATE,
ITEMTITLE VARCHAR2(50 BYTE),
ITEMAUTHOR VARCHAR2(50 BYTE),
ITEMPRICE NUMBER(8,2),
);

Index on ORDER_ID column

CREATE INDEX ABEPOITEMS_ABEPOID ON ABEPOITEMS
(ABEPOID)
NOPARALLEL
ONLINE;

Index on STATUS and STATUS_UPD_DATE column

CREATE INDEX ABEPOITEMS_STATUS_UPDDT ON ABEPOITEMS
(STATUS, STATUSUPDDT)
NOPARALLEL
ONLINE;

SELECT * FROM ABEPOITEMS
WHERE ABEPOID=:B2 AND STATUS = :B1
ORDER BY ABEPOITEMID

Hope my question is small and self-contained.

Thanks,
Charlie

How to ask a question, I followed the rule, as above question.

Charlie Zhu, May 07, 2007 - 12:29 pm UTC

Hi Tom,

I thought I followed your suggestion on how to ask a question. :)
If so, please have a quick look at above question.

Otherwise, I have to buy Jonathan Lewis new book about the Cost based Oracle optimizer.

Thanks,
Charlie
Tom Kyte
May 08, 2007 - 11:11 am UTC

actually, the page you used to post this said "please don't use this to ask a new question"....

I recommend that book , it is a good book

FAIR WARNING

Charlie Zhu, May 08, 2007 - 1:16 pm UTC

Now I got it.

There're many WARNINGs on top and an Instruction in right side bar.

Instructions
This forum is not intended for additional questions. If you would like to ask additional questions, please go to Ask Tom


Looks like there is no chance to post new question, I'll try to find a question relevant.

And thanks for your answer about the place where the transparent encryption key stored in the Oracle Web Conference.

dead locks

Ahmed, August 30, 2007 - 5:42 am UTC

I have tried the below scripts to simulate the dead lock condition. The session is blocked and it's not throwing any error message. I am using oracle version 8.1.7.
Please help me to understand the scenario.

set echo on

drop table t1 cascade constraints;
drop table t2 cascade constraints;


create table t1 ( x int primary key );
create table t2 ( y references t1 );


insert into t1 values ( 1 );
insert into t1 values ( 2 );
commit;

set echo off
PROMPT goto another window, execute:
PROMPT insert into t2 values ( 1 );
PROMPT then come back here and hit enter
set echo on
pause

set echo off
PROMPT the following update will BLOCK as it
PROMPT tries to lock the child (cannot because
PROMPT of the other session).
update t1 set x = 3 where x = 2;




Tom Kyte
September 04, 2007 - 4:53 pm UTC

well, for a dead lock to occur, you need to have both sessions have something locked - my example shows that the foreign key (in 8i and before) takes a full table lock on the child table for the duration of the transaction (in 9i, it is a bit different, just the duration of the DML statement).


after the setup in the above, you could do this:


session 1: insert into t1 values ( 42 );
session 2: insert into t2 values ( 1 );
session 1: update t1 set x = 3 where x = 2; -- this will block on session 2
session 2: insert into t1 values ( 42); -- this will block on session 1


and now you will have a deadlock.

deadlock

Ahmed, September 03, 2007 - 6:59 am UTC

Could you please help me to simulate the deadlock condition in oracle version 8.1.7? I have tried the above scripts and it's throwing any error message.
Tom Kyte
September 05, 2007 - 1:32 pm UTC

there was never a DEADLOCK demonstrated, only a BLOCK.

but, see above, I gave you a way to see a deadlock if you really want to.

How ?

shgoh, December 06, 2007 - 4:25 am UTC

Hi Tom,

What is the best way to check if the system needs to create a key for unindex FK without knowing the characteristics of the usage as stated in ur first reply in this thread ? Application vendor is gone and we found a lots of unindex FK in the system. What is the best way to cater this issue ? Cheers

Rgds
SH
Tom Kyte
December 10, 2007 - 10:01 am UTC


If you are encountering blocking and deadlocks, they (especially the deadlocks) are indicative of this problem, so that would be a "clue"

as well, you have access to v$sql, you can manually look to see if there are any DELETES issued against the parent table OR updates to the parent tables primary key - so you can understand "the characteristics of the usage" in fact...

delete parent deadly slow

A reader, February 04, 2008 - 6:33 pm UTC

Hi

I have a delete statement for a partitioned table with 5 childs tables. One of child table has like 80 million of rows.

The one I am deleting is partitioned by month. Whenever I run the delete statement such as

delete parent_table partition (...) where ....

it takes ageeeeeeeeeeeeeeeeeees, like 3, 4 hours to delete 600000 of rows. I was wondering if I partition the big child table by month as well would Oracle able to used partition pruning? Is it clever enough :-?

Thanks

Tom Kyte
February 04, 2008 - 7:13 pm UTC

do you have an index on the foreign key.

partition pruning (at the table level) would only be relevant if you DID NOT. partition pruning of the table would avoid a full table scan of the entire child table.

so, presuming there is an index, it would not be relevant (to partition child) and if child foreign key is not indexed, you probably want it to be (to avoid the full table lock that is elsewise taken on the child when you delete from the parent)

delete parent deadly slow

LordPepeII, February 06, 2008 - 9:34 am UTC

Hi Tom

Unfortunately the FK is indexed and it is still very very slow, many buffer gets, hundred of millions when deleting the parent table.

I think finally we have to disable the FK to carry out this monthly task but that would need to stop the system sigh

Leading edge index for an FK

Shreyas, April 24, 2008 - 5:58 pm UTC

We faced a deadlock issue due to FKs, and this post (esp example for 9i) was very useful in understanding and simulating the issue.

However, the hypothesis that the leading edge of the index is sufficient to prevent a deadlock does not seem to be true in our case (as there indeed was an index on the actual table).

To simulate, I modified the example you provided above and added one more column on parent table "p".

session 1
---------
drop table c;
drop table p;
drop table some_other_shared_resource;
set echo on

create table p ( x int, y int );
alter table p add primary key (x, y);
create table c ( a int primary key, x int, y int );
alter table c add foreign key (x, y) references p;
create index i on c(x); /* Creating an index here on the leading edge of the FK*/
create table some_other_shared_resource (  x int );

insert into some_other_shared_resource values ( 1 );
insert into p values ( 1, 1 );
insert into p values ( 2, 2 );
insert into c values ( 1, 1, 1 );
insert into c values ( 2, 1, 1 );
insert into c values ( 3, 2, 2 );
insert into c values ( 4, 2, 2 );
commit;

session 2
---------
update c set a = a where a = 3;

session 1
---------
update some_other_shared_resource set x = 1;

session 2
----------
update some_other_shared_resource set x = 1;

session 1
---------
update p set x = x where x=1;

---------------------------------

deadlock on session 2:
SQL> update c set a = a where a = 3;

1 row updated.

SQL> update some_other_shared_resource set x = 1;
update some_other_shared_resource set x = 1
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource



Even though there is an index, I still encounter a deadlock situation. I used Oracle 9.2.0.8.0

The deadlock problem however is resolved if the index is on both the columns
(x, y)
.

Any ideas?

-Shreyas.
Tom Kyte
April 28, 2008 - 12:32 pm UTC

...alter table c add foreign key (x, y) references p;
create index i on c(x); /* Creating an index here on the leading edge of the
FK*/
...

you misunderstood, the foreign key must be on the leading edge of an index.

Not create an index on the leading edge of the foreign key - but rather that the foreign key is entirely on the leading edge of some index.


quote from above:

Any index that contains the foreign key on the leading edge will do


any INDEX that contains the foreign key on the leading edge.


your index does not include the foreign key.

Index to resolve deadlock issue

Shreyas, May 01, 2008 - 5:10 pm UTC

Thanks Tom. I indeed misread.

However, can you please also explain - how is the index on the exact columns (and no less) of the FK helping prevent the deadlock?

In my case - where the index was only on column "x", the cardinality on column "x" was high while "y" stored only 2 values. Hence any query on the table (fired internally by Oracle to check the references?) by using both columns should still use the index and not do an FTS.

Thanks.

Tom Kyte
May 01, 2008 - 9:44 pm UTC

... However, can you please also explain - how is the index on the exact columns
(and no less) of the FK helping prevent the deadlock?
...

because we can do internal stuff on the index structure locking wise that you cannot (that I cannot) to make it so we don't need to lock the entire table.

basically if you delete from parent or update parent primary key - we need to ensure that no one inserts a child record that would point to that parent we are deleting/updating

one way - lock the child table
another way - lock up the index so that no one can insert that key



It has nothing to do with full scan versus index range scan, it is all about LOCKING.

FK Indexes

Scott H, June 05, 2008 - 9:39 am UTC

Tom,
Poking around on here has helped me see why we have been getting some deadlock issues. We have several hundred unindexed foreign keys. My concern is regarding doing a mass index creation. Should we be concerned with performance related issues if we run the mass creation?

Also, we are planning on making all foreign key constraints as the following, assuming this is reccomended practice.
create table with foreign key. create index on foreign key columns as soon as table is created.

Thanks Tom, your documentation here has been very useful to us.

-Scott
Tom Kyte
June 05, 2008 - 10:27 am UTC

ask yourself first:

do we delete from parent table

do we update parent primary key (even if to set it to the same value as many "frameworks" are wont to do)

do we merge into parent table


if you do ANY of the above, indexing the foreign key that points to that parent will likely improve performance

o the delete from parent will no longer LOCK & FULL SCAN child table for each row deleted from parent

o the update of the parent row will no longer LOCK & FULL SCAN child table for each row updated

o the merge into parent will no long LOCK child table for duration of merge




You only need to index if any of the three things are true.

Unindexed Foreign Keys script generates ORA-01467: sort key too long

Chris, August 12, 2008 - 9:29 am UTC

The script that is linked off of http://asktom.oracle.com/tkyte/unindex/index.html (referenced above) produced an ORA-01467: sort key too long error for me.

I removed "max(decode(column_position,16,', '||substr(column_name,1,30),NULL))" in both places and the query worked.
Tom Kyte
August 13, 2008 - 4:32 am UTC

you have an older release and a small block size.... you only need as many decodes as you would have columns in a foreign key so hopefully 8-10 would be more than enough...

indexing foreign key (looking at lock information)

A reader, August 31, 2008 - 1:41 pm UTC

hi tom,

i am looking on how a index on foreign key will help in performance.

i have table named
test1 ( a number, b number);
alter table test1 add primary key(a,b);
insert into test1(1,2);
commit;

i have a table named
test2( a number, b number);
alter table test2 add foreign key (a,b) references test1(a,b);

I have 0 index on the foreign key.
----------------------------------------------------------
Scenario 1
Now I open 3 different session using sqlplus.

S1) insert into test2(1,2);
S2) delete from test1;

S3 login as sysdba) start ?/rdbms/admin/utllockt

I saw S2 waiting for S1
S1 is holding a RX lock
and S2 is requesting a SHARED table lock
and the lock type is a DML LOCK*
Then i rollback everything;

-----------------------------------------------------------
Scenario 2

Now i do a
S1)create index test2idx on test2(a,b);
S1)insert into test2(1,2);

S2) delete from test1;

S3 sysadmin) start ?/rdbms/admin/utllockt

I SAW S2 waiting for S1
S1 is holding a exclusive lock as before
and S2 is requesting a SHARED lock

and the LOCK type is TRANSACTION*
----------------------------------------------------------

I know with an index, there is no need to lock the child table in shared mode. By just checking the index, it is enough to know whether are there child records in the child table.

But

Question1) What is the difference between DML lock and TRANSACTION lock ?

Question2) Since by just checking in the index is enough,
why in scenario 2, S2 is still requesting a shared table lock on the child table ?

Hope to hear your advice TOM.
Thanks in advanced.

Best Regards,
-Noob


Tom Kyte
September 01, 2008 - 12:56 pm UTC

all transactions will have a 'lock' - that identifies them, it gives something for someone to wait on (the TX lock).

When you modify a row in a table, you lock that row and will get a TM lock on that table. If someone else tries to get that row and lock it - you will block them, they will wait on your TX lock - which sticks around until you commit.


your scenarios are incomplete. the insert would fail in scenario 2 since as far as I can tell, your tables are empty.

indexing foreign key (looking at lock information)

A reader, September 01, 2008 - 2:36 pm UTC

hi tom,
thanks for the prompt reply.

In scenario 2.
I have the same setup in Scenario 1.

Test1 (a number, b number)
primary key = test1(a,b);
insert into test1(1,2);
commit;

Test2 (a number, b number)
foreign key = test2(a,b) ref test1(a,b);
create index fkidx on test2(a,b);

Session 1)
Insert into test2(1,2);

Session 2)
Delete from test1;

Session 3)
ranned utllockt script.
---------------------------------------------------
From session 3,
i can see session 2 is waiting for session 1 and session 2 is requesting a shared lock while session 1 is holding a exclusive lock and the LOCK TYPE is TRANSACTION.

q1) can i know why would the insert fail on test2 fail ?

q2) since you have explain what is a Transaction lock, can i know what is a DML lock and what is the difference between them ?

q3) since an index is used on test2, why is there still a need for Session 2 to request a shared lock on the child table ?

Thanks alot
Tom Kyte
September 02, 2008 - 11:36 am UTC

umm, it didn't fail? I don't know what you mean.


of course the DELETE FROM TEST1 will block - it is blocked because the other session that just created test2(1,2) *needs* that record.

You have TX locks to enqueue on (wait on, get in line behind)

You have TM locks to see what objects that session has locks on.

Session 2 is not blocked on test2, it is blocked on TEST1 - it should be rather obvious that we cannot at this moment delete test1(1,2), there is a pending transaction that relies on the existence of that row. You could delete OTHER rows in test1, just not that one!

(eg: put into test1 values (3,4) and in the other session delete from test1 where a = 3 - it'll work, it doesn't need to lock test2)


How to find missing FK

A reader, November 27, 2008 - 9:19 am UTC

Hi Tom,

In an existing application, there exist indexes for foreign key. However, for few tables there are indexes which correspond to Foreign keys, but these Foreign keys do not exist.

Is it possible to

1) based on existing indexes find the missing FK
2) find the missing FK without basing ourselves on an existing index

Thanks a lot
Tom Kyte
November 28, 2008 - 5:26 pm UTC

since you don't need an index for a foreign key - how can you say what you say?

An index does not imply a foreign key


If this is YOUR application, you know the foreign keys.

If this is a third party application, they know what foreign keys they need.


So, I have no idea what you are really trying to do here.


It is impossible to find a missing foreign key. That would be like finding the person who's name is Bob in a room of a million mute strangers you have never met before - only harder.

Side effect on using Forms on non indexes on foreign keys

Claus Rasmussen, December 01, 2008 - 5:49 am UTC

Hi Tom.

I know that you are not into Forms. But I think it is fair to point out, then when you build your application in Forms, you might encounter a problem with full table locking when you don't have indexes on foreign keys.

When you make a block based on an table in forms, per default it will update all columns in that block, in the table. Also the primary key.

That has the effect that when you update a column in that block, (other than the primary key) forms will update the primary key and lock all rows in the detail tabel.

Best regards
Claus Rasmussen
Tom Kyte
December 01, 2008 - 8:09 am UTC

drop forms from your sentence:


...
But I think it is fair to point out, then
when you build your application in ANYTHING, you might encounter a problem with
full table locking when you don't have indexes on foreign keys.
....

that would be a true statement still - don't know why you single out forms, I tried to make it clear that it is a universal issue.

and many application tools - beyond forms - many of them blindly update the primary key...

so yes - absolutely, you are correct - but it applies to many many things - not just forms.

Side effect on using Forms on non indexes on foreign keys

Claus Rasmussen, December 01, 2008 - 8:32 am UTC

Hi Tom

I just mentioned forms, because that is where I have seen this issue many times. And forms per default update the primary key.

But you are right. It is an universal issue.

BR
Claus


A reader, January 14, 2009 - 11:34 am UTC

And what is not Ok with following way to find unindexed foreign keys:

select distinct constraint_name from (
select constraint_name,table_name,column_name,position from user_cons_columns u where
  (table_name,column_name,position) not in ( 
    select table_name, column_name, column_position  from user_ind_columns
  )   
)  

Tom Kyte
January 16, 2009 - 4:53 pm UTC

it doesn't work?

ops$tkyte%ORA10GR2> create table p ( x int, y int, constraint p_pk primary key(x,y) );

Table created.

ops$tkyte%ORA10GR2> create table c ( x int, y int, z int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select distinct constraint_name from (
  2  select constraint_name,table_name,column_name,position from user_cons_columns u
  3  where
  4    (table_name,column_name,position) not in (
  5        select table_name, column_name, column_position  from user_ind_columns
  6              )
  7                  )
  8  /

no rows selected


????

index on foreign key columns

Vin, March 23, 2009 - 12:39 pm UTC

Has the requirement to have index on foreign key columns changed or been modified in some way in 11g?
Thanks
Tom Kyte
March 26, 2009 - 1:15 pm UTC

no, if you

a) update parent primary key column (even if you do not change it..)
b) delete from parent
c) merge into parent

you will get a full table lock on the child table for the duration of the update/delete/merge statement.

FK indexing

Sal, March 31, 2009 - 2:01 pm UTC

Tom,

If there is a VPD policy on a table which adds a predicate like 'labelcol = sys_context('a','b')' onto every query for that table, and if labelcol is not a part of the foreign key (due to a surrogate key being present), will there need to be a separate index on the FK all by itself simply because of the leading edge issue?

So for example, table opportunity has FK prospect_id. Now, it also has labelcol and since we have VPD on it, all indexes start with labelcol as leading edge. So there is an index (labelcol,prospect_id).

Will Oracle be able to utilize it and not lock the child table when a delete happens (since labelcol is low cardinality, maybe skip scan can be used?)

Please advise.

Thanks!
Tom Kyte
April 01, 2009 - 8:18 am UTC


the foreign key needs to be on the LEADING edge of an index. We use the index to prevent child records from being created.

so, index on (labelcol,prospect_id) - not useful for foreign key locking considerations.


ops$tkyte%ORA10GR2> create table p ( x int primary key );

Table created.

ops$tkyte%ORA10GR2> create table c ( x references p, y int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into p (x) values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> insert into p (x) values ( 2 );

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> create index c_idx on c(y,x);

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into c (x,y) values ( 1,0 );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          delete from p where x = 2;
  5          commit;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> rollback;

Rollback complete.

ops$tkyte%ORA10GR2> drop index c_idx;

Index dropped.

ops$tkyte%ORA10GR2> create index c_idx on c(x,y);

Index created.

ops$tkyte%ORA10GR2> insert into c (x,y) values ( 1,0 );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          delete from p where x = 2;
  5          commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.



snowy, April 02, 2009 - 5:20 am UTC

I am quoting your previous reply
<< START QUOTE >>
if you update a parent tables primary key OR you delete from parent
then
if you have 8i and before
a full table lock is taken and held on the child table
for the duration of the transaction
else if you have 9i and above
a full table lock is taken on the child table
for the duration of the statement that does the update/delete (and
for as long as it blocks)
end if;
end
<< END QUOTE >>

Hi Tom, has this changed or improved in any way in 10G.
If not can i assume "if you have 9i and above" as, "also applies to 10G"
Tom Kyte
April 02, 2009 - 10:05 am UTC

actually, my knowledge has increased on this topic, today it would start with:

if you
a) update parent table primary key (watch out, many 'frameworks' do this, they update every column)
b) delete from the parent table
c) MERGE into the parent table
then
.....


it works in the same fashion in 9ir1 through 11gr1 and I don't see it changing. We need to prevent a child record from appearing during this process - and if we have an index - we can do that efficiently, if we do not have an index - we cannot - that simple.


Index on Foreign key

Mohamed, April 02, 2009 - 11:44 am UTC

Dear Tom,

I have a table t1
create table t1(id number primary key,
x number,
y number,
z number
);
then I have 3 other tables t2, t3 and t4.

table t2 has a FKt2 referencing t1(x) + an index on x
table t3 has a FKt3 referencing t1(y) + an index on y
table t4 has a FKt4 referencing t1(z) + an index on z

and finally I have a table t5 having a FKt5(x,y,z).

As long as x,y and z are indexed, do I need to index the FKt5?

Thanks a lot for your answer
Tom Kyte
April 02, 2009 - 12:45 pm UTC

if you have an index on t5 that starts with x,y,z (in any order, just mandatory that they START the index), then you don't need an index on just (x,y,z)

There must be ONE index that has all three of x,y,z in it and they must be on the leading edge.

t2,t3,t4 seem to be superfluous here, I don't know why you included them - they are not relevant to the problem discussion.

A reader, April 07, 2009 - 5:22 am UTC

Thanks Tom for your answer

"
t2,t3,t4 seem to be superfluous here, I don't know why you included them - they are not relevant to the problem discussion.
"
The goal of my question was to definitely know that despite the existence of 3 separates indexes one on x, one on y and one on z, I need to have an extra index on (x,y,z) in order to cover the FK(x,y,z) on table T5

Thanks




Tom Kyte
April 13, 2009 - 11:47 am UTC

right, but the existence - or lack thereof - of tables t2,t3,t4 have NOTHING to do with T5.

It doesn't matter of t2,t3,t4 exist or not, indexes on their columns would have NOTHING to do with T5 and locking issues on T5. Only an index on T5 would/could affect T5.

so, again, t2, t3, t4 - just confuse the issue, they do not 'matter'.

When I truncate child table, delete on Parent tables works faster

Dan, June 16, 2009 - 10:06 am UTC

Hi Tom,

I have created 2 tables.

CREATE TABLE t1 (a NUMBER PRIMARY KEY);

CREATE TABLE t2 (b NUMBER REFERENCES t1(a))

Inserted 1000 records in table t1.

INSERT INTO t1
SELECT ROWNUM A FROM all_objects WHERE ROWNUM < 1001;

Inserted 100000 records in table t2.

INSERT INTO t2
SELECT b FROM t1, (SELECT ROWNUM b FROM all_objects WHERE ROWNUM <1001 );

First I have deleted all the records in t2.

Then I have deleted all the records in t1. IT TOOK 10 SECONDS.

Now I have created index on column b in the table t2.

Followed the same process again.

Inserted 1000 records in table t1.

Inserted 100000 records in table t2.

First I have deleted all the records in t2.

Then I have deleted all the records in t1. now it took less than a second.

Now I have dropped the index on Column b in the table t2.

Inserted 1000 records in table t1.

Inserted 100000 records in table t2.

Now instead of DELETE statement I have issued truncate statement.

TRUNCATE TABLE t2

Then I have deleted all the records in t1. This time delete statement executed in less than a second.

Now I don¿t have any index on column b. but it worked faster.

Any reason?

Regards,
Dan
Tom Kyte
June 16, 2009 - 2:31 pm UTC

when you put 1001*1001 records into a table, it grew to size X
when you deleted them, the table is still size X

if you full scan that table after the delete, it'll still have to full scan X blocks.

if you have an unindexed foreign key, every parent record you delete will force a full scan of the child table (to verify it does not exist in child table). So, you full scanned the child table's X blocks 1001 times.

when you truncate, you take the child table from size X down to "nothing", it takes less time to full scan nothing 1001 times than it does X blocks 1001 times



same thing would happen if you 'shrunk' the table.
adding an index would also make it "take the same"

Thanks Tom

Dan, June 17, 2009 - 3:08 am UTC

Got it & Thanks.

when you have an index vs not having an index

karthi, June 18, 2009 - 7:15 am UTC

Hi Tom,

you have mention follow the links to get the "when you have an index vs not having an index" . But i do not find ant link. could you please provide

[Tom comments]:
Followup November 7, 2002 - 7am US/Eastern:

follow the links -- on the unindex link is a link to that pesky server concepts manual where so
much of the internals of Oracle that are relevant to you and me are explained in nice detail. In
fact, the entire "how this works when you have an index vs not having an index" is right there!



TM lock other then unindex foreign key

joshua, June 26, 2009 - 12:50 am UTC

Hi Tom, what other possible reason are there for a statement to aquire a TM lock other than unindex foreign key and or DDL statements

thanks



Tom Kyte
June 26, 2009 - 10:56 am UTC

<quote src=Expert Oracle Database Architecture>


TM (DML Enqueue) Locks

TM locks are used to ensure that the structure of a table is not altered while you are modifying its contents. For example, if you have updated a table, you will acquire a TM lock on that table. This will prevent another user from executing DROP or ALTER commands on that table. If another user attempts to perform DDL on the table while you have a TM lock on it, he¿ll receive the following error message:

drop table dept
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


This is a confusing message at first, since there is no method to specify NOWAIT or WAIT on a DROP TABLE at all. It is just the generic message you get when you attempt to perform an operation that would be blocked, but the operation does not permit blocking. As you¿ve seen before, it¿s the same message you get if you issue a SELECT FOR UPDATE NOWAIT against a locked row.
The following shows how these locks would appear in the V$LOCK table:

ops$tkyte@ORA10G> create table t1 ( x int );
Table created.
 
ops$tkyte@ORA10G> create table t2 ( x int );
Table created.
 
ops$tkyte@ORA10G> insert into t1 values ( 1 );
1 row created.
 
ops$tkyte@ORA10G> insert into t2 values ( 1 );
1 row created.
 
ops$tkyte@ORA10G> select (select username
  2                         from v$session
  3                        where sid = v$lock.sid) username,
  4         sid,
  5         id1,
  6         id2,
  7         lmode,
  8         request, block, v$lock.type
  9    from v$lock
 10   where sid = (select sid
 11                  from v$mystat
 12                 where rownum=1)
 13  /
 
USERNAME   SID     ID1    ID2 LMODE REQUEST BLOCK TYPE
--------- ---- ------- ------ ----- ------- ----- ----
OPS$TKYTE  161  262151  16584     6       0     0 TX
OPS$TKYTE  161   62074      0     3       0     0 TM
OPS$TKYTE  161   62073      0     3       0     0 TM
 
ops$tkyte@ORA10G> select object_name, object_id
  2    from user_objects
  3   where object_name in ('T1','T2')
  4  /
 
OBJECT_NAME   OBJECT_ID
------------ ----------
T1                62073
T2                62074


Whereas we get only one TX lock per transaction, we can get as many TM locks as the objects we modify. Here, the interesting thing is that the ID1 column for the TM lock is the object ID of the DML-locked object, so it easy to find the object on which the lock is being held.

An interesting aside to the TM lock: the total number of TM locks allowed in the system is configurable by you (for details, see the DML_LOCKS parameter definition in the Oracle Database Reference manual). It may in fact be set to zero. This does not mean that your database becomes a read-only database (no locks), but rather that DDL is not permitted. This is useful in very specialized applications, such as RAC implementations, to reduce the amount of intra-instance coordination that would otherwise take place. You can also remove the ability to gain TM locks on an object-by-object basis using the ALTER TABLE TABLENAME DISABLE TABLE LOCK command. This is a quick way to make it 'harder' to accidentally drop a table, as you will have to re-enable the table lock before dropping the table. It can also be used to detect a full table lock as a result of the unindexed foreign key we discussed previously.

</quote>

index in an usable state

Mohamed, June 26, 2009 - 11:08 am UTC

If you want to see that a delete from a parent table is using the FK index that must exist in the
child table, then do the following

1) delete the child
2) put the FK index in an unusable state
3) delete from the parent table

in step 3 you will be faced to the "index_on_fk is in an unusable state" error.

I am happy to have found this. It will help me explaining why it is very important to index Foreign keys


Tom Kyte
June 26, 2009 - 11:31 am UTC

no, the only thing you need to know about "why it is important" is if you

a) delete from parent - you will get full table lock on child table and full scan child table for every row you delete from parent. That is not good, that is a reason to index the foreign key - to avoid the full table lock and avoid the full table scans

b) update parent primary key - same reason as (a)

c) merge into parent - same reason as (b) it acts like you are updating the parent primary key


and in many cases, when you join parent to child, you want the index for performance, eg:

select * from emp, dept where emp.deptno = dept.deptno and dept.deptno = 10;

it would make sense to index emp(deptno) for data retrieval.


Those are the reasons. The reason you placed would be a reason to NOT index the foreign key.

Can we suppress the index on FK

A reader, July 06, 2009 - 8:30 am UTC

Dear Tom,

We have the following situation

CREATE TABLE test_part_tab
(
ID NUMBER(10) NOT NULL,
DAT_PART DATE NOT NULL,
X NUMBER(10) NOT NULL,
Y NUMBER(10) NOT NULL,
Z NUMBER(10) NOT NULL,
XYZ VARCHAR2(2 BYTE) NOT NULL,
A VARCHAR2(2 BYTE),
B VARCHAR2(2 BYTE),
C VARCHAR2(2 BYTE),
D VARCHAR2(2 BYTE)
)
PARTITION BY RANGE (DAT_PART)
PARTITION P_20090101 VALUES LESS THAN (TO_DATE(' 2007-01-01', 'YYYY-MM-DD'),
PARTITION P_20090201 VALUES LESS THAN (TO_DATE(' 2007-01-01', 'YYYY-MM-DD'),
PARTITION P_20090301 VALUES LESS THAN (TO_DATE(' 2007-01-01', 'YYYY-MM-DD'),
PARTITION P_20090401 VALUES LESS THAN (TO_DATE(' 2007-01-01', 'YYYY-MM-DD'),
PARTITION P_20090501 VALUES LESS THAN (TO_DATE(' 2007-01-01', 'YYYY-MM-DD'),
PARTITION P_20090601 VALUES LESS THAN (TO_DATE(' 2007-01-01', 'YYYY-MM-DD'),
PARTITION P_20090701 VALUES LESS THAN (TO_DATE(' 2007-01-01', 'YYYY-MM-DD'),
)

On this table we have 4 indexes

1. A Locally partitioned index on (x, id, dat_part) enforcing the primary key
2. A locally partitioned index on (id, dat_part) enforcing a FK to a parent table
3. A locally partitioned index on (dat_part)
4. A normal index on (y)

My question is:

(1) I would like to re-organise the pk index as follows (id,dat_part,x) and drop the FK index (id, dat_part). Do you agree with this?

(2) Is it interesting to have both (id,dat_part,x) index and (dat_part) index? Or can we suppress the second one

Thanks in advance

Tom Kyte
July 06, 2009 - 8:34 pm UTC

(1) how could anyone possibly answer that. really.

do you delete from the parent table?
do you merge into the parent table?
do you update the parent table primary key?
do you query from the parent INTO the child - eg

select * from dept, emp where dept.deptno = emp.deptno and dept.deptno = 10;

I would probably want the foreign key deptno in EMP indexed for that query.

select * from customer, zip_code where customer.zip = zip_code.zip and customer.id = 1234;

I probably do not need the zip code foreign key column indexed in this query because I never go from parent to child, but rather from child to parent.



as for the ordering of the primary key column - how do you expect ANYONE to be able to see "good idea" with the amount (or lack entirely thereof) of information present here? We have no clue HOW you use this data. none


2) ... Is it interesting to have both (id,dat_part,x) index and (dat_part) index?
Or can we suppress the second one
....


if I frequently query

"where id = ? and dat_part > ?"
and
"where dat_part > ?" or "where dat_part = ?"

then both indexes make sense. again, it comes back to the QUESTIONS you ask of the data, the predicates. No one can say if it would be safe or wise to remove either of those indexes - without knowing how you use the data.

Regarding FK index differences in 11g

Steve, July 13, 2009 - 5:11 am UTC

Hi Tom,

You said nothing changed in foreign key index requirements
in 11g; however, in 11g our application gets table locks
(and subsequent hanging) thanks to parent table deletes and
an unindexed parent key column, but the problem doesn't
occur in 10g or 9i.

Is there something about 11g that would be "less forgiving"
to poor FK design than earlier versions?

Thanks,

Steve

Tom Kyte
July 14, 2009 - 5:48 pm UTC

yes it does, it is exactly the same in 10g and 9i as 11g.

9i was the "more forgiving" version and the code hasn't changed since.


If you

a) update parent table primary key (even if it doesn't change)
b) delete from parent
c) merge into parent

then you will get a full table lock on child table for the duration of the STATEMENT (in 8i and before it was for the duration of the TRANSACTION)

but 9i, 10g, 11g all behave the same. If you believe they do not, please provide steps to run a simple (keyword - SIMPLE) example in 10g that doesn't lock but locks in 11g.

Regarding FK index differences in 11g

Steve, July 13, 2009 - 5:24 am UTC

sorry... I meant deletes on child tables.
Tom Kyte
July 14, 2009 - 5:48 pm UTC

give example

404 Not Found

Priscila, October 27, 2009 - 6:29 am UTC

Hello Tom,

The link that you mentioned at the beginning ( http://asktom.oracle.com/~tkyte/unindex/index.html ) is not working (404 Not Found). I tried with Mozilla and MS IE.

Any other places where i can find this?

Thanks
Tom Kyte
October 27, 2009 - 11:48 am UTC

see homepage, I had to relocate those articles, there is a link there now

Duplicate Indexes covering the FK

A reader, November 25, 2009 - 8:15 am UTC

Dear Tom,

In one application I found that all tables which possess a Foreing key (fk_col1, fk_col2) have a dedicated
index on (fk_col1, fk_col2). That's good as far as they are deleting and updating from the parent table. But all those tables possess extra indexes like (fk_col1, fk_col2, col3) so that the FK is covereb by at least two indexes.

We have more than 10 tables in this situation.

What will be the benefit If I suppress all these FK indexes?

Thanks in advance for your answer.
Tom Kyte
November 27, 2009 - 3:46 pm UTC

... What will be the benefit If I suppress all these FK indexes? ...

one of three things will happen:

a) it will be good
b) it will be bad
c) it won't make a difference either way.


It could be bad IF col3 is huge and the index on (fk1,fk2,col3) is MUCH MUCH larger than the index on just (fk1,fk2) *and* the index on fk1,fk2 is used in a fast full scan frequently. (so, it is likely to not be bad probably, the cases where it would be bad would be infrequent, edge cases)


Not having the redundant index on (fk1,fk2) - the index on fk1,fk2,col3 is sufficient to prevent the locking issues - then

It would save space.

It would decrease the work done upon insert and delete - and on updates to the foreign keys.

so probably (a) or (c) would be your outcome.

A reader, November 28, 2009 - 12:31 pm UTC

Thanks very much for your answer. I highly appreciate.

Just one clarification. What do you exactly mean by "col3 is huge"?

Best Regards
Tom Kyte
November 28, 2009 - 4:08 pm UTC

if col3 is wide, fat, consumes lots of storage, is big...

eg: an index on fk1,fk2,col3 is much larger than an index on fk1,fk2 alone - then col3 would be "huge"

index on foreign key

alan, February 10, 2010 - 7:49 pm UTC

Hi ,Tom
I have a question:
create table t(x int primary key);
create table t1(x references t);
insert into t values(1);
insert into t values(2);
insert into t1 values(1);
insert into t1 values(2);

I want to make sure the way oracle access t1 when I issue a delete on t by ALTER SESSION SET SQL_TRACE=TRUE;
alter session set sql_trace=true;
delete from t where x=1;
I can find a sql like:
select /*+ all_rows */ count(1) from t1 whree x=:1
and followed by explain.
so I know oracle access t1 with FTS.

BUT:
create ind_t1 on t1(x);

and repeat the above test.
I want find something that tell me oracle access t1 using index ind_t1.
to my supriseed,I can not find anything abort t1 in the trace file generated by SQL_trace.
I search the trace file T1 or ind_t1 ,got 0 result .
Could you tell me what happened here?
How can I find this things in the trace file?
Thank you!
Alan
Tom Kyte
February 16, 2010 - 8:12 am UTC

trick question, it doesn't need to access the table when the index is there - it just needs the index - it is "magic", we can take shortcuts you cannot (hence: the main reason to use server enforced integrity and NOT application enforced integrity).

You cannot see that which does not exist.

A reader, May 20, 2010 - 9:47 pm UTC

Hi Tom,

When I update/delete/insert any rows in the Child table, does oracle create a lock on Parent table for the corresponding rows?

What I understand, Lock will be created only when I update any row in Parent table. My understanding says, it's not Vice Versa. Can you please correct me if I'm wrong?

Thanks!
Tom Kyte
May 24, 2010 - 12:19 pm UTC

ops$tkyte%ORA11GR2> create table p ( x int primary key, y int );

Table created.

ops$tkyte%ORA11GR2> insert into p values ( 1, null );

1 row created.

ops$tkyte%ORA11GR2> create table c ( x references p );

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into c values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          update p set y = 0 where x = 1;
  5          commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.


it did not prevent the update from taking place. The lock would be from the PARENT table to the CHILD table if you update parent primary key, delete from parent, for example:

ops$tkyte%ORA11GR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          update p set x = 1 where x = 1;
  5          commit;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4


ops$tkyte%ORA11GR2> rollback;

Rollback complete.

ops$tkyte%ORA11GR2> create index c_idx on c(x);

Index created.

ops$tkyte%ORA11GR2> insert into c values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          update p set x = 1 where x = 1;
  5          commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

I'm not sure where to find this documentation

robin, June 16, 2010 - 6:09 am UTC

as this link leads to "page not found"

http://asktom.oracle.com/~tkyte/unindex/index.html

I'm having blocking issues when several users are updating different records through the same view. This only happens between users who have logged in through Oracle Label Security. The view contains several Parent/Child tables.

By the way, I am a developer, not a Data Base Administrator (DBA), so I don't have access to many of the trace files, etc. that the DBA has. Our DBA is looking at Oracle Enterprise Manager Console and telling me that the "row contention" appears to occur in a wrapped stored program unit within the LBAC_SYS schema, but then tells me that can't be happening as "it is impossible."

The DBA is telling me it must be happening because of a poorly built table/view, or unindexed foreign keys. This may be true but no blocking occurs when updating records from the schema owner or a database owner who has been given update object privilege -- only Oracle Label security users. Also the same type of views are used to update underlying tables in different schemas using Oracle Label Security with no blocking.

Through my insistence, the DBA did find that he had not applied the policy correctly affected schema, but even after correcting the OLS policy/privileges we have blocking. So I'm trying to prove to the DBA what is not happening so that we can eventually pinpoint what is. Is there anyway you can point me to the right direction that I would find the link referenced? Thanks, Robin


Query Error

Anees, September 08, 2010 - 4:23 am UTC

Tom Please chek this below query and let me correct..

select /*+ ordered */ w1.sid waiting_session,
a.table_name, a.columns, b.columns
from
( select substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) constraint_name,
max(decode(position, 1, substr(column_name,1,30),NULL)) ||
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
and w.kgllktype = h.kgllktype
max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
from user_ind_columns
group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
and b.columns (+) like a.columns || '%'
/

Tom Kyte
September 09, 2010 - 8:31 pm UTC

since I don't know what question you are trying to answer, I cannot "validate" your query - nor am I in the business of code reviewing everyones query.

@Anees

Michel Cadot, September 10, 2010 - 2:29 am UTC

Just copy and paste your code in SQL*Plus and you will see it is obviously wrong:
SQL> select /*+ ordered */ w1.sid  waiting_session,
  2         a.table_name, a.columns, b.columns
  3   from
  4   ( select substr(a.table_name,1,30) table_name,
  5               substr(a.constraint_name,1,30) constraint_name,
  6           max(decode(position, 1,     substr(column_name,1,30),NULL)) ||
  7           max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
  8           max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
  9           max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
 10           max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
 11           max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
 12           max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
 13           max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
 14           max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
 15           max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
 16      and  w.kgllktype = h.kgllktype 
 17           max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
 18           max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
 19           max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
 20           max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
 21           max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
 22       from user_cons_columns a, user_constraints b
 23      where a.constraint_name = b.constraint_name
 24        and b.constraint_type = 'R'
 25      group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
 26   ( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
 27           max(decode(column_position, 1,     substr(column_name,1,30),NULL)) ||
 28           max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
 29           max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
 30           max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
 31           max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
 32           max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
 33           max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
 34           max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
 35           max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
 36           max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
 37           max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
 38           max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
 39           max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
 40       from user_ind_columns
 41      group by substr(table_name,1,30), substr(index_name,1,30) ) b
 42   where a.table_name = b.table_name (+)
 43     and b.columns (+) like a.columns || '%'
 44  /
    and  w.kgllktype = h.kgllktype
    *
ERROR at line 16:
ORA-00936: missing expression

Regards
Michel

Incorrect Tom Kyte Blog entry?

Ravi, November 08, 2010 - 6:57 am UTC

Tom,
There appears to be inconsistency between your post and the script attached to the post. Your post tasks of a query output column named "STAT", which is missing in the query directly assesed with the following link.

http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html

And in that where a link says "This script might help"

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4530093713805#26568859366976

Please can you suppy the original query which has the STAT column.

Thanks

Tom Kyte
November 08, 2010 - 8:26 am UTC

stat is status.

the query I pointed to is a newer one - it just shows "bad" ones - removing the need for a status, they are all 'bad'

foreign key index in 10.2.0.3

prayerhuang, January 06, 2011 - 6:05 pm UTC

Tom,

I did some test in my 10.2.0.3 environment.

create table parentt (
    id  number(4),
    name    varchar2(10),
    constraint parr_pk primary key (id)
)
;
 
create table childd(
    id_p    number(4)
        constraint chi_fk_parr
        references parentt,
    id  number(4),
    name    varchar2(10),
    constraint chid_pk primary key (id_p, id)
)
;
 
insert into parentt values (1,'Smith');
insert into parentt values (2,'Jones');
 
insert into childd values(1,1,'Simon');
insert into childd values(1,2,'Sally');
 
insert into childd values(2,1,'Jack');
insert into childd values(2,2,'Jill');
insert into parentt values (3,'Jame');
 
commit;

then when I delete the parentt record with id =3

SQL> delete from parentt where id = 3;

1 row deleted.

I check the locks

only the following locks

DML Row-X (SX) PARENTT
DML Row-S (SS) CHILDD

I didn't index the foreign key, why there is no full table lock on the CHILDD table?

also so on 10g and 11g, when I can skip creating index on the foreign key columns?

Thanks

Tom Kyte
January 07, 2011 - 9:06 am UTC

No, it is just that the rules changed a tiny bit.

see the last bit of this article, the And Something “Unlearned” section:

http://www.oracle.com/technetwork/issue-archive/2010/10-nov/o60asktom-176254.html


or just page up and read the
"Lock on child Table May 22, 2002 - 4am Central time zone"
comment where we talked about this more than 8 years ago :)

Unindexed foreign key Share lock without delete/update on the parent

Franck Pachot, February 10, 2011 - 10:17 am UTC

Hi Tom,

I have encountered a variation on unindexed foreign key, with 2 levels of referential integrity:
- the first level has its foreign key indexed, so a delete on the parent (A) do not need a Share lock on the child (B).
- the second level, where (C) references (B) and has no index on the foreign key because we know that there are no delete on the parent (B)
- But the referential integrity between A and B is defined with 'on delete set null'.

Then, it seems that a delete on the top level (A) is needs a Share lock on (C) because of the 'on delete set null'.

Of course, the cascading constraint is not a very good idea. In the case I've seen, indexing the foreign key in C will cause problems as there are about 10 tables like C that have several columns referencing B.

But I cannot really understand why a delete on A needs to have a Share lock on table C.
The 'set null' constraint do not cause delete on B, nor it updates the key.

Is it just something that was left when oracle has optimized that referential integrity locks ? Probably because it should not be a common situation. Or am I missing something ?

Well, here is the test case (tested in 10g and 11g)

-- On session 1:

create table A(a number primary key);

create table B(b number primary key,a references A on delete set null);
create index B on B(a);

create table C(c number primary key,b references B);

insert into A(a)values(1);
insert into B(b,a)values(1,1);
insert into C(c,b)values(1,1);

-- On session 2:

delete from A where a=10;

... it hangs

-- On session 1:

select sid from v$mystat where rownum=1
SID
----------------------
194

select waiting_session,holding_session,lock_type,mode_held,mode_requested,lock_id1,object_name
from dba_waiters join user_objects on lock_id1=object_id;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 OBJECT_NA
--------------- --------------- ---------- ---------- -------------- ---------- ---------
162 194 DML Row-X (SX) Share 75592 C

-- The delete is requesting a TM Share lock on the C table.


Regards,
Franck.
Tom Kyte
February 14, 2011 - 7:00 am UTC

The delete against A would have the effect of setting the B.A column to null - which in effect would be the same as deleting the row from B as far as C was concerned.

So, in effect - the delete on A removes a key in B which C is potentially dependent on, so for the same reason you would want to index the foreign key in C if you delete from B - you would need to do it in this case.

It is not a "leftover", it is identical in nature to the "if you delete from the parent, you should probably index the foreign key in the children"

index

sam, February 14, 2011 - 10:22 pm UTC

Tom:

you say above the foreign key column has to be the leading column in an index.

If i have a table with 6 FKs, do i need to create 6 indexes, one index per foreign key assuming i don'ht ave other indexes on table.

I assume if other indexes havy any of the 6 columns as a leading column then i can skip creating an index for that . right?
Tom Kyte
February 15, 2011 - 8:23 am UTC

it depends, you don't give an example.

if the foreign keys are subsets of each other - you might need less then six


fk1 = a
fk2 = a,b
fk3 = a,b,c
fk4 = a,b,c,d
fk5 = a,b,c,d,e
fk6 = a,b,c,d,e,f

given the above, you would need one index at most.

fk1 = a
fk2 = b
fk3 = c
fk4 = d
fk5 = e
fk6 = f

given the above, you would need six indexes at least.

Avoinding redundant FK indexes

Mohamed Houri, February 15, 2011 - 4:17 am UTC

Before Indexing a FK, I generally use such a kind of script to ensure that I am not going to create a redundant index to cover the unindexed FK locking threat.
/*-- ----------------------------------------------------------------------------------------
-- script : verify if the fk columns are already indexed or not
--          the purpose of this script is to avoid creating redundant indexes
--          it has been defined to check FKs with up to 3 columns but can easily be updated to
--          handle Fks with more than 3 columns
--          when checking existing indexes for FK with one column then you can simply supply 'none'
--          without cotes ('') for the remaining columns when asked by the script to do so
-- 
   author   : Mohamed Houri
   date     : december 2010
   version  : v1.0
-- Example 1 sql.world> start index_fk
--               Enter value for m_table: t1 
--               Enter value for m_column: id
--                              Enter value for m_column2: none
-- 
   table_name      index_name      column_nam  column_pos
   --------------- --------------- ---------- ----------
             t1              ind_usr_ni      id             1
    
-- the output says that for the table t1 there exists already an index named ind_usr_ni starting
-- with the column id that you want to use as a FK. So you don't need to create an extra index
-- to cover the locking threat of unindexed foreign keys
--
-- Example 2 sql.world> start index_fk
--               Enter value for m_table: t2
--         Enter value for m_column : col1
--         Enter value for m_column2: col2
--         Enter value for m_column3: none
-- 
  table_name           index_name            column_nam  column_pos
      ------------- --- ------------------------- ---------- ------------
      t2                      t2_usr_ni            col1           1

-- the output says that for the table t2 it exists an index named t2_usr_ni
-- starting with the column col1 but the script did not found an index
-- starting with(col1, col2) or (col2, col1).  
-- So in this situation you need to create an extra index covering the FK and starting with (col1, col2)-- or (col2, col1) to cover the locking threat of unindexed foreign keys
--
-- Example 3 sql.world> start index_fk
--               Enter value for m_table: t3
--               Enter value for m_column:  col1
--                              Enter value for m_column2: col2
--        Enter value for m_column3: none
 table_name                index_name                     column_nam column_pos
 ------------------------- ------------------------------ ---------- ----------
 t3             t3_ni_1               col0                1
 t3             t3_ni_1                        col1                2
 t3             t3_ni_1                     col2                3
 

-- in this situation there exist an index name t3_ni_1 having (col1, col2)
-- as indexed columns but they are at position 2 and 3 respectively. However in order to
-- cover a FK the index should have (col1, col2) at position 1 or 2 or 2 and 1 respectively
-- Hence it is necessary in this case to create an extra index to cover the FK 
-- --------------------------------------------------------------------*/
define m_table_name    = &m_table  
define m_column_name   = &m_column
define m_column_name2  = &m_column2
define m_column_name3  = &m_column3

spool index_col_fk.log
set verify off
set linesize 100
select substr(uc1.table_name,1,25)  table_name
      ,substr(uc1.index_name,1,30)  index_name
      ,substr(uc1.column_name,1,10) column_name
      ,uc1.column_position          column_pos                 
from user_ind_columns uc1   
where uc1.table_name   = upper('&m_table_name')
and uc1.column_name    in (upper('&m_column_name') 
                          ,upper('&m_column_name2')
        ,upper('&m_column_name3')
        )
order by           
    uc1.index_name   
   ,uc1.column_position
   ,uc1.column_position
;
spool off

So yes, each of your 6 FKs should posses an index (even an existing one or the one you have to create) starting with the FK columns in any order. The index has only to start with the FK columns.

Of course, keep in mind that the unindexed FK locking threats appears only when you DELETE/UPDATE/MERGE from the parent table.

Best Regards

Mohamed Houri
Tom Kyte
February 15, 2011 - 8:37 am UTC

... So yes, each of your 6 FKs should posses an index ...


It depends.... See above. You'll need somewhere between 1 and 6.

indexes

sam, February 15, 2011 - 9:15 am UTC

Good explanation. Nice script from mohamed too.

Your link above for the script that find unindexed columns does not work. However, I recall this is in your expert one on one book.

Do indexes on FKs improve performance too?

It is very easy to add indexes. The problem is that is not easy to do regression testing and figure out any impacts on DML statements. I bet that most developers never even bother do regression testing after adding indexing. If a query performance improves, they think the goal has reached until some user in production mentions that system has slowed down when inserting or updating data.

Is there some formula or script that can usually tell you if a schema has too much indexes or too little. Can we assume 3-4 indexes on a table are logical.
Tom Kyte
February 15, 2011 - 9:39 am UTC

unindexed.sql link:

http://asktom.oracle.com/Misc/httpasktomoraclecomtkyteunindex.html



... Do indexes on FKs improve performance too?
...

think about it sam, you can answer that.


What if you have the standard emp/dept tables and you issue:

delete from dept where deptno = 10;

would the index on the foreign key in emp

a) help
b) hurt

and why - forget the locking issue for a moment, just consider the delete itself.

... Is there some formula or script that can usually tell you if a schema has too
much indexes or too little. Can we assume 3-4 indexes on a table are logical. ...

the right number of indexes is somewhere between zero and a lot. The only answer is "it depends"

I've seen tables with 16 or more indexes - and all was done correctly.

I've seen over indexed tables with 4 indexes.

It depends.

index

sam, February 15, 2011 - 3:29 pm UTC

Tom:

Whenever you add an index to speed up the SELECT, do you really go back and do regression testing on your application to see how other DML statements (C, U, D) are affected?

to answer you above, yes the delete should be faster because it will use the index to find the ROW id and delete the row in the child table instead of doing a full scan searching for child records.

But I also think if you have 10 departments and 300 employees you do not need any indexes. a full scan might be faster in this case. It depends on much data you have in EMP table.
Tom Kyte
February 15, 2011 - 4:57 pm UTC

Whenever you add an index to speed up the SELECT, do you really go back and do
regression testing on your application to see how other DML statements (C, U,
D) are affected?


how important is the performance of the various applications that hit that data?

a) very low - probably not
b) very high - sure, you should
c) neither very low nor very high - it depends.

It depends on much data
you have in EMP table.


Tah-dah - Sam, you are getting there - "it depends", perfect :)

indexes

sam, February 15, 2011 - 7:27 pm UTC

well, performance of I,U,D is also somehow important. We do not want user to insert a ROW and wait 10 seconds before he gets a confirmation.

I guess the "it depends" answer changes every day. When you design a system it is hard to predict the data that will be filled in those tables. Do you make your best guess and create your indexing scheme based on that or you keep monitoring the system and add indexes as needed in the future.

I wonder how Oracle ERP applications, SAP, Peoplesoft design their indexes since each customer may have different number of employees. They sell COTS package and walmart will have 200,000 employees while a mom and pop shop may have 50 employees only.


Unindexed foreign key Share lock without delete/update on the parent

A reader, February 24, 2011 - 9:31 am UTC

Hi,

>> The delete against A would have the effect of setting the B.A column to null - which in effect would be the same as deleting the row from B as far as C was concerned.


sorry, but B.A is not part of the key. The table C references B.B not B.A

Maybe my naming is confusing, so let me try an ASCII E/R diagram :
A                   B               C
-                   -               -
a <----set null---- a               c 
                    b <------------ b


A Share lock on C is required when I 'delete from A', but not if I 'update update b set a=null' instead.
I don't understand why a delete from A needs to prevent inserts on C here.

Regards,
Franck.
Tom Kyte
February 24, 2011 - 12:52 pm UTC

Ok, now I see it (yes, the names got confusing :) ). I cannot think of a reason why it should do that. I'll file a bug on that.

Please share the bug number after opening it

Adam Musch, February 25, 2011 - 12:10 am UTC

I got bit by this same situation recently, and I'd rather not open a duplicate bug.

What about Inserts?

orbfish, April 03, 2011 - 12:14 pm UTC

Just read through this excellent article (a few hours of worth-it work). I didn't see anything about the case I ran into, which was that an insert into the parent table was locking all the children. Yes, this makes no sense to me either, but it was an uncommitted insert of the parent that blocked the insert of a child in another connection. Hoping this is something that makes sense to you off the top of your head, it was all in Java so I might find it hard to recreate in straight SQL.

(P.S. Do you promise to make fun of people who use 'IM' speak?)
Tom Kyte
April 12, 2011 - 11:55 am UTC

That would not happen due to an unindexed foreign key:

ops$tkyte%ORA11GR2> create table p( x int primary key );

Table created.

ops$tkyte%ORA11GR2> create table c ( x references p );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into p values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> insert into p values ( 2 );

1 row created.

ops$tkyte%ORA11GR2> insert into c values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into c values ( 2 );

1 row created.

ops$tkyte%ORA11GR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          insert into p values ( 3 );
  5          commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.


if the insert into P locked C, then it would have blocked and self deadlocked.

There was something else afoot here.

It shouldn't be hard to recreate in 'straight sql', java just executes 'straight sql', it doesn't do anything magic.

Re: Please share the bug number

Franck Pachot, April 08, 2011 - 2:41 am UTC

Hi, Bug 12313173 has been opened for the SET NULL lock issue. Regards, Franck.

behavior differs between 11g and 10g?

peng, May 06, 2011 - 7:02 pm UTC

Oracle 11.1.0.7 64bit

session 1:
SQL>create table p1(id number primary key);

Table created.

SQL>create table p2(id number primary key);

Table created.

SQL>create table c(id1 number references p1(id),id2 number references p2(id));

Table created.

SQL>insert into p1 values(10);

1 row created.

SQL>insert into p2 values(100);

1 row created.

SQL>insert into c values(10,100);

1 row created.

SQL>insert into p1 values(20);

1 row created.

SQL>commit;

Commit complete.

SQL>insert into p2 values(200);

1 row created.

SQL>

session 2:
SQL> delete p1 where id=20;

-- hang here untill session 1 commit/rollback, v$lock shows request 4 on table c

if add index on c(id1) and c(id2), then session 2 SQL will run without waiting

However, in Oracle 10.2.0.3, the delete statement in session 2 will run without wait for session 1, even when there is no indexes on FKs.

does this mean 11g is more 'strict' than 10g, and the bahavior changes?

thanks a lot!


Tom Kyte
May 09, 2011 - 7:06 am UTC

yes, it would appear that something has changed between the releases.

RS vs RX

Peng, May 06, 2011 - 11:14 pm UTC

Looks in 10g,when inserting into parent table, oracle put a RS lock on the child;
In 11gr1,oracle put a RX on child, so when upd/del another parent tab of the same child tab, a S lock can't be obtained.
In summary can we say: in 11g,insert on parent table, will block upd/del on other parent tabs of the 'shared' child tab, if the FKs are not indexed. In 10g, this is not true.

Indexes on foreign keys

Jorge, May 11, 2011 - 6:09 pm UTC

Hi Tom,

The documentation states that INSERT, UPDATE, and DELETE statements on the child table do not acquire any locks on the parent table, but what happens below?
[151]>create table parent (id number primary key);

Table created.

[151]>create table child (id number references parent);

Table created.

[151]>insert into parent select level from dual connect by level <=10;

10 rows inserted.

[151]>insert into child select level from dual connect by level <=10;

10 rows inserted.

[151]>commit;

[151]>delete from child where id=1;

1 row deleted.

[156]>delete from parent where id=2;
hung <--

Why the session [156] hung when I tried delete the row 2 from de parent table?

Tom Kyte
May 12, 2011 - 7:37 am UTC

the delete is trying to get a full table lock on the child. It doesn't have to do with any locking in the parent (insert into the parent instead of deleting, you'll not see any blocking)


Robin, May 19, 2011 - 9:04 am UTC

Tom, 

Session#1
 create table t1 ( x int primary key );
 create table t2 ( y references t1 );
 insert into t1 values ( 1 );
 insert into t1 values ( 2 );
 commit;

Session#2
SQL > insert into T1 select 1 from dual where 1=2;

0 rows created.



Go back to Session#1
--------------------

SQL> delete from T1 where x=1;

hung..........


You mentioned,
if (you UPDATE a parent primary key OR you DELETE a row in the parent table)
   AND (child tables fkey is not indexed)
then
   child table becomes locked -- no other session can insert/update/delete in
   the child table
end if;


Are we updating a Primary Key even though there is no record is getting inserted into the Parent table
i.e. insert into T1 select 1 from dual where 1=2;

Thanks
Rob

Tom Kyte
May 23, 2011 - 10:36 am UTC

this is affecting 11g (not 10g and before). The insert used to take a LMODE=2 lock, it now is taking an LMODE=3 lock on the child table when you insert.

Even though you inserted zero rows - it does the locking before the inserting.

So, the rule has changed a bit again.

The insert will lock the child table in a mode that is incompatible with the lock we attempt to gain on the child table with the delete - the delete must wait.

A reader, May 19, 2011 - 1:52 pm UTC

Just an update, I'm using 11g Release 2 (11.2.0.1)

-Rob

Bug 12313173

Michel Cadot, July 22, 2011 - 5:10 am UTC


Just for information, the "bug" Frank has opened on April 8, 2011 was closed on June 19 with status "32 - Not a Bug. To Filer", so support does not agree with Tom (but we can't see why).

Regards
Michel

Tom Kyte
July 22, 2011 - 2:33 pm UTC

I will reopen that when I get back from travels, I believe they are mistaken in their reason (which you cannot see...). I will have to craft a more compelling "no, this is why this is a bug" statement to reopen it.

Primary key Index on FK

Gustavo, July 29, 2011 - 7:48 am UTC

Tom,

Here is an example of what we do in our company tables:

--/
SQL> create table ab (a int not null, b int not null);

Table created
SQL> alter table ab add constraint ab_pk primary key (a,b);

Table altered
SQL> create table c (c int not null);

Table created
SQL> alter table c add constraint c_pk primary key (c);

Table altered
SQL> create table z(a int not null, b int not null, c int not null);

Table created
SQL> alter table z add constraint z_pk primary key (a,b,c);

Table altered
SQL> alter table z add constraint z_fk_ab foreign key(a,b) references ab(a,b);

Table altered
SQL> alter table z add constraint z_fk_c foreign key(c) references c(c);

Table altered
SQL> create index z_ix_ab on z(a,b);

Index created
SQL> create index z_ix_c on z(c);

Index created
--/
-- *** Final indexes on Z ***

NAME TYPE COLUMNS
Z_IX_AB Normal A, B --> *** I really don't like this kind of indexes ***
Z_IX_C Normal C
Z_PK Unique A, B, C

--/

In this case, the foreign key z_fk_ab is the initial part of the primary key constraint
on the table Z, primary key unique index Z_PK, will suport this as well? or you think that
the index Z_IX_AB is still important?

My point here is how usefull is the index Z_IX_AB:
--> Prevent Locks (Z_PK can do that?)
--> Joins AB -> Z (Z_PK can do that?)
--> ... ?


Best Regards,
Gustavo
Tom Kyte
August 01, 2011 - 11:34 am UTC

the index Z_IX_AB would seem to be redundant here - Z_PK can be used for a foreign key on (a,b) (or on A or on A,B,C as well)


Is there a way to Find PARENT Deletion?

Ram, October 07, 2011 - 5:45 am UTC

Hi Tom,

Excellent thread as usual.

Just wanted to know if there is any way to know if the parent table is getting deleted/Updated PK ?

I have a huge application and recently joined the team and I got around 300 results for the Unindexed FKs after running your query.

Now the problem is I do not know how do I find if the parent tables are getting deleted or not ( Since I do not want to just blindly create the Indexes but rather want to create ONLY wherever necessory )

I tried using ALL_SOURCE but it is getting difficult to get the list of objects where parent is deleted ( I mean run the query against ALL_SOURCE for EACH of the parent tables of these 300 tables list obtained from your query ).

Is there an alternative easy way which I'm missing ?

Thanks in Advance,
Ram
Tom Kyte
October 07, 2011 - 1:54 pm UTC

auditing comes to mind. Enable auditing and see.

short of that, you can query v$sql if you have a good steady state of SQL in your shared pool.

how does index on the foreign key affects

yushangfu, December 23, 2011 - 12:04 am UTC

hi tom
can you tell me how does the index on the foreign key works to make it lock the related rows only,but not the full table when we update the primary key of father table?
just bacause it's fast to read data from index than table ?(so,it can know the related row fast,and lock the related row only?)
Tom Kyte
December 23, 2011 - 8:30 am UTC

It is hard to lock data that doesn't exist. During a delete or update of the key on the parent table - we have to prevent a child row from magically "appearing" and trying to point to that row (that is being deleted/updated). We can do that in the index because we know where that row would have to go. We cannot do that at the table level because the row could go anywhere.

Multiple Foreign Key in a child table

Bala, September 03, 2012 - 12:00 am UTC

Hi Tom,

This site is great.

I read topic "Indexes on foreign keys" and i have one question in that:

If i have multiple foreign key(more than two) in a child table and each foreign key refer to a different parent table.

And i query offen using join condition to get result set of many parent and child combinations.

My question is, should i create individual foreign key index or single composite index for all foreign key.

Which would be better with respect to performance.

Thanks in advance...

Tom Kyte
September 10, 2012 - 6:53 pm UTC

it depends.

the foreign key needs to be on a leading edge of the index.

so if you have:

create table child( .., a references t1, b references t2)

you'd need one with A on the leading edge and one with B on the leading edge (other things can following either of A or B).

if you have

create table child( ..., a references t1, b, foreign key(b,a) references t2)

then a single index on (A,B) would work just fine.

dlm on child table will put a lock on father table!

alan, September 11, 2012 - 1:40 am UTC

Hi Tom,
I read
http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#i5704
It said
"DML on a child table does not acquire a table lock on the parent table."
but on my test,it did :
SQL> create table t1(id int primary key);

表已创建。


SQL> create table t2(id references t1(id));

表已创建。

SQL> insert into t1 values(1);

已创建 1 行。

SQL> insert into t1 values(2);

已创建 1 行。

SQL> insert into t1 values(3);

已创建 1 行。

SQL> commit;

提交完成。

SQL> insert into t2 values(3);

已创建 1 行。

SQL> commit;

SQL> update t2 set id=2 where id=3;

已更新 1 行。

from v$lock we can see ,Oracle put a tm lock on father table:

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (4) and type in ('TM','TX') order by 1;

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
         4 TM      74867          0          3          0          0
         4 TM      74869          0          3          0          0
         4 TX     262144        763          6          0          0

SQL> truncate table t1;
truncate table t1
               *
第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效


SQL> truncate table t2;
truncate table t2
               *
第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效

Please give a explanation.

Thank you very much!

alan
Tom Kyte
September 14, 2012 - 2:59 pm UTC

It is a ddl lock, it prevents someone from changing the structure, the physical attributes of a table. It does not prevent dml in this case.

there is an outstanding transaction that depends on the structure of T2 remaining consistent, no physical changes can take place to t2 until that commits.

A reader, September 15, 2012 - 9:14 am UTC

the lock on parent table t1 is because we are updating child record to new value and to make sure no one delete the parent record during the transaction?

Thanks
Tom Kyte
September 16, 2012 - 4:11 am UTC

no, it isn't - it is for to prevent DDL in this case..

saving SCN

Torben Dahl-Nielsen, September 16, 2012 - 1:42 pm UTC

Hi Tom,
I wish to create a procedure, which – say every 3 minites – accesses new transactions from a transaction table, in which there are only inserts (no updates or deletes). The purpose is to maintain an aggregated table – like GL_balances of the E Business Suite - with opening balances and period sums for fast reporting.
I intent to use SCN to keep track of which transactions I need to deal with next time and will retrieve the scn like

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual

This number I would like to save in the beginning, and then I will deal with all the transactions in the range between the old SCN and the just saved SCN. Does this sound like a suitable approach, and if yes: Can I just save the SCN in a data field on type number. Or will I have to decode it into its two parts, which each are stored in a number data field – and encode again when using the stored SCN. If the latter is the case, which functions should I use for encoding / decoding (similar to the timestamp conversion, which is a little rough in precision:

SCN_TO_TIMESTAMP(1408640)
TIMESTAMP_TO_SCN('10-AUG-2005 18:08:03.000')

Best, Torben
Tom Kyte
September 20, 2012 - 1:45 am UTC

if you are thinking of relying on ora_rowscn, please don't. It is not long term reliable enough for this.

an SCN though would just fit into a number.

the timestamp conversion only goes so far back in time and would not be very precise (only +/-3 seconds)

I'd be looking at a materialized view if possible.

short of that - streams to mine the redo.

if you try to do this yourself, you'll have to

a) put a timestamp on each record yourself
b) because of consistent non-blocking reads - you would have to select the minimum of systimestamp and the oldest transaction you see in v$transaction.
c) you'd have to pull every record since that time (yes, you will pull duplicates, that is unavoidable and you have to be able to deal with that)

see:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:16998677475837
for why


or, if you try this yourself, you'll put the primary keys into the logical equivalent of a materialized view log yourself as you insert into this table so you'll know what rows to pull - and then delete those keys as you pull them.

read consistency

Torben Dahl-Nielsen, September 18, 2012 - 3:19 pm UTC

Hi again.
Before you find time to answer I have come up with another question. When I create the mentioned sum table from scratch I use a PL/SQL procedure, which loops over some months calling another procedure, which issues selects collecting data from varying sources. All data are inserted into the same table, and I end (after four hours of updating) with a commit outside the outer procedure. I would like this whole updating to be insistent in time as of the beginning of the procedure. And the question: Does this automatically happen, since it is contained in a procedure – or will I have to store the CSN number in a local variable and refer to it in every select. If I must refer to it: Should it be in every imbedded select (unions, inline selects, views etc. are used) or will it do with the primary selects.
Thanks,
Torben

Tom Kyte
September 20, 2012 - 5:28 am UTC

by default - in read committed isolation - every query will be as of the point in time the query was opened. so if you execute two queries in your stored procedure - they will be as of slightly different points in time.

Unless you use serializable (which is not suggested for 4 hours - definitely not suggested)

or unless you use READ ONLY isolation (probably not appropriate for you)

or unless you use "as of SCN" on each query - in which case you better set up a guaranteed undo retention period!!! else you'll get snapshot too old late into running your four hour process.

Performance of integrity check

A reader, February 06, 2013 - 4:30 pm UTC

Hi Tom,
I have this simple scenario and was wondering what would work best.
I have a parent table that has a FK into a child table.
Let us say we only do inserts (snd selects) to child table.

Now , I need to write a proc that inserts into the child table based on some business rule.
All other things remaining same, performance wise , is it better to check if the parent record exists (eg. select count(*) from parent_table where id=p_id (PK in this case)
and raise exception if it doesn't,
or let the integrity check do that part and handle it in the exception block.

we are running oracle 11g on linux.

thanks for your help!
Tom Kyte
February 07, 2013 - 6:23 am UTC

I was just talking about this, read

http://www.reddit.com/r/oracle/comments/17rjr8/database_developers_triggers_good_or_bad/

USE DECLARATIVE RI - period. do not do anything in a trigger/procedural code that can be done declaratively.


You would be surprised how hard and how limiting doing the RI check yourself is. It is *NOT* just a simple "check if parent exists", you have to serialize operations (reads don't block writes, writes don't block reads, two people operating at the same time won't block each other nor will they see each others work)


A reader, February 07, 2013 - 9:36 am UTC

Just what I needed to see. thanks again, Tom!

Is it relevant for 11g and 12c

Spur, November 21, 2013 - 6:38 pm UTC

Tom - Is foreign key needing to have a index with leading edge to avoid table lock of child table still relevant for 11g and 12c?

All FK columns required in index?

gwellman@princeton.com, January 15, 2015 - 10:04 pm UTC

Do *all* columns of the FK need to be on the leading edge of the index?

I have a situation where parent tables often have a two-column PK, where the first column is highly selective and the second column much less so (it's like a version number).

So, an index (on the child) with just the first column of the FK is definitely good enough to support select queries that join to the child on the FK.

Naively I assumed that meant the index would also properly support the FK relationship itself, e.g. preventing table locks on the child when a parent PK is updated (or deleted). Was I wrong?

Would it make a difference if (on the child) the second column of the FK is a virtual column set to a constant? (That may sound strange, but as mentioned, the second column is like a version number, and the child only needs one version of the parent to exist.)

If it matters, this is on 11g.

Thanks!

A reader, September 14, 2016 - 10:13 am UTC


link not working

Matthew Sultana, April 07, 2017 - 1:00 pm UTC

The article refers to a link that does not work
Connor McDonald
April 08, 2017 - 5:22 am UTC

<code>
column columns format a20 word_wrapped
column table_name format a30 word_wrapped

select decode( b.table_name, NULL, '****', 'ok' ) Status,
a.table_name, a.columns, b.columns
from
( select a.table_name, a.constraint_name,
max(decode(position, 1, substr(column_name,1,30),NULL)) ||
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name ) a,
( select table_name, index_name,
max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
from user_ind_columns
group by table_name, index_name ) b
where a.table_name = b.table_name (+)
and b.columns (+) like a.columns || '%'
/

<code>

Beware of adding this indexes

J.P. Lourens, May 28, 2020 - 9:35 pm UTC

Based on this advice, I added some indexes on columns included in a foreign key (in Oracle 11.2).

However, after adding this indexes, some select query (including my child table) started throwing ORA-01652: unable to extend temp segment errors.
From explain-plan I learned, that suddenly this new index on the foreignkey column was used, where previously this was the index on the PK of the child table.
For now I removed this new index again.
Chris Saxon
May 29, 2020 - 11:21 am UTC

Just goes to show it's important to test on your data! Almost all performance recommendations have edge cases where they don't apply.

Impact on SELECT queries?

Rob, July 08, 2020 - 7:51 pm UTC

In the top post Tom said
"So, when do you NOT need to index a foriegn key. In general when the following conditions are met:
o you do NOT delete from the parent table. (especially with delete cascade -- it is a double whammy)
o you do NOT update the parent tables unique/primary key value.
o you do NOT join from the PARENT to the CHILD (like DEPT->EMP)."


Can you please provide a test case where it can be seen? Is this still true in 2020?

We have many small tables with pretty much static data. The IDs are being referenced in some bigger tables (few 100k rows). Should we consider creating indexes on foreign keys? First two points above don't apply.
Chris Saxon
July 09, 2020 - 11:18 am UTC

If you join the small table and large - or just filter on the FK column in general - then yes, you should consider an index.

This will be particularly useful if there's large skew in the FK values in the child table (common in my experience).

For example, this create a three-row lookup table. The child table has:

- 1 row with value 1
- 1000 rows with value 2
- 100000 rows with value 3

If you search for value 1 in the child, you'd kinda like the optimizer to use an index:

create table small ( pk primary key, val ) as
  select level, to_char ( to_date ( level, 'j' ), 'jsp' )
  from   dual connect by level <= 3;
  
create table large (
  pk integer primary key,
  fk references small ( pk )
);

insert into large values ( 1, 1 );
insert into large
  with rws as (
  select level + 1 pk, 2 fk from dual
  connect by level <= 1000
)
  select * from rws;

insert into large
  with rws as (
  select level + 1002 pk, 3 fk from dual
  connect by level <= 100000
)
  select * from rws;
commit;
exec dbms_stats.gather_table_stats ( user, 'large', method_opt => 'for all columns' ) ;
exec dbms_stats.gather_table_stats ( user, 'small', method_opt => 'for all columns' ) ;

alter session set statistics_level = all;
set serveroutput off

select * from small s
join   large l
on     s.pk = l.fk
where  s.pk = 1;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

-------------------------------------------------------------------------------------------------------    
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
-------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT             |              |      1 |        |      1 |00:00:00.01 |     204 |    
|   1 |  NESTED LOOPS                |              |      1 |      1 |      1 |00:00:00.01 |     204 |    
|   2 |   TABLE ACCESS BY INDEX ROWID| SMALL        |      1 |      1 |      1 |00:00:00.01 |       2 |    
|*  3 |    INDEX UNIQUE SCAN         | SYS_C0016815 |      1 |      1 |      1 |00:00:00.01 |       1 |    
|*  4 |   TABLE ACCESS FULL          | LARGE        |      1 |      1 |      1 |00:00:00.01 |     202 |    
-------------------------------------------------------------------------------------------------------    
                                                                                                           
Predicate Information (identified by operation id):                                                        
---------------------------------------------------                                                        
                                                                                                           
   3 - access("S"."PK"=1)                                                                                  
   4 - filter("L"."FK"=1)

create index lfk on large ( fk );

select * from small s
join   large l
on     s.pk = l.fk
where  s.pk = 1;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

------------------------------------------------------------------------------------------------------------------------    
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |    
------------------------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT                     |              |      1 |        |      1 |00:00:00.01 |       5 |      4 |    
|   1 |  NESTED LOOPS                        |              |      1 |      1 |      1 |00:00:00.01 |       5 |      4 |    
|   2 |   TABLE ACCESS BY INDEX ROWID        | SMALL        |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |    
|*  3 |    INDEX UNIQUE SCAN                 | SYS_C0016815 |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |    
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| LARGE        |      1 |      1 |      1 |00:00:00.01 |       3 |      4 |    
|*  5 |    INDEX RANGE SCAN                  | LFK          |      1 |      1 |      1 |00:00:00.01 |       2 |      4 |    
------------------------------------------------------------------------------------------------------------------------    
                                                                                                                            
Predicate Information (identified by operation id):                                                                         
---------------------------------------------------                                                                         
                                                                                                                            
   3 - access("S"."PK"=1)                                                                                                   
   5 - access("L"."FK"=1)

Clarification

Rob, July 08, 2020 - 8:01 pm UTC

Sorry for a haste post -
I was requesting a test script for the last point only - "you do NOT join from the PARENT to the CHILD (like DEPT->EMP)."

A reader, April 27, 2021 - 9:48 am UTC


Then why not automate index creation on FK constraint?

Bhavesh, May 16, 2022 - 6:46 am UTC

To me it sounds like 99% of the cases would require index on FK. Then question is why hasn't Oracle automated index creation on creation of foreign key constraint. Just as it creates for primary key constraint. Why leave it to the developers?
Connor McDonald
May 23, 2022 - 3:31 am UTC

Where does "99%" come from? I'd contend that its probably way less than that.

Look at any app for how often it:

a- deletes a row from a parent table
b- updates the primary key in a parent table

(b) is idealy never, because that doesn't sound like a primary key to me.

Which leaves (a)


Yes, but what about join queries?

A reader, May 27, 2022 - 9:14 am UTC

Thanks Connor for your response.

Following paragraph from Tom's Book
Oracle Database Transactions and Locking Revealed: Building High Performance Through Concurrency. I believe point 3 below is going to be 99% of the cases.

When do you not need to index a foreign key? The answer is, in general, when the following conditions are met:

You do not delete from the parent table.

You do not update the parent table’s unique/primary key value (watch for unintended updates to the primary key by tools).

You do not join from the parent to the child (like DEPT to EMP).
Connor McDonald
May 31, 2022 - 1:51 am UTC

Yes, but point 3 is really *nothing* special about a foreign key index, but just purely an index, ie, does it improve the performance of my query.

no foreign key index - locking on child table - cannot simulate

alan koh, July 18, 2022 - 6:08 pm UTC

hi all

reading the foreign key documentation but not able to simulate child table locking on unindexed foreign key

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html#GUID-5015CC53-4059-4CD6-B892-F211E8BDE2F9

everytime i tried to update the primary key on the parent table to something else - it will prompt me straight away that ORA-02292 child record found

create table TEST ( a number primary key , text varchar2(50) );
insert into test values (1,'a');
insert into test values (2,'b');
insert into test values (3,'c');

commit;


create table TEST_FK ( a_fk number, text varchar2(50),
constraint afk foreign key (a_fk) references test (a) 
);

insert into test_fk values (1,'a');
insert into test_fk values (2,'b');
insert into test_fk values (3,'c');

commit;

update test set a=5 where a=3;
SQL Error: ORA-02292: integrity constraint (XXX.AFK) violated - child record found


how do i know if there is full table share lock on the TEST_FK table ?
Chris Saxon
July 19, 2022 - 12:19 pm UTC

Leave the inserts for TEST_FK uncommited.

Then - in a second session - run the update. The update will be blocked, waiting for the inserts to commit/rollback.

You can see the problem in one session using autonomous transactions. Again: leave the inserts uncommitted. Then run the update in autonomous transaction.

This will be stuck waiting to acquire the lock. Eventually the database will recognize this and raise a deadlock error:

create table TEST ( a number primary key , text varchar2(50) );
insert into test values (1,'a');
insert into test values (2,'b');
insert into test values (3,'c');

commit;

create table TEST_FK ( a_fk number, text varchar2(50),
constraint afk foreign key (a_fk) references test (a) 
);

insert into test_fk values (1,'a');
insert into test_fk values (2,'b');
insert into test_fk values (3,'c');

declare
  pragma autonomous_transaction;
begin
  update test set a=5 where a=3;
end;
/

ORA-00060: deadlock detected while waiting for resource

no foreign key index - locking on child table - cannot simulate

alan koh, July 20, 2022 - 5:00 pm UTC

thank you for the reply

i have done these

session 1

create table TEST ( a number primary key , text varchar2(50) );
insert into test values (1,'a');
insert into test values (2,'b');
insert into test values (3,'c');

commit;

create table TEST_FK ( a_fk number, text varchar2(50),
constraint afk foreign key (a_fk) references test (a) 
);

insert into test_fk values (1,'a');
insert into test_fk values (2,'b');
insert into test_fk values (3,'c');


session 2

declare
  pragma autonomous_transaction;
begin
  update test set a=5 where a=3;
end;
/



in v$lock - i can see session 2 holding a share lock on TEST_FK and is blocked by session 1 holding a row exclusive lock on TEST_FK


q1) why do we need session 1 to remain uncommitted to see this phenomenal ?

q2) why does session 2 need pragma autonomous_transaction ?

thank you


Chris Saxon
July 21, 2022 - 10:53 am UTC

The update in session 2 is blocked by the lock session 1 holds. This is because session 1 might add or remove child rows that cause or avoid the FK error. Session 2 has to wait to see session 1's outcome.

When you commit session 1, session 2 can complete its update.

Session 2 only tries to lock the child table while running the update. Once it's complete it releases the lock - even if the transaction 2 is still uncommitted.

Without session 1 blocking it, session 2 gets and releases the locks far too quickly for you to see them (at least with tiny tables like this).

You don't need an autonomous transaction in session 2 - this is to demonstrate the problem when running all the statements in the same session. If using two sessions you can run the update as normal.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.