一次简单的SQL调整

本文介绍了一次Oracle数据库慢查询优化经历,发现字段类型不匹配导致未使用索引,通过调整查询方式显著提升性能,并讨论了绑定变量的重要性。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

突然在awr上看到了这么一条sql执行了十几秒:

select * from q_mo where MO_MSGID=5825623001725210715;

以为是没建索引,看了下索引在的。不管了,先跑个执行计划再说:

SQL> select * from sms.q_mo where MO_MSGID=5825623001725210715;
 
Elapsed: 00:00:18.60
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2547691741
 
--------------------------------------------------------------------------------
---------------
 
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
Pstart| Pstop |
 
--------------------------------------------------------------------------------
---------------
 
|   0 | SELECT STATEMENT       |      |     1 |    98 | 14305   (1)| 00:02:52 |
      |       |
 
|   1 |  PARTITION RANGE SINGLE|      |     1 |    98 | 14305   (1)| 00:02:52 |
    1 |     1 |
 
|*  2 |   TABLE ACCESS FULL    | Q_MO |     1 |    98 | 14305   (1)| 00:02:52 |
    1 |     1 |
 
--------------------------------------------------------------------------------
---------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(TO_NUMBER("MO_MSGID")=5825623001725210715)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      52437  consistent gets
      50784  physical reads
          0  redo size
       1395  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

看了下,一条简单的sql跑了18秒多,果然没走索引,是全表扫描。再往下看,MO_MSGID根本就是varchar2类型的。没想到oracle优化器是这么转的,我以为类型不同oracle会把后面的数字转成字符串,没想到的是它用to_number函数把MO_MSGID这一列都转成了数值型,这样自然就不会走原来的索引了。

修改以后如下:

SQL> select * from sms.q_mo where MO_MSGID='5825623001725210715';
 
Elapsed: 00:00:00.03
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1889654823
 
--------------------------------------------------------------------------------
---------------------------------------
 
| Id  | Operation                          | Name             | Rows  | Bytes |
Cost (%CPU)| Time     | Pstart| Pstop |
 
--------------------------------------------------------------------------------
---------------------------------------
 
|   0 | SELECT STATEMENT                   |                  |     1 |    98 |
    4   (0)| 00:00:01 |       |       |
 
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| Q_MO             |     1 |    98 |
    4   (0)| 00:00:01 |     1 |     1 |
 
|*  2 |   INDEX RANGE SCAN                 | MO_SMOMSGID_PART |     1 |       |
    3   (0)| 00:00:01 |       |       |
 
--------------------------------------------------------------------------------
---------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("MO_MSGID"='5825623001725210715')
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          2  physical reads
          0  redo size
       1399  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

运行时间一下从18.6秒减少到了0.03秒了,原本逻辑读52437减少到5,物理读50784减少到2。


这里还要说下绑定变量。我们都知道在高并发的OLTP系统中,使用绑定变量还是能够显著提高oracle数据库的运行效率的。但我们这里并发量不算高,和开发说了几次他们也没当回事,我也就没有再坚持。但是在awr的SQL Statistics里面top10的sql语句都是按照单位*执行次数来排列的。这样没有使用绑定变量的sql语句虽然效率很低,但是oracle是把它当做单独的sql来看待的,所以在top10里面不一定能够排的上,那么在后期的调优过程中就很可能被忽略掉。

Oracle SQL中,如果你想一次插入多行数据,可以使用`BULK INSERT`语句或者手动创建一个包含多行数据的临时表或者集合类型,然后一次性插入。这里分别介绍这两种方法: ### 1. BULK INSERT 对于大量数据,`BULK INSERT`效率较高,适合从外部文件(如CSV、TXT等)导入数据。假设有一个名为`your_table`的表和一个外部文件`data_file.csv`,你可以这样做: ```sql BULK INSERT your_table FROM 'data_file.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; ``` 注意调整`FIELDS TERMINATED BY`和`LINES TERMINATED BY`以匹配你的文件格式。如果有头部信息,可以使用`ROW FORMAT DELIMITED`选项。 ### 2. 使用临时表或集合类型 如果数据是在内存中准备好的,可以先创建一个临时表或者使用PL/SQL的`DBMS_METADATA`包来动态创建表,然后插入数据: ```sql -- 创建临时表 CREATE GLOBAL TEMPORARY TABLE temp_table ( column1 datatype, column2 datatype, ... ) ON COMMIT PRESERVE ROWS; -- 插入多行数据 BEGIN FOR i IN 1..count LOOP INSERT INTO temp_table VALUES (value1, value2, ...); END LOOP; END; / -- 将数据插入目标表 INSERT INTO your_table SELECT * FROM temp_table; COMMIT; -- 确保数据持久化 ``` 或者使用PL/SQL的`BULK COLLECT`: ```sql DECLARE TYPE my_type IS TABLE OF your_table%ROWTYPE INDEX BY PLS_INTEGER; l_table my_type; BEGIN FOR i IN 1..count LOOP l_table(i) := your_table_row_data(i); END LOOP; INSERT ALL INTO your_table SELECT * FROM TABLE(l_table); ... INTO another_table SELECT * FROM TABLE(l_table); ... SELECT COUNT(*) INTO v_rows_inserted FROM your_table; END; / ```
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值