-2

In the below link it is specified when we should create index on particular column or columns:

https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT1895

  1. The indexed columns are queried frequently and return a small percentage of the total number of rows in the table.

  2. A referential integrity constraint exists on the indexed column or columns. The index is a means to avoid a full table lock that would otherwise be required if you update the parent table primary key, merge into the parent table, or delete from the parent table.

  3. A unique key constraint will be placed on the table and you want to manually specify the index and all index options.

First point is clear, could anyone please explain 2nd and 3rd point?

Wolfgang
  • 515
  • 1
  • 11
  • 41
  • Well Albert Einstein, why don't you look up the term 'referential integrity constraint' as your first point of call. – Nick.Mc Aug 08 '16 at 05:31
  • It's not clear what you are not understanding about the text as given. – Jeffrey Kemp Aug 08 '16 at 07:45
  • @Albert Einstein, I will try to explain the second point. Suppose there is a DEPT table and an EMP table. The DEPT_ID is the PK in the DEPT table and foreign key in the EMP table. Suppose you have not defined an index on the DEPT_ID column in the EMP table, in such a case whenever you will update the DEPT_ID in the DEPT table or delete a row from the DEPT table a full table lock will be acquired on the EMP table. Suppose you have an index on the DEPT_ID column in the EMP table only the concerned rows will be locked in the EMP table. – phonetic_man Aug 08 '16 at 10:07
  • @phonetic_man. Thank a ton, I can understand it fully now. Could you please post your comment in answer section. I would like to select it as answer. – Wolfgang Aug 08 '16 at 13:18

1 Answers1

1

I will try to explain the second point. Suppose there is a DEPT table and an EMP table. The DEPT_ID is the PK in the DEPT table and foreign key in the EMP table. Suppose you have not defined an index on the DEPT_ID column in the EMP table, in such a case whenever you will update the DEPT_ID in the DEPT table or delete a row from the DEPT table a full table lock will be acquired on the EMP table. Suppose you have an index on the DEPT_ID column in the EMP table only the concerned rows will be locked in the EMP table.

As far as the third point goes I would recommend that you read the following links.

https://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1106547

https://asktom.oracle.com/pls/asktom/f?p=100:11:2017491281965674::::P11_QUESTION_ID:36858373078604

phonetic_man
  • 1,088
  • 8
  • 12