想问下这段sql有能调优的地方吗？必须关联两个表一起查！

Question

select *  from (select *
          from (select TRANS_ID,
                       txn_id,
                       (select name
                          from t_type a
                         where a.code = t.txn_id
                           and category = 'TXN') txn_name,
                       (case
                         when (TRAN_STATUS = 1) then
                          '有效'
                         when (TRAN_STATUS = 2) then
                          '撤销'
                         when (TRAN_STATUS = 3) then
                          '被冲正'
                         else
                          '暂挂'
                       end) TRAN_STATUS,
                       '卡' status,
                       txn_date,
                       pan,
                       txn_amt,
                       shop_id,
                       (select name from t_shop a where a.code = t.shop_id) shop_name,
                       cust_no,
                       (select name
                          from t_client_info a
                         where a.code = t.cust_no) cust_name,
                       g_id,
                       (select name from t_goods s where s.code = t.g_id) g_name,
                       g_price,
                       g_qty,
                       old_pan
                  from t_transaction_int t
                 order by txn_date desc)
         where rownum <= 10000
        union
        select *
          from (select TRANS_ID,
                       txn_id,
                       (select name
                          from t_type a
                         where a.code = t.txn_id
                           and category = 'TXN') txn_name,
                       '有效' TRAN_STATUS,
                       '卡' status,
                       txn_date,
                       pan,
                       txn_amt,
                       shop_id,
                       (select name from t_shop a where a.code = t.shop_id) shop_name,
                       '' cust_no,
                       (case
                         when (t.issu_bank is null) then
                          '卡'
                         else
                          (select name from t_bank a where a.code = t.issu_bank)
                       end) cust_name,
                       g_id,
                       (select name from t_goods s where s.code = t.g_id) g_name,
                       g_price,
                       g_qty,
                       '' old_pan
                  from t_transaction_ext t
                 order by txn_date desc)
         where rownum <= 10000)
 order by txn_date desc

Answer 1

select *
  from (select TRANS_ID,
               txn_id,
               (select name
                  from t_type a
                 where a.code = t.txn_id
                   and category = 'TXN') txn_name,
               /*(case
                 when (TRAN_STATUS = 1) then
                  '有效'
                 when (TRAN_STATUS = 2) then
                  '撤销'
                 when (TRAN_STATUS = 3) then
                  '被冲正'
                 else
                  '暂挂'
               end) TRAN_STATUS,*/
               decode(TRAN_STATUS, 1, '有效', 2, '撤销', 3, '被冲正', '暂挂') as TRAN_STATUS '卡' status,
               txn_date,
               pan,
               txn_amt,
               shop_id,
               (select name from t_shop a where a.code = t.shop_id) shop_name,
               cust_no,
               (select name from t_client_info a where a.code = t.cust_no) cust_name,
               g_id,
               (select name from t_goods s where s.code = t.g_id) g_name,
               g_price,
               g_qty,
               old_pan
          from (select *
                  from (select * from t_transaction_int order by txn_date desc)
                 where rownum <= 10000) t
        union
        select TRANS_ID,
               txn_id,
               (select name
                  from t_type a
                 where a.code = t.txn_id
                   and category = 'TXN') txn_name,
               '有效' TRAN_STATUS,
               '卡' status,
               txn_date,
               pan,
               txn_amt,
               shop_id,
               (select name from t_shop a where a.code = t.shop_id) shop_name,
               '' cust_no,
               (case
                 when (t.issu_bank is null) then
                  '卡'
                 else
                  (select name from t_bank a where a.code = t.issu_bank)
               end) cust_name,
               g_id,
               (select name from t_goods s where s.code = t.g_id) g_name,
               g_price,
               g_qty,
               '' old_pan
          from (select *
                  from (select * from t_transaction_ext order by txn_date desc)
                 where rownum <= 10000) t)
 order by txn_date desc

Answer 2

但书上写着9i之后引入了case 效率是要比decode来的快，用plsql实测case快了 0.01秒

Answer 3

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------Plan hash value: 2819955526---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   | 20000 |  6582K|       |   474K  (1)| 01:34:52 |
|   1 |  VIEW                       |                   | 20000 |  6582K|       |   474K  (1)| 01:34:52 |
|   2 |   SORT UNIQUE               |                   | 20000 |  2871K|  6944K|   474K (54)| 01:34:52 |
|   3 |    UNION-ALL                |                   |       |       |       |            |       |
|   4 |     VIEW                    |                   | 10000 |  1503K|       |   221K  (1)| 00:44:19 |
|*  5 |      COUNT STOPKEY          |                   |       |       |       |            |       |PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------|   6 |       VIEW                  |                   |  3445K|   506M|       |   221K  (1)| 00:44:19 |
|*  7 |        SORT ORDER BY STOPKEY|                   |  3445K|   828M|  2243M|   221K  (1)| 00:44:19 |
|   8 |         TABLE ACCESS FULL   | T_TRANSACTION_INT |  3445K|   828M|       | 33812   (1)| 00:06:46 |
|   9 |     VIEW                    |                   | 10000 |  1367K|       |   252K  (1)| 00:50:25 |
|* 10 |      COUNT STOPKEY          |                   |       |       |       |            |       |
|  11 |       VIEW                  |                   |  4493K|   599M|       |   252K  (1)| 00:50:25 |
|* 12 |        SORT ORDER BY STOPKEY|                   |  4493K|   938M|  2340M|   252K  (1)| 00:50:25 |
|  13 |         TABLE ACCESS FULL   | T_TRANSACTION_EXT |  4493K|   938M|       | 38670   (1)| 00:07:45 |
---------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------   5 - filter(ROWNUM<=10000)
   7 - filter(ROWNUM<=10000)
  10 - filter(ROWNUM<=10000)
  12 - filter(ROWNUM<=10000)
用explain分析得出一下结果，但解决方案不知道怎么弄。

Answer 4

分析得知是全表扫描，但两个表本身是有主键的索引的。集合到一起来就变全表扫描了，用hint好像也不起作用，不能如何在这基础上增加个索引呢？还有其他什么优化方案吗？

Answer 5

增加查询条件（where）自然就会走索引，你where条件没有自然就走全表扫描，而且还有排序速度自然会慢。根据你sql所表现出来的业务，只能把 union 改为 union all 提高一点速度了。
其余的没办法。

Answer 6

where条件我是有的，在程序上我会加上，但就算sql加上时间条件，在explain分析是还是提示全表扫描的。
虽然有主键索引，但当时写sql的时候用union all的就少了2条，而用union就没少，我也没深究原因，就直接用union了。

Answer 7

从你的执行计划看，下面这样的过滤起不到任何作用。  5 - filter(ROWNUM<=10000)
  7 - filter(ROWNUM<=10000)
  10 - filter(ROWNUM<=10000)
  12 - filter(ROWNUM<=10000)

Answer 8

union需要排序剃重。
union all 不需要排序剃重。
因此 union all的速度要比union 快，并且重复数据会被显示，不会出现你说的【当时写sql的时候用union all的就少了2条，而用union就没少，我也没深究原因，就直接用union了。】

Answer 9

1.中间查询的临时表T_TYPE、T_SHOP、T_CLIENT_INFO都需要在关联字段上建立主键或索引；
2.可能的话，尽量减少T_TRANSACTION_INT、T_TRANSACTION_EXT查询出来的数据量，如增加TXN_DATE条件等；
3.如果资源允许，增加sql并发数/*+parallel(t,8)*/

Answer 10

第一个不是很明白，这三个表里面都有自己的主键索引。
第三个/*+parallel(t,8)*/ 应该增加在哪个地方？

Answer 11

1.例如这个，t_shop 的shop_id字段应该建立主键约束，一是提升性能，二是避免查询出多条导致异常(select name from t_shop a where a.code = t.shop_id)
3.select * from (select *
  from (select/*+parallel(t,8)*/  TRANS_ID,
  txn_id,