使用复合索引优化一个分析函数SQL

一个非常简单的SQL如何优化，类似于：SELECT *
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY owner ORDER BY created) RN, T.*
       FROM T)
WHERE RN = 1;


的确很简单，也是我们经常使用的，意思是找每个?owner?对应的最小的created,注意：这里的每个owner的created可能最小的有很多，如果一个owner对应的重复created不多，那不能这么解决，但是用了row_number,那就代表随机取一条记录。这个问题中恰恰是针对每个owner具有一些重复的最小created.


-----------------------test--------------------

DROP TABLE t;
CREATE TABLE t AS SELECT * FROM all_objects;
--很显然，这样的查询可能存在这样的索引
CREATE INDEX idx1_t ON t(owner,created);
BEGIN
dbms_stats.gather_table_stats(USER,'t',estimate_percent => 100,method_opt => 'for all columns size 1',cascade => TRUE);
END;
/



SQL> SELECT num_rows,blocks  FROM user_tables WHERE table_name='T';

  NUM_ROWS     BLOCKS
---------- ----------
     71911       1050

已用时间:  00: 00: 00.05
SQL> set linesize 9999
SQL> SELECT blevel,leaf_blocks,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor FROM use
_indexes WHERE TABLE_name='T';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
---------- ----------- ------------- ----------------------- ----------------------- -----------------
         1         249          2157                       1                       1              1978



索引的选择性应该说，还是不错的，对应的表数据也比较CLUSTERING。

因为分析函数，特别是排名分析函数必须要全部数据扫描一遍然后还需要做排序动作，因此，针对没有谓词或谓词选择性差的话，可能就有一定的效率问题。但是通过普通SQL改写，可能会获得更好的效果。

通过语义分析，用子查询：
SELECT *
         FROM (SELECT ROW_NUMBER() OVER(PARTITION BY owner ORDER BY created) RN,
                       t.*
                FROM t
                --因为每个owner对应的最小created可能有多个，但是原始查询只保留一个，因为此时结果集已经很小，再次用分析函数
                WHERE (owner, created) IN
                      (
                        --从索引中全部获取需要的数据，然后分组，找到每组最小的
                     SELECT owner, MIN(created) FROM t GROUP BY owner
                   )
              )
WHERE rn = 1;



计划和统计信息对比：
分析函数：
已用时间:  00: 00: 00.19
执行计划
----------------------------------------------------------
Plan hash value: 3047187157


-----------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      | 71911 |    11M|       |  1884   (1)| 00:00:23 |
|*  1 |  VIEW                    |      | 71911 |    11M|       |  1884   (1)| 00:00:23 |
|*  2 |   WINDOW SORT PUSHED RANK|      | 71911 |  6811K|  9440K|  1884   (1)| 00:00:23 |
|   3 |    TABLE ACCESS FULL     | T    | 71911 |  6811K|       |   288   (1)| 00:00:04 |
-----------------------------------------------------------------------------------------


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


   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "OWNER" ORDER BY "CREATED")<=1)




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1029  consistent gets
          0  physical reads
          0  redo size
       4302  bytes sent via SQL*Net to client
        437  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         33  rows processed





子查询：
已用时间:  00: 00: 00.03


执行计划
----------------------------------------------------------
Plan hash value: 1133738081


-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |    46 |  7866 |   141   (5)| 00:00:02 |
|*  1 |  VIEW                          |          |    46 |  7866 |   141   (5)| 00:00:02 |
|*  2 |   WINDOW SORT PUSHED RANK      |          |    46 |  5658 |   141   (5)| 00:00:02 |
|   3 |    NESTED LOOPS                |          |       |       |            |          |
|   4 |     NESTED LOOPS               |          |    46 |  5658 |   140   (4)| 00:00:02 |
|   5 |      VIEW                      | VW_NSO_1 |    33 |   858 |    73   (6)| 00:00:01 |
|   6 |       HASH GROUP BY            |          |    33 |   462 |    73   (6)| 00:00:01 |
|   7 |        INDEX FAST FULL SCAN    | IDX1_T   | 71911 |   983K|    70   (2)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN          | IDX1_T   |    33 |       |     1   (0)| 00:00:01 |
|   9 |     TABLE ACCESS BY INDEX ROWID| T        |     1 |    97 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


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


   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "OWNER" ORDER BY "CREATED")<=1)
   8 - access("OWNER"="OWNER" AND "CREATED"="MIN(CREATED)")




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       337  consistent gets
          0  physical reads
          0  redo size
       4302  bytes sent via SQL*Net to client
        437  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         33  rows processed



子查询充分利用索引的效果，从瘦表索引中分组获取结果，然后外层查询又可以从索引中获取需要的排序，从而提高效率，避免了全表扫描带来的高代价，效率立马提升5,6倍。当然要分清具体情况，这里的分组统计结果比较少，索引选择性好才可以这么干。

得出一个结论：并不是SQL写的复杂，就效率低，而是如何每个步骤都高效滴得到结果，尽量尽早高效过滤掉大部分结果，减少中间结果集，这样就可以获得很好的预期效率。也可以知道，分析函数不是万能的，它有自己的缺点，如何避免它的缺点，得具体问题具体分析，比如如果这个问题分析函数有高效谓词的话，通过索引，那么分析函数也能获得很好的效率。