查詢改善？

iqc.r_kpn_incoming_t       3000000資料量

sfis1.c_comp_type_t         20資料量

select distinct a.hh_pn, a.reserve1, a.reserve2 from iqc.r_kpn_incoming_t a,sfis1.c_comp_type_t b
where a.reserve1 = b.comp_code and b.flag =1;

47,985ms elapsed
Plan hash value: 916263499
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                                   | Rows  | Bytes   |TempSpc  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                                              |   670K  |    16M  |                   | 21970   (2)| 00:04:24 |
|   1 |  HASH UNIQUE                  |                                              |   670K  |    16M  |    81M        | 21970   (2)| 00:04:24 |
|*  2 |   HASH JOIN                       |                                              |  2369K |    58M  |                   | 12492   (1)| 00:02:30 |
|*  3 |    TABLE ACCESS FULL  | C_COMP_TYPE_T           |     9        |    45     |                    |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | R_KPN_INCOMING_T    |  2908K |    50M  |                    | 12474   (1)| 00:02:30 |
----------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."RESERVE1"="B"."COMP_CODE")
   3 - filter(TO_NUMBER("B"."FLAG")=1)
   Statistics
-----------------------------------------------------------
               4  user calls
             355  physical read total multi block requests
       371294208  physical read total bytes
       371294208  cell physical IO interconnect bytes
               0  commit cleanout failures: block lost
               0  IMU commits
               0  IMU Flushes
               0  IMU contention
               0  IMU bind flushes
               0  IMU mbu flush

記錄下來

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24867586/viewspace-711465/，如需轉載，請註明出處，否則將追究法律責任。