求sql优化

数据环境介绍：

--创建表
create table NOTIFY_mahanso
(
  ID                NUMBER(12) not null,
  BASE_ID NUMBER(12) not null,
  NOTIFY_TIMES      NUMBER(3) not null,
  NOTIFY_TYPE       VARCHAR2(2) not null,
  GMT_CREATE        TIMESTAMP(6) not null,
  GMT_MODIFIED      TIMESTAMP(6) not null,
  NOTIFY_URL        VARCHAR2(200) not null
)

--创建索引
create index ix_NOTIFY_mahanso_NOTIFY_TIMES on NOTIFY_mahanso(NOTIFY_TIMES)
create index ix_NOTIFY_mahanso_gmt_create on NOTIFY_mahanso(gmt_create)


--制造数据
declare
     v_base_id number;
     v_notify_times varchar2(20);
     v_notify_type varchar2(20);
     time timestamp(6);
begin
     for i in 1..100000
     loop
for j in 1..5000
   loop
   select TRUNC(DBMS_RANDOM.VALUE (0, 1000)) into v_base_id from dual;
   select TRUNC(DBMS_RANDOM.VALUE (0, 9)) into v_notify_times from dual;
   select '0'||TRUNC(DBMS_RANDOM.VALUE (1, 4)) into v_notify_type from dual;
   SELECT (SYSDATE - 1)+DBMS_RANDOM.VALUE (1, 1800) / 3600 / 24 into time FROM DUAL;
   
       insert into NOTIFY_mahanso values(mahanso.nextval,v_pay_order_base_id,v_base_id,v_notify_type,time,time,'http://mahanso');
     end loop;
   commit;
     end loop;
end;



--需要优化的sql
select t.*
  from NOTIFY_mahanso t
where t.NOTIFY_TIMES <= 6
   and round((sysdate - CAST(t.gmt_create AS DATE)) * 24 * 60) >=
       (CASE WHEN t.NOTIFY_TIMES = 0 THEN 0 WHEN t.NOTIFY_TIMES <= 3
        THEN(5 * (select power(2, t.NOTIFY_TIMES) from dual))
        ELSE(60 * 2 * (select power(2, t.NOTIFY_TIMES - 3) from dual)) END)
   and round((sysdate - CAST(t.gmt_create AS DATE)) * 24) <= 25
order by t.GMT_CREATE asc


--执行计划展示
SQL> set linesize 1000
SQL> set autotrace traceonly
SQL> select t.*
  2    from NOTIFY_mahanso t
  3   where t.NOTIFY_TIMES <= 6
  4     and round((sysdate - CAST(t.gmt_create AS DATE)) * 24 * 60) >=
  5         (CASE WHEN t.NOTIFY_TIMES = 0 THEN 0 WHEN t.NOTIFY_TIMES <= 3
  6          THEN(5 * (select power(2, t.NOTIFY_TIMES) from dual))
  7          ELSE(60 * 2 * (select power(2, t.NOTIFY_TIMES - 3) from dual)) END)
  8     and round((sysdate - CAST(t.gmt_create AS DATE)) * 24) <= 25
  9   order by t.GMT_CREATE asc;

74118 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4284370147

-----------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                             |    85 |  4335 |       | 16284   (1)| 00:03:16 |
|   1 |  SORT ORDER BY      |                             |    85 |  4335 |  2200K| 16284   (1)| 00:03:16 |
|*  2 |   FILTER            |                             |       |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| NOTIFY_mahanso              | 15387 |   766K|       |   691  (10)| 00:00:09 |
|   4 |    FAST DUAL        |                             |     1 |       |       |     2   (0)| 00:00:01 |
|   5 |     FAST DUAL       |                             |     1 |       |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROUND((SYSDATE@!-CAST(INTERNAL_FUNCTION("T"."GMT_CREATE") AS DATE))*24*60)>=CASE
              WHEN ("T"."NOTIFY_TIMES"=0) THEN 0 WHEN ("T"."NOTIFY_TIMES"<=3) THEN 5* (SELECT /*+ */
              POWER(2,:B1) FROM "SYS"."DUAL" "DUAL") ELSE 120* (SELECT /*+ */ POWER(2,:B2-3) FROM "SYS"."DUAL"
              "DUAL") END )
   3 - filter("T"."NOTIFY_TIMES"<=6 AND ROUND((SYSDATE@!-CAST(INTERNAL_FUNCTION("T"."GMT_CREATE")
              AS DATE))*24)<=25)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3517  consistent gets
          0  physical reads
          0  redo size
    2301890  bytes sent via SQL*Net to client
      54820  bytes received via SQL*Net from client
       4943  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      74118  rows processed

SQL> select /*+ rule */ t.*
  2    from NOTIFY_mahanso t
  3   where t.NOTIFY_TIMES <= 6
  4     and round((sysdate - CAST(t.gmt_create AS DATE)) * 24 * 60) >=
  5         (CASE WHEN t.NOTIFY_TIMES = 0 THEN 0 WHEN t.NOTIFY_TIMES <= 3
  6          THEN(5 * (select power(2, t.NOTIFY_TIMES) from dual))
  7          ELSE(60 * 2 * (select power(2, t.NOTIFY_TIMES - 3) from dual)) END)
  8     and round((sysdate - CAST(t.gmt_create AS DATE)) * 24) <= 25
  9   order by t.GMT_CREATE asc;

74118 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3180159295

----------------------------------------------------------------------
| Id  | Operation                     | Name                         |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                              |
|   1 |  SORT ORDER BY                |                              |
|*  2 |   FILTER                      |                              |
|*  3 |    TABLE ACCESS BY INDEX ROWID| NOTIFY_mahanso               |
|*  4 |     INDEX RANGE SCAN          | IX_ET_PAY_ORDER_NOTIFY_TIMES |
|   5 |    FAST DUAL                  |                              |
|   6 |     FAST DUAL                 |                              |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROUND((SYSDATE@!-CAST(INTERNAL_FUNCTION("T"."GMT_CREATE")
              AS DATE))*24*60)>=CASE  WHEN ("T"."NOTIFY_TIMES"=0) THEN 0 WHEN
              ("T"."NOTIFY_TIMES"<=3) THEN 5* (SELECT POWER(2,:B1) FROM "SYS"."DUAL"
              "DUAL") ELSE 120* (SELECT POWER(2,:B2-3) FROM "SYS"."DUAL" "DUAL") END )
   3 - filter(ROUND((SYSDATE@!-CAST(INTERNAL_FUNCTION("T"."GMT_CREATE")
              AS DATE))*24)<=25)
   4 - access("T"."NOTIFY_TIMES"<=6)

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      24791  consistent gets
        740  physical reads
          0  redo size
    2181248  bytes sent via SQL*Net to client
      54820  bytes received via SQL*Net from client
       4943  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      74118  rows processed



问题：
1、目前sql的写法是否合理，是否可以有更好的写法提高sql查询效率

2、有索引的情况下，默认执行sql全表扫描（ 3517  consistent gets），强制rule为INDEX RANGE SCAN（24791  consistent gets）为什么会有这样的结果


希望兄弟姐们帮我解惑，非常感谢。

ix_NOTIFY_mahanso_gmt_create
这个索引没有用的

cbo从统计信息中计算出t.NOTIFY_TIMES <= 6条件的选择性不佳，所以不走索引，如果你统计信息正确，那么就是对的，比走索引好

谢谢dingjun123

1、我的sql是否有更好的写法呢？

2、我在执行计划里面看ix_NOTIFY_mahanso_gmt_create确实没用，gmt_create是查询条件啊，为什么会没用呢。

都被函数处理了，还走什么索引啊，当然走也可以，因为你排序用到了，但是没有啥必要
你那写法不是还不错嘛，调整干嘛？