ORACLE MYSQL中join 字段类型不同索引失效的情况有哪些

小编给大家分享一下ORACLE MYSQL中join 字段类型不同索引失效的情况有哪些，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

脚本如下：


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,'gaopeng');
insert into testjoin1 values(2,'gaopeng');
insert into testjoin1 values(3,'gaopeng');
insert into testjoin1 values(4,'gaopeng');
insert into testjoin1 values(5,'gaopeng');
insert into testjoin2 values('1','gaopeng');




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")) --虽然TESTJOIN2是被驱动表由于隐士转换索引用不到

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' --虽然TESTJOIN2是被驱动表由于隐士转换索引用不到 possible_keys可以看出
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` `a` straight_join `test`.`testjoin2` `b` where (`test`.`a`.`id` = `test`.`b`.`id`)

以上是“ORACLE MYSQL中join 字段类型不同索引失效的情况有哪些”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！