Shulou(Shulou.com)05/31 Report--

Editor to share with you about the invalidation of different indexes of join field types in ORACLE MYSQL, I believe most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

The script is as follows:

Mysql:

Drop table testjoin1

Drop table testjoin2

Create table testjoin1 (id int, name varchar (20)

Create table testjoin2 (id varchar (20), name varchar (20), key (id))

Oracle:

Drop table testjoin1

Drop table testjoin2

Create table testjoin1 (id int,name varchar2 (20))

Create table testjoin2 (id varchar (20), name varchar2 (20))

Create index test_id_2 on testjoin2 (id)

Insert into testjoin1 values (1 recording gaopeng')

Insert into testjoin1 values (2 recording gaopeng')

Insert into testjoin1 values (3 recorder gaopeng')

Insert into testjoin1 values (4)

Insert into testjoin1 values (5 minutes gaopeng')

Insert into testjoin2 values ('1m.m.gaopeng`)

Hermit Transformation in ORACLE

SQL > select / * + use_nl (a b) ordered * / * from testjoin1 a join testjoin2 b on a.id=b.id

ID NAME ID NAME

--

1 gaopeng 1 gaopeng

Execution Plan

Plan hash value: 2498279186

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

| | 0 | SELECT STATEMENT | | 1 | 49 | 5 (0) | 00:00:01 |

| | 1 | NESTED LOOPS | | 1 | 49 | 5 (0) | 00:00:01 |

| | 2 | TABLE ACCESS FULL | TESTJOIN1 | 5 | 125 | 2 (0) | 00:00:01 |

| | * 3 | TABLE ACCESS FULL | TESTJOIN2 | 1 | 24 | 1 (0) | 00:00:01 |

Predicate Information (identified by operation id):

3-filter ("A". "ID" = TO_NUMBER ("B". "ID"))-although TESTJOIN2 is a driven table because the hermit conversion index is not needed

Mysql > explain select * from testjoin1 a Straight_JOIN testjoin2 b on a.id=b.id

+- -- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+- -- +

| | 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |

| | 1 | SIMPLE | b | NULL | ALL | id | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) |

+- -- +

2 rows in set, 3 warnings (0.00 sec)

Warning (Code 1739): Cannot use ref access on index 'id' due to type or collation conversion on field' id'-although TESTJOIN2 is a driven table because the hermit conversion index does not use possible_keys, you can see

Warning (Code 1739): Cannot use range access on index 'id' due to type or collation conversion on field' id'

Note (Code 1003): / * select#1 * / select `test`.`a`.`id`AS `id`, `test`.`a`.`name`AS `name`, `test`.`b`.`id`AS `id`, `test`.`b`.`name`AS `name`from `test`.`testjoin1``straight_join `test`.`testjoin2`b` where (`test`.`a`.`id` = `test`b`id`)

The above is all the contents of this article entitled "what are the invalidation of different indexes of join field types in ORACLE MYSQL?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.