Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, steven .

Asked: May 02, 2000 - 1:20 pm UTC

Last updated: August 04, 2017 - 7:03 am UTC

Version:

Viewed 10K+ times! This question is

You Asked

Do you have a script that lists all the
foreign keys with no associated indexes?


and Connor said...

SQL> select table_name, constraint_name,
2 cname1 || nvl2(cname2,','||cname2,null) ||
3 nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
4 nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
5 nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
6 columns
7 from ( select b.table_name,
8 b.constraint_name,
9 max(decode( position, 1, column_name, null )) cname1,
10 max(decode( position, 2, column_name, null )) cname2,
11 max(decode( position, 3, column_name, null )) cname3,
12 max(decode( position, 4, column_name, null )) cname4,
13 max(decode( position, 5, column_name, null )) cname5,
14 max(decode( position, 6, column_name, null )) cname6,
15 max(decode( position, 7, column_name, null )) cname7,
16 max(decode( position, 8, column_name, null )) cname8,
17 count(*) col_cnt
18 from (select substr(table_name,1,30) table_name,
19 substr(constraint_name,1,30) constraint_name,
20 substr(column_name,1,30) column_name,
21 position
22 from user_cons_columns ) a,
23 user_constraints b
24 where a.constraint_name = b.constraint_name
25 and b.constraint_type = 'R'
26 group by b.table_name, b.constraint_name
27 ) cons
28 where col_cnt > ALL
29 ( select count(*)
30 from user_ind_columns i
31 where i.table_name = cons.table_name
32 and i.column_name in (cname1, cname2, cname3, cname4,
33 cname5, cname6, cname7, cname8 )
34 and i.column_position <= cons.col_cnt
35 group by i.index_name
36 )
37 /


Rating

  (22 ratings)

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

Comments

unindex.sql

mo, November 08, 2002 - 7:18 am UTC

Tom:

I tried several times to download this file and it just says "Getting file information" and keeps going for ever.

Any other places where i can find this?

Thanks,

Tom Kyte
November 08, 2002 - 8:08 am UTC

loads up just dandy for me. try right click / save link as.

No complaints from anyone else. Try an open systems browser like mozilla or netscape if you are using MS IE. See if it is some mistake in their implementation.

unindex

mo, November 08, 2002 - 8:38 am UTC

Tom:

Thanks, it worked. 

what is the 2nd column in the list represent:

SQL> @unindex

STAT TABLE_NAME                     COLUMNS              COLUMNS
---- ------------------------------ -------------------- --------------------
**** APPLICATION_INSTANCES          AI_APP_CODE
ok   EMP                            DEPTNO               DEPTNO


2.  After generating a report, do you recommend creating indexes on the ****** by

SQL>CREATE INDEX APPLICATION_INSTANCES_AI_APP_CODE_index_01
  ON APPLICATION_INSTANCES.AI_APP_CODE;

3.  Is there a script that automates these or you have todo it one by one.

Thank you, 

Tom Kyte
November 08, 2002 - 10:43 am UTC

1) huh? the second column is the table name...

2) I do not recommend creating indexes unless you have a need. You have my book "expert one on one oracle" -- see page 108 for a discussion

3) you have all of the info you need to turn my unindex.sql into a "create_the_indexes.sql" script.

The Second Column

Mark A. Williams, November 08, 2002 - 10:53 am UTC

Tom:

I believe Mo is wondering about (amongst other things) the second column in the "Columns" pair... You must have encrypted the script, so that no one would ever be able to read it and figure out what it does. :)

- Mark

Tom Kyte
November 08, 2002 - 11:03 am UTC

Ahh -- yes, well, that second column repeated there is the list of indexed columns that would be used.  For example:

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

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table c ( A references p, B int, constraint c_pk primary key (a,b) );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> column columns format a20 word_wrapped
ops$tkyte@ORA817DEV.US.ORACLE.COM> column table_name format a30 word_wrapped
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> 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)) ||
 15               max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
 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)) ||
 28               max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
 29               max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
 30               max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
 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  /

STAT TABLE_NAME                     COLUMNS              COLUMNS
---- ------------------------------ -------------------- --------------------
ok   C                              A                    A, B




<b>see -- the fkey is on A, but the index we'll use is the one on A, B.  I just wanted to print out both.

BTW: if you have my book -- the script I have in there is "better" -- it is "100%" -- it finds all cases.  The above query can have some "false positives"!!

</b>


Loved the comment on "you must have encrypted it".  It does surprise me when people just won't "dig a little bit" to try and figure it out. 

index

mo, November 08, 2002 - 11:27 am UTC

Tom:

Thanks. I will read p108 of your book this weekend.

But it seems you are saying "do not always create index on foreign keys" which contradicts with the article "unindexed Foreign Keys" which seems to favor it for all of them.

Thanks,


Tom Kyte
November 08, 2002 - 12:57 pm UTC

read the book -- i clearly lay out when you NEED the index. Other times you do not.

unindex

mo, January 15, 2003 - 5:42 pm UTC

Tom:

If you have a SQL statement that joins several tables A, B and C and you have indexes on the columns in the WHERE clause, are you still doing a FULL TABLE SCAN if you have one foreign key that is unindexed? So indexes will not take effect in this search at all?

Thank you,

Tom Kyte
January 16, 2003 - 8:10 am UTC

huh? you totally missed the point.

the optimizer will use any index that exists if it makes sense.

indexes + fkeys are for concurrency as well as performance reasons.

index

mo, January 16, 2003 - 10:02 am UTC

Tom:

I will read your chapter 7 to clear up some issues on indexes this weekend But let me confirm with you.

I have DEPT table with 10 records and EMP table with 1 million records. I have 50 employees with hiredate '16-JAN-2003'. I want to query the people that have been hired today.

Case 1: I have an index on emp(hiredate) and on foreign key emp(deptno). I do:

select a.*,b.* from dept a, emp b where a.deptno=b.deptno
and b.hiredate = to_date('16-jan-2003','dd-mon-yyyy');

I should only scan 50 records in the emp table?Correct

2. Case 2: I have an index on emp(hiredate) and no foreign key index. I do the same query, I should be scanning the 1 million records in emp table because foreign key is not indexed?

3. Case 3. I do not have a hiredate index nor FK index. The result will be as case 2. scan 1 million records.

Is this correct?

THank you


Tom Kyte
January 16, 2003 - 10:09 am UTC

case 1: the fkey is not relevant to the discussion. the fkey or lack thereof is not relevant at all.

Assuming the optimizer chooses to use the index on hiredate, yes, an index range scan resulting in 50 table access by rowids to the EMP which would probably just full scan dept after that to join to it since dept is tiny.


case 2: not relevant. who cares of emp.deptno is indexed or not in this case. it makes no difference.

the optimizer would do the SAME THING as case one. I cannot even imagine why it would be "different" or why you would think it would be. Look at the predicate -- think "If I was asked to do this query plan, what would I do" -- the index or lack thereof on emp.deptno doesn't come into play at all.


case 3: full scan of emp -- but only because of the lack of an index on hiredate, emp.deptno -- doesn't come into play at all.

index

mo, January 16, 2003 - 10:27 am UTC

Tom:

As a followup to what you said:

1. It sounds like the optimzier has its own mind to decide whether to use the hiredate index or not? Does this mean it can do a full scan even though a hiredate index is there?

2. You say in your book Chapter 2 P111. "When you query from the parent to the child. you will find that not having the index in place will slow down the queries:
select * from dept, emp where emp.deptno = dept.deptno and dept.deptno = :x; "

This contradicts what you say here that having an index on deptno should not matter.

I thought the whole purpose of indexing foreign keys is to avoid deadlocks and to eliminate slow queries (parent/child).

Thank you,

Tom Kyte
January 16, 2003 - 11:01 am UTC

1) of course it can.

2) no it doesn't, it does not at all. I would like you to think about it for a bit... Look at the queries. Tell me -- what is the DRIVING table in each case. try to *envision* what is going on.


the whole purpose of indexing fkeys is

a) to avoid taking a full table lock on the child table when you DELETE from parent or UPDATE a parent primary key.

b) to avoid really bad performance with ON DELETE CASCADE (a full scan of child for every deleted parent row)

that is all.

unindex

mo, January 31, 2003 - 5:51 pm UTC

Tom:

1. from your book P302, you say that when you perform a join query between parent (dept) and child (emp), you will find that having unindexed foreign key will slow down your query. Here you say it should not matter. I am really confused?

2. Is there a way to check the number of records scanned when the query runs? I was trying to check if TKPROF does that but I did not know how you set it up in SQL*PLUS?

Thank you,


Tom Kyte
January 31, 2003 - 6:18 pm UTC

1) depends on the way you query.

from PARENT to CHILD, you would like an index on the child's foreign key.

from CHILD to PARENT - matters not if the childs fkey is indexed.

consider:

select * from emp, dept where emp.empno = 1234 and emp.deptno = dept.deptno;

tell me -- does it matter if emp(deptno) is indexed? Nah, the query plan should be:

a) goto emp_pk and get the rowid for empno = 1234
b) goto emp using that rowid and get the row
c) using the deptno you just got in b) use dept_pk to find the rowid for the row
in dept
d) goto dept using that rowid

2) if you mean the rows flowing through each step of the query plan -- only tkprof does that. If you mean total rows returned -- autotrace traceonly will do that.

But isn't index-on-foreign-key less important with 9i way of locking the child table?

Nilendu Misra, January 31, 2003 - 6:56 pm UTC

From what I understand, (in versions prior to 9i) while dropping a single row from the parent table - Oracle used to hold an 'exclusive' lock on the entire child table, so there could be NO DML possible on the child table till the deletion on the parent table is over.

This would not be the case however, if the foreign key(s) in the child table is indexed. In that case Oracle will pick up a shared lock on the *index* on the foreign key and *not* on the entire table in exclusive mode. This allowed DML on child table.

In 9i, I read that Oracle does "much less" locking on child table when parent is being DML'ed upon. The lock on the child table in 9i is taken "momentarily and released immediately". So there would be less need to create index on the foreign keys in Oracle 9i for that locking issue.







Tom Kyte
January 31, 2003 - 7:05 pm UTC

there is still a lock -- a blocking lock.  try this:



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

Table created.

ops$tkyte@ORA920> create table c ( x references p );

Table created.

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

1 row created.

ops$tkyte@ORA920> insert into p values ( 2 );

1 row created.

ops$tkyte@ORA920> insert into c values ( 1 );

1 row created.

ops$tkyte@ORA920> commit;

Commit complete.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into c values ( 1 );

1 row created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2          pragma autonomous_transaction;
  3  begin
  4          delete from p where x = 2;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

<b>so, my advice would still be

if you 

a) delete from parent
b) update parents primary key

then you should INDEX the fkeys of the children.  period</b>

 

unindex foreign key

mo, February 01, 2003 - 1:54 pm UTC

Tom:

I did not realize that when you have a join statement you can go from child to parent and parent to child.

Let me clarify this. you said this is going from child to parent:

select * from emp, dept where emp.empno = 1234 and emp.deptno = dept.deptno

would this be going from parent to child and when an indexed foreign key makes a difference?

select * from emp, dept where dept.deptno = 10 and emp.deptno = dept.deptno.

2. What I mean by finding out the number of records scanned is when I do a query

select * from emp, dept where emp.empno = 1234 and emp.deptno = dept.deptno


I want to know how many rows oracle read before giving me my result. did it read one row, 100 rows, before telling me there is one employee.

Can you point me on how do I set tkprof utility and how to read the output. I could not find it in your book?

Thanks,


Tom Kyte
February 01, 2003 - 2:06 pm UTC

forget foreign keys for a minute..

just use common sense here -- you have two tables T1 and T2. can you join t1->t2 and t2->t1? sure, why not?

Forget fkeys, just think "relational database"

fkeys are for data integrity -- period.

joining is joining is joining is joining. period.

Look at the predicate and ask yourself "hey, if I was a computer, what indexes would I like to see in order to make this go fast". fkeys have nothing to do with it.


when you look at your query:

select * from emp, dept where dept.deptno = 10 and emp.deptno = dept.deptno.


just think of it like this:

select * from a, b where b.c = 10 and b.c = a.c;


now, what indexes would you like to see. forget the fkeys, just think "what indexes might be good to have". Maybe one on a(c) for the join, and maybe one on b(c) for the "b.c=10" part.


2) you cannot see the rows in autotrace, you can see the consistent gets (which is the important thing).



you did not read the most important chapter in the book then. Read chapter 10. It is all about tkprof, tracing, statspack....



indexed

mo, February 01, 2003 - 6:14 pm UTC

Tom:

OK I am thinking like you said. What is the differenct between joining EMP --> DEPT or DEPT -->EMP . the end result is the same (columns order may differ). IT is one big table with all rows of both tables. (not sure if an index makes the join faster but from your comment it seems it does). So I need an index on the common column between EMP and DEPT which is deptno.

This says that you always need to index the column you join the two tables with to make the join faster?

2. Now after we create one big fat table as a result of the join comes to play "where emp.empno=1234" or "where dept.deptno=10". In case one you need an index on emp.empno and in case 2 you need an index on dept.deptno which is already there because it is primary key. Am I wrong?

Thanks,

Tom Kyte
February 01, 2003 - 6:39 pm UTC

No, thats not what I'm saying..... In fact, if they are both really big and you are going for most of the rows in both -- an index could be fatal. see

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

for example. Use an index there to join and boy does it take a long time..


I never speak in absolutes ;) wait, I just did -- theres a conundrum for you...

Given my knowledge of EMP and DEPT and that there are just a few emps/deptno -- it would make sense to have deptno indexed in that particular query.


2) correct. not wrong.

index

mo, February 02, 2003 - 1:13 pm UTC

Tom:

Thanks, I think I do understand what is happening.
A join between two tables does not mean that oracle is reading/creating all records in both tables like I thought. If this is the case then what is the use of the index as you said.

Oracle (depending on the query) read the index on the child table and then goes to the index on the parent table to get results if columns were on the index. If not then it uses the rowid of each index to get the record from table.

2. Does not the CBO know when it is fatal to use an index and when not. I thought it automatically knows what is the best thing to do. If you return more than 20% of rows in table then it does a full scan if not it uses the index if it exists?Correct

3. I read your example on Page 301 on why oracle does not store nulls in b-tree index but did not quite understand how you are using this select statement to null values when 90% of rows have same value. Do you have other examples here?

Does this mean if I have a table with 10,000 records and 10 records with x=null and I do
select x from table where x is null;
I have to do a full tbale scan. IT does not make sense?

Tom Kyte
February 02, 2003 - 1:32 pm UTC

2) yes it does -- see that link, it did the right thing.

20% is not exactly the number, see my chapter on indexes in "Expert one on one Oracle" -- look at the colocated/disorganized example. Same table, same data, same indexes, same query -- differently plans (one index, one full scan)



3) on that page -- what I was getting at was this:

...
Previously, I said that the fact that totally NULL index entries are not stored in a B*Tree can be used to your advantage - here is how. Say you have a table with a column that takes on exactly two values. The values are very skewed - say 90% or more of the rows take on one value and 10% or less take on the other value. We can index this column efficiently to gain quick access to the rows that have the 10% or less value. This comes in handy when you would like to use an index to get to the 10% or less rows but want to FULL scan to get to the 90% or more rows and you want to conserve space. The solution is to use a NULL for the 90% value and whatever value you want for the 10% value. Say the table was a 'queue' table of sorts. People inserted rows for another process to work on. The vast majority of the rows in this table are in the processed state, very few in the unprocessed state. I might set up the table like this:
.........

my point was --

a) you have a table with a column with 2 (or few) values
b) the vast majority are of the same value
c) you would like to use an index to get rapid access to the minority

therefore, store NULL in the column for the "vast majority" of records. When you index this column ONLY the values that are NON NULL will be in the index. The index will be small, compact and when used -- would only access a small fraction of the table.

We are purposefully SKIPPING the indexing of the 90% of the rows here.


On that very same page I have an example that shows "select x from table where x is null" may in fact use an index -- confusion on my part here as to how you could ask that particular question given I have the counter example sitting right in front of you already??

If there is a concatenated index on t(x,y) -- and y is NOT NULL, then "where x is null" can use that index.

If there is a single column index on t(x) OR a concatenated index on t(x,y) and y is NULLABLE (as is X), then "where x is null" cannot use either of those indexes (since the totally null entries are not to be found in the index!)





index

mo, February 07, 2003 - 6:33 pm UTC

Tom:

1. I looked at your example of colocated/disorganized example and understood how you look at the clustering of the table relative to the index. But then you say it only applies if your query is a range scan which you do very few times. Does this mean the cluster factor example does not count when you have exact match query or other queries?

2.When you say colocated does it mean onw row is on one block while the other one row can be stored on several blocks. So when oracle read an idnex value it may have to go to several blocks to read it. Or is it always one row = one block if it fits in the block size?

3.you say:
<therefore, store NULL in the column for the "vast majority" of records. When
you index this column ONLY the values that are NON NULL will be in the index.
The index will be small, compact and when used -- would only access a small
fraction of the table.>

Can you show me how you do it in emp table. let us say you have a gender column and there are 100,000 employees of which 90,000 are MALE. Now how can I store nulls instead of 'MALE' just to get to not index the values. Is that what you mean?

4. Also You said a b tree index will have 1 to 1 entry between the index and the table. If nulls are not indexed then you may have a table with 10,000 records and only 2000 index entries?Am i correct?

Thank you,



Tom Kyte
February 08, 2003 - 8:59 am UTC

1) select * from t where c = 'foo'

that could be an index range scan
or a unique index (and we know it will hit A row)

it is a matter of the the number of ROWS you will retrieve via the index.

2) colocated means "together". If the index has entries:

1,2,3,4,4,4,5,5,5,6,7,8,9,9,9

in order -- and the data in the TABLE is physically organized such that all of the 1's are together, then the 2's then the 3's and and so -- the table and index are nicely clustered. I called this "colocated" -- together.

the cluster factor is simply a measure of how sorted the table is with respect to the index in question. If I read 15 rows from one index block (those rows are colocated together -- I'm reading them via a range scan) -- how many different database blocks will I have to read to pick up those 15 rows. Are the rows that are colocated in the INDEX -- also colocated in the TABLE? If so, cluster factor is good. If not, cluster factor is bad.

3) just insert nulls instead of M?

4) from the index to the table there is a 1:1 relationship. From the table to the index there is a 1:(0 or 1) relationship.


which column to index

A reader, June 06, 2003 - 6:22 pm UTC

HI Tom
If I have a table as follows:

create table t
(
jguid raw(16) not null,
cguid raw(16) not null,
constraint j_fk1 foreign key( jguid )
references j_table( jguid ) on delete cascade,
constraint c_fk foreign key( cguid )
references c_table( cguid )
on delete cascade
) monitoring;

This has two foreign keys and since rows in both involved
parent tables can be deleted, I need to create the
foreign keys. My question is - should I create
indexes on (jguid only and cguid only) or just
one index on jguid and cguid.

Assume that this table is always accessed using both jguid and cguid.

Tom Kyte
June 06, 2003 - 8:16 pm UTC

you need jguid on the leading edge of one index and cguid on the leading edge of another.

2 indexes.

Foreign Key and Indexes

A reader, October 31, 2003 - 12:58 pm UTC

Hi Tom,

I am new to my current project. The data model is designed in such a way that most of the static text columns in lookup tables such as "name", "short name", and "description" are stored in a "TEXTS" table and the columns in the tables only contain the corresponding TEXTID. According to the data architect, is to provide multi-language support in the application.

For example:

ORGTYPES(
ORGTYPEID NUMBER,
ORGTYPENAME NUMBER -- the textid of the orgtype name in
)

TEXTS
(
TEXTID NUMBER(14) NOT NULL,
TEXTENGLISH VARCHAR2(128 BYTE) NOT NULL
)

TRANSLATIONS(
TRANSLATIONID NUMBER(14) NOT NULL,
TRANSLATIONTEXT VARCHAR2(128 BYTE) NOT NULL,
LANGUAGEID NUMBER(4) NOT NULL,
TEXTID NUMBER(14) NOT NULL
)

My questions:
1. Is this a good design?
2. Is there any other way to provide multi-language support in Oracle?
3. Right now most of the foreign keys from the lookup tables to TEXTS:TEXTID are not indexed. The texts are static and are rarely changes. Should I index them? This will affect almost 60 lookup tables in the database.

Thanks for your time.


Tom Kyte
November 01, 2003 - 12:12 pm UTC

it is a standard technique for boilerplate text, yes.

Enforce 1 to 1+ relationship

A reader, November 11, 2003 - 1:04 pm UTC

Hi Tom,

Is there any way that I can enforce a (1 to 1+) relationship? That is, for each parent, there must be at least one child record.

For example, I want each order has at least one order line items.

Any idea? Thanks.

Tom Kyte
November 11, 2003 - 2:16 pm UTC

well, it is something that could only be validated at COMMIT time (else you would have a really hard time creating the record at all -- if the parent exists without child, parent cannot exist sort of thing...)


so, yes, we can do it with a little bit of recursive integrity -- that lets us defer the checking until commit time:

ops$tkyte@ORA920PC> create sequence pk;
 
Sequence created.
 
ops$tkyte@ORA920PC> create sequence li_sequence;
 
Sequence created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create table po
  2  ( po_id  int primary key,
  3    data      char(10),
  4    li_seq int not null deferrable initially deferred
  5  )
  6  /
 
Table created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create table li
  2  ( li_id  references po,
  3    li_seq int,
  4    data      char(10),
  5    constraint li_pk primary key(li_id,li_seq)
  6  )
  7  /
 
Table created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> alter table po add constraint li_fk foreign key(po_id,li_seq) references li(li_id,li_seq);
 
Table altered.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> variable x number
ops$tkyte@ORA920PC> insert into po values ( pk.nextval, 'data', null ) returning po_id into :x;
 
1 row created.
 
ops$tkyte@ORA920PC> insert into li values ( pk.currval, li_sequence.nextval, 'data' );
 
1 row created.
 
ops$tkyte@ORA920PC> update po set li_seq = li_sequence.currval where po_id = :x;
 
1 row updated.
 
ops$tkyte@ORA920PC> commit;
 
Commit complete.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert into po values ( pk.nextval, 'data', null ) returning po_id into :x;
 
1 row created.
 
ops$tkyte@ORA920PC> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (OPS$TKYTE.SYS_C007240) violated
 
 
ops$tkyte@ORA920PC> insert into po values ( pk.nextval, 'data', 55 ) returning po_id into :x;
insert into po values ( pk.nextval, 'data', 55 ) returning po_id into :x
*
ERROR at line 1:
ORA-02291: integrity constraint (OPS$TKYTE.LI_FK) violated - parent key not found
 
 
ops$tkyte@ORA920PC> commit;
 
Commit complete.
 

Antoher idea for 1 : 1..n

Michael, November 12, 2003 - 8:30 am UTC

Hello!

Here's antoher idea to implement an 1 : 1..n relationship:

SQL> create table t_dept as
  2  select * from scott.dept where 1=0;

Tabelle wurde angelegt.

SQL> create table t_emp as
  2  select * from scott.emp where 1=0;

Tabelle wurde angelegt.

SQL> alter table t_dept
  2  add (primary key(deptno));

Tabelle wurde geändert.

SQL> alter table t_dept
  2  add (emp_count number(38) default 0 not null
  3       constraint dept_must_have_employees
  4       check (emp_count > 0)
  5       deferrable initially deferred
  6      );

Tabelle wurde geändert.

SQL> alter table t_emp
  2  add (foreign key (deptno) references t_dept(deptno)
  3      );

Tabelle wurde geändert.

SQL> create or replace trigger emp_trig
  2  after insert or delete or update of deptno on t_emp
  3  for each row
  4  begin
  5    if inserting then
  6      update t_dept
  7      set emp_count = emp_count + 1
  8      where deptno = :new.deptno;
  9    elsif updating then
 10      update t_dept
 11      set emp_count = emp_count - 1
 12      where deptno = :old.deptno;
 13      update t_dept
 14      set emp_count = emp_count + 1
 15      where deptno = :old.deptno;
 16    elsif deleting then
 17      update t_dept
 18      set emp_count = emp_count - 1
 19      where deptno = :old.deptno;
 20    end if;
 21  end;
 22  /

Trigger wurde erstellt.

SQL> insert into t_dept(deptno, dname, loc)
  2  values (10, 'DEVELOPMENT', 'NY');

1 Zeile wurde erstellt.

SQL> commit;
commit
*
FEHLER in Zeile 1:
ORA-02091: Transaktion wurde zurückgesetzt
ORA-02290: Verstoß gegen CHECK-Regel (MIKE.DEPT_MUST_HAVE_EMPLOYEES)

SQL> insert into t_dept(deptno, dname, loc)
  2  values (10, 'DEVELOPMENT', 'NY');

1 Zeile wurde erstellt.

SQL> insert into t_emp(ename, deptno)
  2  values ('BLAKE', 10);

1 Zeile wurde erstellt.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

Now with 2 sessions:

Session A:

SQL> insert into t_emp(ename, deptno)
  2  values ('CLARK', 10);

1 Zeile wurde erstellt.

Switch to session B:

SQL> delete t_emp
  2  where ename = 'BLAKE';

Session B blocks now, till Session A commits or rolls back.

Switch to session A:

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

Session B unblocks and commits:

1 Zeile wurde gelöscht.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.
 

Link did not work for me.

Robert Ware, November 29, 2006 - 12:50 pm UTC

Tom,

Has this url changed. </code> http://asktom.oracle.com/~tkyte/unindex/index.html <code>

Thanks,



Tom Kyte
November 30, 2006 - 9:22 am UTC

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


they disabled user directory indexing :(

404

Basil, October 02, 2009 - 1:06 pm UTC

Not Found
The requested URL /~tkyte/unindex/index.html was not found on this server.

Oracle-Application-Server-10g/10.1.2.3.0 Oracle-HTTP-Server Server at wd0362-04.oracle.com Port 80

Strange blocking situation with unidexed FKs

VKOUL, October 16, 2009 - 1:44 am UTC

Pls. go through the following ...

drop table emp;

drop table dept;

CREATE TABLE DEPT
(
  DEPTNO    NUMBER,
  DEPTNAME  VARCHAR2(25),
  LOCATION  VARCHAR2(20),
  CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
)
/

CREATE TABLE EMP
(
  EMPID       NUMBER,
  EMPNAME     VARCHAR2(25),
  SAL         NUMBER(10,2),
  COMMISSION  NUMBER(10,2),
  MANAGER     NUMBER,
  DEPTNO      NUMBER,
  CONSTRAINT PK_EMP PRIMARY KEY (EMPID),
  CONSTRAINT FK_EMP_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO)
)
/

insert into dept values (10, 'Dept 10', 'Location 10');
insert into dept values (20, 'Dept 20', 'Location 20');
insert into dept values (30, 'Dept 30', 'Location 30');
insert into dept values (40, 'Dept 40', 'Location 40');

insert into emp values (4444, 'Emp Name Of 4444', 1000,       500,   4450, 20);
insert into emp values (4445, 'Emp Name Of 4445', 200,        444,   4452, 20);
insert into emp values (4446, 'Emp Name Of 4446', 34444,      56.98, 4452, 20);
insert into emp values (4447, 'Emp Name Of 4447', 5667.89,    343,   4452, 20);
insert into emp values (4449, 'Emp Name Of 4449', 45673,      787,   4455, 10);
insert into emp values (4450, 'Emp Name Of 4450', 4353453.77, 21332, NULL, 10);
insert into emp values (4451, 'Emp Name Of 4451', 435435,     454,   4450, 10);
insert into emp values (4452, 'Emp Name Of 4452', 76676,      5576,  4450, 30);
insert into emp values (4453, 'Emp Name Of 4453', 23232,      67,    4452, 30);
insert into emp values (4454, 'Emp Name Of 4454', 6565,       5465,  4450, 30);
insert into emp values (4455, 'Emp Name Of 4455', 6767,       45,    4454, 30);

ALTER TABLE EMP ADD CONSTRAINT FK_EMP_MANAGER FOREIGN KEY (MANAGER) REFERENCES EMP (EMPID);



**************************************************
Now from a new session 1 do the following (first):

SQL> update dept set deptno=50 where deptno=40;

1 row updated.

SQL >
**************************************************
Now from a new session 2 do the following (second):

SQL> update emp set sal=sal+1 where deptno=10;

3 rows updated.

SQL >
**************************************************
So far so good

**************************************************
Now from the session 2 do the following (first):

SQL> update emp set sal=sal+1 where deptno=10;

3 rows updated.

SQL >
**************************************************
Now from the session 1 do the following (second):

SQL> update dept set deptno=50 where deptno=40;

...

I get blocked here

SQL >
**************************************************

Q1 : In the second scenario I am getting blocked because of non-existence of index on the FK column (deptno), am I right ?

Q2 : Why I am not getting blocked in the first scenario ?

The v$lock shows waiting on REQUEST 4 in the second scenario and in the first scenario there is no blocking ahown in v$lock.

Q3 : Could it be a bug ?

Oracle 9.2.0.8 is my environment.

other version of the query

Donat Callens, May 11, 2010 - 7:57 am UTC

Hereby another query to verify unindexed foreign keys. Its uses connect_by_root instead of max(decode(position to aggregate the column list. This permits to remove the limitation of 16 columns.
It also shows the ON DELETE action associated with the foreign key.
The first two factored subqueries are there only for performance reasons. They ensure the CONNECT BY clause is faster.

WITH my_user_cons_columns AS
   (SELECT table_name,
      constraint_name,
      column_name,
      position
   FROM user_cons_columns
   ),
   my_user_ind_columns AS
   (SELECT table_name,
      index_name,
      column_name,
      column_position
   FROM user_ind_columns
   )
SELECT parent_table  AS "Parent Table",
   parent_columns    AS "Parent Columns",
   parent_constraint AS "Parent Constraint",
   delete_rule       AS "On delete",
   child_constraint  AS "Child Constraint",
   child_table       AS "Child Table",
   child_columns     AS "Unindexed Child Columns"
FROM
   (SELECT table_name                 AS parent_table,
      cons_columns                    AS parent_columns,
      constraint_name                 AS parent_constraint,
      connect_by_root delete_rule     AS delete_rule,
      connect_by_root constraint_name AS child_constraint,
      connect_by_root table_name      AS child_table,
      connect_by_root cons_columns    AS child_columns
   FROM
      (SELECT table_name,
         constraint_name,
         MAX (SUBSTR (sys_connect_by_path (column_name, ','), 2)) cons_columns
      FROM my_user_cons_columns
         START WITH position = 1
         CONNECT BY position = prior position + 1
         AND table_name      = prior table_name
         AND constraint_name = prior constraint_name
      GROUP BY table_name,
         constraint_name
      )
   JOIN user_constraints USING (table_name, constraint_name)
   WHERE level                           = 2
      CONNECT BY nocycle constraint_name = prior r_constraint_name
   )
LEFT JOIN
   (SELECT table_name,
      MAX (SUBSTR (sys_connect_by_path (column_name, ','), 2)) index_columns
   FROM my_user_ind_columns
      START WITH column_position = 1
      CONNECT BY column_position = prior column_position + 1
      AND table_name             = prior table_name
      AND index_name             = prior index_name
   GROUP BY table_name,
      index_name
   )
ON (child_table                             = table_name
   AND instr (index_columns, child_columns) = 1)
WHERE table_name   IS NULL
ORDER BY 1,
   2,
   3;

Missing content

Chuck Talk, July 05, 2017 - 6:47 pm UTC

The content linked herein is no longer available.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.