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

A row-value expression, also known as a row-value constructor, can be seen in many SQL usage scenarios, usually through in, but what's the difference between MySQL and Oracle. Let's do a few simple tests to illustrate.

The difference between the version 5.7 of MySQL 5.6 and Magi

First of all, let's take a look at the differences in MySQL 5.6,5.7, which is worth talking about in this respect.

We create a table users and then simulate the differences between different versions of the same statement.

In MySQL version 5. 6.

Create table users (

Userid int (11) unsigned not null

Username varchar (64) default null

Primary key (userid)

Key (username)

) engine=innodb default charset=UTF8; inserts 200000 data.

Delimiter $$

Drop procedure if exists proc_auto_insertdata$$

Create procedure proc_auto_insertdata ()

Begin

Declare

Init_data integer default 1

While init_dataexplain select userid,username from users where (userid,username) in ((1) minicame user1'), (2) pencil user2')\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: users

Type: index

Possible_keys: NULL

Key: username

Key_len: 195

Ref: NULL

Rows: 19762

Extra: Using where; Using index

1 row in set (0.00 sec) We can use extended to get more detailed information, but we don't see much information here.

Explain extended select userid,username from users where (userid,username) in ((1) minicame user1'), (2) pencil user2')\ G

> show warnings

| | Note | 1003 | / * select#1 * / select `test`.`users`.`userid`AS `userid`, `test`.`users`username`AS `username`from `test`.`users`where ((`test`.`users`.`username`, `test`.users`.`username`) in (1m) User1'), (2YJ) User2') in MySQL 5.7. |

We create the table users in the same way, insert the data, and you can see that the scanning method of range is used and the index is used.

> explain select userid,username from users where (userid,username) in ((1) minicame user1'), (2) minicame user2')\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: users

Partitions: NULL

Type: range

Possible_keys: PRIMARY,username,idx_users

Key: username

Key_len: 199

Ref: NULL

Rows: 2

Filtered: 100.00

Extra: Using where; Using index

1 row in set, 3 warnings (0.00 sec) information obtained by using extended.

| | Warning | 1739 | Cannot use ref access on index 'username' due to type or collation conversion on field' username' |

| | Warning | 1739 | Cannot use ref access on index 'username' due to type or collation conversion on field' username' Note | 1003 | / * select#1 * / select `test`.`users`.`userid`AS `userid`, `test`.`users`.`username`AS `username`from `test`.`users`userid` ((`test`.`users`.userid`, `test`.users`.`username`) in (1useruser1')), ((2useruser2')) can be clearly improved in MySQL 5.7 through the above method. |

Row value expressions in Oracle

In Oracle, we directly use the environment of 11gR2 for testing.

Create the table users and insert the data.

Create table users (

Userid number primary key

Username varchar2 (64) default null

Create a few additional indexes to see which one will be used in the end

Create index idx_username on users (username)

Create index idx_usres on users (userid,username); insert data to collect statistics

Insert into users select level userid,'user' | | level username from dual connect by levelnull,tabname= > 'USERS',cascade= > true); we use explain plan for to get the execution plan. It is obvious that the compound index is used, and the statement is transformed by the following red predicate information.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

-

Plan hash value: 1425496436

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 2 | 26 | 2 (0) | 00:00:01 |

| | 1 | INLIST ITERATOR | | |

| | * 2 | INDEX RANGE SCAN | IDX_USRES | 2 | 26 | 2 (0) | 00:00:01 |

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

-

2-access (("USERID" = 1 AND "USERNAME" = 'user1' OR "USERID" = 2 AND

("USERNAME" = 'user2')) it can be seen that Oracle has been implemented and can be used to compare learning through these aspects.

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.