Re: One Conceptual Question!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 01 Aug 1998 14:17:08 GMT
Message-ID: <35c42175.6964033@192.86.155.100>

A copy of this was sent to violin.hsiao_at_mail.pouchen.com.tw (Violin) (if that email address didn't require changing) On Fri, 31 Jul 1998 04:21:57 GMT, you wrote:

>Hello,
>I have a question,maybe you know the answer.
>I know if I create primary key(PK_TABLE1) of TABLE1,
>Oracle will create a unique index(PK_TABLE1) for the table.
>Alter table TABLE1 add constraint FK_TABLE1 foreign key (COL1)
>reference TABLE2 (COL1),
>I couldn't see any index object for this foreign key.
>But if I create a index named FK_TABLE1 with COL1 of TABLE1,
>Will it be faster in searching the TABLE1 references TABLE2??
>Could anyone give me some suggestion?
>Please Cc to : violin.hsiao_at_mail.pouchen.com.tw,Thank you :)

creating a foreign key does not create an index.

see chapter 6 (v7 docs) or chapter 9 (v8 docs) in the application developers guide "Concurrency Control, Indexes, and Foreign Keys" for locking considerations with unindexed fkeys.

One reason indexes are not created is because they are not needed to enforce the constraint (whereas a unique index is the most efficient way to enforce the uniqueness of a primary key). Another reason is that in many cases, the foreign key is already on the leading edge of some index and indexing the foreign key would just lead to a unnecessary index that would slow things down (inserts/updates/deletes anyway). For example:

create table DOCUMENT ( doc_id int primary key, .... ); create table DOCUMENT_VERSION (doc_id int references document(doc_id),

                               version int, 
                               ....
                               constraint document_version_pk primary key
                              (doc_id, version) );

So, doc_id in document_version is already on the leading edge of an index, indexing just doc_id in that table would be a waste.

Yes, in most cases (not every but most) it makes sense to ensure your foreign key columns are indexed (or on the leading edge of an index as above). For example, in the above you might have a query like:

  select * from
    document, document_version
   where document.doc_id = document_version.doc_id      and document.title = 'Some Title';

if document_version didn't have an index on doc_id, this would force a full scan on document_version.

If you want, here is a script that lists all fkeys and marks those that do not have an index (or are not part of an index already)

---

column columns format a20 word_wrapped
column table_name format a30 word_wrapped

select decode( b.table_name, NULL, '****', 'ok' ) Status,

           a.table_name, a.columns, b.columns
from
( select a.table_name, a.constraint_name,

	     max(decode(position, 1,     column_name,NULL)) || 
	     max(decode(position, 2,', '||column_name,NULL)) || 
	     max(decode(position, 3,', '||column_name,NULL)) || 
	     max(decode(position, 4,', '||column_name,NULL)) || 
	     max(decode(position, 5,', '||column_name,NULL)) || 
	     max(decode(position, 6,', '||column_name,NULL)) || 
	     max(decode(position, 7,', '||column_name,NULL)) || 
	     max(decode(position, 8,', '||column_name,NULL)) || 
	     max(decode(position, 9,', '||column_name,NULL)) || 
	     max(decode(position,10,', '||column_name,NULL)) || 
	     max(decode(position,11,', '||column_name,NULL)) || 
	     max(decode(position,12,', '||column_name,NULL)) || 
	     max(decode(position,13,', '||column_name,NULL)) || 
	     max(decode(position,14,', '||column_name,NULL)) || 
	     max(decode(position,15,', '||column_name,NULL)) || 
	     max(decode(position,16,', '||column_name,NULL)) columns

    from user_cons_columns a, user_constraints b    where a.constraint_name = b.constraint_name      and b.constraint_type = 'R'
   group by a.table_name, a.constraint_name ) a, ( select table_name, index_name,

	     max(decode(column_position, 1,     column_name,NULL)) || 
	     max(decode(column_position, 2,', '||column_name,NULL)) || 
	     max(decode(column_position, 3,', '||column_name,NULL)) || 
	     max(decode(column_position, 4,', '||column_name,NULL)) || 
	     max(decode(column_position, 5,', '||column_name,NULL)) || 
	     max(decode(column_position, 6,', '||column_name,NULL)) || 
	     max(decode(column_position, 7,', '||column_name,NULL)) || 
	     max(decode(column_position, 8,', '||column_name,NULL)) || 
	     max(decode(column_position, 9,', '||column_name,NULL)) || 
	     max(decode(column_position,10,', '||column_name,NULL)) || 
	     max(decode(column_position,11,', '||column_name,NULL)) || 
	     max(decode(column_position,12,', '||column_name,NULL)) || 
	     max(decode(column_position,13,', '||column_name,NULL)) || 
	     max(decode(column_position,14,', '||column_name,NULL)) || 
	     max(decode(column_position,15,', '||column_name,NULL)) || 
	     max(decode(column_position,16,', '||column_name,NULL)) columns

    from user_ind_columns
   group by table_name, index_name ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%' /

---

Hope this helps.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Aug 01 1998 - 09:17:08 CDT