当前位置:首页 > Articles > 正文
Comments Off on SQL语句无法走索引的一些情况分析及优化 –丁俊

SQL语句无法走索引的一些情况分析及优化 –丁俊

摘要:                

        因为优化器还不够强大,还存在诸多限制,或者一些其它原因, 导致SQL应该走索引性能比较好,但事实上却无法正确利用索引。这时候,除了给ORACLE需要的正确统计信息之外,创建合适索引索引或SQL写法对索引能够被正确使用也起到关键作用。

        要让优化器正确使用上需要的索引,需要至少考虑两点:

        1).如何避免优化器的限制

        2).根据业务数据特点改写SQL语句

说明:这里说的走不了索引,是指走不了正常的RANGE(UNIQUE) SCAN,非(FAST) FULL INDEX SCAN。

1. 谓词使用不等于(<>,!=),就算强制索引,也是扫

描全部的索引,走不了RANGE SCAN

        如果事实走索引定位较好,那么解决方法:
1)如果不等条件之外的值不多,而且是确定的,可以改为等值或IN查询,比如status状态字段一般           值类别很少
2)如果不等条件之外的值很多,可以改为> OR <的形式,当然第2种方法包含了方法1.
例如:

DROP TABLE  t;
CREATE TABLE t AS SELECT LEVEL ID,rpad(‘x’,10,’x’) padding,CAST(‘JACK’ AS VARCHAR2(100)) NAME
FROM dual
CONNECT BY LEVEL<100000;

 

–构造3行较少的值

INSERT INTO t VALUES(100000,’y’,’DINGJUN1′);
INSERT INTO t VALUES(100001,’y’,’DINGJUN2′);
INSERT INTO t VALUES(100002,’y’,’DINGJUN3′);
COMMIT;
ALTER TABLE t MODIFY NAME NOT NULL;

 

–创建索引

CREATE INDEX idx_t ON t(NAME);

 BEGIN
dbms_stats.gather_table_stats(ownname => USER,tabname => ‘T’,estimate_percent => 100,

method_opt => ‘for columns name size   10’,cascade => TRUE);
END;
/

 

<>无法利用索引:

dingjun123@ORADB>  SELECT * FROM t
2   WHERE t.name<>’JACK’;
3 rows selected.
Elapsed: 00:00:00.01
Execution Plan
———————————————————-
Plan hash value: 1601196873
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     4 |    88 |   107   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |     4 |    88 |   107   (2)| 00:00:02 |
————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“T”.”NAME”<>’JACK’)

将<>改写为OR连接后,能够正确使用索引,走OR扩展:

dingjun123@ORADB>  SELECT * FROM t
2   WHERE t.NAME>’JACK’ OR t.NAME <‘JACK’;
3 rows selected.
Elapsed: 00:00:00.06
Execution Plan
———————————————————-
Plan hash value: 2945726203
————————————————————————————–
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————–
|   0 | SELECT STATEMENT             |       |     4 |    88 |     6   (0)| 00:00:01 |
|   1 |  CONCATENATION               |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    22 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_T |     1 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T     |     3 |    66 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_T |     4 |       |     2   (0)| 00:00:01 |
————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
3 – access(“T”.”NAME”>’JACK’)
5 – access(“T”.”NAME”<‘JACK’)
filter(LNNVL(“T”.”NAME”>’JACK’))

如果业务允许,改为下列也是走索引的,不再演示。

SELECT * FROM t

WHERE t.NAME IN (‘DINGJUN1’,’ DINGJUN2’,’ DINGJUN3’);

2. 由于B*Tree索引不存储全为NULL的行,造成无

法走索引的情况

      B*Tree索引不存储全为NULL的列的值(单列NULL,多列全为NULL),如果碰到由于NULL原因走不了索引,这时候,有一些技巧可以考虑。如下所述:
构造数据如下:

SQL> DROP TABLE t;

表已删除。

SQL> CREATE TABLE t AS SELECT * FROM dba_objects;

表已创建。

SQL> INSERT INTO t SELECT * FROM t;

已创建73035行。

SQL> commit;

提交完成。

 

SQL> SELECT COUNT(*) FROM t;

COUNT(*)

———-

146070

置1000条object_id为NULL

SQL> UPDATE t SET object_id = NULL WHERE ROWNUM<1000;

已更新999行。

SQL> CREATE INDEX idx_t ON t(object_id);

索引已创建。

SQL>exec dbms_stats.gather_table_stats(ownname=>user,tabname=>’T’,cascade=>true);

PL/SQL 过程已成功完成。

例如:COUNT统计NULL的数据走不了索引:

SQL> set autotrace traceonly exp
SQL> SELECT  COUNT(*) FROM t WHERE object_id  IS NULL;

执行计划

———————————————————-
Plan hash value: 2966233522
—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |     1 |     5 |   588   (1)| 00:00:08 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  1023 |  5115 |   588   (1)| 00:00:08 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – filter(“OBJECT_ID” IS NULL)


   因为B*Tree不存储全为NULL的值,所以走了FULL TABLE SCAN。

2.1   建立函数索引将NULL值纳入到索引中

SQL> drop index idx_t;

索引已删除。

SQL> create index idx_t on t(nvl(object_id,0));

索引已创建。

SQL> SELECT  COUNT(*) FROM t WHERE nvl(object_id,0)=0;

执行计划

———————————————————-

Plan hash value: 1500240790

—————————————————————————
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT  |       |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T |  1461 | 18993 |     1   (0)| 00:00:01 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(NVL(“OBJECT_ID”,0)=0)

  建立函数索引之后的确可以,类似地可以使用NVL,DECODE,CASE WHEN等建立函数索引,但是函数索引有明显的缺点:

        1)必须修改SQL语句和函数索引匹配
2)这种类似的函数索引,必须要求object_id不存在0的数据,这样有所限制,如果object_id存在0,那么会使统计错误

2.2   采用改进的函数索引,伪列组合索引

SQL> drop index idx_t;

索引已删除。

SQL> create index idx_t on t(object_id,0);

索引已创建。

SQL> SELECT  COUNT(*) FROM t WHERE object_id is null;

执行计划

———————————————————-
Plan hash value: 1500240790
—————————————————————————
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT  |       |     1 |     5 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T |  1023 |  5115 |     4   (0)| 00:00:01 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“OBJECT_ID” IS NULL)

 t(object_id,0)是一个特殊的函数索引,使用object_id作为前导列,它有明显的优点,可以不管object_id的值,也不用改SQL,这比较好,因为加入的0是1个字节,也不会很大。

2.3    建立组合索引,屏蔽有NULL的列

SQL> drop index idx_t;

索引已删除。

SQL> create index idx_t on t(object_id,object_name);

索引已创建。

SQL> SELECT  COUNT(*) FROM t WHERE object_id is null;

执行计划

———————————————————-
Plan hash value: 1500240790
—————————————————————————
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT  |       |     1 |     5 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T |  1023 |  5115 |     8   (0)| 00:00:01 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“OBJECT_ID” IS NULL)

 建立object_id,object_name索引,可以不用改SQL,必须要求object_name有not null约束(或者SQL语句中限定了另一列肯定取的是NOT NULL),这种方法最好可能有组合查询,这样可以充分利用索引的第2列。

根据B*Tree索引中不完全存储NULL的特点,在实际应用中,可以根据具体需求灵活应用一些技巧来将含有NULL的列纳入到索引中,从而提高SQL
语句的效率。

 

 

3. LIKE前通配查询询,LIKE全通配查询走不了索

   解决方法: 

1)是否可以根据业务需求把前通配去掉

dingjun123@ORADB> SELECT * FROM t WHERE t.NAME LIKE ‘%DINGJUN%’;
Elapsed: 00:00:00.04
Execution Plan
———————————————————-
Plan hash value: 1601196873
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |  5000 |   107K|   107   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |  5000 |   107K|   107   (2)| 00:00:02 |
————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“T”.”NAME” LIKE ‘%DINGJUN%’)
–改为后通配,走索引
dingjun123@ORADB> SELECT * FROM t WHERE t.NAME LIKE ‘DINGJUN%’;
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
Plan hash value: 1594971208
————————————————————————————-
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————-
|   0 | SELECT STATEMENT            |       |     3 |    66 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     3 |    66 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     3 |       |     2   (0)| 00:00:01 |
————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
2 – access(“T”.”NAME” LIKE ‘DINGJUN%’)
filter(“T”.”NAME” LIKE ‘DINGJUN%’)

  2) 是否和此LIKE一样的前通配或全通配的SQL有很多,此谓词的LIKE变化不大,如果是,考虑建立函数索引,否则对于全通配问题最好办法就是全文索引

dingjun123@ORADB>   CREATE INDEX idx1_t ON t (instr(NAME,’DINGJUN’));
Index created.
Elapsed: 00:00:00.16dingjun123@ORADB>  SELECT * FROM t WHERE instr(t.NAME,’DINGJUN’)>0;
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
Plan hash value: 2071967826
————————————————————————————–
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————–
|   0 | SELECT STATEMENT            |        |  5000 |   102K|     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |  5000 |   102K|     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX1_T |   900 |       |     3   (0)| 00:00:01 |
————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – access(INSTR(“NAME”,’DINGJUN’)>0)

3)如果只是前通配,可以使用reverse函数索引(不是翻转键索引)

SELECT  * FROM t WHERE t.NAME LIKE ‘%DINGJUN1’;

CREATE INDEX idx2_t ON t(REVERSE(NAME));

语句要改写,使用reverse函数谓词,并且查找值倒查:

dingjun123@ORADB> SELECT * FROM t WHERE REVERSE(t.NAME) LIKE ‘1NUJGNID%’;
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
Plan hash value: 3787301248
————————————————————————————–
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————–
|   0 | SELECT STATEMENT            |        |  5000 |   102K|     8   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |  5000 |   102K|     8   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX2_T |   900 |       |     4   (0)| 00:00:01 |
————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – access(REVERSE(“NAME”) LIKE ‘1NUJGNID%’)
filter(REVERSE(“NAME”) LIKE ‘1NUJGNID%’)

注意:如果查找的是中文,得注意,不可直接写 REVERSE(t.NAME) LIKE ‘1NUJGNID%’,因为REVERSE内部会按字节翻转,中文的写法可以用REVERSE转换,比如
SELECT  * FROM t WHERE REVERSE(t.name) LIKE REVERSE(‘数据’)||’%’;否则查询出的数据不对。

4. 对索引列使用了函数,数学运算,其他表达式等

解决方法去掉对索引列的相关运算,保持索引列纯净。

优化器目前对一些数学运算,还无法做很好的消除动作,所以对于索引列应该尽量保持纯净,则可能无法用上正确的索引

dingjun123@ORADB>  CREATE INDEX idx3_t ON t(ID);
Index created.
Elapsed: 00:00:00.13

dingjun123@ORADB>  ALTER TABLE T MODIFY ID NOT NULL;
Table altered.
Elapsed: 00:00:00.10

dingjun123@ORADB> SELECT * FROM t WHERE ID+0=1;
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
Plan hash value: 1601196873
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |  1000 | 21000 |   107   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |  1000 | 21000 |   107   (2)| 00:00:02 |
————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“ID”+0=1)

dingjun123@ORADB> SELECT * FROM t WHERE ID=1;
Elapsed: 00:00:00.01
Execution Plan
———————————————————-
Plan hash value: 2351669764
————————————————————————————–
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————–
|   0 | SELECT STATEMENT            |        |  1000 | 21000 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |  1000 | 21000 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX3_T |   400 |       |     1   (0)| 00:00:01 |
————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – access(“ID”=1)

  举例只是举简单的数学运算,可能运算还和其他列运算,比如where ID+ext_col…

 

 

5. ORACLE使用了隐式类型转换,导致索引无法使

  解决方法:

       必须避免隐式类型转换,全部要求显式类型转换(非索引列),且避免对索引列进行类型转换(有函数索引除外)
如果类型不一致,不管是否发生自动类型转换,谓词的右值应该显式转换为与索引列保持一致(对于非索引列的运算也应该如此)。

dingjun123@ORADB>  DROP TABLE t1;
Table dropped.
Elapsed: 00:00:00.03

dingjun123@ORADB>  CREATE TABLE t1(x VARCHAR2(100));
Table created.
Elapsed: 00:00:00.04

dingjun123@ORADB>  CREATE INDEX idx_t1 ON t1(x);
Index created.
Elapsed: 00:00:00.04

dingjun123@ORADB>  SELECT * FROM t1 WHERE x = 1;
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
Plan hash value: 3617692013
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     1 |    52 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    52 |     2   (0)| 00:00:01 |
————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(TO_NUMBER(“X”)=1)

dingjun123@ORADB>  SELECT * FROM t1 WHERE x = ‘1’;
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
Plan hash value: 1369807930
—————————————————————————
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT |        |     1 |    52 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |    52 |     1   (0)| 00:00:01 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(“X”=’1′)

发生类型转换,可以通过执行计划中的谓词信息获知。通过分析发现,X因为是VARCHAR2,优先级比数值类型低,遇到数值类型,会TO_NUMBER,所以索引失效,通过传入与索引列类型一致的字符串后解决。

6. 通过业务的逻辑特点改写语句的正确利用索引

  例如(一个典型的例子):

DROP TABLE t_objects;
CREATE TABLE t_objects
AS
SELECT * FROM dba_objects;
CREATE INDEX idx_t_objects ON t_objects(last_ddl_time,created);
BEGIN
dbms_stats.gather_table_stats(ownname => USER,tabname => ‘t_objects’,estimate_percent => 100,cascade => TRUE);
END;
/

需求:查找创建时间是2013年的,并且最后ddl时间比创建时间大1天以上。

dingjun123@ORADB> SELECT * FROM
2  t_objects t
3  WHERE t.last_ddl_time-t.created>1
4  AND t.created>=DATE’2013-1-1′;
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
Plan hash value: 3629755566
——————————————————————————-
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————-
|   0 | SELECT STATEMENT  |           |  1171 |   110K|   300   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T_OBJECTS |  1171 |   110K|   300   (1)| 00:00:04 |
——————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 – filter(“T”.”CREATED”>=TO_DATE(‘ 2013-01-01 00:00:00’,
‘syyyy-mm-dd hh24:mi:ss’) AND “T”.”LAST_DDL_TIME”-“T”.”CREATED”>1)

 

这个索引是组合索引,上面的语句对前导列进行了运行,也不符合走index skip scan的条件,所  以,走FULL TABLE SCAN。那么是否可以通过逻辑改写走索引呢,通过第4点得知,将create_date移到右边,语句如下:

dingjun123@ORADB> SELECT   * FROM
2  t_objects t
3  WHERE  t.last_ddl_time>=(t.created+1)
4  AND t.created>=DATE’2013-1-1′;
Elapsed: 00:00:00.00

Execution Plan
———————————————————-
Plan hash value: 3629755566
——————————————————————————-
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————-
|   0 | SELECT STATEMENT  |           |   403 | 39091 |   301   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T_OBJECTS |   403 | 39091 |   301   (1)| 00:00:04 |
——————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 – filter(“T”.”CREATED”>=TO_DATE(‘ 2013-01-01 00:00:00’,
‘syyyy-mm-dd hh24:mi:ss’) AND “T”.”LAST_DDL_TIME”>=INTERNAL_FUNCTION(“T”
.”CREATED”)+1)

   

通过改写后发现,还是没有走索引,因为ORACLE认为前导列右边的created不固定,无法从指定  索引处查找。通过这个分析得知,ORACLE谓词传递有一定限制,create_date+1无法做谓词传递给last_ddl_time,改写:

dingjun123@ORADB> SELECT   * FROM
2  t_objects t
3  WHERE t.last_ddl_time>=(t.created)
4  AND t.last_ddl_time>=(t.created+1)
5  AND t.created>=DATE’2013-1-1′;
Elapsed: 00:00:00.00

Execution Plan
———————————————————-
Plan hash value: 641904483
———————————————————————————————
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————
|   0 | SELECT STATEMENT            |               |     2 |   194 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJECTS     |     2 |   194 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_OBJECTS |     6 |       |    10   (0)| 00:00:01 |
———————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“T”.”LAST_DDL_TIME”>=TO_DATE(‘ 2013-01-01 00:00:00’, ‘syyyy-mm-dd
hh24:mi:ss’) AND “T”.”CREATED”>=TO_DATE(‘ 2013-01-01 00:00:00’, ‘syyyy-mm-dd
hh24:mi:ss’) AND “T”.”LAST_DDL_TIME” IS NOT NULL)
filter(“T”.”CREATED”>=TO_DATE(‘ 2013-01-01 00:00:00’, ‘syyyy-mm-dd
hh24:mi:ss’) AND “T”.”LAST_DDL_TIME”>=”T”.”CREATED” AND
“T”.”LAST_DDL_TIME”>=INTERNAL_FUNCTION(“T”.”CREATED”)+1)

上面的oracle知道谓词传递给last_ddl_time了,T”.”LAST_DDL_TIME”>=TO_DATE(‘ 2013-01-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’),也可以手动谓词传递,last_ddl_time肯定大于等于DATE’2013-1-2’:

dingjun123@ORADB> SELECT   * FROM
2  t_objects t
3  WHERE t.last_ddl_time>=DATE’2013-1-2′
4  AND t.last_ddl_time>=(t.created+1)
5  AND t.created>=DATE’2013-1-1′;
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
Plan hash value: 641904483
———————————————————————————————
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————
|   0 | SELECT STATEMENT            |               |    13 |  1261 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJECTS     |    13 |  1261 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_OBJECTS |    13 |       |    10   (0)| 00:00:01 |
———————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“T”.”LAST_DDL_TIME”>=TO_DATE(‘ 2013-01-02 00:00:00’, ‘syyyy-mm-dd
hh24:mi:ss’) AND “T”.”CREATED”>=TO_DATE(‘ 2013-01-01 00:00:00’, ‘syyyy-mm-dd
hh24:mi:ss’) AND “T”.”LAST_DDL_TIME” IS NOT NULL)
filter(“T”.”CREATED”>=TO_DATE(‘ 2013-01-01 00:00:00’, ‘syyyy-mm-dd
hh24:mi:ss’) AND “T”.”LAST_DDL_TIME”>=INTERNAL_FUNCTION(“T”.”CREATED”)+1)

如果查询条件无t.created>=DATE’2013-1-2′,如下面语句:

SELECT * FROM
t_objects t
WHERE t.last_ddl_time-t.created>1;

QL> set autotrace traceonly exp
QL>  SELECT * FROM
2      t_objects t
3      WHERE t.last_ddl_time-t.created>1;

执行计划

———————————————————

Plan hash value: 3629755566
——————————————————————————
Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————
0 | SELECT STATEMENT  |           | 11706 |  1108K|   297   (2)| 00:00:04 |
*  1 |  TABLE ACCESS FULL| T_OBJECTS | 11706 |  1108K|   297   (2)| 00:00:04 |
——————————————————————————

redicate Information (identified by operation id):
————————————————–

1 – filter(“T”.”LAST_DDL_TIME”-“T”.”CREATED”>1)

SQL> set autotrace off
SQL>  SELECT COUNT(*) FROM
2      t_objects t;

COUNT(*)
———-
74059

SQL>  SELECT COUNT(*) FROM
2      t_objects t
3      WHERE t.last_ddl_time-t.created>1;

  COUNT(*)
———-
216

应该走索引更佳,因为没有其他过滤条件,可以考虑建立函数索引: 

SQL>     CREATE INDEX idx1_t_object ON t_objects(last_ddl_time-created);

索引已创建。

注意收集直方图,因为分布不均

SQL> exec dbms_stats.gather_table_stats(ownname => USER,tabname => ‘t_objects’,estimate_percent => 100,method_opt => ‘for all indexed columns’,cascade => TRUE);

 PL/SQL 过程已成功完成。

SQL>      SELECT COUNT(*) FROM
2      t_objects t
3      WHERE t.last_ddl_time-t.created>1;

执行计划

———————————————————-
Plan hash value: 3236535878
———————————————————————————–
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————–
|   0 | SELECT STATEMENT  |               |     1 |     9 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |               |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX1_T_OBJECT |   986 |  8874 |     4   (0)| 00:00:01 |
———————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – access(“LAST_DDL_TIME”-“CREATED”>1)

统计信息

———————————————————-
1  recursive calls
0  db block gets
3  consistent gets
0  physical reads
0  redo size
424  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

     当然,对于两个都是范围的查询,这里只能通过一个列来轮询索引,先做access,再做filter。SQL语句的逻辑改写很重要,往往通过逻辑改写就能改变SQL的执行计划,从不好的计划到好的计划,比如semi join,anti join与or,往往走FILTER导致执行计划较差,这时候就需要通过逻辑等价改写来进行优化。

啦啦啦

7 作者简介

丁俊,网名:dingjun123

ITPUB开发版资深版主、ITPUB社区专家、ITPUB名人堂成员、ChinaUnix BLOG专家,
ITPUB 2010-2013连续4届最佳精华获得者、2011-2014连续4届最佳版主。

电子工业出版社终身荣誉作者,《剑破冰山-Oracle开发艺术》副主编。

曾多次参与和ORACLE相关的公共活动:ITPUB 2011演讲嘉宾、OOW 2013 上海 weibo特使等.

更多信息请访问:http://www.acoug.org/members/1734.html

[sdfile url=”http://pan.baidu.com/s/1gdw663p”]

本文章由 Eygle 于2015年03月28日发布在Articles分类下,
转载请注明:SQL语句无法走索引的一些情况分析及优化 –丁俊-中国 Oracle 用户组

好文章就要一起分享!

更多

评论已关闭!