http://asktom.oracle.com/tkyte/unindex
Unindexed Foreign Keys
Having Unindexed foreign keys can be a performance issue. There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.
To read about this issue, please see the Concepts Guide the section on Maintaining Data Integrity/Concurrency Control, Indexes, and Foreign Keys.
The second issue has to do with performance in general of a parent child relationship. Consider that if you have an on delete cascade and have not indexed the child table (eg: EMP is child of DEPT. Delete deptno = 10 should cascade to EMP. If deptno in emp is not indexed -- full table scan). This full scan is probably undesirable and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.
Also consider that for most (not all, most) parent child relationships, we query the objects from the 'master' table to the 'detail' table. The glaring exception to this is a code table (short code to long description). For master/detail relationships, if you do not index the foreign key, a full scan of the child table will result.
So, how do you easily discover if you have unindexed foreign keys in your schema? This script can help. When you run it, it will generate a report such as:
The **** in the first row shows me that I have an unindexed foreign key in the table APPLICATION_INSTANCES. The ok in the second row shows me I have a table EMP with an indexed foreign key.
Having Unindexed foreign keys can be a performance issue. There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.
To read about this issue, please see the Concepts Guide the section on Maintaining Data Integrity/Concurrency Control, Indexes, and Foreign Keys.
The second issue has to do with performance in general of a parent child relationship. Consider that if you have an on delete cascade and have not indexed the child table (eg: EMP is child of DEPT. Delete deptno = 10 should cascade to EMP. If deptno in emp is not indexed -- full table scan). This full scan is probably undesirable and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.
Also consider that for most (not all, most) parent child relationships, we query the objects from the 'master' table to the 'detail' table. The glaring exception to this is a code table (short code to long description). For master/detail relationships, if you do not index the foreign key, a full scan of the child table will result.
So, how do you easily discover if you have unindexed foreign keys in your schema? This script can help. When you run it, it will generate a report such as:
SQL> @unindex
STAT TABLE_NAME COLUMNS COLUMNS
---- ------------------------------ -------------------- --------------------
**** APPLICATION_INSTANCES AI_APP_CODE
ok EMP DEPTNO DEPTNO
The **** in the first row shows me that I have an unindexed foreign key in the table APPLICATION_INSTANCES. The ok in the second row shows me I have a table EMP with an indexed foreign key.
posted by Thomas Kyte at 2:48 PM
46 Comments:
The issue I started bloging http://rafudb.blogspot.com/2009/02/unindex.html
Yep, I've learned my lesson with unindexed foreign keys in terms of both table locks and performance.
Hi,
Thanks for this - seems to be a small formatting problem at the bottom of the post though (when viewed in IE 7 anyway)
Regards
Thanks, That was exactly the issue I was having.
HI tom
good morning.i am dilly visitor of your blogs.
i have a querry regarding
Tkprof report?
i have your book,i know how to create a tkprof report.
But unfortuntely i did not understand the trace file which was stored on the UDUMP,Becoz the output
Can you help me regarding this?
Thanks in advance..
@Santu
the tkprof report is what you need - a human readable thing.
If you want the trace file format, I did write that up in Expert one on one Oracle, Wrox Press - but I would encourage you to avoid reading the raw trace file - everything you need is in the tkprof report, nicely formatted!
Hi Tom,
Does the rule of adding an index to a foreign key constrained column also apply in the case where that column is a partition or sub-partition key? In other words, if the column is a partition key, isn't this effectively also an index on that column?
Thanks in advance,
Mark
@mark
we need an *index*, a partition key is not indexed by us, you would have to index it.
it is the index structure that is relevant.
ops$tkyte%ORA11GR2> create table p ( x number primary key );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> CREATE TABLE c
2 (
3 x number references p(x),
4 y varchar2(30)
5 )
6 PARTITION BY RANGE (x)
7 (
8 PARTITION part1 VALUES LESS THAN (2),
9 PARTITION part2 VALUES LESS THAN (3)
10 )
11 /
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into p values ( 1 );
1 row created.
ops$tkyte%ORA11GR2> insert into p values ( 2 );
1 row created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into c values ( 2, 'hello' );
1 row created.
ops$tkyte%ORA11GR2> declare
2 pragma autonomous_transaction;
3 begin
4 delete from p where x = 1;
5 commit;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
ops$tkyte%ORA11GR2> rollback;
Rollback complete.
ops$tkyte%ORA11GR2> create index c_idx on c(x);
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into c values ( 2, 'hello' );
1 row created.
ops$tkyte%ORA11GR2> declare
2 pragma autonomous_transaction;
3 begin
4 delete from p where x = 1;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
when there is no index, self deadlock as the delete tries to lock the child table. When index exists, the delete from parent and insert into child do not conflict.
This is much simpler now with new LISTAGG function in 11g:
SELECT decode(b.table_name, NULL, '****', 'ok') status, a.table_name, a.columns fk_columns, b.columns index_columns
FROM (SELECT a.table_name,
a.constraint_name,
LISTAGG(a.column_name, ',') within GROUP(ORDER BY a.position) columns
FROM dba_cons_columns a,
dba_constraints b
WHERE a.constraint_name = b.constraint_name
AND b.constraint_type = 'R'
AND a.owner = b.owner
GROUP BY a.table_name, a.constraint_name) a,
(SELECT table_name,
index_name,
LISTAGG(c.column_name, ',') within GROUP(ORDER BY c.column_position) columns
FROM dba_ind_columns c
GROUP BY table_name, index_name) b
WHERE a.table_name = b.table_name(+)
AND b.columns(+) LIKE a.columns || '%'
ORDER BY status, table_name
@darko
indeed it is! thanks
@darko
Your listagg version is reportting false negatives. Its listagg ordering is based only on column position. Here you can find another version of unindex query using listagg http://rafudb.blogspot.com/2009/11/unindex-112.html
@Rafu
Not quite sure what do you want to say with "false negatives"!?
Your SQL doesn’t report concatenated keys and indexes created in wrong order of columns.
ExaMPLE:
Parent_table, PK= col1, col2
Child_table, FK=col1,col2 index= col2,col1
How do I get the parent table?
@Saikumar
query user_constraints
Thanks, that was awesome, resolved my issue.
Hi Tom,
I have a parent table A with PK on column C1. This PK is referenced as FK in multiple tables and some of the child tables DO NOT have an index on FK.
Now if I'm NOT updating the PK column on parent table (A) but updating other columns on table A with PK column in the WHERE clause, is there any reason why Oracle should acquire a lock on child table/s or even scan (FULL or INDEX) the child tables?
I ask this because I'm facing this issue in one of our env's (after upgrading from 9.2 to 11.2) and even INSERTS to parent table hanging.
Thanks in advance.
Nik
@Nik,
No, there is not.
do you have a test case for us? should just take one or two rows in each table...
Once i find the unindexed foreing keys then what i need to do?
sorry for asking a simple question.since i am not aware how to start tuning and how to resolve performance issueds caused through SQLs I have asked such simple question.
Thanks,
imran khan
@Anonymous
if you delete from the parent table OR you update the parent tables primary key, you would want an index on the unindexed foreign key to prevent the full table lock.
When the delete statement was running I didn't see any blocking or blocked sessions nor locks on the table by other users . My actual problem is that one of the user was trying to delete 89 million records and that operation was running for 10 hours. He asked me about the problem but I wasn't sure from where i need to start for troubleshooting it.
Please help..
Thanks!
Oh for heavens sake.
Deleting 89,000,000 records is going to take the better part of your entire life.
That is absurd. Delete is the single most resource intensive statement *ever*.
Why doesn't anyone ever state what "their actual problem is" to begin with????????????
The only solution to this is "do not delete 89,000,000 records, that would be silly"
Use a CREATE TABLE AS SELECT , drop the old table, rename this new table, index it, add constraints/grants etc and be done with it.
Use parallel if appropriate
Use nologging if appropriate
but do not use DML
Thanks a lot for that reply.
one last question , Could you make me your student . I want to learn performance and tuning from you and would like to learn it from scratch . Please let me know how could I start it and what kind of procedure I need to follow to become a good troubleshooter of performance.
Please help as I feel its very essential for me to learn it .
Thanks and Regards,
Imran Khan
@Imran Khan
No, I don't really have time or resources for a "student"
To become proficient at performance and trouble shooting will take you between five and ten years of hands on experience.
I understand that it needs time. I will try to become an expert but atleast guide me which books I can start with and how could I explore it accordingly .
Thanks and Regards,
Imran khan.
Read the concepts guide a few times, until you have it memorized.
http://docs.oracle.com/cd/E11882_01/server.112/e25789/toc.htm
if you do that, you'll know more than most Oracle developers/DBAs
Hi Tom,
I can understand that unindexed foreign keys can cause performance issues for update and deletes.
This may be a silly question, but can unindexed foreign keys cause performance issues for select statements. I am asking pure select statements, think of at time in database when there are no update and deletes going on.
Second question is if I index all unindexed foreign keys then is it a good think of we should only create indexes on those keys which require indexes under our normal operations else when we will have lots of indexes then inserts will be slow.
Please answer.
--Harvey.
Hi tom,
I have indexed foreign keys of child tables. but still deleting one record from parent table takes as much as 15 to 20 minutes. Please let me know where why this is happening ?
@jaya
trace it, probably that parent record had thousands of child records.
in any case, the recursive delete against the child table would be there - you would see it and have the trace for it to determine if it is the culprit or if something else was.
Hi,
In my db one parent record of parent table is having not more than 15 child records.
One more point i would like add is foreign keys in child table are composite indexed.
I did not get your point " the recursive delete against the child table would be there" how to check this ?
Regards,
Jaya
@Jaya
do you know how to trace things?
run this:
create table p as select * from all_objects;
alter table p add constraint p_pk primary key(object_id);
create table c as select object_id, object_name, object_type from p;
insert into c select * from c;
insert into c select * from c;
insert into c select * from c;
insert into c select * from c;
commit;
alter table c add constraint c_fk_pk foreign key(object_id) references p on delete cascade;
create index c_idx on c(object_id);
*/
exec dbms_monitor.session_trace_enable( waits=>true );
delete from p where object_id = 5;
exit
and after you run tkprof on the resulting trace file, you'll find something like this:
delete from p where object_id = 5
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 1 2 7 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 1 2 7 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 931
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE P (cr=5 pr=1 pw=0 time=1860 us)
1 1 1 INDEX UNIQUE SCAN P_PK (cr=2 pr=0 pw=0 time=38 us cost=1 size=13 card=1)(object id 132067)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
delete from "OPS$TKYTE"."C" where "OBJECT_ID" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 3 66 16
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 66 16
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE C (cr=3 pr=0 pw=0 time=436 us)
16 16 16 INDEX RANGE SCAN C_IDX (cr=3 pr=0 pw=0 time=25 us)(object id 132069)
which shows you exactly where you spent your time and what plans were used for your SQL.
Hi Tom,
Trace things are new to me. i never used that.
what is tkprof how to use it ? how to generate trace fileand how to use tkprof on tracefile ?
Regards,
Jaya
@Jaya
http://docs.oracle.com/cd/E11882_01/server.112/e16638/sqltrace.htm#i4640
Tom,
I went through documentation and it was helpful. Please clarify below things.
I am not able to run below statements from normal schema
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SESSION SET SQL_TRACE = FALSE;
How to rectify this ?
I did in alternate way by loggin into sysdba user in test server and below is output I got :
sqlplus "usr/pass@test as sysdba"
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET SQL_TRACE = TRUE;
delete from np_hist.np_port_req_history_dtls where np_txn_id='2095'
ALTER SESSION SET SQL_TRACE = FALSE;
tkprof mnptest_ora_3402.trc myfile.txt explain=np_hist/hist_test@mnptest table=np_hist.plan_table sys=no waits=yes
OUTPUT :
TKPROF: Release 10.2.0.4.0 - Production on Sat Nov 24 15:22:43 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: mnptest_ora_3402.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 2 13 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 2 13 1
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 4 463 11 5
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 4 463 11 5
Misses in library cache during parse: 0
2 user SQL statements in session.
3 internal SQL statements in session.
5 SQL statements in session.
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: mnptest_ora_3402.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
3 internal SQL statements in trace file.
5 SQL statements in trace file.
5 unique SQL statements in trace file.
57 lines in trace file.
7 elapsed seconds in trace file.
The output says 0 statements explained why ? Am i missing something in the process .
Regards
Jaya
@Jaya,
never ever use sysdba for anything other than true DBA things (like an upgrade or patch). sysdba is special, sysdba is magic, do not use it - things work differently for sysdba.
I showed you how to enable tracing and gave you a set of directions
use dbms_monitor as I demonstrated.
run the command you wish to trace.
EXIT sqlplus
then run tkprof against the resulting tracefile
do this AS A NORMAL USER. grant yourself execute on dbms_monitor if necessary.
the other settings are not necessary, they are already set by default.
if you need assistance getting it all going, please consult with your DBA - they'll know how to do this all in their sleep.
Hi Thomas,
I have executed TKPROF as suggested by you. In the trace file there are thousands of lines saying "db file scattered read"
I have pasted output from converted file below. I do not understand where exactly the problem is.
Command executed :
exec dbms_monitor.session_trace_enable( waits=>true );
delete from np_port_req_history_dtls where np_Txn_id='727';
commit;
exit
sample OUTPUT of Converted file:
delete from np_port_req_history_dtls where np_Txn_id='727'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 59.12 289.77 1327084 1335438 16 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 59.13 289.78 1327084 1335438 16 1
post the same exact information I posted please, what you posted is not useful.
query + plan + parse/exec/fetch report.
just like I did above
Hi Thomas,
I can paste more than 4096 character over here. Trace report itself is 8000KB. Any alternate way to post query,trace file and tkprof result file.
If i paste here trace result file, tkprof output file i get error you can not have more than 4096 chars.
@jaya,
please just read what I posted above, see what I posted above, and post *just that*. that is all I'm asking - to do what I've ALREADY DEMONSTRATED to do!
I'll post it again for your reference, this is what I want to see from you for your delete:
delete from p where object_id = 5
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 1 2 7 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 1 2 7 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 931
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE P (cr=5 pr=1 pw=0 time=1860 us)
1 1 1 INDEX UNIQUE SCAN P_PK (cr=2 pr=0 pw=0 time=38 us cost=1 size=13 card=1)(object id 132067)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
delete from "OPS$TKYTE"."C" where "OBJECT_ID" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 3 66 16
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 66 16
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE C (cr=3 pr=0 pw=0 time=436 us)
16 16 16 INDEX RANGE SCAN C_IDX (cr=3 pr=0 pw=0 time=25 us)(object id 132069)
see - just YOUR delete + parse/exec/fetch report + plan
and
the RECURSIVE DELETE + parse/exec/fetch report + plan
that is all. Just do what I did.
delete from np_port_req_history_dtls where np_Txn_id='727'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 59.12 289.77 1327084 1335438 16 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 59.13 289.78 1327084 1335438 16 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58 (NP_HIST)
Rows Row Source Operation
------- ---------------------------------------------------
1 DELETE NP_PORT_REQ_HISTORY_DTLS (cr=1335438 pr=1327084 pw=0 time=289776844 us)
1 INDEX UNIQUE SCAN PK_NP_PORT_REQ_HISTORY_DTLS (cr=3 pr=2 pw=0 time=18963 us)(object id 64445)
Rows Execution Plan
------- ---------------------------------------------------
0 DELETE STATEMENT MODE: ALL_ROWS
1 DELETE OF 'NP_PORT_REQ_HISTORY_DTLS'
1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_NP_PORT_REQ_HISTORY_DTLS' (INDEX (UNIQUE))
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.01 0 0 0 0
Execute 3 59.13 289.77 1327084 1335438 17 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 59.14 289.78 1327084 1335438 17 2
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 6.83 16.77
db file sequential read 7 0.01 0.04
log file sync 3 0.01 0.01
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 15 0.00 0.00 0 0 0 0
Execute 42 59.12 289.73 1327077 1335435 0 0
Fetch 127 0.01 0.25 31 157 0 91
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 184 59.13 289.98 1327108 1335592 0 91
Misses in library cache during parse: 3
Misses in library cache during execute: 3
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 198 0.02 0.55
db file scattered read 83284 0.35 257.90
latch: object queue header operation 1 0.00 0.00
latch: cache buffers chains 1 0.00 0.00
3 user SQL statements in session.
42 internal SQL statements in session.
45 SQL statements in session.
1 statement EXPLAINed in this session.
I'm ready to give up here.
you didn't post what I asked for. Please re-read what I posted, the method I told you to use (further above)
there should be TWO delete statements, the one your type in, the one we do for you.
nothing else. but at least that!
I am confused with "there should be TWO delete statements, the one your type in, the one we do for you"
I followed below steps
exec dbms_monitor.session_trace_enable( waits=>true );
delete from np_port_req_history_dtls where np_Txn_id='727'
commit;
exit.
sigh, i don't know what else to say. sorry.
Tom,
I am sorry for this. One thing i can not paste entire tkprof output here. I hope you are not asking for .trc file got generated before running tkprof.
I tried to follow your sample output but i dont know where i am making mistakes
@jaya
go ahead and email me the TRC file. email is my firstname.lastname@oracle.com
Hi Tom,
Can you please answer the question asked on "SUN JUL 22, 08:50:00 PM EDT"
Thank you.
--Harvey.
@Harvey,
think about it, for a select it is only about access paths.
if you query:
select * from emp, dept where emp.deptno = dept.deptno and dept.deptno = 10;
what would the plan be if emp.deptno (a foreign key) was not indexed and would you like that plan very much? would an index for accessing EMP by deptno make sense in such a case?
I wrote this above in the article:
... Also consider that for most (not all, most) parent child relationships, we query the objects from the 'master' table to the 'detail' table. The glaring exception to this is a code table (short code to long description). For master/detail relationships, if you do not index the foreign key, a full scan of the child table will result.....
so, if this is a child table (like EMP) that is queried from the parent - or if you delete from the parent - or if you update the parent primary key - index it.
otherwise - do not.
POST A COMMENT
<< Home