一条sql语句的改进探索(r5笔记第70天)
昨天同事找我，让我帮忙看两个sql问题，第一个问题是一个sql语句执行频率极高，但是目前的执行速度还是比较慢，希望我看看能不能调优一下。另外一个问题是一个查询执行速度比较慢，但是执行频率不高。我们还是循序渐进，一个一个来看。第一个sql语句如下5knr1ywqugtq8/* TransactionLog_selectByAccRangeTrx_3 */SELECT TRANSACTION_LOG.TRANS_DATE, TRANSACTION_LOG.TRANS_TYPE, TRANSACTION_LOG.PARTITION_ID, TRANSACTION_LOG.ACCOUNT_ID, TRANSACTION_LOG.TRANSACTION_ID, TRANSACTION_LOG.SUB_TRANSACTION_ID, TRANSACTION_LOG.SYS_UPDATE_DATE, TRANSACTION_LOG.SYS_CREATION_DATE, TRANSACTION_LOG.DL_SERVICE_CODE, TRANSACTION_LOG.APPLICATION_ID, TRANSACTION_LOG.DL_UPDATE_STAMP, TRANSACTION_LOG.OPERATOR_ID, TRANSACTION_LOG.PERIOD_KEY, TRANSACTION_LOG.ENTITY_PERIOD_KEY FROM TRANSACTION_LOG WHERE TRANSACTION_LOG.ACCOUNT_ID = :1 AND TRANSACTION_LOG.PARTITION_ID = :2 AND TRANSACTION_LOG.TRANSACTION_ID >= :3 AND TRANSACTION_LOG.TRANSACTION_ID <= :4 AND TRANSACTION_LOG.TRANS_TYPE IN (SELECT /*+ cardinality(1)*/ DISTINCT column_value as transType FROM table (SELECT CAST(:5 AS ar1_ Varchar2Array_tp) FROM DUAL))执行计划如下，从执行计划来看，看起来执行计划还是不错的。索引也使用到了，而且消耗也不高。Plan hash value: 88692238----------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 93 (100)| | | || 1 | NESTED LOOPS | | | | | | | || 2 | NESTED LOOPS | | 1 | 81 | 93 (2)| 00:00:02 | | || 3 | VIEW | VW_NSO_1 | 1 | 12 | 19 (0)| 00:00:01 | | || 4 | HASH UNIQUE | | 1 | 2 | | | | ||* 5 | FILTER | | | | | | | || 6 | COLLECTION ITERATOR PICKLER FETCH| | 8168 | 16336 | 19 (0)| 00:00:01 | | || 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | || 8 | PARTITION RANGE MULTI-COLUMN | | 7 | | 73 (0)| 00:00:01 |KEY(MC)|KEY(MC)||* 9 | INDEX RANGE SCAN | TRANSACTION_LOG_2IX | 7 | | 73 (0)| 00:00:01 |KEY(MC)|KEY(MC)||* 10 | TABLE ACCESS BY LOCAL INDEX ROWID | TRANSACTION_LOG | 1 | 69 | 73 (0)| 00:00:01 | 1 | 1 |----------------------------------------------------------------------------------------------------------------------------------索引情况如下：INDEX_NAME INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G TRANSACTION_LOG_1IX NORMAL NONUNIQUE YES TRANSACTION_ID,TRANS_TYPE TABLE N/A 270774323 01-APR-15 N TRANSACTION_LOG_2IX NORMAL NONUNIQUE YES ACCOUNT_ID,TRANS_TYPE TABLE N/A 270776953 01-APR-15 N TRANSACTION_LOG_PK NORMAL UNIQUE YES SUB_TRANSACTION_ID,PARTITION_ID,PERIOD_KEY TABLE N/A 270772197 01-APR-15 N可以看到索引还是用了TRANSACTION_LOG_2IX，即使用了字段ACCOUNT_ID,TRANS_TYPE来进行数据过滤。TRANSACTION_LOG_1IX和TRANSACTION_LOG_2IX，其实会发现无论是使用第一个还是第二个，数据的分布还是基本平均的。TABLE_NAME INDEX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS INDEX SKIP SCAN | AR1_TRANSACTION_LOG_1IX | 1 | | 73 (0)| 00:00:01 |KEY(MC)|KEY(MC)|/*+opt_param('_optimizer_skip_scan_enabled', 'true')*/93 (2)| 00:00:02 | | |TRANSACTION_LOG_2IX | 7 | | 73 (0)| 00:00:01 |KEY(MC)|KEY(MC)|TRANSACTION_LOG_1IX和TRANSACTION_LOG_2IX都是非唯一性索引,如果能够使用TRANSACTION_LOG_PK其实还是有很大的改进空间。和开发同事的交流来看，他们认为改动难度较大，需要改动的代码量很大,可能需要的时间也较长。但是也不失为一种方式。所以这个方案也是可行但是能不能落地还是一个问号。毕竟在双方的一个权衡中也需要很多的协调。
Elapsed: 00:00:00.70