Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Indexing Foreign Keys in Oracle

'Index the foreign keys on Oracle' is a basic recommandation for years. But each recent release has improved the behaviour of unindexed foreign keys. It is important to know where we are in the current versions about that 'myth'
by

Franck Pachot

on 24 March 2017

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Indexing Foreign Keys in Oracle

Do we really need to index foreign key ?
Now the real life...
82 sessions waiting
= 82 users blocked
= 82 customers waiting
1414332420
is in hexa:
544D 0004
TM Share
1414332419
is in hexa:
544D 0003
TM Row-X
A delete from the parent blocks all DML on the child
Released immediately (since 9i) after the delete is done
Without an index on the Foreign Key
But delete can take a long time:
has to scan the child table ... as there is no index
itself waiting on other sessions (e.g. on row lock)
NO
index
on FK
full scan
whole table locked 'only'
during the delete duration
long duration
Referential
Integrity

When inserting a child, the parent must exist
Lock the parent row
Lock the parent table
Lock the child table
Lock the index entry
TM Share -> no DML on child
No shared lock -> Contention
TM Share -> no DML on parent
We must lock the row to prevent concurrent DELETE
Lock the parent row
But cannot concurrently insert for the same parent
We can do a SELECT FOR UPDATE
parent row locked in shared mode
Lock the child table
Lock the parent table
but no DML at all allowed on parent
Lock the whole table in Share mode
Pin the index entry
In an index, an insert can go at only one place
We can 'lock' (buffer pin) the index block
index -> high concurrency
No lock on the child without an index
mode 2: Row-Share
mode 3: Row-eXclusive


mode 4: Share
mode 6: Exclusive
Row-X notifies a DML intention
insert, delete, update, merge, select for update
Share prevents concurrent DML intentions
Table OBJECT_ID
index rebuild, referential integrity (w/o index)
'Share' is for reads that block writes
'Exclusive' is for writes
long
lock
Indexed FK - all versions
Which DML on the parent ?
Alternative approaches to maintain referential integrity on a multi-user system without affecting concurreny
Do not use Foreign Keys
Other alternative ?
and manage integrity in the application layer
select from parent where ... for update
lock parent in share mode
lock child in share mode
We cannot release the lock before the end of the transaction
We cannot use the index
We can :
But:
(pin the block and see uncommited transactions)
Why ?
What ?
When ?
How ?
Do we still
need to index
Foreign Keys ?

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=5970280.8
WELCOME
Do we still need to index Foreign Keys ?
Franck Pachot
2012
2012 © Trivadis
TechEvent Presentation
AGENDA
Thank You
Time
for Questions
2012 © Trivadis
TechEvent Presentation
Oracle behaviour before 8.1.7
Oracle 9.0.1 -> RX after the delete
Oracle 9.2.0 -> RX only for cascade delete
Imagine a foreign key
Order -> Customer
Imagine a foreign key
Order -> Country
Oracle 9.2.0.1 -> RS
Oracle 11.1.0.6 -> RX
Oracle 7
Oracle 8i
Oracle 9iR1
Oracle 9iR2
Oracle 11gR1
Oracle 11gR2
without index
with an index
TM Share on parent
TM Share on child
TM Share -> Row X
TM Share -> none
no lock


Row S remaining
Row X remaining
What
is the locking issue with FK ?
Why
Oracle need to lock the table ?
When
do we need index the FK ?
How
to test the locking behaviour ?
Index
the foreign key
or whole child table will be
locked
when doing
delete
on the parent
Let's test it...
ASCII 0x54='T'
ASCII 0x54='M'
Oracle lock modes
alternative 1
alternative 2
alternative 3
alternative 4
when inserting a child
when inserting a child
when inserting a parent
when inserting a parent
delete the parent row
update the referenced columns
on delete cascade
Generally the Primary key
but we can reference any unique constraint
merge even without delete (fixed in 10.2.0.5)
on delete set null (closed as not a bug)
Which kind of index on FK ?
Create DEPT-EMP tables
No index on FK
Delete on parent, not commited
Session 1
Any lock on the child ?
No locks remaining.
then we don't need an index ?
Let's create one anyway...
Session 2
We recreate the EMP table
Now with an index on the FK
Delete on parent, not commited
Session 1
Let's test the same on the child
The foreign key is now indexed
Now we can't acquire table locks :(
Parallel DML hanging...
DDL impossible
The child table is locked in Row-X
With an index on FK
Without index on FK
There is no remaining lock
Session 2
Session 2
We can do all kind of DML and DDL on child
DML still
possible
So, from that demo (11g):
ksqgtl *** TX-000e0010-000038f9 mode=6 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x000007FF1566EF98, ktcdix=2147483647, topxcb=0x000007FF1566EF98
ktcipt(topxcb)=0x0


ksqgtl *** TM-00016ae5-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x000007FF1566EF98, ktcdix=2147483647, topxcb=0x000007FF1566EF98

ksqrcl: TM,16ae5,0
Tracing locks
with Event 10704
mode 2: Row-Share
mode 3: Row-eXclusive


mode 4: Share
mode 6: Exclusive

mode 5: Share + Row-X
Row-X notifies a DML intention
insert, delete, update, merge, select for update
Share prevents concurrent DML
index rebuild, referential integrity (w/o index)
'Share' is for blocking reads
'Exclusive' is for writes
'Row' is for the intention to read/write rows
Oracle lock modes
ksqgtl *** TM-00016ae3-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x000007FF1566EF98, ktcdix=2147483647, topxcb=0x000007FF1566EF98
ktcipt(topxcb)=0x0
ksqgtl *** TX-00080007-00018d4e mode=6 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x000007FF1566EF98, ktcdix=2147483647, topxcb=0x000007FF1566EF98
ktcipt(topxcb)=0x0
TM Row-X
on DEPT (parent)
TM Share
on EMP (child)
Transaction lock
on deleted row
released
immediately
other locks not released before rollback
ksqgtl *** TM-00016ae3-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x000007FF1566EF98, ktcdix=2147483647, topxcb=0x000007FF1566EF98
ktcipt(topxcb)=0x0
ksqgtl *** TM-00016ae5-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x000007FF1566EF98, ktcdix=2147483647, topxcb=0x000007FF1566EF98
ktcipt(topxcb)=0x0
TM Row-X
on DEPT (parent)
TM Row-X
on EMP (child)
Transaction lock
on deleted row
nothing released before the end of transaction
alter session set events '10704 trace name context forever, level 2'
Without Index
With Index
Now with an index on FK
7358 is the OBJECT_ID of the child table
The delete on PRINTERS
hold TM-Share on child (ORDERS)
All DML on ORDERS
is waiting for that delete to complete
'Row' is for the intention to read/write rows,
not blocking among them
mode 5: Share + Row-X
PK
deptno
EMP
DEPT
PK
empno
FK
deptno
No index on FK
Delete from PRINTER ...
Insert into ORDER ...
If there is an uncommited delete, we must wait...
When deleting a parent, no child should exist
If there is an uncommited insert, we must wait...
merge bug fixed
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=223303.1
Starting in version 9.2.0, a Row-SS lock is taken on the parent table for any DML issued against the child table. This will occur with or without an index on the foreign key column of the child table.
Further, there will be a ROW-SS lock taken on the child table for any INSERT/DELETE/UPDATE issued against the parent table when an index on the foreign key column of the child table is defined. There will be a ROW-SX lock taken on the child table for an DELETE issued against the parent table when no index on the foreign key column of the child table is defined and a delete cascade constraint is defined.
In both cases, the lock is held until the transaction is committed or rolled back.

This change in behaviour was introduced in version 9.2.0 to address problems with parallel DML hanging in certain situations. This problem with PDML was addressed in Bug:1657607.
https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=2546492
The reason is to prevent a deadlock/hang for consecutive dmls on tables
with referential constraints of each other.
More information about the Row-S lock introduced in 9.2.0.1
More information about the Row-X lock introduced in 11.1.0.6
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PATCH&id=5909305.8
An ORA-600[kcbgcur_3] can occur when deleting a row from a table that has a primary key constraint, and concurrently rebuilding the index on a foreign key by using an 'alter index rebuild' command.

This fix introduces a notable change of behaviour thus:

=> Without this fix, DML against a child table takes an
SS mode DML lock on the parent table.

=> With this fix, DML against a child table takes an
SX mode DML lock on the parent table.

This fix is enabled by default in 11.1.0.6 onwards.
Row-X is a 11g 'feature'
It was Row-S before
and Row-S appeard in 9i
Core Message
Index for performance reasons first
focus on parent tables that has DML (deletes)
Don't automatically add new index on each foreign key
don't rely on old ideas from Oracle 7 and 8
don't trust even official documentation

In doubt, test on your version, you application
We need to lock the whole table in Share mode
No DML on the child during the delete
Oracle behaviour after 8.1.7
But an insert can go anywhere in a table

fixing a bug
with PDML
fixing a bug
with online rebuild
Common belief
in 11.2
Production database:
When
the parent may have delete
and
child table is large
or
has frequent DML on it
When referenced colums change
But also some bugs
table CHILD (a,b,c) ... references PARENT(a,b)
create index on (a,b)
create index on (b,a)
create index on (a,b,
c
)
create index on (a,b,
c
) compress
create unique index on (a,b,
c
)
create index on (a,b,
upper(c)
)
CHILD is an IOT with primary key (a,b,
c
)

create index on (
c
,b,a)
create index on (a
,c
,b)
create index on (a,
upper(b)
)
create index on (a,b
desc
)
create bitmap index on (a,b)
index can be used to avoid table lock when an index range scan goes directly to the potential inserted row
(no index on FK)
All RDBMS except Oracle
All other RDBMS


Oracle without index

Oracle 7->8i

Oracle 9i->11g

Oracle with index on FK

Oracle 7->11g
Lock the parent row (shared)




lock the parent table (TM Share)

lock the child table (TM Share)



pin the index buffer
Locking the parent
when inserting a child
Select for update
TM Share on whole table
only one row, but exclusively :(
Oracle do not have shared row locks
allows concurrent insert for same parent
but blocks all DML on parent
Locking the child
when deleting a parent
TM Share on whole table
Pin index leaf block buffer
because we don't know where a potential
insert can go on a heap table
because we know where a potential insert can go in an index
Referential integrity implementation
Oracle behaviour before 8.1.7
Oracle 9.0.1 -> RX after the delete
Oracle 9.2.0 -> RX only for cascade delete
Oracle behaviour after 8.1.7
Indexed FK - all versions
Oracle 9.2.0.1 -> RS
Oracle 11.1.0.6 -> RX
never implemented
Or don't create foreign key constraints
and check integrity in application layer ?
You can lock tables, but
can't release locks before the end of transaction
can't use the index leaf block buffer pin
Index IT
to improve delete performance
it improves the delete performance
and requires lighter table locks
without index:
without index:
Goal is to find quickly if there is one child or not
Already created index may already index the foreign keys
especially when child is large and/or has frequent DML
Kernel Service Queue GeT Lock
Kernel Service Queue Release or Convert Lock
so it is a bad idea...
Monitor 'enq: TM' in mode 4 or 5 (Share or SSX)
Improvement
https://supporthtml.oracle.com/epmos/faces/ui/km/BugDisplay.jspx?id=12313173
Lot of possibilities. Don't index FK blindly !
And we thought that we need to index the foreign key to avoid locking the child...
The difference here is that the child table is big and the delete is long.
On the first demo, delete was quick and we did not see the lock that was released immediately
Non-index behavior has improved a lot
Indexed behavior has degrade a bit
Here is non-indexed foreign key issue on production
A delete from the parent
has blocked all DML on child
It is difficult to see locks when they are released quickly, so here is how to trace them.
if you have deletes on the parent
(even if they are not frequent)
Do do we still need to index foreign keys ?
Yes
if you see 'TM Share' locks
Yes
and delete can be long without index
some are not needed at all
some can use already created indexes
Do do we need to index ALL foreign keys ?
No
franck.pachot@trivadis.com
be careful when using a data modeling tool that generates indexes for each foreign keys
they will probably create too many indexes
overhead for all inserts and deletes
overhead for updates on the indexed columns
increase maintenance window if you need to move/rebuild
Full transcript