| Line 9... |
Line 9... |
| 9 |
--
|
9 |
--
|
| 10 |
-- Note: The outer join of release_content was causing a slow query
|
10 |
-- Note: The outer join of release_content was causing a slow query
|
| 11 |
-- This sql is a bit faster
|
11 |
-- This sql is a bit faster
|
| 12 |
--
|
12 |
--
|
| 13 |
-- Based on UsedByAllPackagesInThisRelease.asp
|
13 |
-- Based on UsedByAllPackagesInThisRelease.asp
|
| 14 |
-- Chnages to improve the performance of that query could be used here too
|
14 |
-- Changes to improve the performance of that query could be used here too
|
| 15 |
--
|
15 |
--
|
| - |
|
16 |
--
|
| - |
|
17 |
-- Create a small set of packages with dependency and built time information
|
| 16 |
With small_pd as ( SELECT pv1.build_time, pd.*, pd.pkg_id || pv1.v_ext as pkg_sig, pd.dpkg_id || pv2.v_ext as dpkg_sig
|
18 |
With small_pd as ( SELECT pv1.build_time, pd.*, pd.pkg_id || pv1.v_ext as pkg_sig, pd.dpkg_id || pv2.v_ext as dpkg_sig
|
| 17 |
FROM package_dependencies pd, package_versions pv1, package_versions pv2
|
19 |
FROM package_dependencies pd, package_versions pv1, package_versions pv2
|
| 18 |
WHERE pv1.pv_id IN ( SELECT pv_id FROM release_content WHERE rtag_id = :RTAG_ID )
|
20 |
WHERE pv1.pv_id IN ( SELECT pv_id FROM release_content WHERE rtag_id = :RTAG_ID )
|
| 19 |
AND pd.pv_id = pv1.pv_id AND pd.dpv_id = pv2.pv_id ),
|
21 |
AND pd.pv_id = pv1.pv_id AND pd.dpv_id = pv2.pv_id ),
|
| - |
|
22 |
-- Remove those packages that are Advisory Ripple, Pegged or from an SDK
|
| - |
|
23 |
bb as ( select * from small_pd
|
| - |
|
24 |
where pkg_sig not in (
|
| - |
|
25 |
select pkg_id || v_ext as pkg_sig from package_versions
|
| - |
|
26 |
where pv_id in (
|
| - |
|
27 |
select pv_id from advisory_ripple WHERE rtag_id = :RTAG_ID
|
| - |
|
28 |
union
|
| - |
|
29 |
select pv_id from pegged_versions WHERE rtag_id = :RTAG_ID
|
| - |
|
30 |
union
|
| - |
|
31 |
select pv_id from release_content WHERE rtag_id = :RTAG_ID and sdktag_id is not null
|
| - |
|
32 |
)
|
| - |
|
33 |
)
|
| - |
|
34 |
),
|
| - |
|
35 |
-- Generate a tree of packages starting at the package of interest
|
| 20 |
aa as (SELECT * from (
|
36 |
aa as (SELECT * from (
|
| 21 |
SELECT pv_id, build_time
|
37 |
SELECT pv_id, build_time
|
| - |
|
38 |
FROM bb connect by
|
| - |
|
39 |
prior pkg_sig = dpkg_sig
|
| 22 |
FROM small_pd pd connect by prior pkg_sig = dpkg_sig start with dpkg_sig in (SELECT pkg_id || v_ext FROM package_versions where pv_id = :PV_ID )
|
40 |
start with dpkg_sig in (SELECT pkg_id || v_ext FROM package_versions where pv_id = :PV_ID )
|
| 23 |
UNION
|
41 |
UNION
|
| 24 |
SELECT pv_id, build_time from package_versions where pv_id = :PV_ID
|
42 |
SELECT pv_id, build_time from package_versions where pv_id = :PV_ID
|
| 25 |
) ),
|
43 |
) ),
|
| - |
|
44 |
-- Sum the build times
|
| 26 |
total as (SELECT sum(bd.build_time) as BuildTime, 1 as item
|
45 |
total as (SELECT sum(bd.build_time) as BuildTime, 1 as item
|
| 27 |
FROM aa bd , release_content rc
|
46 |
FROM aa bd , release_content rc
|
| 28 |
WHERE rc.rtag_id(+) = :RTAG_ID
|
47 |
WHERE rc.rtag_id(+) = :RTAG_ID
|
| 29 |
AND rc.pv_id(+) = bd.pv_id ),
|
48 |
AND rc.pv_id(+) = bd.pv_id ),
|
| - |
|
49 |
-- Package Build Time
|
| 30 |
pbt as (SELECT build_time as BuildTime, 1 as item from package_versions where pv_id = :PV_ID )
|
50 |
pbt as (SELECT build_time as BuildTime, 1 as item from package_versions where pv_id = :PV_ID )
|
| - |
|
51 |
-- Return Total and package times in a single row
|
| 31 |
select total.buildtime as Total_BuildTime, pbt.BuildTime from total, pbt where total.item = pbt.item
|
52 |
select total.buildtime as Total_BuildTime, pbt.BuildTime from total, pbt where total.item = pbt.item
|
| - |
|
53 |
--
|