March 28, 2001 - 4am Central time zone
Reviewer: Helena Markova from Bratislava, Slovakia
Indexes on foreign keys
May 9, 2001 - 10am Central time zone
Reviewer: Thasin Sardar from Lansing, MI USA
Great info!
May 25, 2001 - 12pm Central time zone
Reviewer: A reader
July 12, 2001 - 9am Central time zone
Reviewer: A reader
July 12, 2001 - 10am Central time zone
Reviewer: A reader
July 17, 2001 - 5am Central time zone
Reviewer: Martin Gamperl from Graz, AUSTRIA
August 9, 2001 - 2am Central time zone
Reviewer: shibu from bangalore ,India
very informative
October 25, 2001 - 4pm Central time zone
Reviewer: an from DE
excellent!
And now.... A moment for the newbies!
January 25, 2002 - 9am Central time zone
Reviewer: Paul from Carmel, IN
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? :)
Followup January 25, 2002 - 1pm Central time zone:
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
January 26, 2002 - 8am Central time zone
Reviewer: Reader
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>
Followup January 26, 2002 - 6pm Central time zone:
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 http://asktom.oracle.com/~tkyte/unindex/index.html and run the query as I have it coded.
Another twist - what if the child tables are empty?
May 1, 2002 - 5pm Central time zone
Reviewer: Doug from CT, USA
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?
Followup May 1, 2002 - 9pm Central time zone:
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
May 22, 2002 - 4am Central time zone
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.
Followup May 22, 2002 - 8am Central time zone:
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.
May 23, 2002 - 2am Central time zone
Reviewer: kiro from Bulgaria
Delete Cascade Locking
August 16, 2002 - 3pm Central time zone
Reviewer: Mark from Huston,TX
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
Followup August 16, 2002 - 3pm Central time zone:
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?
November 6, 2002 - 11pm Central time zone
Reviewer: Praveen from India
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
Followup November 7, 2002 - 7am Central time zone:
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
January 13, 2003 - 1am Central time zone
Reviewer: Bala from Kuala Lumpur
If there are 99 reasons for indexing the foreign key why not hard wire this into oracle?
Followup January 13, 2003 - 8am Central time zone:
because of the last reason not to.
Do I need to index?
February 13, 2003 - 1pm Central time zone
Reviewer: Sri
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.
Followup February 13, 2003 - 5pm Central time zone:
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
February 14, 2003 - 3pm Central time zone
Reviewer: Sri
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.
Followup February 14, 2003 - 8pm Central time zone:
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
March 31, 2003 - 6am Central time zone
Reviewer: Madhuri Bondre from India
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
Followup March 31, 2003 - 9am Central time zone:
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://download-west.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 );
so, the parent table has two rows -- ONLY ONE of which can possibly be a parent key in a parent
child relationship.
Now, this child record is NOT associated with the parent at all
ops$tkyte@ORA920> insert into t2 values ( 1, null );
1 row created.
As evidenced by this insert, (2,null) goes right in
ops$tkyte@ORA920> insert into t2 values ( 2, null );
1 row created.
but note that there is in fact a foreign key
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.
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.
What's behind the .......and many others...
March 31, 2003 - 9am Central time zone
Reviewer: pasko from HH,Germany
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.
Followup March 31, 2003 - 9am Central time zone:
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 ..:)-
March 31, 2003 - 10am Central time zone
Reviewer: pasko
Functional unique index as foreign key
April 16, 2003 - 1am Central time zone
Reviewer: Madhuri Bondre from India
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
Followup April 16, 2003 - 10am Central time zone:
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
April 16, 2003 - 6am Central time zone
Reviewer: madhuri bondre from india
i am sorry
docode = decode
unique functional index with example
April 17, 2003 - 3am Central time zone
Reviewer: Madhuri Bondre from India
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.
Followup April 17, 2003 - 10am Central time zone:
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
April 17, 2003 - 7am Central time zone
Reviewer: Alla from Boston, MA USA
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
Followup April 17, 2003 - 10am Central time zone:
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
April 17, 2003 - 11am Central time zone
Reviewer: Alla from Boston, MA USA
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
Followup April 17, 2003 - 11am Central time zone:
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
April 18, 2003 - 1am Central time zone
Reviewer: Madhuri Bondre from India
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.
Followup April 18, 2003 - 11am Central time zone:
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
April 19, 2003 - 1am Central time zone
Reviewer: Madhuri Bondre from India
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.
Followup April 19, 2003 - 12pm Central time zone:
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
April 21, 2003 - 12am Central time zone
Reviewer: Madhuri Bondre from India
Thanks for your reply
Question about script to detect unindexed foreign keys
May 13, 2003 - 11am Central time zone
Reviewer: Arun Mathur from Marietta, GA
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.
Followup May 13, 2003 - 4pm Central time zone:
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
May 13, 2003 - 1pm Central time zone
Reviewer: John Hurley from Clevelahd, OH USA
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
May 14, 2003 - 2pm Central time zone
Reviewer: Arun Mathur from Marietta, GA
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
July 2, 2003 - 9am Central time zone
Reviewer: A reader from Houston
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
Followup July 2, 2003 - 9am Central time zone:
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
July 2, 2003 - 10am Central time zone
Reviewer: A reader from Houston
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
July 2, 2003 - 10am Central time zone
Reviewer: A reader from Houston
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)
Followup July 2, 2003 - 10am Central time zone:
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
July 2, 2003 - 11am Central time zone
Reviewer: A reader from Houston
That meaans we have not got into a deadlock situation - right? this is just one session blocking
the other - Is that correct?
Followup July 2, 2003 - 11am Central time zone:
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
July 2, 2003 - 12pm Central time zone
Reviewer: A reader from Houston
The only wat to trace a deadlock is through the oracle trace files. Is that correct?
Followup July 2, 2003 - 12pm Central time zone:
yes
Re: Lock on child Table
July 2, 2003 - 12pm Central time zone
Reviewer: A reader from Houston
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?
Followup July 2, 2003 - 12pm Central time zone:
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
July 2, 2003 - 1pm Central time zone
Reviewer: A reader from Houston
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
Followup July 2, 2003 - 2pm Central time zone:
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
July 2, 2003 - 2pm Central time zone
Reviewer: A reader from Houston
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
Re: Lock on child Table
July 2, 2003 - 3pm Central time zone
Reviewer: A reader from Houston
Thank you
PARENT to the CHILD & Driving Table
July 3, 2003 - 11am Central time zone
Reviewer: Robert from PA
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 ?
Followup July 3, 2003 - 11am Central time zone:
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.
July 11, 2003 - 5pm Central time zone
Reviewer: A reader
cascade deletes on parent record
July 16, 2003 - 1pm Central time zone
Reviewer: Menon from ca
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:)
Followup July 16, 2003 - 4pm Central time zone:
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!
July 16, 2003 - 4pm Central time zone
Reviewer: A reader from ca
that confirms what I was thinking (after having
read your book)
Regards
Menon:)
please review this .....
August 25, 2003 - 8pm Central time zone
Reviewer: Reader
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.
Followup August 26, 2003 - 8am Central time zone:
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
August 26, 2003 - 1pm Central time zone
Reviewer: nitin from india
good note
very good
August 28, 2003 - 8am Central time zone
Reviewer: A reader from Paradise, P.R.China
Lock on child Table May 22, 2002
August 28, 2003 - 8am Central time zone
Reviewer: A reader
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.
Followup August 29, 2003 - 7am Central time zone:
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
December 31, 2003 - 4am Central time zone
Reviewer: Kiran Shah from India
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.
Followup December 31, 2003 - 9am Central time zone:
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...
January 1, 2004 - 2am Central time zone
Reviewer: Kiran Shah from India
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
Followup January 1, 2004 - 9am Central time zone:
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....
January 3, 2004 - 2am Central time zone
Reviewer: Kiran Shah from India
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...
Followup January 3, 2004 - 9am Central time zone:
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.
January 5, 2004 - 1am Central time zone
Reviewer: Kiran Shah from India
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
February 6, 2004 - 11am Central time zone
Reviewer: Andres from Estonia
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)
Followup February 7, 2004 - 1pm Central time zone:
no, it cannot.
deadlock and FKs
February 7, 2004 - 3am Central time zone
Reviewer: A reader
Hi
if we dont index FKs and update parent tables can this cause deadlocks?
How can I reproduce this?
Followup February 7, 2004 - 2pm Central time zone:
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?
February 9, 2004 - 11am Central time zone
Reviewer: A reader
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?
Followup February 9, 2004 - 12pm Central time zone:
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
February 9, 2004 - 12pm Central time zone
Reviewer: A reader
Hi
I am sorry, it works, just that I must update Child first then Parent
Sql Query Timeout?
February 17, 2004 - 3am Central time zone
Reviewer: Amar Desai from India
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
Followup February 17, 2004 - 8am Central time zone:
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
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/users.htm#1148
(the admin guide, a great place to start to discover things like this...)
Query timeout
February 19, 2004 - 2am Central time zone
Reviewer: Amar from India
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
Followup February 19, 2004 - 10am Central time zone:
http://download-west.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
April 7, 2004 - 5am Central time zone
Reviewer: Venkat from India
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
Followup April 7, 2004 - 9am Central time zone:
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
April 8, 2004 - 3pm Central time zone
Reviewer: A reader
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
Followup April 8, 2004 - 3pm Central time zone:
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
April 8, 2004 - 3pm Central time zone
Reviewer: A reader
My 8k block size is not enough ?
SQL> sho parameter db_block_size
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_block_size integer
8192
Followup April 8, 2004 - 5pm Central time zone:
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
April 8, 2004 - 10pm Central time zone
Reviewer: A reader
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
April 21, 2004 - 7pm Central time zone
Reviewer: Neelam from CA
I was going through the documentation
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c22integ.htm#8565
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
Followup April 21, 2004 - 9pm Central time zone:
blocking locks -- real lock locks.
no one is prevented from modifying any of the data in the child table.
Just a simple question
April 30, 2004 - 6pm Central time zone
Reviewer: A reader
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
Followup May 1, 2004 - 9am Central time zone:
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
May 19, 2004 - 9am Central time zone
Reviewer: Venkat from India
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?
Followup May 19, 2004 - 10am Central time zone:
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
June 5, 2004 - 7am Central time zone
Reviewer: Raaghid from India
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)
Followup June 5, 2004 - 9am Central time zone:
define "taking too much time"
time taken
June 7, 2004 - 12am Central time zone
Reviewer: Raaghid from India
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.
Followup June 7, 2004 - 8am Central time zone:
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
October 20, 2004 - 11am Central time zone
Reviewer: Thirumaran
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
Followup October 20, 2004 - 11am Central time zone:
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)
December 15, 2004 - 10am Central time zone
Reviewer: A reader
Get Error
January 4, 2005 - 8am Central time zone
Reviewer: Ram Joshi from India
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
Followup January 4, 2005 - 8am Central time zone:
did you run catblock.sql which creates dba_kgllock?
connect / as sysdba
@?/rdbms/admin/catblock
Please Clarify
January 4, 2005 - 9am Central time zone
Reviewer: Siva from Bangalore,India
Hello Tom,
Is there any possibility that Indexes slow down the
retrieval of data?
Please do reply.
Bye!
Locking whole child table
January 21, 2005 - 1pm Central time zone
Reviewer: Carol from Victoria, CA
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.
Followup January 21, 2005 - 7pm Central time zone:
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
January 23, 2005 - 6pm Central time zone
Reviewer: Andres from Estonia
/---/
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
February 1, 2005 - 2pm Central time zone
Reviewer: Jeff from Indianapolis, IN USA
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 http://asktom.oracle.com/~tkyte/unindex/index.html
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)."
Followup February 1, 2005 - 3pm Central time zone:
c/osi/asktom/
Indexes on Foreign Keys
February 1, 2005 - 4pm Central time zone
Reviewer: Jeff from Indianapolis, IN
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!
Followup February 2, 2005 - 4am Central time zone:
sorry, I was speaking in sqlplus ;)
change the word osi in the url to asktom.
To Jeff
February 1, 2005 - 6pm Central time zone
Reviewer: A Reader
Jeff,
I think this is the URL you are looking for:
http://asktom.oracle.com/~tkyte/unindex/index.html
If you look at the bottom of the Ask Tom home page, you will see a link to articles ...
c/osi/asktom
February 1, 2005 - 6pm Central time zone
Reviewer: Bob B from Albany, NY
c/osi/asktom = change osi to asktom.
http://asktom.oracle.com/~tkyte/unindex/index.html does not work
http://asktom.oracle.com/~tkyte/unindex/index.html *DOES* work
Indexes on Foreign Keys (Bob B and Mr. A Reader)
February 2, 2005 - 8am Central time zone
Reviewer: Jeff from Indianapolis, IN
OK now I am just embarassed. It was a long day yesterday.
Thanks!
Pls. change the url(s) in your db.
February 7, 2005 - 11pm Central time zone
Reviewer: A reader
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.
Followup February 8, 2005 - 1am Central time zone:
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.
March 9, 2005 - 3pm Central time zone
Reviewer: DBStar from USA
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
Followup March 9, 2005 - 3pm Central time zone:
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
March 25, 2005 - 10am Central time zone
Reviewer: A reader
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
Strange access path
March 29, 2005 - 3pm Central time zone
Reviewer: Sanjaya Balasuriya from Colombo, Sri Lanka
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.
Followup March 29, 2005 - 4pm Central time zone:
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
April 6, 2005 - 6am Central time zone
Reviewer: Sanjaya Balasuriya from Colombo, Sri Lanka
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
Followup April 6, 2005 - 9am Central time zone:
please, utilize this space for followups relevant to the original question.
April 13, 2005 - 3am Central time zone
Reviewer: A reader
Creating an index on a table that is being processed
April 14, 2005 - 12pm Central time zone
Reviewer: A reader from Pensacola, FL
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)
Followup April 14, 2005 - 1pm Central time zone:
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...
April 14, 2005 - 11pm Central time zone
Reviewer: A reader
Thanks
May 16, 2005 - 3am Central time zone
Reviewer: sam from India
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 ??
Followup May 16, 2005 - 8am Central time zone:
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
May 16, 2005 - 10am Central time zone
Reviewer: A reader
Excellent!
May 16, 2005 - 12pm Central time zone
Reviewer: Jairo Ojeda from Costa Rica
Foreign keys and indexes
June 14, 2005 - 6pm Central time zone
Reviewer: MJ from New Mexico, USA
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
Followup June 15, 2005 - 3am Central time zone:
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
June 26, 2005 - 6am Central time zone
Reviewer: Yousef from Kuwait
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
)
/
Followup June 26, 2005 - 9am Central time zone:
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
June 26, 2005 - 9am Central time zone
Reviewer: A reader
Thank you so much
reader
July 6, 2005 - 2pm Central time zone
Reviewer: A reader
http://asktom.oracle.com/~tkyte/unindex/index.html does not seem to work
Followup July 7, 2005 - 8am Central time zone:
change osi to asktom, osi was the old server name
Full Table Scan
July 19, 2005 - 1pm Central time zone
Reviewer: A reader from Canada
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?
Deleting of rows with primary keys
November 6, 2005 - 5am Central time zone
Reviewer: Bala from India
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?
Followup November 6, 2005 - 8am Central time zone:
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...
November 9, 2005 - 11am Central time zone
Reviewer: Kashif from Herndon, VA
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
Followup November 11, 2005 - 10am Central time zone:
A rose smells as sweet, by whatever name you call it.
a foreign key is a foreign key is a foreign key :)
index
November 10, 2005 - 10pm Central time zone
Reviewer: sam
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
Followup November 12, 2005 - 8am Central time zone:
<> 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
November 12, 2005 - 9am Central time zone
Reviewer: mo
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
Followup November 12, 2005 - 11am Central time zone:
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
May 1, 2006 - 3pm Central time zone
Reviewer: rr
How to find index on foeign key?
Followup May 2, 2006 - 3am Central time zone:
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.
http://asktom.oracle.com/~tkyte/unindex/unindex.sql shows how to find the UNINDEXED foreign keys.
fk
May 1, 2006 - 9pm Central time zone
Reviewer: rr
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?
Followup May 2, 2006 - 3am Central time zone:
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
May 2, 2006 - 7pm Central time zone
Reviewer: rr
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;
Followup May 3, 2006 - 1am Central time zone:
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
September 20, 2006 - 9am Central time zone
Reviewer: John G. Galinski from Richmond, TX USA
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?
Followup September 20, 2006 - 3pm Central time zone:
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
September 20, 2006 - 6pm Central time zone
Reviewer: John G. Galinski from Richmond, TX USA
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.
October 3, 2006 - 5pm Central time zone
Reviewer: Rahul from Texas, USA
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>
Followup October 3, 2006 - 7pm Central time zone:
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
October 25, 2006 - 8am Central time zone
Reviewer: A reader
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
Followup October 25, 2006 - 9am Central time zone:
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
November 17, 2006 - 5am Central time zone
Reviewer: Deepak from India
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.
Followup November 17, 2006 - 8am Central time zone:
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
November 21, 2006 - 7pm Central time zone
Reviewer: Joanna from Arlington, WA
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?
Followup November 22, 2006 - 4pm Central time zone:
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
January 11, 2007 - 1am Central time zone
Reviewer: Tariq Zia Lakho from Pakistan
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
January 13, 2007 - 2am Central time zone
Reviewer: Tariq Zia Lakho from Pakistan
Dear Tom,
I am still waiting for your reply.
Nulls on foreign keys
February 15, 2007 - 11am Central time zone
Reviewer: Richard from Salzburg, Austria
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!
Followup February 16, 2007 - 10am Central time zone:
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
February 16, 2007 - 11am Central time zone
Reviewer: Richard from Salzburg, Austria
column order for the Unindexed Foreign Keys query
April 2, 2007 - 8am Central time zone
Reviewer: Donat Callens from Belgium
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%'
Followup April 4, 2007 - 9am Central time zone:
seems to get tons of false positives.
corrected my version of the query
April 10, 2007 - 5am Central time zone
Reviewer: Donat from Belgium
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;
Followup April 10, 2007 - 10am Central time zone:
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?
April 10, 2007 - 2pm Central time zone
Reviewer: A reader
Tom,
Has the requirement to have index on foreign key columns changed or been modified in some way in 10gr2?
Thanks
Followup April 10, 2007 - 3pm Central time zone:
no, same as in 9i
EXCELLENT
April 11, 2007 - 11am Central time zone
Reviewer: SANTO from BANGALORE,INDIA
IT IS VERY NICE INFORMATION TO KNOW....
index stats and Oracle optimizer behavior
May 3, 2007 - 6pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
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.
May 7, 2007 - 12pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
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
Followup May 8, 2007 - 11am Central time zone:
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
May 8, 2007 - 1pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
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
August 30, 2007 - 5am Central time zone
Reviewer: Ahmed from Pakistan
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;
Followup September 4, 2007 - 4pm Central time zone:
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
September 3, 2007 - 6am Central time zone
Reviewer: Ahmed from Pakistan
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.
Followup September 5, 2007 - 1pm Central time zone:
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 ?
December 6, 2007 - 4am Central time zone
Reviewer: shgoh from MY
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
Followup December 10, 2007 - 10am Central time zone:
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
February 4, 2008 - 6pm Central time zone
Reviewer: A reader
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
Followup February 4, 2008 - 7pm Central time zone:
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
February 6, 2008 - 9am Central time zone
Reviewer: LordPepeII
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
April 24, 2008 - 5pm Central time zone
Reviewer: Shreyas from NY, USA
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.
Followup April 28, 2008 - 12pm Central time zone:
...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
May 1, 2008 - 5pm Central time zone
Reviewer: Shreyas from NY, USA
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.
Followup May 1, 2008 - 9pm Central time zone:
... 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
June 5, 2008 - 9am Central time zone
Reviewer: Scott H from New York, NY, USA
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
Followup June 5, 2008 - 10am Central time zone:
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
August 12, 2008 - 9am Central time zone
Reviewer: Chris from Ottawa, Ontario, Canada
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.
Followup August 13, 2008 - 4am Central time zone:
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)
August 31, 2008 - 1pm Central time zone
Reviewer: A reader
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
Followup September 1, 2008 - 12pm Central time zone:
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)
September 1, 2008 - 2pm Central time zone
Reviewer: A reader
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
Followup September 2, 2008 - 11am Central time zone:
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
November 27, 2008 - 9am Central time zone
Reviewer: A reader
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
Followup November 28, 2008 - 5pm Central time zone:
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
December 1, 2008 - 5am Central time zone
Reviewer: Claus Rasmussen from Switzerland
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
Followup December 1, 2008 - 8am Central time zone:
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
December 1, 2008 - 8am Central time zone
Reviewer: Claus Rasmussen from Switzerland
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
January 14, 2009 - 11am Central time zone
Reviewer: A reader from Germany
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
)
)
Followup January 16, 2009 - 4pm Central time zone:
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
March 23, 2009 - 12pm Central time zone
Reviewer: Vin from NY
Has the requirement to have index on foreign key columns changed or been modified in some way in
11g?
Thanks
Followup March 26, 2009 - 1pm Central time zone:
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
March 31, 2009 - 2pm Central time zone
Reviewer: Sal from Pittsburgh, PA
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!
Followup April 1, 2009 - 8am Central time zone:
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.
April 2, 2009 - 5am Central time zone
Reviewer: snowy from Japan
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"
Followup April 2, 2009 - 10am Central time zone:
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
April 2, 2009 - 11am Central time zone
Reviewer: Mohamed
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
Followup April 2, 2009 - 12pm Central time zone:
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.
April 7, 2009 - 5am Central time zone
Reviewer: A reader
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
Followup April 13, 2009 - 11am Central time zone:
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
June 16, 2009 - 10am Central time zone
Reviewer: Dan from India
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
Followup June 16, 2009 - 2pm Central time zone:
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
June 17, 2009 - 3am Central time zone
Reviewer: Dan from India
Got it & Thanks.
when you have an index vs not having an index
June 18, 2009 - 7am Central time zone
Reviewer: karthi from India
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
June 26, 2009 - 12am Central time zone
Reviewer: joshua from malaysia
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
Followup June 26, 2009 - 10am Central time zone:
<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
June 26, 2009 - 11am Central time zone
Reviewer: Mohamed from France
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
Followup June 26, 2009 - 11am Central time zone:
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
July 6, 2009 - 8am Central time zone
Reviewer: A reader
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
Followup July 6, 2009 - 8pm Central time zone:
(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
July 13, 2009 - 5am Central time zone
Reviewer: Steve from Munich, Germany
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
Followup July 14, 2009 - 5pm Central time zone:
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
July 13, 2009 - 5am Central time zone
Reviewer: Steve from Munich, Germany
sorry... I meant deletes on child tables.
Followup July 14, 2009 - 5pm Central time zone:
give example
404 Not Found
October 27, 2009 - 6am Central time zone
Reviewer: Priscila from Madrid, Spain
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
Followup October 27, 2009 - 11am Central time zone:
see homepage, I had to relocate those articles, there is a link there now
Duplicate Indexes covering the FK
November 25, 2009 - 8am Central time zone
Reviewer: A reader
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.
Followup November 27, 2009 - 3pm Central time zone:
... 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.
November 28, 2009 - 12pm Central time zone
Reviewer: A reader
Thanks very much for your answer. I highly appreciate.
Just one clarification. What do you exactly mean by "col3 is huge"?
Best Regards
Followup November 28, 2009 - 4pm Central time zone:
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"
|