wedssd

-- 2013-07-15 --
SELECT COUNT(*) AS c
from (select max(version_no) version_no, soft_id
                           from DIAG_SOFT_VERSION
                           WHERE vALID_FLAG=1
                           group by soft_id) vv
   join DIAG_SOFT s on s.soft_id=vv.soft_id
   left join DIAG_SOFT_DESC sd on (sd.SOFT_ID=s.SOFT_ID AND sd.LAN_ID = :1 )
   join DIAG_USER_SOFT_CENTER us on s.SOFT_ID=us.SOFT_ID
where exists( select 'X'
              from DIAG_SOFT_VERSION_DETAIL d
                join DIAG_SOFT_VERSION v on d.version_id=v.version_id
              where v.soft_id=s.soft_id
                and to_char(v.CREATER_TIME, 'yyyy-mm-dd') <= to_char(us.FREE_END_TIME, 'yyyy-mm-dd')
                AND (v.VALID_FLAG = :2)
                AND (d.CHECK_STATE = :3) )
                AND (s.VALID_FLAG = :4)
                AND (us.SERIAL_NO = :5)
                AND (s.pdt_type_id = :6)
order by s.UPDATE_TIME desc


select count(*) as c
from (select max(version_no) version_no, soft_id
      from diag_soft_version
      where valid_flag = 1
      group by soft_id) vv
   join diag_soft s on s.soft_id = vv.soft_id
   join diag_user_soft_center us on s.soft_id = us.soft_id
   left join diag_soft_desc sd on (sd.soft_id = s.soft_id and sd.lan_id = :1)
where exists( select 'X'
              from diag_soft_version_detail d
                join diag_soft_version v on d.version_id = v.version_id
              where v.soft_id = s.soft_id
                and v.creater_time <= us.free_end_time
                and (v.valid_flag = :2)
                and (d.check_state = :3) )
and (s.valid_flag = :4)
and (us.serial_no = :5)
and (s.pdt_type_id = :6)
order by s.update_time desc





SQL> select * from table(sys.dbms_xplan.display_cursor('89nfp8fv1y8vw'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  89nfp8fv1y8vw, child number 0
-------------------------------------
SELECT COUNT(*) AS c  from (select max(version_no) version_no, soft_id
from DIAG_SOFT_VERSION WHERE vALID_FLAG=1 group by soft_id)vv
join DIAG_SOFT s on s.soft_id=vv.soft_id        left join
DIAG_SOFT_DESC sd on (sd.SOFT_ID=s.SOFT_ID         AND
           sd.LAN_ID = :1                              )        join
DIAG_USER_SOFT_CENTER us on s.SOFT_ID=us.SOFT_ID      where exists(
            select 'X' from DIAG_SOFT_VERSION_DETAIL d join
DIAG_SOFT_VERSION v on d.version_id=v.version_id where
v.soft_id=s.soft_id               and to_char(v.CREATER_TIME,
'yyyy-mm-dd') <= to_char(us.FREE_END_TIME, 'yyyy-mm-dd')
                                         AND
 (v.VALID_FLAG = :2)                           AND
       (d.CHECK_STATE = :3)                                  )
               AND                             (s.VALID_FLAG = :4)
                                               AND

Plan hash value: 973547522

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                               |       |       |       |   433 (100)|          |
|   1 |  SORT AGGREGATE                  |                               |     1 |    46 |       |            |          |
|*  2 |   FILTER                         |                               |       |       |       |            |          |
|*  3 |    HASH JOIN OUTER               |                               |    57 |  2622 |       |   203   (1)| 00:00:03 |
|*  4 |     HASH JOIN                    |                               |    41 |  1640 |       |   135   (2)| 00:00:02 |
|   5 |      NESTED LOOPS                |                               |       |       |       |            |          |
|   6 |       NESTED LOOPS               |                               |    41 |  1517 |       |   109   (0)| 00:00:02 |
|*  7 |        TABLE ACCESS FULL         | DIAG_SOFT                     |    40 |   600 |       |    11   (0)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN          | COM_IND_DUSC                  |     1 |       |       |     2   (0)| 00:00:01 |
|   9 |       TABLE ACCESS BY INDEX ROWID| DIAG_USER_SOFT_CENTER         |     1 |    22 |       |     3   (0)| 00:00:01 |
|  10 |      VIEW                        |                               |  1757 |  5271 |       |    25   (4)| 00:00:01 |
|  11 |       HASH GROUP BY              |                               |  1757 | 17570 | 66000 |    25   (4)| 00:00:01 |
|* 12 |        TABLE ACCESS FULL         | DIAG_SOFT_VERSION             |  2704 | 27040 |       |    13   (0)| 00:00:01 |
|* 13 |     TABLE ACCESS FULL            | DIAG_SOFT_DESC                |  2010 | 12060 |       |    68   (0)| 00:00:01 |
|  14 |    NESTED LOOPS                  |                               |       |       |       |            |          |
|  15 |     NESTED LOOPS                 |                               |     1 |    22 |       |     8   (0)| 00:00:01 |
|* 16 |      TABLE ACCESS BY INDEX ROWID | DIAG_SOFT_VERSION             |     1 |    15 |       |     4   (0)| 00:00:01 |
|* 17 |       INDEX RANGE SCAN           | IND_SOFT_ID_DIAG_SOFT_VERSION |     3 |       |       |     1   (0)| 00:00:01 |
|* 18 |      INDEX RANGE SCAN            | IND_VERSION_ID_DSD            |     5 |       |       |     1   (0)| 00:00:01 |
|* 19 |     TABLE ACCESS BY INDEX ROWID  | DIAG_SOFT_VERSION_DETAIL      |     5 |    35 |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------

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

   2 - filter( IS NOT NULL)
   3 - access("SD"."SOFT_ID"="S"."SOFT_ID")
   4 - access("S"."SOFT_ID"="VV"."SOFT_ID")
   7 - filter(("S"."PDT_TYPE_ID"=:6 AND "S"."VALID_FLAG"=:4))
   8 - access("S"."SOFT_ID"="US"."SOFT_ID" AND "US"."SERIAL_NO"=:5)
  12 - filter("VALID_FLAG"=1)
  13 - filter("SD"."LAN_ID"=:1)
  16 - filter(("V"."VALID_FLAG"=:2 AND TO_CHAR(INTERNAL_FUNCTION("V"."CREATER_TIME"),'yyyy-mm-dd')<=TO_CHAR(:B1,'y
              yyy-mm-dd')))
  17 - access("V"."SOFT_ID"=:B1)
  18 - access("D"."VERSION_ID"="V"."VERSION_ID")
  19 - filter("D"."CHECK_STATE"=:3)


61 rows selected.

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

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