【SQL】Oracle数据库sql优化小技巧索引不管用怎么办01时间：2022-10-25 18:06:52> 本节两个小案例，一个是求最小值和最大值，另一个是找出空值，不走索引，如何提高查询效率 #### 先看看求最大值和最小值 ``` sql SQL> select min(object_id),max(object_id) from t1; MIN(OBJECT_ID) MAX(OBJECT_ID) -------------- -------------- 2 90852 Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- Plan hash value: 3724264953 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 347 (1)| 00:00:05 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | TABLE ACCESS FULL| T1 | 89081 | 1130K| 347 (1)| 00:00:05 | --------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1247 consistent gets 0 physical reads 0 redo size 613 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed ``` 如上例所示，求表t1的最大值和最小值，t1表object_id有索引，但是数据库没有选择，最后选择全表扫描，这样效率比较慢。主要原因是执行计划或者说是数据库本身无法在索引扫描方式决定，无法同时从两端开始扫描（或者说是独立完成最大最小值扫描）。那么我们可以分开求值，如下所示，4个逻辑读即可，虽然拆分成两个子查询，但因为sql语句走索引，提高了效率： ``` sql SQL> select (select min(object_id) from t1) min_id,(select max(object_id) from t1) max_id from dual; MIN_ID MAX_ID ---------- ---------- 2 90852 Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 1709123826 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| T1_IDX_ID | 1 | 13 | 2 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 13 | | | | 4 | INDEX FULL SCAN (MIN/MAX)| T1_IDX_ID | 1 | 13 | 2 (0)| 00:00:01 | | 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 597 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed ``` #### null值不走索引 t2表查找object_id为null的，无法走索引，单列索引，我们都知道不存储null值，那么复合索引，如果符合索引中所有列均为null值，也不会存储，索引该效果也不行，怎么办？ 先看看原始执行计划： ``` sql SQL> select object_id from t2 where object_id is null; OBJECT_ID ---------- Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- Plan hash value: 1513984157 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 104 | 141 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| T2 | 8 | 104 | 141 (1)| 00:00:02 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID" IS NULL) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 6 recursive calls 0 db block gets 517 consistent gets 0 physical reads 0 redo size 587 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4 rows processed ``` 根据索引特性及复合索引，我们可以创建一个复合索引并存储一个常量，这样所有值都能存入。如下所示： ``` sql --创建索引语句 SQL> create index t2_id_1 on t2(object_id,1); Index created. Elapsed: 00:00:00.32 --再次查询，走该复合索引了，逻辑读明显减少 SQL> select object_id from t2 where object_id is null; OBJECT_ID ---------- Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- Plan hash value: 2188508051 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 104 | 14 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| T2_ID_1 | 8 | 104 | 14 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJECT_ID" IS NULL) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 74 consistent gets 1 physical reads 0 redo size 587 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4 rows processed ``` 有很多时候，我们创建了索引，数据库不走，我们可以通过10053查看数据库的选择执行路径的情况，也可以有很多方法来解决，再众多方法中，我们需要找出更高效的方法。
