OsmcPlatformInfoQuery

From NGDCWiki

Jump to: navigation, search

Contents

Platform Info Query

Kevin's email

Hi Nancy -

I wanted to continue some of the conversations we were having in Boulder about a couple of queries that needed modification.  Maybe best to do this one at a time.

So, the first query needed to be modified is this one (you can recreate this type of query yourself from the OSMC UI by clicking the "Platform Info" button).  This is basically a metadata query, which will return One row for each Platform/parameter/day combo:

SELECT
    DISTINCT ds.platform_code id,
    ds.platform_type type,
    ds.country country,
    ds.last_report_latitude latitude,
    ds.last_report_longitude_390 longitude,
    to_char(ds.last_report_date, 'YYYY-MM-DD hh24:mi:ss') Last_Report,
    p.name,
    NVL(plat.url,'noURL') URL
FROM
    osmc.daily_summary_mv ds,
    osmc.platform plat,
    osmc.observation_value ov,
    osmc.parameter p
   ,osmc.platform_type_association pta
   ,osmc.platform_type_view ptv
 WHERE
    (ds.operational_status_id = 2 OR
     ds.observation_day >= to_date('2008-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
     ds.observation_day <= to_date('2008-02-21 23:59:59','yyyy-mm-dd hh24:mi:ss')) 
     AND ds.platform_code = plat.platform_code
     and plat.id = pta.platform_id
     and ptv.id = pta.platform_type_id
     AND (ds.last_report_longitude_390 >= 242 AND ds.last_report_longitude_390 <= 254 ) AND
     (ds.last_report_latitude>=3 AND ds.last_report_latitude<=9) AND
     ds.platform_code = plat.platform_code(+) AND
     ov.observation_date >= to_date('2008-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
     ov.observation_date <= to_date('2008-02-21 23:59:59','yyyy-mm-dd hh24:mi:ss') AND
     ds.last_report_obs_id = ov.observation_id(+)  AND
     ov.parameter_id = p.id(+)
UNION
SELECT
    DISTINCT ds.platform_code id,
    ds.platform_type type,
    ds.country country,
    ds.last_report_latitude latitude,
    ds.last_report_longitude_390 longitude,
    to_char(ds.last_report_date, 'YYYY-MM-DD hh24:mi:ss') Last_Report,
    NULL,
    NVL(plat.url,'noURL') URL
FROM
    osmc.daily_summary_mv ds,
    osmc.platform plat
      ,osmc.platform_type_association pta
      ,osmc.platform_type_view ptv
WHERE
     ds.platform_code = plat.platform_code
     and plat.id = pta.platform_id
     and ptv.id = pta.platform_type_id
   AND ds.observation_day = to_date('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
   AND ds.operational_status_id = 2  AND (ds.last_report_longitude_390 >= 242 AND ds.last_report_longitude_390 <= 254 ) 
   AND (ds.last_report_latitude>=3 AND ds.last_report_latitude<=9)
   AND ds.platform_code = plat.platform_code(+)
order by id,Last_Report


The results look something like this (truncated) list:


ID 	TYPE 	COUNTRY 	LATITUDE 	LONGITUDE 	LAST_REPORT 	NAME 	More Information
32315 	TROPICAL MOORED BUOYS 	US 	5.028 	249.901 	2008-02-19 22:00:00 	ATMP 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.028 	249.901 	2008-02-19 22:00:00 	WINDDIR 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.028 	249.901 	2008-02-19 22:00:00 	WINDSPD 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.03 	249.9 	2008-02-19 22:00:00 	ATMP 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.03 	249.9 	2008-02-19 22:00:00 	SST 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.03 	249.9 	2008-02-19 22:00:00 	WINDDIR 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.03 	249.9 	2008-02-19 22:00:00 	WINDSPD 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.039 	249.906 	2008-02-20 22:00:00 	ATMP 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.039 	249.906 	2008-02-20 22:00:00 	WINDDIR 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.039 	249.906 	2008-02-20 22:00:00 	WINDSPD 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.038 	249.901 	2008-02-21 20:00:00 	ATMP 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.038 	249.901 	2008-02-21 20:00:00 	WINDDIR 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.038 	249.901 	2008-02-21 20:00:00 	WINDSPD 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.04 	249.9 	2008-02-21 20:00:00 	ATMP 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.04 	249.9 	2008-02-21 20:00:00 	SST 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.04 	249.9 	2008-02-21 20:00:00 	WINDDIR 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.04 	249.9 	2008-02-21 20:00:00 	WINDSPD 	  Data   Time Series


What I would prefer is to have only one line per platform/parameter combo, rather than having an additional, separate line for each day as well.  So instead of the above, something like this:


ID 	TYPE 	COUNTRY 	LATITUDE 	LONGITUDE 	LAST_REPORT 	NAME 	More Information
32315 	TROPICAL MOORED BUOYS 	US 	5.038 	249.901 	2008-02-21 20:00:00 	ATMP 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.038 	249.901 	2008-02-21 20:00:00 	WINDDIR 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.038 	249.901 	2008-02-21 20:00:00 	WINDSPD 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.04 	249.9 	2008-02-21 20:00:00 	ATMP 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.04 	249.9 	2008-02-21 20:00:00 	SST 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.04 	249.9 	2008-02-21 20:00:00 	WINDDIR 	  Data   Time Series
32315 	TROPICAL MOORED BUOYS 	US 	5.04 	249.9 	2008-02-21 20:00:00 	WINDSPD 	  Data   Time Series


What we decided in Boulder that the answer was to use some sort of "max" function in the query, similar to this query:

SELECT
  ds.platform_code plat_id
, ds.count_observations cnt
, ds.platform_type type
, ds.country country
, ds.last_report_latitude latitude
, ds.last_report_longitude_390 longitude
,  to_char(ds.last_report_date, 'YYYY-MM-DD hh24:mi:ss') Last_Report
,  to_char(ds.last_report_date, 'YYYY-MM-DD hh24:mi:ss') CTIME
FROM
  osmc.daily_summary_mv ds
, (select
     ds.platform_code
    ,max(ds.last_report_date) last_report_date
   from
      osmc.daily_summary_mv ds
      ,osmc.platform plat
      ,osmc.platform_type_association pta
      ,osmc.platform_type_view ptv
   where ds.observation_day >= to_date('2008-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss')
     AND ds.observation_day <= to_date('2008-02-24 23:59:59','yyyy-mm-dd hh24:mi:ss')
     AND ds.platform_code = plat.platform_code
     and plat.id = pta.platform_id
     and ptv.id = pta.platform_type_id
     AND (ds.last_report_longitude_390 >= 203.5 AND ds.last_report_longitude_390 <= 285.5 ) AND ds.last_report_latitude>=-8 AND ds.last_report_latitude<=33
     
   group by ds.platform_code) z
WHERE z.last_report_date = ds.last_report_date
  and z.platform_code = ds.platform_code
  and ds.observation_day >= to_date('2008-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss')
  AND ds.observation_day <= to_date('2008-02-24 23:59:59','yyyy-mm-dd hh24:mi:ss')  


Would it be possible for you to take a look at that original query and suggest ways in which I could modify it to achieve only one line per platform/parameter combination?  Am I even making sense?  Is there a better way to discuss this?

thanks -
kevin

-- 
Kevin O'Brien                   UW/JISAO	
Research Scientist              NOAA/PMEL/TMAP
206-526-6751                    http://tmap.pmel.noaa.gov

"The contents of this message are mine personally and do 
 not necessarily reflect any position of the Government 
 or the  National Oceanic and Atmospheric Administration."

original

SELECT
    DISTINCT ds.platform_code id,
    ds.platform_type type,
    ds.country country,
    ds.last_report_latitude latitude,
    ds.last_report_longitude_390 longitude,
    to_char(ds.last_report_date, 'YYYY-MM-DD hh24:mi:ss') Last_Report,
    p.name,
    NVL(plat.url,'noURL') URL
FROM
    osmc.daily_summary_mv ds,
    osmc.platform plat,
    osmc.observation_value ov,
    osmc.parameter p
   ,osmc.platform_type_association pta
   ,osmc.platform_type_view ptv
 WHERE
    (ds.operational_status_id = 2 OR
     ds.observation_day >= to_date('2008-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
     ds.observation_day <= to_date('2008-02-21 23:59:59','yyyy-mm-dd hh24:mi:ss')) 
     AND ds.platform_code = plat.platform_code
     and plat.id = pta.platform_id
     and ptv.id = pta.platform_type_id
     AND (ds.last_report_longitude_390 >= 242 AND ds.last_report_longitude_390 <= 254 ) AND
     (ds.last_report_latitude>=3 AND ds.last_report_latitude<=9) AND
     ds.platform_code = plat.platform_code(+) AND
     ov.observation_date >= to_date('2008-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
     ov.observation_date <= to_date('2008-02-21 23:59:59','yyyy-mm-dd hh24:mi:ss') AND
     ds.last_report_obs_id = ov.observation_id(+)  AND
     ov.parameter_id = p.id(+)
UNION
SELECT
    DISTINCT ds.platform_code id,
    ds.platform_type type,
    ds.country country,
    ds.last_report_latitude latitude,
    ds.last_report_longitude_390 longitude,
    to_char(ds.last_report_date, 'YYYY-MM-DD hh24:mi:ss') Last_Report,
    NULL,
    NVL(plat.url,'noURL') URL
FROM
    osmc.daily_summary_mv ds,
    osmc.platform plat
      ,osmc.platform_type_association pta
      ,osmc.platform_type_view ptv
WHERE
     ds.platform_code = plat.platform_code
     and plat.id = pta.platform_id
     and ptv.id = pta.platform_type_id
   AND ds.observation_day = to_date('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
   AND ds.operational_status_id = 2  AND (ds.last_report_longitude_390 >= 242 AND ds.last_report_longitude_390 <= 254 ) 
   AND (ds.last_report_latitude>=3 AND ds.last_report_latitude<=9)
   AND ds.platform_code = plat.platform_code(+)
order by id,Last_Report;

modified

alter session set nls_date_format = 'syyyy/mm/dd hh24:mi:ss';
select distinct
     z.id
    ,ds.platform_type type
    ,ds.country country
    ,ds.last_report_latitude latitude
    ,ds.last_report_longitude_390 longitude
    ,z.last_report
    ,p.name
    ,nvl(plat.url,'noURL') URL
from 
     osmc.daily_summary_mv ds
    ,osmc.platform plat
    ,osmc.observation_value ov
    ,osmc.parameter p
    ,osmc.platform_type_association pta
    ,osmc.platform_type_view ptv
    ,
     (select
         ds.platform_code id
        ,max(ds.observation_day) last_report
      from osmc.daily_summary_mv ds
      where
        (ds.observation_day >= to_date('2008-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss') 
         AND ds.observation_day < to_date('2008-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss')
        ) 
         AND (ds.last_report_longitude_390 >= 242 AND ds.last_report_longitude_390 <= 254 ) AND
             (ds.last_report_latitude>=3 AND ds.last_report_latitude<=9)
      group by ds.platform_code) z
where ds.platform_code = plat.platform_code
  and plat.id = pta.platform_id
  and pta.platform_type_id = ptv.id
  and ov.observation_date >= to_date('2008-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss') 
  AND ov.observation_date < to_date('2008-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss')
  and z.last_report = ov.observation_date
  and ds.last_report_obs_id = ov.observation_id
  and ov.parameter_id = p.id
  and z.id = ds.platform_code
  and z.last_report = ds.observation_day
UNION
SELECT
    DISTINCT ds.platform_code id,
    ds.platform_type type,
    ds.country country,
    ds.last_report_latitude latitude,
    ds.last_report_longitude_390 longitude,
    ds.observation_day last_report,
    NULL,
    NVL(plat.url,'noURL') URL
FROM
    osmc.daily_summary_mv ds
   ,osmc.platform plat
   ,osmc.platform_type_association pta
   ,osmc.platform_type_view ptv
WHERE
     ds.platform_code = plat.platform_code
     and plat.id = pta.platform_id
     and ptv.id = pta.platform_type_id
     and ds.observation_day = to_date('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
     and ds.operational_status_id = 2  
     and (ds.last_report_longitude_390 >= 242 AND ds.last_report_longitude_390 <= 254 ) 
     and (ds.last_report_latitude>=3 AND ds.last_report_latitude<=9)
     and ds.platform_code = plat.platform_code
order by id,Last_Report;

explain plan

[auerbach@cheetah auerbach]$ sqlplus las@devl.ndbc.noaa.gov

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Mar 3 17:07:03 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set linesize 5000;
SQL> set pagesize 100;
SQL> alter session set nls_date_format = 'syyyy/mm/dd hh24:mi:ss';

Session altered.

SQL> explain plan for
  2  select distinct
  3       z.id
  4      ,ds.platform_type type
  5      ,ds.country country
  6      ,ds.last_report_latitude latitude
  7      ,ds.last_report_longitude_390 longitude
  8      ,z.last_report
  9      ,p.name
 10      ,nvl(plat.url,'noURL') URL
 11  from
 12       osmc.daily_summary_mv ds
 13      ,osmc.platform plat
 14      ,osmc.observation_value ov
 15      ,osmc.parameter p
 16      ,osmc.platform_type_association pta
 17      ,osmc.platform_type_view ptv
 18      ,
 19       (select
 20           ds.platform_code id
 21          ,max(ds.observation_day) last_report
 22        from osmc.daily_summary_mv ds
 23        where
 24          (ds.observation_day >= to_date('2008-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss')
 25           AND ds.observation_day < to_date('2008-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss')
 26          )
 27           AND (ds.last_report_longitude_390 >= 242 AND ds.last_report_longitude_390 <= 254 ) AND
 28               (ds.last_report_latitude>=3 AND ds.last_report_latitude<=9)
 29        group by ds.platform_code) z
 30  where ds.platform_code = plat.platform_code
 31    and plat.id = pta.platform_id
 32    and pta.platform_type_id = ptv.id
 33    and ov.observation_date >= to_date('2008-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss')
 34    AND ov.observation_date < to_date('2008-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss')
 35    and z.last_report = ov.observation_date
 36    and ds.last_report_obs_id = ov.observation_id
 37    and ov.parameter_id = p.id
 38    and z.id = ds.platform_code
 39    and z.last_report = ds.observation_day
 40  UNION
 41  SELECT
 42      DISTINCT ds.platform_code id,
 43      ds.platform_type type,
 44      ds.country country,
 45      ds.last_report_latitude latitude,
 46      ds.last_report_longitude_390 longitude,
 47      ds.observation_day last_report,
 48      NULL,
 49      NVL(plat.url,'noURL') URL
 50  FROM
 51      osmc.daily_summary_mv ds
 52     ,osmc.platform plat
 53     ,osmc.platform_type_association pta
 54     ,osmc.platform_type_view ptv
 55  WHERE
 56       ds.platform_code = plat.platform_code
 57       and plat.id = pta.platform_id
 58       and ptv.id = pta.platform_type_id
 59       and ds.observation_day = to_date('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
 60       and ds.operational_status_id = 2
 61       and (ds.last_report_longitude_390 >= 242 AND ds.last_report_longitude_390 <= 254 )
 62       and (ds.last_report_latitude>=3 AND ds.last_report_latitude<=9)
 63       and ds.platform_code = plat.platform_code
 64  order by id,Last_Report;

Explained.

SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1452056214

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |                           |     2 |   231 |   330   (3)| 00:00:04 |       |       |
|   1 |  SORT UNIQUE                                     |                           |     2 |   231 |   329  (11)| 00:00:04 |       |       |
|   2 |   UNION-ALL                                      |                           |       |       |            |          |       |       |
|*  3 |    HASH JOIN                                     |                           |     1 |   133 |   300   (2)| 00:00:04 |       |       |
|   4 |     NESTED LOOPS                                 |                           |     1 |   131 |   296   (2)| 00:00:04 |       |       |
|   5 |      NESTED LOOPS                                |                           |     1 |   123 |   295   (2)| 00:00:04 |       |       |
|   6 |       NESTED LOOPS                               |                           |     1 |   109 |   293   (2)| 00:00:04 |       |       |
|   7 |        NESTED LOOPS                              |                           |     1 |   103 |   292   (2)| 00:00:04 |       |       |
|   8 |         NESTED LOOPS                             |                           |     1 |    76 |   291   (2)| 00:00:04 |       |       |
|   9 |          VIEW                                    |                           |     1 |    16 |   285   (2)| 00:00:04 |       |       |
|* 10 |           FILTER                                 |                           |       |       |            |          |       |       |
|  11 |            HASH GROUP BY                         |                           |     1 |    26 |   285   (2)| 00:00:04 |       |       |
|  12 |             PARTITION RANGE SINGLE               |                           |    16 |   416 |   284   (1)| 00:00:04 |    17 |    17 |
|* 13 |              MAT_VIEW ACCESS BY LOCAL INDEX ROWID| DAILY_SUMMARY_MV          |    16 |   416 |   284   (1)| 00:00:04 |    17 |    17 |
|* 14 |               INDEX RANGE SCAN                   | DAILY_SUMMARY_MV_IDX1     | 12872 |       |   120   (1)| 00:00:02 |    17 |    17 |
|  15 |          PARTITION RANGE SINGLE                  |                           |     1 |    60 |     6   (0)| 00:00:01 |    17 |    17 |
|* 16 |           MAT_VIEW ACCESS BY LOCAL INDEX ROWID   | DAILY_SUMMARY_MV          |     1 |    60 |     6   (0)| 00:00:01 |    17 |    17 |
|* 17 |            INDEX RANGE SCAN                      | DAILY_SUMMARY_MV_IDX1     |    63 |       |     5   (0)| 00:00:01 |    17 |    17 |
|  18 |         TABLE ACCESS BY INDEX ROWID              | PLATFORM                  |     1 |    27 |     1   (0)| 00:00:01 |       |       |
|* 19 |          INDEX UNIQUE SCAN                       | PLATFORM_PLAFORM_CODE_UK  |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 20 |        INDEX RANGE SCAN                          | NS_PLATFORM_TYPE_ASSOC_PK |     1 |     6 |     1   (0)| 00:00:01 |       |       |
|  21 |       PARTITION RANGE SINGLE                     |                           |     1 |    14 |     2   (0)| 00:00:01 |    17 |    17 |
|* 22 |        INDEX RANGE SCAN                          | NS_OBSERVATION_VALUES_PK  |     1 |    14 |     2   (0)| 00:00:01 |    17 |    17 |
|  23 |      TABLE ACCESS BY INDEX ROWID                 | PARAMETER                 |     1 |     8 |     1   (0)| 00:00:01 |       |       |
|* 24 |       INDEX UNIQUE SCAN                          | NS_PARAMETER_PK           |     1 |       |     0   (0)| 00:00:01 |       |       |
|  25 |     VIEW                                         |                           |    55 |   110 |     3   (0)| 00:00:01 |       |       |
|* 26 |      FILTER                                      |                           |       |       |            |          |       |       |
|  27 |       CONNECT BY WITHOUT FILTERING               |                           |       |       |            |          |       |       |
|  28 |        TABLE ACCESS FULL                         | PLATFORM_TYPE             |    55 |  1320 |     3   (0)| 00:00:01 |       |       |
|  29 |    NESTED LOOPS                                  |                           |     1 |    98 |    27   (0)| 00:00:01 |       |       |
|  30 |     MERGE JOIN CARTESIAN                         |                           |    12 |  1104 |    27   (0)| 00:00:01 |       |       |
|  31 |      NESTED LOOPS                                |                           |     1 |    90 |    24   (0)| 00:00:01 |       |       |
|  32 |       PARTITION RANGE SINGLE                     |                           |     1 |    63 |    23   (0)| 00:00:01 |     1 |     1 |
|* 33 |        MAT_VIEW ACCESS BY LOCAL INDEX ROWID      | DAILY_SUMMARY_MV          |     1 |    63 |    23   (0)| 00:00:01 |     1 |     1 |
|* 34 |         INDEX RANGE SCAN                         | DAILY_SUMMARY_MV_IDX1     |  1078 |       |     8   (0)| 00:00:01 |     1 |     1 |
|  35 |       TABLE ACCESS BY INDEX ROWID                | PLATFORM                  |     1 |    27 |     1   (0)| 00:00:01 |       |       |
|* 36 |        INDEX UNIQUE SCAN                         | PLATFORM_PLAFORM_CODE_UK  |     1 |       |     0   (0)| 00:00:01 |       |       |
|  37 |      BUFFER SORT                                 |                           |    55 |   110 |    26   (0)| 00:00:01 |       |       |
|  38 |       VIEW                                       |                           |    55 |   110 |     3   (0)| 00:00:01 |       |       |
|* 39 |        FILTER                                    |                           |       |       |            |          |       |       |
|  40 |         CONNECT BY WITHOUT FILTERING             |                           |       |       |            |          |       |       |
|  41 |          TABLE ACCESS FULL                       | PLATFORM_TYPE             |    55 |  1320 |     3   (0)| 00:00:01 |       |       |
|* 42 |     INDEX UNIQUE SCAN                            | NS_PLATFORM_TYPE_ASSOC_PK |     1 |     6 |     0   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("PTA"."PLATFORM_TYPE_ID"="ID")
  10 - filter(MAX("DS"."OBSERVATION_DAY")>=TO_DATE('2008-02-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              MAX("DS"."OBSERVATION_DAY")<TO_DATE('2008-02-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  13 - filter("DS"."LAST_REPORT_LATITUDE"<=9 AND "DS"."LAST_REPORT_LONGITUDE_390">=242 AND "DS"."LAST_REPORT_LONGITUDE_390"<=254 AND
              "DS"."LAST_REPORT_LATITUDE">=3)
  14 - access("DS"."OBSERVATION_DAY">=TO_DATE('2008-02-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "DS"."OBSERVATION_DAY"<TO_DATE('2008-02-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  16 - filter("Z"."ID"="DS"."PLATFORM_CODE")
  17 - access("Z"."LAST_REPORT"="DS"."OBSERVATION_DAY")
       filter("DS"."OBSERVATION_DAY">=TO_DATE('2008-02-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "DS"."OBSERVATION_DAY"<TO_DATE('2008-02-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  19 - access("DS"."PLATFORM_CODE"="PLAT"."PLATFORM_CODE")
  20 - access("PLAT"."ID"="PTA"."PLATFORM_ID")
  22 - access("Z"."LAST_REPORT"="OV"."OBSERVATION_DATE" AND "DS"."LAST_REPORT_OBS_ID"="OV"."OBSERVATION_ID")
       filter("OV"."OBSERVATION_DATE">=TO_DATE('2008-02-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "OV"."OBSERVATION_DATE"<TO_DATE('2008-02-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  24 - access("OV"."PARAMETER_ID"="P"."ID")
  26 - filter(TO_DATE('2008-02-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss')<TO_DATE('2008-02-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              TO_DATE('2008-02-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss')>TO_DATE('2008-02-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  33 - filter("DS"."OPERATIONAL_STATUS_ID"=2 AND "DS"."LAST_REPORT_LONGITUDE_390">=242 AND "DS"."LAST_REPORT_LATITUDE">=3 AND
              "DS"."LAST_REPORT_LATITUDE"<=9 AND "DS"."LAST_REPORT_LONGITUDE_390"<=254)
  34 - access("DS"."OBSERVATION_DAY"=TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  36 - access("DS"."PLATFORM_CODE"="PLAT"."PLATFORM_CODE")
  39 - filter(242<=254 AND 3<=9)
  42 - access("PLAT"."ID"="PTA"."PLATFORM_ID" AND "ID"="PTA"."PLATFORM_TYPE_ID")

Note
-----
   - 'PLAN_TABLE' is old version

82 rows selected.

results

ID                   TYPE                                     CO   LATITUDE  LONGITUDE LAST_REPORT          NAME                 URL
-------------------- ---------------------------------------- -- ---------- ---------- -------------------- 
32315                TROPICAL MOORED BUOYS                    US      5.038    249.901  2008/02/21 20:00;00 ATMP                 noURL
32315                TROPICAL MOORED BUOYS                    US      5.038    249.901  2008/02/21 20:00;00 WINDDIR              noURL
32315                TROPICAL MOORED BUOYS                    US      5.038    249.901  2008/02/21 20:00;00 WINDSPD              noURL
32315                TROPICAL MOORED BUOYS                    US       5.04      249.9  2008/02/21 20:00;00 ATMP                 noURL
32315                TROPICAL MOORED BUOYS                    US       5.04      249.9  2008/02/21 20:00;00 SST                  noURL
32315                TROPICAL MOORED BUOYS                    US       5.04      249.9  2008/02/21 20:00;00 WINDDIR              noURL
32315                TROPICAL MOORED BUOYS                    US       5.04      249.9  2008/02/21 20:00;00 WINDSPD              noURL
32558                DRIFTING BUOYS (GENERIC)                 US      6.747    242.879  2008/02/21 23:37;00 SST                  noURL
32558                DRIFTING BUOYS (GENERIC)                 US       6.75     242.88  2008/02/21 23:37;00 SST                  noURL
3900408              PROFILING FLOATS AND GLIDERS (GENERIC)   US      7.784    252.161  2008/02/20 23:15;00 ZSAL                 http://wo.jcommops.org/cgi-bin/WebObjects/Argo.woa/wa/ptf?wmo=3900408
3900408              PROFILING FLOATS AND GLIDERS (GENERIC)   US      7.784    252.161  2008/02/20 23:15;00 ZTMP                 http://wo.jcommops.org/cgi-bin/WebObjects/Argo.woa/wa/ptf?wmo=3900408
43001                TROPICAL MOORED BUOYS                    US      8.056     249.85  2008/02/21 20:00;00 ATMP                 noURL
43001                TROPICAL MOORED BUOYS                    US      8.056     249.85  2008/02/21 20:00;00 WINDDIR              noURL
43001                TROPICAL MOORED BUOYS                    US      8.056     249.85  2008/02/21 20:00;00 WINDSPD              noURL
43001                TROPICAL MOORED BUOYS                    US       8.06     249.85  2008/02/21 20:00;00 ATMP                 noURL
43001                TROPICAL MOORED BUOYS                    US       8.06     249.85  2008/02/21 20:00;00 SST                  noURL
43001                TROPICAL MOORED BUOYS                    US       8.06     249.85  2008/02/21 20:00;00 WINDDIR              noURL
43001                TROPICAL MOORED BUOYS                    US       8.06     249.85  2008/02/21 20:00;00 WINDSPD              noURL
51635                DRIFTING BUOYS (GENERIC)                 US       8.81      245.3  2008/02/21 23:48;00 SST                  noURL
51635                DRIFTING BUOYS (GENERIC)                 US       8.81    245.304  2008/02/21 23:48;00 SST                  noURL
51739                DRIFTING BUOYS (GENERIC)                 US      8.298    243.732  2008/02/21 23:48;00 SST                  noURL
51739                DRIFTING BUOYS (GENERIC)                 US        8.3     243.73  2008/02/21 23:48;00 SST                  noURL
51767                DRIFTING BUOYS (GENERIC)                 US      6.338    243.489  2008/02/21 23:47;00 SST                  noURL
51767                DRIFTING BUOYS (GENERIC)                 US       6.34     243.49  2008/02/21 23:47;00 SST                  noURL

autotrace

[auerbach@cheetah auerbach]$ sqlplus las@devl.ndbc.noaa.gov

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Mar 3 17:11:46 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set timing on;
SQL> set linesize 5000;
SQL> set pagesize 100;
SQL> set autotrace trace;
SQL> select distinct
  2       z.id
  3      ,ds.platform_type type
  4      ,ds.country country
  5      ,ds.last_report_latitude latitude
  6      ,ds.last_report_longitude_390 longitude
  7      ,z.last_report
  8      ,p.name
  9      ,nvl(plat.url,'noURL') URL
 10  from
 11       osmc.daily_summary_mv ds
 12      ,osmc.platform plat
 13      ,osmc.observation_value ov
 14      ,osmc.parameter p
 15      ,osmc.platform_type_association pta
 16      ,osmc.platform_type_view ptv
 17      ,
 18       (select
 19           ds.platform_code id
 20          ,max(ds.observation_day) last_report
 21        from osmc.daily_summary_mv ds
 22        where
 23          (ds.observation_day >= to_date('2008-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss')
 24           AND ds.observation_day < to_date('2008-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss')
 25          )
 26           AND (ds.last_report_longitude_390 >= 242 AND ds.last_report_longitude_390 <= 254 ) AND
 27               (ds.last_report_latitude>=3 AND ds.last_report_latitude<=9)
 28        group by ds.platform_code) z
 29  where ds.platform_code = plat.platform_code
 30    and plat.id = pta.platform_id
 31    and pta.platform_type_id = ptv.id
 32    and ov.observation_date >= to_date('2008-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss')
 33    AND ov.observation_date < to_date('2008-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss')
 34    and z.last_report = ov.observation_date
 35    and ds.last_report_obs_id = ov.observation_id
 36    and ov.parameter_id = p.id
 37    and z.id = ds.platform_code
 38    and z.last_report = ds.observation_day
 39  UNION
 40  SELECT
 41      DISTINCT ds.platform_code id,
 42      ds.platform_type type,
 43      ds.country country,
 44      ds.last_report_latitude latitude,
 45      ds.last_report_longitude_390 longitude,
 46      ds.observation_day last_report,
 47      NULL,
 48      NVL(plat.url,'noURL') URL
 49  FROM
 50      osmc.daily_summary_mv ds
 51     ,osmc.platform plat
 52     ,osmc.platform_type_association pta
 53     ,osmc.platform_type_view ptv
 54  WHERE
 55       ds.platform_code = plat.platform_code
 56       and plat.id = pta.platform_id
 57       and ptv.id = pta.platform_type_id
 58       and ds.observation_day = to_date('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
 59       and ds.operational_status_id = 2
 60       and (ds.last_report_longitude_390 >= 242 AND ds.last_report_longitude_390 <= 254 )
 61       and (ds.last_report_latitude>=3 AND ds.last_report_latitude<=9)
 62       and ds.platform_code = plat.platform_code
 63  order by id,Last_Report;

24 rows selected.

Elapsed: 00:00:01.34

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=330 Card=2 Bytes=231)
   1    0   SORT (UNIQUE) (Cost=329 Card=2 Bytes=231)
   2    1     UNION-ALL
   3    2       HASH JOIN (Cost=300 Card=1 Bytes=133)
   4    3         NESTED LOOPS (Cost=296 Card=1 Bytes=131)
   5    4           NESTED LOOPS (Cost=295 Card=1 Bytes=123)
   6    5             NESTED LOOPS (Cost=293 Card=1 Bytes=109)
   7    6               NESTED LOOPS (Cost=292 Card=1 Bytes=103)
   8    7                 NESTED LOOPS (Cost=291 Card=1 Bytes=76)
   9    8                   VIEW (Cost=285 Card=1 Bytes=16)
  10    9                     FILTER
  11   10                       HASH (GROUP BY) (Cost=285 Card=1 Bytes=26)
  12   11                         PARTITION RANGE (SINGLE) (Cost=284 Card=16 Bytes=416)
  13   12                           MAT_VIEW ACCESS (BY LOCAL INDEX ROWID) OF 'DAILY_SUMMARY_MV' (MAT_VIEW) (Cost=284 Card=16 Bytes=416)
  14   13                             INDEX (RANGE SCAN) OF 'DAILY_SUMMARY_MV_IDX1' (INDEX) (Cost=120 Card=12872)
  15    8                   PARTITION RANGE (SINGLE) (Cost=6 Card=1 Bytes=60)
  16   15                     MAT_VIEW ACCESS (BY LOCAL INDEX ROWID) OF 'DAILY_SUMMARY_MV' (MAT_VIEW) (Cost=6 Card=1 Bytes=60)
  17   16                       INDEX (RANGE SCAN) OF 'DAILY_SUMMARY_MV_IDX1' (INDEX) (Cost=5 Card=63)
  18    7                 TABLE ACCESS (BY INDEX ROWID) OF 'PLATFORM'(TABLE) (Cost=1 Card=1 Bytes=27)
  19   18                   INDEX (UNIQUE SCAN) OF 'PLATFORM_PLAFORM_CODE_UK' (INDEX (UNIQUE)) (Cost=0 Card=1)
  20    6               INDEX (RANGE SCAN) OF 'NS_PLATFORM_TYPE_ASSOC_PK' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=6)
  21    5             PARTITION RANGE (SINGLE) (Cost=2 Card=1 Bytes=14)
  22   21               INDEX (RANGE SCAN) OF 'NS_OBSERVATION_VALUES_PK' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=14)
  23    4           TABLE ACCESS (BY INDEX ROWID) OF 'PARAMETER' (TABLE) (Cost=1 Card=1 Bytes=8)
  24   23             INDEX (UNIQUE SCAN) OF 'NS_PARAMETER_PK' (INDEX(UNIQUE)) (Cost=0 Card=1)
  25    3         VIEW (Cost=3 Card=55 Bytes=110)
  26   25           FILTER
  27   26             CONNECT BY (WITHOUT FILTERING)
  28   27               TABLE ACCESS (FULL) OF 'PLATFORM_TYPE' (TABLE)(Cost=3 Card=55 Bytes=1320)
  29    2       NESTED LOOPS (Cost=27 Card=1 Bytes=98)
  30   29         MERGE JOIN (CARTESIAN) (Cost=27 Card=12 Bytes=1104)
  31   30           NESTED LOOPS (Cost=24 Card=1 Bytes=90)
  32   31             PARTITION RANGE (SINGLE) (Cost=23 Card=1 Bytes=63)
  33   32               MAT_VIEW ACCESS (BY LOCAL INDEX ROWID) OF 'DAILY_SUMMARY_MV' (MAT_VIEW) (Cost=23 Card=1 Bytes=63)
  34   33                 INDEX (RANGE SCAN) OF 'DAILY_SUMMARY_MV_IDX1' (INDEX) (Cost=8 Card=1078)
  35   31             TABLE ACCESS (BY INDEX ROWID) OF 'PLATFORM' (TABLE) (Cost=1 Card=1 Bytes=27)
  36   35               INDEX (UNIQUE SCAN) OF 'PLATFORM_PLAFORM_CODE_UK' (INDEX (UNIQUE)) (Cost=0 Card=1)
  37   30           BUFFER (SORT) (Cost=26 Card=55 Bytes=110)
  38   37             VIEW (Cost=3 Card=55 Bytes=110)
  39   38               FILTER
  40   39                 CONNECT BY (WITHOUT FILTERING)
  41   40                   TABLE ACCESS (FULL) OF 'PLATFORM_TYPE' (TABLE) (Cost=3 Card=55 Bytes=1320)
  42   29         INDEX (UNIQUE SCAN) OF 'NS_PLATFORM_TYPE_ASSOC_PK' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=6)

Statistics
----------------------------------------------------------
         15  recursive calls
          0  db block gets
        876  consistent gets
          0  physical reads
          0  redo size
       1618  bytes sent via SQL*Net to client
        988  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         24  rows processed
Personal tools