ww

SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3647926450

------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               | 47251 |    12M|   612   (1)| 00:00:08 |
|*  1 |  HASH JOIN RIGHT OUTER   |               | 47251 |    12M|   612   (1)| 00:00:08 |
|   2 |   TABLE ACCESS FULL      | SYS_VENDER    |   518 | 17094 |     9   (0)| 00:00:01 |
|*  3 |   HASH JOIN RIGHT OUTER  |               | 47251 |    10M|   602   (1)| 00:00:08 |
|   4 |    TABLE ACCESS FULL     | SYS_VENDER    |   518 | 17094 |     9   (0)| 00:00:01 |
|*  5 |    HASH JOIN RIGHT OUTER |               | 47251 |  9413K|   593   (1)| 00:00:08 |
|   6 |     TABLE ACCESS FULL    | SYS_SOFT_CONF |    89 |  3204 |     5   (0)| 00:00:01 |
|*  7 |     HASH JOIN RIGHT OUTER|               | 47251 |  7752K|   587   (1)| 00:00:08 |
|   8 |      TABLE ACCESS FULL   | SYS_PDT_TYPE  |    30 |   510 |     3   (0)| 00:00:01 |
|*  9 |      TABLE ACCESS FULL   | SYS_PRODUCT   | 47251 |  6967K|   583   (1)| 00:00:08 |
------------------------------------------------------------------------------------------

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

   1 - access("P"."FILIALE_ID"="F"."VENDER_ID"(+))
   3 - access("P"."VENDER_ID"="V"."VENDER_ID"(+))
   5 - access("P"."SOFT_CONF_ID"="SC"."SOFT_CONF_ID"(+))
   7 - access("P"."PDT_TYPE_ID"="PT"."PDT_TYPE_ID"(+))
   9 - filter("P"."PDT_STATE"=1 OR "P"."PDT_STATE"=3)

SQL> l
  1  select /*+ index(p ind_sys_product) */ p.*,pt.PDT_TYPE,sc.name,v.VENDER_NAME,f.VENDER_NAME as FILIAL_ENAME,'' as CUSTOMER_NAME
  2  from SYS_PRODUCT p
  3           left join SYS_PDT_TYPE pt on p.pdt_type_id = pt.pdt_type_id
  4           left join SYS_SOFT_CONF sc on p.SOFT_CONF_ID = sc.SOFT_CONF_ID
  5           left join sys_vender v on p.VENDER_ID = v.VENDER_ID
  6           left join sys_vender f on p.filiale_id = f.VENDER_ID
  7* where 1=1 and p.pdt_state in (1,3)
 
 
其中sys_product表，共94502行，product_part列值只有0和1。PDT_TYPE_ID列唯一值为30，无空值。
SOFT_CONF_ID列有空值，CUSTOMER_NUMBER列有7w+空值，FILIALE_ID列有3w空值，VENDER_ID列有3w+空值。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22126868/viewspace-765990/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22126868/viewspace-765990/