数据库|mysql教程
SQL不走索引,数据类型不一致导致的SQL不走索引,数据库,
数据库-mysql教程
99娱乐棋牌源码,ubuntu有应用商店,golang爬虫伪造ip,php最基本,seo各种标签lzw
前几天，同事发来一条SQL，说是更新操作的时候执行的很慢，我看了下，数据量也不是很大。再查看执行计划，发现是执行路径错误导致
源码哪里出售,ubuntu有哪些容器,蚂蚁短租爬虫,php 查出条数,SEO这本书lzw
代理cc攻击易语言源码,vscode怎么没有运行结果,ubuntu 能做啥,tomcat启动要好久,国外爬虫卖家,php连接sql语句,seo关键字优化渠道,IT科技资讯新闻类织梦网站模板,dz搬家后插件模板语言lzw
前几天，，同事发来一条SQL，说是更新操作的时候执行的很慢，我看了下，数据量也不是很大。再查看执行计划，发现是执行路径错误导致的，可是为什么会走错误的执行路径呢？统计信息并没有太大的问题。在这里模拟下：
数据准备：
–1.数据准备，表一：
DROP TABLE t_test_1;
create table T_TEST_1
(
ownerVARCHAR2(30),
object_name VARCHAR2(128),
subobject_name VARCHAR2(30),
object_idNUMBER,
data_object_id NUMBER,
object_type VARCHAR2(19),
created DATE,
last_ddl_time DATE,
timestampVARCHAR2(19),
status VARCHAR2(7),
temporaryVARCHAR2(1),
generatedVARCHAR2(1),
secondaryVARCHAR2(1)
);
INSERT INTO T_TEST_1
SELECT * FROM dba_objects;
COMMIT;
UPDATE t_test_1 a SET a.object_type = ‘TABLE’;
COMMIT;
–2.数据准备，表二：
DROP TABLE t_test_2;
create table T_TEST_2
(
ownerVARCHAR2(30),
object_name VARCHAR2(128),
subobject_name VARCHAR2(30),
–这里数据类型和T_TEST_1中object_id的数据类型不一致
object_idVARCHAR2(100),
data_object_id NUMBER,
object_type VARCHAR2(19),
created DATE,
last_ddl_time DATE,
timestampVARCHAR2(19),
status VARCHAR2(7),
temporaryVARCHAR2(1),
generatedVARCHAR2(1),
secondaryVARCHAR2(1),
–这里数据类型和T_TEST_1中object_id的数据类型一致
object_id2NUMBER
);
INSERT INTO T_TEST_2
SELECT a.*, a.object_id object_id2 FROM dba_objects a;
COMMIT;
SELECT * FROM t_test_1;
CREATE INDEX ind_t_test_2_id1 ON t_test_2(object_id) TABLESPACE TBS_LUBINSU_DATA;
CREATE INDEX ind_t_test_2_id2 ON t_test_2(object_id2) TABLESPACE TBS_LUBINSU_DATA;
T_TEST_2表中的object_id和object_id2两个字段都创建了索引
在这里需要更新表1的对象类型字段object_type：
–更新数据
UPDATE t_test_1 a
SET a.object_type =
(SELECT i.object_type FROM t_test_2 i WHERE i.object_id = a.object_id);
UPDATE t_test_1 a
SET a.object_type =
(SELECT i.object_type FROM t_test_2 i WHERE i.object_id2 = a.object_id);
第一条SQL中T_TEST_2的object_id和T_TEST_1中的object_id数据类型是不一致的，而第二条中两个字段数据类型是一致的。
我们来看下执行计划：
SQL> EXPLAIN PLAN FOR
2 UPDATE t_test_1 a
3 SET a.object_type =
4 (SELECT i.object_type FROM t_test_2 i WHERE i.object_id = a.object_id);
Explained
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2933162137
——————————————————————————-
| Id | Operation| Name| Rows | Bytes | Cost (%CPU)| Time|
——————————————————————————-
| 0 | UPDATE STATEMENT || 64296 | 1506K| 137 (3)| 00:00:02 |
| 1 | UPDATE | T_TEST_1 | | | ||
| 2 | TABLE ACCESS FULL| T_TEST_1 | 64296 | 1506K| 137 (3)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| T_TEST_2 | 603 | 37989 | 150 (3)| 00:00:02 |
——————————————————————————-
Predicate Information (identified by operation id):
—————————————————
3 – filter(TO_NUMBER(“I”.”OBJECT_ID”)=:B1)
Note
—–
– dynamic sampling used for this statement
19 rows selected
