Monthly Archives: 3月 2021

SQL中or操作借助改写提升效率案例一则

2021 年 03 月 27 日 – 17:11

昨日，朋友发来一则SQL询问有没有什么优化思路，并反馈给我Oracle 的STA给出的SQL”优化”建议是加并行操作提升SQL响应时间，SQL 脱敏后TEXT与执行计划如下：

SELECT A.ID,
       C.ANN AS FILENAME,
       B.CNT AS CONT,
       A.TT,
       A.INFO,
       A.U_TIME AS PUB_DT
FROM A
LEFT JOIN  B ON A.ID = B.O_ID
AND B.CNT IS NOT NULL
LEFT JOIN  C ON C.O_ID = A.ID
WHERE A.U_TIME > NVL(TO_DATE(:1 , 'yyyy-MM-dd HH24:mi:ss'), DATE '2015-01-01')
  OR B.U_TIME > NVL(TO_DATE(:2 , 'yyyy-MM-dd HH24:mi:ss'), DATE '2015-01-01')

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |   337K(100)|          |   6187 |00:00:47.88 |     670K|    636K|
|   1 |  NESTED LOOPS OUTER          |                 |      1 |     48 |   337K  (1)| 01:07:29 |   6187 |00:00:47.88 |     670K|    636K|
|*  2 |   FILTER                     |                 |      1 |        |            |          |   6187 |00:00:47.61 |     641K|    636K|
|*  3 |    HASH JOIN OUTER           |                 |      1 |     48 |   337K  (1)| 01:07:27 |   5594K|00:00:46.87 |     641K|    636K|
|   4 |     TABLE ACCESS FULL        | A               |      1 |   5442K| 52131   (1)| 00:10:26 |   5592K|00:00:06.88 |     200K|    200K|
|*  5 |     TABLE ACCESS FULL        | B               |      1 |   2442K|   142K  (1)| 00:28:36 |   2460K|00:00:24.05 |     441K|    436K|
|   6 |   TABLE ACCESS BY INDEX ROWID| C               |   6187 |      1 |     3   (0)| 00:00:01 |   5657 |00:00:00.25 |   29121 |     0 |
|*  7 |    INDEX RANGE SCAN          | C               |   6187 |      1 |     2   (0)| 00:00:01 |   5657 |00:00:00.18 |   23463 |     0 |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("A"."U_TIME">TO_DATE(' 2021-03-17 13:38:11', 'syyyy-mm-dd hh24:mi:ss') OR "B"."U_TIME">TO_DATE('2021-03-17
              13:38:11', 'syyyy-mm-dd hh24:mi:ss')))
   3 - access("A"."ID"="B"."O_ID")
   5 - filter("B"."CNT" IS NOT NULL)
   7 - access("C"."O_ID"="A"."ID")

从真实的执行计划中看到最终反馈6188行记录，并且资源消耗（buffer gets和reads）高达600K+,这个资源消耗，SQL执行了47s并不奇怪。SQL逻辑并不复杂，并且SQL中存在or的操作，并且朋友反馈如果传值为空可以不用考虑，这种情况基本不会出现，并且经过确认Utime字段上存在索引的，那么我们可以考虑将此SQL进行拆分,同时考虑到or的每一个步骤都返回行数不大的情况下，用到索引，将or改写成union all+lnnvl的形式：

SELECT /*+        gather_plan_statistics  */ A.ID,
                                             C.ANN AS FILENAME,
                                             B.CNT AS CONT,
                                             A.TT,
                                             A.INFO,
                                             A.U_TIME AS PUB_DT
FROM A
LEFT JOIN B ON A.ID = B.O_ID
AND B.CNT IS NOT NULL
LEFT JOIN C ON C.O_ID = A.ID
WHERE A.U_TIME > NVL(TO_DATE('2021-03-17 13:38:11' , 'yyyy-MM-dd HH24:mi:ss'), DATE'2015-01-01')
UNION ALL
SELECT A.ID,
       C.ANN AS FILENAME,
       B.CNT AS CONT,
       A.TT,
       A.INFO,
       A.U_TIME AS PUB_DT
FROM A
LEFT JOIN B ON A.ID = B.O_ID
AND B.CNT IS NOT NULL
LEFT JOIN C ON C.O_ID = A.ID
WHERE B.U_TIME > NVL(TO_DATE('2021-03-17 13:38:11', 'yyyy-MM-dd HH24:mi:ss'), DATE '2015-01-01')
  AND LNNVL(A.U_TIME > NVL(TO_DATE('2021-03-17 13:38:11' , 'yyyy-MM-dd HH24:mi:ss'), DATE'2015-01-01'));

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |      1 |        |   170 (100)|          |   6187 |00:00:00.75 | 66639 |      2 |
|   1 |  UNION-ALL                     |                     |      1 |        |            |          |   6187 |00:00:00.75 | 66639 |      2 |
|   2 |   NESTED LOOPS OUTER           |                     |      1 |     17 |    94   (0)| 00:00:02 |   5253 |00:00:00.55 | 45122 |      2 |
|   3 |    NESTED LOOPS OUTER          |                     |      1 |     17 |    48   (0)| 00:00:01 |   5253 |00:00:00.33 | 27337 |      2 |
|   4 |     TABLE ACCESS BY INDEX ROWID| A                   |      1 |     17 |     6   (0)| 00:00:01 |   5253 |00:00:00.09 | 10508 |      0 |
|*  5 |      INDEX RANGE SCAN          | A_UPD               |      1 |     17 |     3   (0)| 00:00:01 |   5253 |00:00:00.05 |  5255 |      0 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| B                   |   5253 |      1 |     3   (0)| 00:00:01 |   4749 |00:00:00.23 | 16829 |      2 |
|*  7 |      INDEX RANGE SCAN          | B_TXT               |   5253 |      1 |     2   (0)| 00:00:01 |   4749 |00:00:00.12 | 12079 |      1 |
|   8 |    TABLE ACCESS BY INDEX ROWID | C                   |   5253 |      1 |     3   (0)| 00:00:01 |   4798 |00:00:00.20 | 17785 |      0 |
|*  9 |     INDEX RANGE SCAN           | C                   |   5253 |      1 |     2   (0)| 00:00:01 |   4798 |00:00:00.13 | 12987 |      0 |
|  10 |   NESTED LOOPS OUTER           |                     |      1 |     14 |    75   (0)| 00:00:01 |    934 |00:00:00.15 | 21517 |      0 |
|  11 |    NESTED LOOPS                |                     |      1 |     14 |    37   (0)| 00:00:01 |    934 |00:00:00.09 | 17148 |      0 |
|* 12 |     TABLE ACCESS BY INDEX ROWID| B                   |      1 |     14 |     9   (0)| 00:00:01 |   5591 |00:00:00.04 |  3535 |      0 |
|* 13 |      INDEX RANGE SCAN          | B_UPD               |      1 |     14 |     3   (0)| 00:00:01 |   5591 |00:00:00.01 |   978 |      0 |
|* 14 |     TABLE ACCESS BY INDEX ROWID| A                   |   5591 |      1 |     2   (0)| 00:00:01 |    934 |00:00:00.05 | 13613 |      0 |
|* 15 |      INDEX UNIQUE SCAN         | PK_A                |   5591 |      1 |     1   (0)| 00:00:01 |   5590 |00:00:00.03 |  8023 |      0 |
|  16 |    TABLE ACCESS BY INDEX ROWID | C                   |    934 |      1 |     3   (0)| 00:00:01 |    859 |00:00:00.05 |  4369 |      0 |
|* 17 |     INDEX RANGE SCAN           | C                   |    934 |      1 |     2   (0)| 00:00:01 |    859 |00:00:00.04 |  3510 |      0 |
-------------------------------------------------------------------------------------------------------------------------------------------------

恰好被驱动的表连接列中均有索引，经过优化后SQL，并且带入多个变量测试，耗时效率不到1秒。

SQL中or操作借助改写提升效率案例一则已关闭评论

SQL EXISTS写法遭遇FILTER

2021 年 03 月 07 日 – 10:46

前几天，看到一篇公众号文章谈到一个SQL优化的问题，原SQL逻辑大致如下，看到这个SQL，发现其中一个小问题感觉需要提示出来，否则很容易忽视。

select min(created)
FROM t1
WHERE '1606270446000' = '1606270446000'
  AND EXISTS
    (SELECT 1
     FROM t2 m
     WHERE m.object_type = t1.object_type
       AND m.owner = t1.owner
       AND m.object_id = '33'
       AND t1.namespace = '4')
  OR EXISTS
    (SELECT 1
     FROM t3 n
     WHERE n.object_type = t1.object_type
       AND n.owner = t1.owner
       AND n.object_id = '33'
       AND t1.namespace = '4');

Plan hash value: 1283715866

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			| Name	  | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |	 A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|	  |	 1 |	    |	395 (100)|	    |	   1 |00:00:00.15 |   14591 |
|   1 |  SORT AGGREGATE 		|	  |	 1 |	  1 |		 |	    |	   1 |00:00:00.15 |   14591 |
|*  2 |   FILTER			|	  |	 1 |	    |		 |	    |	2628 |00:00:00.15 |   14591 |
|   3 |    TABLE ACCESS FULL		| T1	  |	 1 |	101K|	391   (1)| 00:00:05 |	 203K|00:00:00.04 |    2867 |
|   4 |    UNION-ALL			|	  |   7925 |	    |		 |	    |	   1 |00:00:00.03 |   11724 |
|*  5 |     FILTER			|	  |   7925 |	    |		 |	    |	   1 |00:00:00.01 |    5863 |
|*  6 |      TABLE ACCESS BY INDEX ROWID| T3	  |   2929 |	  1 |	  2   (0)| 00:00:01 |	   1 |00:00:00.01 |    5863 |
|*  7 |       INDEX RANGE SCAN		| N_IDX01 |   2929 |	  1 |	  1   (0)| 00:00:01 |	2929 |00:00:00.01 |    2934 |
|*  8 |     FILTER			|	  |   7924 |	    |		 |	    |	   0 |00:00:00.01 |    5861 |
|*  9 |      TABLE ACCESS BY INDEX ROWID| T2	  |   2928 |	  1 |	  2   (0)| 00:00:01 |	   0 |00:00:00.01 |    5861 |
|* 10 |       INDEX RANGE SCAN		| M_IDX01 |   2928 |	  1 |	  1   (0)| 00:00:01 |	2928 |00:00:00.01 |    2933 |
-----------------------------------------------------------------------------------------------------------------------------

COLUMN_NAME			      NDV NUL  NUM_NULLS DATA_TYPE    LOW_VAL_25		HIGH_VAL_25		  LAST_ANALYZED        HISTOGRAM
------------------------------ ---------- --- ---------- ------------ ------------------------- ------------------------- -------------------- ---------
CREATED 			    28480 Y	       0 DATE	      78760202122A38		78790318010404		  2021-03-07 10:26:30  NONE
DATA_OBJECT_ID			    29582 Y	  144370 NUMBER       80			C31A3E5F		  2021-03-07 10:26:30  NONE
EDITION_NAME				0 Y	  203482 VARCHAR2							  2021-03-07 10:26:30  NONE
GENERATED				2 Y	       0 VARCHAR2     4E			59			  2021-03-07 10:26:30  NONE
LAST_DDL_TIME			     3050 Y	      10 DATE	      78760201122A38		787903040E3124		  2021-03-07 10:26:30  NONE
NAMESPACE			       19 Y	      10 NUMBER       C102			C141			  2021-03-07 10:26:30  FREQUENCY
OBJECT_ID			   203482 Y	       0 NUMBER       C102			C3152353		  2021-03-07 10:26:30  NONE
OBJECT_NAME			    48784 Y	       0 VARCHAR2     2F31303030333233645F44656 73756E2F7574696C2F7265736 2021-03-07 10:26:30  NONE
OBJECT_TYPE			       44 Y	       0 VARCHAR2     434C5553544552		57494E444F57		  2021-03-07 10:26:30  FREQUENCY
OWNER				      251 Y	       0 VARCHAR2     414752			5A4142424958		  2021-03-07 10:26:30  FREQUENCY
SECONDARY				1 Y	       0 VARCHAR2     4E			4E			  2021-03-07 10:26:30  NONE
STATUS					2 Y	       0 VARCHAR2     494E56414C4944		56414C4944		  2021-03-07 10:26:30  NONE
SUBOBJECT_NAME			      171 Y	  202738 VARCHAR2     5030			575248245F5741495453545F3 2021-03-07 10:26:30  NONE
TEMPORARY				2 Y	       0 VARCHAR2     4E			59			  2021-03-07 10:26:30  NONE
TIMESTAMP			     3887 Y	      10 VARCHAR2     313939372D30342D31323A313 323032312D30332D30343A313 2021-03-07 10:26:30  NONE

从执行计划中看到，采用了FILTER的方式，原文中该SQL执行了“2小时39分钟40秒，逻辑读2874多万，物理读270多万”，T表数据量很大，Filter 主查询结果集大，子查询结果小，也不能影响驱动关系，驱动表只能是主查询。驱动表驱动次数越多，效率越差。主查询结果集大，只能利用改写的方式消除FILTER。原文作者利用了如下方式进行改写：

SELECT min(t.created)
FROM t1 t,
     t2 m ,
     t3 n
WHERE ('1606270446000' = '1606270446000')
  AND m.object_type = t.object_type
  AND m.owner = t.owner
  AND m.object_id = '33'
  AND t.namespace = '1'
  OR (n.object_type = t.object_type
      AND n.owner = t.owner
      AND n.object_id = '33'
      AND t.namespace = '4');

因为最终的结果集取MIN，则将半连接直接改为了inner join，无需考虑重复数据的问题，但是需要注意的是，半连接（in或exists）改为inner join时，如果最终结果集没有去重逻辑，需要将中间涉及关联的结果集去重后，再改写为内连接，从而体现“半”连接，这是在我们日常SQL改写中，常见却又容易忽略的问题：

create index t1_idx01 on t1(object_type,owner,namespace,created);
create index m_idx01 on t2(object_id);
create index n_idx01 on t3(object_id);

SELECT created  --普通结果集
FROM t1,
  (SELECT DISTINCT m.object_type,  --distinct去重
                   m.owner
   FROM t2 m
   WHERE m.object_id = '33') x1,
  (SELECT DISTINCT n.object_type,  --distinct去重
                   n.owner
   FROM t3 n
   WHERE n.object_id = '33') x2
WHERE 1=1
  AND (x1.object_type = t1.object_type
       AND x1.owner = t1.owner)
  OR (x2.object_type = t1.object_type
      AND x2.owner = t1.owner)
  AND t1.namespace = '4';

Plan hash value: 3647106578

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			  | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		  |	     |	    1 |        |    16 (100)|	       |   2628 |00:00:00.01 |	   215 |
|   1 |  CONCATENATION			  |	     |	    1 |        |	    |	       |   2628 |00:00:00.01 |	   215 |
|   2 |   NESTED LOOPS			  |	     |	    1 |      3 |     8	(25)| 00:00:01 |   2628 |00:00:00.01 |	   195 |
|   3 |    MERGE JOIN CARTESIAN 	  |	     |	    1 |      1 |     6	(34)| 00:00:01 |      1 |00:00:00.01 |	     6 |
|   4 |     VIEW			  |	     |	    1 |      1 |     3	(34)| 00:00:01 |      1 |00:00:00.01 |	     3 |
|   5 |      HASH UNIQUE		  |	     |	    1 |      1 |     3	(34)| 00:00:01 |      1 |00:00:00.01 |	     3 |
|   6 |       TABLE ACCESS BY INDEX ROWID | T2	     |	    1 |      1 |     2	 (0)| 00:00:01 |      1 |00:00:00.01 |	     3 |
|*  7 |        INDEX RANGE SCAN 	  | M_IDX01  |	    1 |      1 |     1	 (0)| 00:00:01 |      1 |00:00:00.01 |	     2 |
|   8 |     BUFFER SORT 		  |	     |	    1 |      1 |     6	(34)| 00:00:01 |      1 |00:00:00.01 |	     3 |
|   9 |      VIEW			  |	     |	    1 |      1 |     3	(34)| 00:00:01 |      1 |00:00:00.01 |	     3 |
|  10 |       HASH UNIQUE		  |	     |	    1 |      1 |     3	(34)| 00:00:01 |      1 |00:00:00.01 |	     3 |
|  11 |        TABLE ACCESS BY INDEX ROWID| T3	     |	    1 |      1 |     2	 (0)| 00:00:01 |      1 |00:00:00.01 |	     3 |
|* 12 | 	INDEX RANGE SCAN	  | N_IDX01  |	    1 |      1 |     1	 (0)| 00:00:01 |      1 |00:00:00.01 |	     2 |
|* 13 |    INDEX RANGE SCAN		  | T1_IDX01 |	    1 |      3 |     2	 (0)| 00:00:01 |   2628 |00:00:00.01 |	   189 |
|  14 |   NESTED LOOPS			  |	     |	    1 |      1 |     8	(25)| 00:00:01 |      0 |00:00:00.01 |	    20 |
|  15 |    NESTED LOOPS 		  |	     |	    1 |      1 |     5	(20)| 00:00:01 |   2628 |00:00:00.01 |	    17 |
|  16 |     VIEW			  |	     |	    1 |      1 |     3	(34)| 00:00:01 |      1 |00:00:00.01 |	     3 |
|  17 |      HASH UNIQUE		  |	     |	    1 |      1 |     3	(34)| 00:00:01 |      1 |00:00:00.01 |	     3 |
|  18 |       TABLE ACCESS BY INDEX ROWID | T2	     |	    1 |      1 |     2	 (0)| 00:00:01 |      1 |00:00:00.01 |	     3 |
|* 19 |        INDEX RANGE SCAN 	  | M_IDX01  |	    1 |      1 |     1	 (0)| 00:00:01 |      1 |00:00:00.01 |	     2 |
|* 20 |     INDEX RANGE SCAN		  | T1_IDX01 |	    1 |      1 |     2	 (0)| 00:00:01 |   2628 |00:00:00.01 |	    14 |
|* 21 |    VIEW 			  |	     |	 2628 |      1 |     3	(34)| 00:00:01 |      0 |00:00:00.01 |	     3 |
|  22 |     SORT UNIQUE 		  |	     |	 2628 |      1 |     3	(34)| 00:00:01 |   2628 |00:00:00.01 |	     3 |
|  23 |      TABLE ACCESS BY INDEX ROWID  | T3	     |	    1 |      1 |     2	 (0)| 00:00:01 |      1 |00:00:00.01 |	     3 |
|* 24 |       INDEX RANGE SCAN		  | N_IDX01  |	    1 |      1 |     1	 (0)| 00:00:01 |      1 |00:00:00.01 |	     2 |
--------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   4 - SEL$2   / X1@SEL$1
   5 - SEL$2
   6 - SEL$2   / M@SEL$2
   7 - SEL$2   / M@SEL$2
   9 - SEL$3   / X2@SEL$1
  10 - SEL$3
  11 - SEL$3   / N@SEL$3
  12 - SEL$3   / N@SEL$3
  13 - SEL$1_1 / T1@SEL$1
  16 - SEL$2   / X1@SEL$1_2
  18 - SEL$2   / M@SEL$2
  19 - SEL$2   / M@SEL$2
  20 - SEL$1_2 / T1@SEL$1_2
  21 - SEL$3   / X2@SEL$1_2
  23 - SEL$3   / N@SEL$3
  24 - SEL$3   / N@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_1")
      USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1))
      OUTLINE_LEAF(@"SEL$1_2")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$1_1" "X1"@"SEL$1")
      NO_ACCESS(@"SEL$1_1" "X2"@"SEL$1")
      INDEX(@"SEL$1_1" "T1"@"SEL$1" ("T1"."OBJECT_TYPE" "T1"."OWNER" "T1"."NAMESPACE" "T1"."CREATED"))
      NO_ACCESS(@"SEL$1_2" "X1"@"SEL$1_2")
      INDEX(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."OBJECT_TYPE" "T1"."OWNER" "T1"."NAMESPACE" "T1"."CREATED"))
      NO_ACCESS(@"SEL$1_2" "X2"@"SEL$1_2")
      LEADING(@"SEL$1_1" "X1"@"SEL$1" "X2"@"SEL$1" "T1"@"SEL$1")
      LEADING(@"SEL$1_2" "X1"@"SEL$1_2" "T1"@"SEL$1_2" "X2"@"SEL$1_2")
      USE_MERGE_CARTESIAN(@"SEL$1_1" "X2"@"SEL$1")
      USE_NL(@"SEL$1_1" "T1"@"SEL$1")
      USE_NL(@"SEL$1_2" "T1"@"SEL$1_2")
      USE_NL(@"SEL$1_2" "X2"@"SEL$1_2")
      INDEX_RS_ASC(@"SEL$2" "M"@"SEL$2" ("T2"."OBJECT_ID"))
      USE_HASH_AGGREGATION(@"SEL$2")
      INDEX_RS_ASC(@"SEL$3" "N"@"SEL$3" ("T3"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

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

   7 - access("M"."OBJECT_ID"=33)
  12 - access("N"."OBJECT_ID"=33)
  13 - access("X2"."OBJECT_TYPE"="T1"."OBJECT_TYPE" AND "X2"."OWNER"="T1"."OWNER" AND "T1"."NAMESPACE"=4)
  19 - access("M"."OBJECT_ID"=33)
  20 - access("X1"."OBJECT_TYPE"="T1"."OBJECT_TYPE" AND "X1"."OWNER"="T1"."OWNER")
  21 - filter((LNNVL("X2"."OBJECT_TYPE"="T1"."OBJECT_TYPE") OR LNNVL("X2"."OWNER"="T1"."OWNER") OR
	      LNNVL("T1"."NAMESPACE"=4)))
  24 - access("N"."OBJECT_ID"=33)
SQL EXISTS写法遭遇FILTER已关闭评论

SQL优化，写法很关键

2021 年 03 月 04 日 – 10:16

近期听到一位Oracle业界前辈，说道:”SQL优化，关键在写法”。我在生产过程中碰到的SQL问题，因为写法有误造成SQL产生严重的性能问题非常多，开发人员可能更关心我的代码功能是否实现，SQL上线前因测试系统环境、数据量与生产存在差异，导致SQL在生产上实际运行时效率远远小于预期，前几天，碰到如下这个SQL案例，个人感觉非常典型，SQL写法很关键！

SELECT count(*)
FROM pnrresultdata
WHERE TO_NUMBER(SYSDATE - CREATEtime)* 86400 > 5*60
  AND TO_NUMBER(SYSDATE - CREATEtime)* 86400 < 4*60*60
  AND username ='baosheng_dps'
  AND (getTime >0)
  AND postType='R'
  AND isSuccess=0

SQL逻辑很简单，只是单纯输出一下符合条件的count(*)，资源消耗如下图所示

然而，表中CREATETIME字段选择性很好，NUM_ROWS:64186660 NDV：2497536，此字段上没有索引，但是因为写法问题，常规组合索引中包含此字段也没有办法利用上索引，非常常见的原因：字段上参与了运算。

将SQL等价改写为：

SELECT count(*)
FROM pnrresultdata
WHERE CREATETIME < SYSDATE - interval '5' MINUTE
  AND CREATETIME > SYSDATE - interval '4' hour
  AND username ='baosheng_dps'
  AND (getTime >0)
  AND postType='R'
  AND isSuccess=0

原SQL执行计划，采用了ISSUCCESS列上的低效索引：

-------------------------------------
SQL_ID  76d58v13x8z90, child number 0
-------------------------------------
SELECT count(*)
FROM pnrresultdata
WHERE TO_NUMBER(SYSDATE - CREATEtime)* 86400 > 5*60
  AND TO_NUMBER(SYSDATE - CREATEtime)* 86400 < 4*60*60
  AND username ='baosheng_dps'
  AND (getTime >0)
  AND postType='R'
  AND isSuccess=0

Plan hash value: 3822156680

-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |      1 |        |       | 14814 (100)|          |      1 |00:00:01.03 |     296K|   4761 |
|   1 |  SORT AGGREGATE              |                         |      1 |      1 |    26 |            |          |      1 |00:00:01.03 |     296K|   4761 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| PNRRESULTDATA           |      1 |      1 |    26 | 14814   (1)| 00:02:58 |    429 |00:00:01.03 |     296K|   4761 |
|*  3 |    INDEX RANGE SCAN          | PNRRESULTDATA_ISSUCCESS |      1 |    452K|       |  2765   (1)| 00:00:34 |    413K|00:00:00.06 |    2949 |     34 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------

2 - filter(("USERNAME"='baosheng_dps' AND "POSTTYPE"='R' AND TO_NUMBER(SYSDATE@!-"CREATETIME")*86400>300 AND
              TO_NUMBER(SYSDATE@!-"CREATETIME")*86400<14400 AND "GETTIME">0))
   3 - access("ISSUCCESS"=0)

改写后，因其他字段缺失索引，目前状态仍不为最佳，同时出现了BITMAP CONVERSION

-------------------------------------
SQL_ID  00xz7m7u33wgg, child number 1
-------------------------------------
SELECT count(*)
FROM pnrresultdata
WHERE CREATETIME < SYSDATE - interval '5' MINUTE
  AND CREATETIME > SYSDATE - interval '4' hour
  AND username ='baosheng_dps'
  AND (getTime >0)
  AND postType='R'
  AND isSuccess=0

Plan hash value: 2537524006

------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                          |      1 |        |       |  9313 (100)|          |      1 |00:00:00.23 |    4339 |   1531 |
|   1 |  SORT AGGREGATE                    |                          |      1 |      1 |    26 |            |          |      1 |00:00:00.23 |    4339 |   1531 |
|*  2 |   FILTER                           |                          |      1 |        |       |            |          |    429 |00:00:00.23 |    4339 |   1531 |
|*  3 |    TABLE ACCESS BY INDEX ROWID     | PNRRESULTDATA            |      1 |    429 | 11154 |  9313   (1)| 00:01:52 |    429 |00:00:00.23 |    4339 |   1531 |
|   4 |     BITMAP CONVERSION TO ROWIDS    |                          |      1 |        |       |            |          |   1164 |00:00:00.23 |    3477 |   1531 |
|   5 |      BITMAP AND                    |                          |      1 |        |       |            |          |      1 |00:00:00.23 |    3477 |   1531 |
|   6 |       BITMAP CONVERSION FROM ROWIDS|                          |      1 |        |       |            |          |      6 |00:00:00.06 |    1937 |      0 |
|*  7 |        INDEX RANGE SCAN            | PNRRESULTDATA_ISSUCCESS  |      1 |    452K|       |  2765   (1)| 00:00:34 |    288K|00:00:00.03 |    1937 |      0 |
|   8 |       BITMAP CONVERSION FROM ROWIDS|                          |      1 |        |       |            |          |      2 |00:00:00.17 |    1540 |   1531 |
|   9 |        SORT ORDER BY               |                          |      1 |        |       |            |          |    177K|00:00:00.16 |    1540 |   1531 |
|* 10 |         INDEX RANGE SCAN           | PNRRESULTDATA_CREATETIME |      1 |    452K|       |  1593   (1)| 00:00:20 |    177K|00:00:00.10 |    1540 |   1531 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------

结合字段的选择性与在索引中的过滤必要，需要创建:

create index t1_idx01 on t1(username,postType,isSuccess,CREATEtime) &tablespace parallel ∥

SQL优化，写法很关键已关闭评论