ORACLE MYSQL中join 字段类型不同索引失效的状况有哪些
发布时间：2021-12-26 13:05:07 所属栏目：MySql教程 来源：互联网
导读：小编给大家分享一下ORACLE MYSQL中join 字段类型不同索引失效的情况有哪些，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ 脚本如下： mysql: drop table testjoin1; drop t
|
小编给大家分享一下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 字段类型不同索引失效的情况有哪些”这篇文章的所有内容，感谢各位的阅读！ （编辑：91站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
