Comparison between Oracle and MySQL when deleting fields _ MySQL

When Oracle and MySQL delete fields, we do not know how many people know clearly. in Oracle, if a composite index is used, assume that the index (a, B, c) has three fields, how does Oracle handle a field deleted (including unused. Similarly, what should Oracle do if it is a constraint?

Using oracle as an example, I made a comparison with mysql to see how mysql handles this problem. I will not discuss who is better or worse, but I hope you will know the differences and details.

Let's first look at the Oracle example. we create a table and then create a constraint on it to create an index:

SQL 10G> create table test (a int, B int, c int); Table created. SQL 10G> alter table test add constraint pk_test primary key (a, B); Table altered. SQL 10G> create index ind_test on test (B, c); Index created.

Then, check the created constraints and indexes.

SQL 10G> select t. constraint_name, c. constraint_type, t. column_name, t. position, c. status, c. validated 2 from user_cons_columns t, user_constraints c 3 where c. constraint_name = t. constraint_name 4 and c. constraint_type! = 'C' 5 and t. table_name = 'test' 6 order by constraint_name, position; CONSTRAINT_NAME C COLUMN_NAME position status validated ----------------------------- ---------- ------------- PK_TEST p a 1 enabled validated PK_TEST p B 2 enabled validated SQL 10G> select t. index_name, t. column_name, t. column_position, I. status 2 from user_ind_columns t, user_indexes I 3 where t. index_name = I. index_name 4 and t. table_name = 'test' 5 * order by index_name, column_position INDEX_NAME COLUMN_NAME COLUMN_POSITION STATUS -------------- ------------ --------------- -------- IND_TEST B 1 VALID IND_TEST C 2 VALID

Now, we first delete the fields on the index. In fact, there is no physical deletion, but it is set to unused:

SQL 10G> ALTER TABLE test SET UNUSED (c); Table altered. SQL 10G> select t. index_name, t. column_name, t. column_position, I. status 2 from user_ind_columns t, user_indexes I 3 where t. index_name = I. index_name 4 and t. table_name = 'test' 5 order by index_name, column_position; no rows selected

If something is found, the index is also deleted. What about deleting the fields on the constraint?

SQL 10G> alter table test set unused (B); alter table test set unused (B) * ERROR at line 1: ORA-12991: column is referenced in a multi-column constraint SQL 10G> alter table test set unused (B) CASCADE CONSTRAINTS; Table altered. SQL 10G> select t. constraint_name, c. constraint_type, t. column_name, t. position, c. status, c. validated 2 from user_cons_columns t, user_constraints c 3 where c. constraint_name = t. c Onstraint_name 4 and c. constraint_type! = 'C' 5 and t. table_name = 'test' 6 order by constraint_name, position; no rows selected

As we can see, a normal deletion will report an error. If cascade is specified, the corresponding constraints will also be deleted.

After reading the Oracle processing process, we can see that mysql is doing this to delete the field on the index.

Mysql> create table test (a int, B int, c int); Query OK, 0 rows affected (0.72 sec) mysql> alter table test add primary key (a, B ); query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index ind_test on test (B, c); Query OK, 0 rows affected (0.32 sec) Records: 0 Duplicates: 0 Warnings: 0

Perform the same operation. first delete a field in the composite index and then delete a field in the constraint.

Mysql> alter table test drop c; Query OK, 0 rows affected (0.58 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from test; + ------- + ------------ + ---------- + -------------- + Upper + ----------- + | Table | Non_unique | Key_name | primary | Column_name | Collation | + ------- + ------------ + ---------- + Upper + ----------- + | test | 0 | PRIMARY | 1 | a | A | test | 0 | PRIMARY | 2 | B | A | test | 1 | ind_test | 1 | B | A | + ------- + ------------ + ---------- + -------------- + ------------- + ----------- + 3 rows in set (0.06 sec) mysql> alter table test drop B; Query OK, 0 rows affected (0.28 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from test; + ------- + ------------ + ---------- + -------------- + Upper + ----------- + | Table | Non_unique | Key_name | primary | Column_name | Collation | + ------- + ------------ + ---------- + Upper + ----------- + | test | 0 | PRIMARY | 1 | a | A | + ------- + ------------ + ---------- + -------------- + ------------- + ----------- + 1 row in set (0.03 sec)

We can see that the mysql processing method is different. mysql only removes the field from the index, rather than deleting the index.

The purpose of this article is to remind you that when you delete columns, including unused, you must be careful whether a composite index contains this field. otherwise, accidentally deleting an index may cause a large error.