ORACLE foreign key and lock, ORACLE

ORACLE foreign key and lock, ORACLE

In oracle, if the foreign key is not indexed, modification to the parent table will cause the child table to be added with the full table lock. There are two scenarios:

1. Delete the rows in the parent table. If no index exists on the foreign key, the sub-table will be added with the full table lock.

2. update the primary key of the parent table (according to the principles of the relational database, updating the primary key is a huge "taboo", so this is generally not the case). If there is no index on the foreign key, this will cause the sub-table to be added with the full table lock.

Although these full table locks are short-lived in Oracle9i and later versions, they only exist during DML operations, rather than during the entire transaction. However, even so, these full table locks may (and will indeed) cause serious locking problems.

You can use the following statement to check whether there are foreign keys not indexed:

SELECT TABLE_NAME,       CONSTRAINT_NAME,       CNAME1 || NVL2(CNAME2, ', ' || CNAME2, NULL) ||       NVL2(CNAME3, ', ' || CNAME3, NULL) ||       NVL2(CNAME4, ', ' || CNAME4, NULL) ||       NVL2(CNAME5, ', ' || CNAME5, NULL) ||       NVL2(CNAME6, ', ' || CNAME6, NULL) ||       NVL2(CNAME7, ', ' || CNAME7, NULL) ||       NVL2(CNAME8, ', ' || CNAME8, NULL) COLUMNS  FROM (SELECT B.TABLE_NAME,               B.CONSTRAINT_NAME,               MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,               MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,               MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,               MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,               MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,               MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,               MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,               MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,               COUNT(*) COL_CNT          FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,                       SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,                       SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,                       POSITION                  FROM USER_CONS_COLUMNS) A,               USER_CONSTRAINTS B         WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME           AND B.CONSTRAINT_TYPE = 'R'         GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS WHERE COL_CNT > ALL (SELECT COUNT(*)          FROM USER_IND_COLUMNS I         WHERE I.TABLE_NAME = CONS.TABLE_NAME           AND I.COLUMN_NAME IN (CNAME1,                                 CNAME2,                                 CNAME3,                                 CNAME4,                                 CNAME5,                                 CNAME6,                                 CNAME7,                                 CNAME8)           AND I.COLUMN_POSITION <= CONS.COL_CNT         GROUP BY I.INDEX_NAME)

This script will process a maximum of eight foreign key constraints (If your foreign key has more columns, you may have to reconsider your design )!

In addition to the full table lock, foreign keys that are not indexed may also cause problems in the following situations:
1. If there is on delete cascade and no index is added to the sub-table: for example, if emp is a dept sub-table, delete deptno = 10 should be cascade (cascade) to emp. If deptno in emp has no index, a full table scan is performed on emp when each row in the dept table is deleted. This full table scan may be unnecessary. If multiple rows are deleted from the parent table, the scanner will scan the child table every time a row is deleted from the parent table.

2. query a sub-table from the parent table: Consider the emp/dept example here. Using deptno to query emp tables is quite common. If you run the following queries frequently, you will find that the query speed slows down if no index is available:

select *     from dept, emp    where emp.deptno=dept.deptno       and dept.deptno=:x;

Therefore, pay attention to whether external keys and indexes are required for special tables to prevent Oracle data from being "too locked.

 

-- Refer to Oracle expert Advanced Programming

ORACLE foreign key constraints required

The main points are as follows: I have been fighting for this issue with others more than once. 1. If your program is more rigorous, you may also encounter bugs. If you decide on your own, you should leave it to the database for judgment. It is fast and good. Most people do not consider concurrency issues. Once you have considered it, you have to manually lock it, and the efficiency is very low. Data may bypass your application and enter the database. When a foreign key is determined to be apportioned to the transaction level, the overhead can be ignored, and the user is not aware of it at all. If you want to import data in batches, You can temporarily block the foreign key and use the NOVALIDATE option to quickly restore it, provided that your data is clean. The foreign key constraint is designed to prevent you from messing around. This is a protection for you. Is driving a seat belt troublesome? Sometimes it can save your life. In addition, foreign keys provide important information to CBO to generate an optimal plan. Inverse: qingyun. The main points are as follows. program Logic, integrity, I will make a rigorous judgment in the storage process or package; 2. performance problems, which is the key reason I do not like most. For example, a business flow meter frequently inserts data. If the table contains three foreign keys, insert one at a time, you must search for the three tables corresponding to the three foreign keys to determine whether there is any corresponding data. If the three tables are large, therefore, the Judgment time of these three tables is very common. Although the field of the associated table pointed to by the foreign key must be an index, I think many times, this judgment was originally controlled in the program. Another judgment through the foreign key is to reduce performance. In fact, it doesn't matter if the judgment is not made in some places, but the foreign key is used, it is necessary to determine the time. No matter how quickly oracle optimizes the foreign key's data retrieval speed, it is always a small consumption; 3. maintenance is troublesome. Software of many companies is customized. This kind of customization is relatively random. During project development and implementation, it is necessary to repair and supplement tables frequently; there are bugs or other situations in the business logic, which require frequent manual data maintenance and complicated foreign key Association, which is very troublesome.; 4. the foreign key determines the sequence of data generation between the two tables. The most common is the document Master/Slave table. Sometimes, when generating the document, it is Mr Ming and then generate the master table; if the foreign key is pinned, this cannot be implemented. Of course, some key businesses really need foreign keys. For example, the database has a total of 100 tables, there may be 300 foreign keys; I am dizzy, too dognistic; if I want to build 300 indexes, I would like to, because to improve operational efficiency, while foreign keys are only rigorous detection, database operation efficiency is only reduced, and there is no possibility of improvement. In fact, this is just a problem of design habits. If you are interested, just talk about your habits. There is another voice that has a unique opinion. The Foreign keys are exposed for your reference, and the program is closed. It is also a tested program and a foreign key constraint, the possibility of constraint failure caused by human factors is obvious, so it is too tragic to use foreign keys because of conflicts with the high availability of the system. Newkid is working on securities software. But he hasn't started yet. However, our company is engaged in securities software, and there is no foreign key in the table. Hahaha

How to use oracle Foreign keys?

1. Foreign keys are a type of constraint. The so-called constraint, as the name suggests, restricts your operations in the database. What operations do you have on the database? It is nothing more than adding, deleting, modifying, and querying the database. The foreign key is used to increase the data degree more reasonably with the help of the database.

2. Therefore, the foreign key is only a helper and cannot be used as you said [whether B. NAME is automatically displayed or not ].

3. The foreign key is used to avoid the following situations.
For example
The class information table stores the class information table.
The student table stores student information, including class information.

At this time, the class information stored in the "Student table" should exist in the "class info table". If you manually control the information, you will inevitably miss it, with foreign keys, the database can be automatically controlled. When the data inserted into the student table does not exist in the class information table, the database cannot be inserted.

---
The above is helpful to you.