Shulou(Shulou.com)06/01 Report--

1. Testing method

In the test environment, create two tables, PK1 and FK1, and associate them with the foreign key PK_ID

Check the impact on the database when foreign keys are indexed and indexed

two。 Test environment

As shown below, create two tables, the primary table is PK1, the child table is FK1, the primary key of the primary table is the ID column, the primary key of the child table is also the ID column, and the foreign key is constrained to the ID column whose PK_ID,references is PK1.

Primary table key

Subtable key

Main table column

Subtable column

Data for main and child tables

3. Summary of test results

Test category

Test content

Test result

Foreign key has no index

a. The child table has a delete operation. At the same time, the primary table has delete operation, or update primary key operation.

b. Delete master table record or master child table association query

a. The child table has a delete operation. At the same time, the primary table has delete operation, or update primary key operation. The hang status of the master table operation occurs when the above two conditions are met.

B if the foreign key is not indexed, the master table record or the master child table association query will be deleted and the full table scan of the child table will be carried out.

Foreign keys have indexes

a. The child table has a delete operation. At the same time, the primary table has delete operation, or update primary key operation.

b. Delete master table record or master child table association query

There is no main table hang and related full table scan

(1) the foreign key does not have an index, which will cause table locks in the child table:

a. The child table has a delete operation.

b. The primary table has delete operations, or update primary key operations.

The hang status of the master table operation occurs when the above two conditions are met.

(2) if the foreign key is not indexed, the master table record or the master child table association query will be deleted, and the full table scan of the child table will be carried out.

(3) only when the foreign key creates the index, the operation in (1) will not have a lock or hang state, and the operation in (2) can use the index.

4. Test scenario 4.1. Non-indexed foreign keys

1. When updating a child table, the primary table cannot delete columns or update primary key columns.

two。 When deleting a subtable column, the primary table cannot delete the column or update the primary key column

When associative queries are made at the same time, full table scans are used.

If the master table data is deleted, a full table scan will be performed on the child table.

SQL ID: 6j3nfuym9sr2q Plan Hash: 715003257

Delete from pk1

Where

Id=8

Call count cpu elapsed disk query current rows

--

Parse 1 0.00 0.00 00 00

Execute 1 0.00 0.00 0 1 7 1

Fetch 0 0.00 0.00 00 00

--

Total 2 0.00 0.00 0 1 7 1

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 90

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

0 000 DELETE PK1 (cr=8 pr=0 pw=0 time=2968 us)

1 1 1 INDEX UNIQUE SCAN PK_ID (cr=1 pr=0 pw=0 time=47 us cost=0 size=13 card=1) (object id 76853)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

-- Waited--

SQL*Net message to client 1 0.00 0.00

SQL*Net message from client 1 4.08 4.08

*

SQL ID: 6j3sg0cgf9vvf Plan Hash: 92442012

Select / * + all_rows * / count (1)

From

TEST. "FK1" where "PK_ID" =: 1

Call count cpu elapsed disk query current rows

--

Parse 1 0.00 0.00 00 00

Execute 1 0.00 0.00 00 00

Fetch 1 0.00 0.00 0 7 0 1

--

Total 3 0.00 0.00 0 7 0 1

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS (recursive depth: 1)

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

1 1 1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=604 us)

0 000 TABLE ACCESS FULL FK1 (cr=7 pr=0 pw=0 time=523 us cost=3 size=13 card=1)

* *

4.2. Foreign keys are indexed

After the child table is indexed

After the delete or update operation of the child table, the primary key or delete column of the primary table will not acquire the table lock and can be executed normally.

At the same time, indexes are also used when associating queries.

The deletion of the main table will not perform a full table scan.

SQL ID: 6j3nfuym9sr2q Plan Hash: 715003257

Delete from pk1

Where

Id=8

Call count cpu elapsed disk query current rows

--

Parse 1 0.00 0.00 00 00

Execute 1 0.00 0.00 0 1 6 1

Fetch 0 0.00 0.00 00 00

--

Total 2 0.00 0.00 0 1 6 1

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 90

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

0 000 DELETE PK1 (cr=1 pr=0 pw=0 time=874 us)

1 1 1 INDEX UNIQUE SCAN PK_ID (cr=1 pr=0 pw=0 time=47 us cost=0 size=13 card=1) (object id 76853)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

-- Waited--

SQL*Net message to client 1 0.00 0.00

SQL*Net message from client 1 9.67 9.67

*

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.