OsmcQueryAmod

From NGDCWiki

Jump to: navigation, search

Contents

Modified version of Query A we'd like to use? (with spatial object)

  • joins tables, doesn't use daily summary table
  • is a specific, not a generalized query, i.e. does not take into account ship delay or non-reporting platforms
  • uses spatial object (but spatial index is not in place at NDBC, so can't use it there)
  • returns what I (NAA) think are the expected results:
    • the count of the number of times the parameter was measured in the time range and area specified
    • the location and time of the last report of specified parameter by the platform that measured the parameter
  • uses platform_type_view
  • however, does not include a input/conversion from -180/180 to 30/390
  • gives additional platform subtype/type information, but is flexible and accommodates querying on anything by using the platform_type_view
  • need to add url from platform table, I forgot about that

SQL for modified Query A

select 
  p.platform_code
, oo.cnt parameter_period_count
, oo.platform_subtype
, oo.platform_type
, oo.country
, o.shape.sdo_point.y last_parameter_report_lat
, o.shape.sdo_point.x last_parameter_report_lon
, o.observation_date last_parameter_report_date
from 
       observation o
    ,  platform p
    ,(
      select p.id
           , trunc(o.observation_date) ob_date
           , max(o.observation_date) max_ob_time
           , pr.name
           , ptv.name platform_subtype
           , ptv.platform_type
           , c.name country
           , count(*) cnt
      from observation o
          ,platform p
          ,platform_type_association pta
          ,platform_type_view ptv
          ,observation_value ov
          ,parameter pr
          ,country c
      where o.observation_date  >= to_date('2007-11-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.observation_date  <  to_date('2007-11-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and ov.observation_date  >= to_date('2007-11-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and ov.observation_date  <  to_date('2007-11-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.observation_date = ov.observation_date
        and o.id = ov.observation_id
        and o.platform_id = p.id
        and ov.parameter_id = pr.id
        and p.country_id = c.id
        and p.id = pta.platform_id
        and pta.platform_type_id = ptv.id
        and pr.name = 'ATMP'
-- specify a parameter name
        and SDO_ANYINTERACT(o.shape,
            SDO_GEOMETRY(2003, 8307, NULL,
              SDO_ELEM_INFO_ARRAY(1,1003,3),
              SDO_ORDINATE_ARRAY(-45,-45, 45,45))
            ) = 'TRUE'
-- specify a rectangle with lower-left, upper-right coordinates
        and c.name = 'UNITED STATES'
-- specify a country name
        and ptv.platform_type = 'SHIPS'
-- specify any platform type (e.g. VOSCLIM, SHIPS, etc.)
      group by p.id
             , trunc(o.observation_date)
             , pr.name
             , ptv.platform_type
             , c.name
             , ptv.name
    ) oo
where oo.id = o.platform_id
  and oo.max_ob_time = o.observation_date
  and oo.id = p.id
  and o.platform_id = p.id
order by oo.platform_subtype,p.platform_code;

Results from modified Query A

PLATFORM_CODE        PARAMETER_PERIOD_COUNT PLATFORM_SUBTYPE                         PLATFORM_TYPE                            COUNTRY                                            LAST_PARAMETER_REPORT_LAT LAST_PARAMETER_REPORT_LON LAST_PARAMETER_REPORT_DATE 
-------------------- ---------------------- ---------------------------------------- ---------------------------------------- -------------------------------------------------- ------------------------- ------------------------- ------------------------- 
15655                4                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      42.5                      27.48                      2007/11/04 18:00:00      
A8BL4                5                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      33.6                      -43.9                      2007/11/04 12:00:00      
C6FU4                1                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      44.3                      -8.9                       2007/11/04 00:00:00      
PBGH                 6                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      43.5                      10.3                       2007/11/04 15:00:00      
WAHV                 3                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      15.1                      41.9                       2007/11/04 12:00:00      
WDD3826              1                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      36.3                      -31.9                      2007/11/04 06:00:00      
3EFX5                1                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      40.8                      -2.5                       2007/11/04 00:00:00      
C6QK                 4                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      40.8                      -33.8                      2007/11/04 18:00:00      
ELPX7                4                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      -5.1                      -34.9                      2007/11/04 18:00:00      
KCKB                 3                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      -17.8                     38.7                       2007/11/04 12:00:00      
KGXA                 2                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      34.8                      -39.7                      2007/11/04 18:00:00      
KHJB                 2                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      35.9                      -7.5                       2007/11/04 18:00:00      
KRHX                 3                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      38                        -14.6                      2007/11/04 18:00:00      
KRPB                 2                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      35.9                      -8.9                       2007/11/04 12:00:00      
PDGS                 12                     VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      29.7                      -11.2                      2007/11/04 21:00:00      
V7BW7                1                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      38.4                      8.8                        2007/11/04 12:00:00      
VRZN7                4                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      37.5                      -19.8                      2007/11/04 18:00:00      
WDC7175              2                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      -2                        -39.7                      2007/11/04 18:00:00      
WRYC                 2                      VOSCLIM                                  SHIPS                                    UNITED STATES                                      41                        -29.8                      2007/11/04 18:00:00      
WRYD                 3                      VOSCLIM                                  SHIPS                                    UNITED STATES                                      41.5                      -38.6                      2007/11/04 18:00:00      

20 rows selected

Check of modified Query A

select id,platform_code from platform where platform_code = 'PDGS';

ID                     PLATFORM_CODE        
---------------------- -------------------- 
4805                   PDGS                 

1 rows selected

ALTER SESSION SET NLS_DATE_FORMAT = 'SYYYY/MM/DD HH24:MI:SS';
select o.observation_date
     , o.ID
     , o.platform_id
     , p.name
     , ov.value
     , o.datasource_id
 from observation o,observation_value ov,parameter p 
where o.platform_id = 4805
  and p.name = 'ATMP'
  and o.observation_date  >= to_date('2007-11-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
  and o.observation_date  <  to_date('2007-11-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
  and ov.observation_date  >= to_date('2007-11-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
  and ov.observation_date  <  to_date('2007-11-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
  and o.observation_date = ov.observation_date
  and o.id = ov.observation_id
  and ov.parameter_id = p.id
order by o.observation_date;

ALTER SESSION SET succeeded.
OBSERVATION_DATE          ID                     PLATFORM_ID            NAME                 VALUE                  DATASOURCE_ID          
------------------------- ---------------------- ---------------------- -------------------- ---------------------- ---------------------- 
 2007/11/04 01:00:00      60807748               4805                   ATMP                 18                     1                      
 2007/11/04 05:00:00      60797048               4805                   ATMP                 19                     4                      
 2007/11/04 07:00:00      60801504               4805                   ATMP                 19                     4                      
 2007/11/04 08:00:00      60803076               4805                   ATMP                 19                     4                      
 2007/11/04 10:00:00      60807018               4805                   ATMP                 20                     4                      
 2007/11/04 11:00:00      60810001               4805                   ATMP                 20                     4                      
 2007/11/04 12:00:00      60812525               4805                   ATMP                 21                     4                      
 2007/11/04 18:00:00      60826107               4805                   ATMP                 19                     4                      
 2007/11/04 19:00:00      60829364               4805                   ATMP                 20                     4                      
 2007/11/04 19:00:00      60878542               4805                   ATMP                 20                     3                      
 2007/11/04 20:00:00      60832280               4805                   ATMP                 20.1                   4                      
 2007/11/04 21:00:00      60835092               4805                   ATMP                 20                     4                      

12 rows selected


Explain Plan

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

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Jan 23 15:04:22 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> explain plan for
  2  select
  3    p.platform_code
  4  , oo.cnt parameter_period_count
  5  , oo.platform_subtype
  6  , oo.platform_type
  7  , oo.country
  8  , o.shape.sdo_point.y last_parameter_report_lat
  9  , o.shape.sdo_point.x last_parameter_report_lon
 10  , o.observation_date last_parameter_report_date
 11  from
 12         osmc.observation o
 13      ,  osmc.platform p
 14      ,(
 15        select p.id
 16             , trunc(o.observation_date) ob_date
 17             , max(o.observation_date) max_ob_time
 18             , pr.name
 19             , ptv.name platform_subtype
 20             , ptv.platform_type
 21             , c.name country
 22             , count(*) cnt
 23        from osmc.observation o
 24            ,osmc.platform p
 25            ,osmc.platform_type_association pta
 26            ,osmc.platform_type_view ptv
 27            ,osmc.observation_value ov
 28            ,osmc.parameter pr
 29            ,osmc.country c
 30        where o.observation_date  >= to_date('2007-11-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
 31          and o.observation_date  <  to_date('2007-11-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
 32          and ov.observation_date  >= to_date('2007-11-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
 33          and ov.observation_date  <  to_date('2007-11-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
 34          and o.observation_date = ov.observation_date
 35          and o.id = ov.observation_id
 36          and o.platform_id = p.id
 37          and ov.parameter_id = pr.id
 38          and p.country_id = c.id
 39          and p.id = pta.platform_id
 40          and pta.platform_type_id = ptv.id
 41          and pr.name = 'ATMP'
 42  -- specify a parameter name
 43          and SDO_ANYINTERACT(o.shape,
 44              SDO_GEOMETRY(2003, 8307, NULL,
 45                SDO_ELEM_INFO_ARRAY(1,1003,3),
 46                SDO_ORDINATE_ARRAY(-45,-45, 45,45))
 47              ) = 'TRUE'
 48  -- specify a rectangle with lower-left, upper-right coordinates
 49          and c.name = 'UNITED STATES'
 50  -- specify a country name
 51          and ptv.platform_type = 'SHIPS'
 52  -- specify any platform type (e.g. VOSCLIM, SHIPS, etc.)
 53        group by p.id
 54               , trunc(o.observation_date)
 55               , pr.name
 56               , ptv.platform_type
 57               , c.name
 58               , ptv.name
 59      ) oo
 60  where oo.id = o.platform_id
 61    and oo.max_ob_time = o.observation_date
 62    and oo.id = p.id
 63    and o.platform_id = p.id
 64  order by oo.platform_subtype,p.platform_code;

Explained.
SQL> set linesize 5000;

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3847106040

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                           |     1 |   131 |   732   (1)| 00:00:09 |       |       |
|   1 |  SORT ORDER BY                                 |                           |     1 |   131 |   732   (1)| 00:00:09 |       |       |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID            | OBSERVATION               |     1 |    24 |     3   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                                |                           |     1 |   131 |   731   (1)| 00:00:09 |       |       |
|   4 |     NESTED LOOPS                               |                           |     1 |   107 |   728   (1)| 00:00:09 |       |       |
|   5 |      VIEW                                      |                           |     1 |    97 |   727   (1)| 00:00:09 |       |       |
|*  6 |       FILTER                                   |                           |       |       |            |          |       |       |
|   7 |        HASH GROUP BY                           |                           |     1 |   147 |   727   (1)| 00:00:09 |       |       |
|*  8 |         HASH JOIN                              |                           |     1 |   147 |   726   (1)| 00:00:09 |       |       |
|   9 |          NESTED LOOPS                          |                           |     1 |   103 |   723   (1)| 00:00:09 |       |       |
|  10 |           NESTED LOOPS                         |                           |     1 |    95 |   722   (1)| 00:00:09 |       |       |
|  11 |            NESTED LOOPS                        |                           |     1 |    89 |   721   (1)| 00:00:09 |       |       |
|  12 |             NESTED LOOPS                       |                           |    11 |   825 |   688   (1)| 00:00:09 |       |       |
|  13 |              NESTED LOOPS                      |                           |   307 |  6754 |    23   (0)| 00:00:01 |       |       |
|* 14 |               TABLE ACCESS FULL                | COUNTRY                   |     1 |    15 |     3   (0)| 00:00:01 |       |       |
|  15 |               TABLE ACCESS BY INDEX ROWID      | PLATFORM                  |   307 |  2149 |    20   (0)| 00:00:01 |       |       |
|* 16 |                INDEX RANGE SCAN                | PLATFORM_COUNTRY_ID_FK    |   307 |       |     1   (0)| 00:00:01 |       |       |
|  17 |              PARTITION RANGE SINGLE            |                           |     1 |    53 |   688   (1)| 00:00:09 |    16 |    16 |
|  18 |               TABLE ACCESS BY LOCAL INDEX ROWID| OBSERVATION               |     1 |    53 |   688   (1)| 00:00:09 |    16 |    16 |
|  19 |                BITMAP CONVERSION TO ROWIDS     |                           |       |       |            |          |       |       |
|  20 |                 BITMAP AND                     |                           |       |       |            |          |       |       |
|  21 |                  BITMAP CONVERSION FROM ROWIDS |                           |       |       |            |          |       |       |
|  22 |                   SORT ORDER BY                |                           |       |       |            |          |       |       |
|* 23 |                    DOMAIN INDEX                | OBSERVATION_SPX           |     2 |       |            |          |       |       |
|  24 |                  BITMAP CONVERSION FROM ROWIDS |                           |       |       |            |          |       |       |
|  25 |                   SORT ORDER BY                |                           |       |       |            |          |       |       |
|* 26 |                    INDEX RANGE SCAN            | OBSERVATION_IDX1          |     2 |       |     2   (0)| 00:00:01 |    16 |    16 |
|  27 |             PARTITION RANGE SINGLE             |                           |     1 |    14 |     3   (0)| 00:00:01 |    16 |    16 |
|* 28 |              INDEX RANGE SCAN                  | NS_OBSERVATION_VALUES_PK  |     1 |    14 |     3   (0)| 00:00:01 |    16 |    16 |
|* 29 |            INDEX RANGE SCAN                    | NS_PLATFORM_TYPE_ASSOC_PK |     1 |     6 |     1   (0)| 00:00:01 |       |       |
|* 30 |           TABLE ACCESS BY INDEX ROWID          | PARAMETER                 |     1 |     8 |     1   (0)| 00:00:01 |       |       |
|* 31 |            INDEX RANGE SCAN                    | PARAMETER_IDX1            |     1 |       |     0   (0)| 00:00:01 |       |       |
|  32 |          VIEW                                  | PLATFORM_TYPE_VIEW        |    55 |  2420 |     3   (0)| 00:00:01 |       |       |
|  33 |           VIEW                                 |                           |    55 |  3135 |     3   (0)| 00:00:01 |       |       |
|* 34 |            FILTER                              |                           |       |       |            |          |       |       |
|  35 |             CONNECT BY WITHOUT FILTERING       |                           |       |       |            |          |       |       |
|  36 |              TABLE ACCESS FULL                 | PLATFORM_TYPE             |    55 |  1320 |     3   (0)| 00:00:01 |       |       |
|  37 |      TABLE ACCESS BY INDEX ROWID               | PLATFORM                  |     1 |    10 |     1   (0)| 00:00:01 |       |       |
|* 38 |       INDEX UNIQUE SCAN                        | NS_PLATFORM_PK            |     1 |       |     0   (0)| 00:00:01 |       |       |
|  39 |     PARTITION RANGE ITERATOR                   |                           |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 40 |      INDEX RANGE SCAN                          | OBSERVATION_IDX1          |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------------------------------

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

   6 - filter("PR"."NAME"='ATMP' AND "C"."NAME"='UNITED STATES')
   8 - access("PTA"."PLATFORM_TYPE_ID"="PTV"."ID")
  14 - filter("C"."NAME"='UNITED STATES')
  16 - access("P"."COUNTRY_ID"="C"."ID")
  23 - access("MDSYS"."SDO_ANYINTERACT"("O"."SHAPE","MDSYS"."SDO_GEOMETRY"(2003,8307,NULL,"SDO_ELEM_INFO_ARRAY"(1,1003,3),"SDO_ORDIN
              ATE_ARRAY"((-45),(-45),45,45)))='TRUE' AND "O"."PLATFORM_ID"="P"."ID" AND "O"."OBSERVATION_DATE">=TO_DATE('2007-11-04 00:00:00',
              'yyyy-mm-dd hh24:mi:ss') AND "O"."OBSERVATION_DATE"<TO_DATE('2007-11-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  26 - access("O"."PLATFORM_ID"="P"."ID" AND "O"."OBSERVATION_DATE">=TO_DATE('2007-11-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "O"."OBSERVATION_DATE"<TO_DATE('2007-11-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
       filter("O"."OBSERVATION_DATE"<TO_DATE('2007-11-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "O"."OBSERVATION_DATE">=TO_DATE('2007-11-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "O"."PLATFORM_ID"="P"."ID")
  28 - access("O"."OBSERVATION_DATE"="OV"."OBSERVATION_DATE" AND "O"."ID"="OV"."OBSERVATION_ID")
       filter("OV"."OBSERVATION_DATE"<TO_DATE('2007-11-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "OV"."OBSERVATION_DATE">=TO_DATE('2007-11-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  29 - access("P"."ID"="PTA"."PLATFORM_ID")
  30 - filter("OV"."PARAMETER_ID"="PR"."ID")
  31 - access("PR"."NAME"='ATMP')
  34 - filter(ANY='SHIPS' AND TO_DATE('2007-11-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')<TO_DATE('2007-11-05 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND TO_DATE('2007-11-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')>TO_DATE('2007-11-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  38 - access("OO"."ID"="P"."ID")
  40 - access("O"."PLATFORM_ID"="P"."ID" AND "OO"."MAX_OB_TIME"="O"."OBSERVATION_DATE")
       filter("OO"."ID"="O"."PLATFORM_ID")

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

77 rows selected.

Results and Timing

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

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Jan 23 15:09:43 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> select
  2    p.platform_code
  3  , oo.cnt parameter_period_count
  4  , oo.platform_subtype
  5  , oo.platform_type
  6  , oo.country
  7  , o.shape.sdo_point.y last_parameter_report_lat
  8  , o.shape.sdo_point.x last_parameter_report_lon
  9  , o.observation_date last_parameter_report_date
 10  from
 11         osmc.observation o
 12      ,  osmc.platform p
 13      ,(
 14        select p.id
 15             , trunc(o.observation_date) ob_date
 16             , max(o.observation_date) max_ob_time
 17             , pr.name
 18             , ptv.name platform_subtype
 19             , ptv.platform_type
 20             , c.name country
 21             , count(*) cnt
 22        from osmc.observation o
 23            ,osmc.platform p
 24            ,osmc.platform_type_association pta
 25            ,osmc.platform_type_view ptv
 26            ,osmc.observation_value ov
 27            ,osmc.parameter pr
 28            ,osmc.country c
 29        where o.observation_date  >= to_date('2007-11-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
 30          and o.observation_date  <  to_date('2007-11-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
 31          and ov.observation_date  >= to_date('2007-11-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
 32          and ov.observation_date  <  to_date('2007-11-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
 33          and o.observation_date = ov.observation_date
 34          and o.id = ov.observation_id
 35          and o.platform_id = p.id
 36          and ov.parameter_id = pr.id
 37          and p.country_id = c.id
 38          and p.id = pta.platform_id
 39          and pta.platform_type_id = ptv.id
 40          and pr.name = 'ATMP'
 41  -- specify a parameter name
 42          and SDO_ANYINTERACT(o.shape,
 43              SDO_GEOMETRY(2003, 8307, NULL,
 44                SDO_ELEM_INFO_ARRAY(1,1003,3),
 45                SDO_ORDINATE_ARRAY(-45,-45, 45,45))
 46              ) = 'TRUE'
 47  -- specify a rectangle with lower-left, upper-right coordinates
 48          and c.name = 'UNITED STATES'
 49  -- specify a country name
 50          and ptv.platform_type = 'SHIPS'
 51  -- specify any platform type (e.g. VOSCLIM, SHIPS, etc.)
 52        group by p.id
 53               , trunc(o.observation_date)
 54               , pr.name
 55               , ptv.platform_type
 56               , c.name
 57               , ptv.name
    ) oo
 58   59  where oo.id = o.platform_id
 60    and oo.max_ob_time = o.observation_date
 61    and oo.id = p.id
 62    and o.platform_id = p.id
 63  order by oo.platform_subtype,p.platform_code;


Autotrace Results

Modified Version of Query A (without spatial object)

  • Run against DEVL instance at NDBC to compare result set with Query A
    • Note that the result set is quite different...
    • Convert longitudes to -180/180:
      • 223.71840468842848 - 390 = -166.28159531157152
      • 248.9643297687431 - 390 = -141.0356702312569

SQL

select
      p.platform_code
    , oo.cnt parameter_period_count
    , oo.platform_subtype
    , oo.platform_type
    , oo.country
    , o.shape.sdo_point.y last_parameter_report_lat
    , CASE                                                                                                                              WHEN o.shape.sdo_point.x >= 0                                                                                                   THEN o.shape.sdo_point.x + 30                                                                                                 ELSE (o.shape.sdo_point.x + 390)                                                                                                END as lon_390
    , o.shape.sdo_point.x last_parameter_report_lon
   , o.observation_date last_parameter_report_date
   from
          OSMC.observation o
       ,  OSMC.platform p
       ,(
         select p.id
              , trunc(o.observation_date) ob_date
              , max(o.observation_date) max_ob_time
              , pr.name
              , ptv.name platform_subtype
              , ptv.platform_type
              , c.name country
              , count(*) cnt
         from OSMC.observation o
             ,OSMC.platform p
             ,OSMC.platform_type_association pta
             ,OSMC.platform_type_view ptv
             ,OSMC.observation_value ov
             ,OSMC.parameter pr
             ,OSMC.country c
         where o.observation_date  >= to_date('2007-09-18 00:00:00','YYYY-MM-DD HH24:MI:SS')
           and o.observation_date  <  to_date('2007-09-19 00:00:00','YYYY-MM-DD HH24:MI:SS')
           and ov.observation_date  >= to_date('2007-09-18 00:00:00','YYYY-MM-DD HH24:MI:SS')
           and ov.observation_date  <  to_date('2007-09-19 00:00:00','YYYY-MM-DD HH24:MI:SS')
           and o.observation_date = ov.observation_date
           and o.id = ov.observation_id
           and o.platform_id = p.id
           and ov.parameter_id = pr.id
           and p.country_id = c.id
           and p.id = pta.platform_id
           and pta.platform_type_id = ptv.id
           and pr.name = 'SST'
   -- specify a parameter name
   --        and SDO_ANYINTERACT(o.shape,
   --            SDO_GEOMETRY(2003, 8307, NULL,
   --              SDO_ELEM_INFO_ARRAY(1,1003,3),
   --              SDO_ORDINATE_ARRAY(-45,-45, 45,45))
   --            ) = 'TRUE'
   -- specify a rectangle with lower-left, upper-right coordinates
   AND o.shape.sdo_point.x >= -166.28159531157152
   AND o.shape.sdo_point.x <= -141.0356702312569
   AND o.shape.sdo_point.y >= -60.009277233033764
   AND o.shape.sdo_point.y <= -44.61119292862682
   --        and c.name = 'UNITED STATES'
   -- specify a country name
           and ptv.platform_type = 'DRIFTING BUOYS'
   -- specify any platform type (e.g. VOSCLIM, SHIPS, etc.)
         group by p.id
                , trunc(o.observation_date)
                , pr.name
                , ptv.platform_type
                , c.name
                , ptv.name
       ) oo
   where oo.id = o.platform_id
     and oo.max_ob_time = o.observation_date
     and oo.id = p.id
     and o.platform_id = p.id
   order by oo.platform_subtype,p.platform_code;

Autotrace results

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

SQL*Plus: Release 10.1.0.5.0 - Production on Fri Nov 9 15:10:44 2007

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 autotrace on;
SQL> set pagesize 1000;
SQL> set linesize 5000;
SQL> set timing on;
SQL> alter session set nls_date_format = 'syyyy/mm/dd hh24:mi:ss';

Session altered.

Elapsed: 00:00:00.19
SQL> select
  2        p.platform_code
  3      , oo.cnt parameter_period_count
  4      , oo.platform_subtype
  5      , oo.platform_type
  6      , oo.country
  7      , o.shape.sdo_point.y last_parameter_report_lat
  8      , CASE                                                                                                                              WHEN o.shape.sdo_point.x >= 0                                                                                                   THEN o.shape.sdo_point.x + 30                                                                                                 ELSE (o.shape.sdo_point.x + 390)                                                                                                END as lon_390
  9      , o.shape.sdo_point.x last_parameter_report_lon
 10     , o.observation_date last_parameter_report_date
 11     from
 12            OSMC.observation o
 13         ,  OSMC.platform p
 14         ,(
 15           select p.id
 16                , trunc(o.observation_date) ob_date
 17                , max(o.observation_date) max_ob_time
 18                , pr.name
 19                , ptv.name platform_subtype
 20                , ptv.platform_type
 21                , c.name country
 22                , count(*) cnt
 23           from OSMC.observation o
 24               ,OSMC.platform p
 25               ,OSMC.platform_type_association pta
 26               ,OSMC.platform_type_view ptv
 27               ,OSMC.observation_value ov
 28               ,OSMC.parameter pr
 29               ,OSMC.country c
 30           where o.observation_date  >= to_date('2007-09-18 00:00:00','YYYY-MM-DD HH24:MI:SS')
 31             and o.observation_date  <  to_date('2007-09-19 00:00:00','YYYY-MM-DD HH24:MI:SS')
 32             and ov.observation_date  >= to_date('2007-09-18 00:00:00','YYYY-MM-DD HH24:MI:SS')
 33             and ov.observation_date  <  to_date('2007-09-19 00:00:00','YYYY-MM-DD HH24:MI:SS')
 34             and o.observation_date = ov.observation_date
 35             and o.id = ov.observation_id
 36             and o.platform_id = p.id
 37             and ov.parameter_id = pr.id
 38             and p.country_id = c.id
 39             and p.id = pta.platform_id
 40             and pta.platform_type_id = ptv.id
 41             and pr.name = 'SST'
 42     -- specify a parameter name
 43     --        and SDO_ANYINTERACT(o.shape,
 44     --            SDO_GEOMETRY(2003, 8307, NULL,
 45     --              SDO_ELEM_INFO_ARRAY(1,1003,3),
 46     --              SDO_ORDINATE_ARRAY(-45,-45, 45,45))
 47     --            ) = 'TRUE'
 48     -- specify a rectangle with lower-left, upper-right coordinates
 49     AND o.shape.sdo_point.x >= -166.28159531157152
 50     AND o.shape.sdo_point.x <= -141.0356702312569
 51     AND o.shape.sdo_point.y >= -60.009277233033764
 52     AND o.shape.sdo_point.y <= -44.61119292862682
 53     --        and c.name = 'UNITED STATES'
 54     -- specify a country name
 55             and ptv.platform_type = 'DRIFTING BUOYS'
 56     -- specify any platform type (e.g. VOSCLIM, SHIPS, etc.)
 57           group by p.id
 58                  , trunc(o.observation_date)
 59                  , pr.name
 60                  , ptv.platform_type
 61                  , c.name
 62                  , ptv.name
 63         ) oo
 64     where oo.id = o.platform_id
 65       and oo.max_ob_time = o.observation_date
 66       and oo.id = p.id
 67       and o.platform_id = p.id
 68     order by oo.platform_subtype,p.platform_code;

PLATFORM_CODE        PARAMETER_PERIOD_COUNT PLATFORM_SUBTYPE                         PLATFORM_TYPE                            COUNTRY                       LAST_PARAMETER_REPORT_LAT    LON_390 LAST_PARAMETER_REPORT_LON LAST_PARAMETER_REPOR
-------------------- ---------------------- ---------------------------------------- ---------------------------------------- -------------------------------------------------- ------------------------- ---------- ------------------------- --------------------
16560                                    16 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                    -54.13     223.77                    -166.23  2007/09/18 23:30:00
16563                                    43 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                    -53.33     246.94                    -143.06  2007/09/18 23:40:00
17521                                    57 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                   -51.567    245.052                   -144.948  2007/09/18 23:29:00
17521                                    57 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                    -51.57     245.05                    -144.95  2007/09/18 23:29:00
17523                                    44 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                    -46.37     223.92                    -166.08  2007/09/18 23:20:00
17524                                    39 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                    -45.53     229.52                    -160.48  2007/09/18 23:30:00
55627                                    42 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                    -45.23     238.63                    -151.37  2007/09/18 23:10:00
55627                                    42 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                   -45.227    238.632                   -151.368  2007/09/18 23:10:00
55802                                    42 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                    -45.77     244.23                    -145.77  2007/09/18 23:10:00
55917                                    41 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                    -45.99     241.66                    -148.34  2007/09/18 23:00:00
55930                                    45 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                    -45.86     247.16                    -142.84  2007/09/18 23:10:00
55930                                    45 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                   -45.862    247.162                   -142.838  2007/09/18 23:10:00
55937                                    62 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                   -47.519    225.016                   -164.984  2007/09/18 23:10:00
55937                                    62 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                    -47.52     225.02                    -164.98  2007/09/18 23:10:00
55940                                    43 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                    -50.56     234.05                    -155.95  2007/09/18 23:20:00
56539                                    44 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           AUSTRALIA                           -53     225.47                    -164.53  2007/09/18 23:20:00
56539                                    44 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           AUSTRALIA                       -53.004    225.473                   -164.527  2007/09/18 23:20:00
74543                                    59 DRIFTING BUOYS (GENERIC)                 DRIFTING BUOYS                           UNITED STATES                    -50.91     234.76                    -155.24  2007/09/18 23:20:00

18 rows selected.

Elapsed: 00:00:05.50

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=15191 Card=1 Bytes=134)
   1    0   SORT (ORDER BY) (Cost=15191 Card=1 Bytes=134)
   2    1     TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'OBSERVATION' (TABLE) (Cost=3 Card=1 Bytes=24)
   3    2       NESTED LOOPS (Cost=15190 Card=1 Bytes=134)
   4    3         NESTED LOOPS (Cost=15187 Card=1 Bytes=110)
   5    4           VIEW (Cost=15186 Card=1 Bytes=98)
   6    5             FILTER
   7    6               HASH (GROUP BY) (Cost=15186 Card=1 Bytes=126)
   8    7                 NESTED LOOPS (Cost=15185 Card=1 Bytes=126)
   9    8                   HASH JOIN (Cost=15184 Card=1 Bytes=111)
  10    9                     NESTED LOOPS (Cost=15180 Card=1 Bytes=65)
  11   10                       NESTED LOOPS (Cost=15179 Card=1 Bytes=58)
  12   11                         NESTED LOOPS (Cost=15178 Card=1 Bytes=49)
  13   12                           NESTED LOOPS (Cost=15176 Card=2 Bytes=78)
  14   13                             PARTITION RANGE (SINGLE) (Cost=13896 Card=426 Bytes=10650)
  15   14                               TABLE ACCESS (FULL) OF 'OBSERVATION' (TABLE) (Cost=13896 Card=426 Bytes=10650)
  16   13                             PARTITION RANGE (SINGLE) (Cost=3 Card=1 Bytes=14)
  17   16                               INDEX (RANGE SCAN) OF 'NS_OBSERVATION_VALUES_PK' (INDEX (UNIQUE)) (Cost=3 Card=1 Bytes=14)
  18   12                           TABLE ACCESS (BY INDEX ROWID) OF 'PARAMETER' (TABLE) (Cost=1 Card=1 Bytes=10)
  19   18                             INDEX (RANGE SCAN) OF 'PARAMETER_IDX1' (INDEX) (Cost=0 Card=1)
  20   11                         TABLE ACCESS (BY INDEX ROWID) OF 'PLATFORM' (TABLE) (Cost=1 Card=1 Bytes=9)
  21   20                           INDEX (UNIQUE SCAN) OF 'NS_PLATFORM_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
  22   10                       INDEX (RANGE SCAN) OF 'NS_PLATFORM_TYPE_ASSOC_PK' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=7)
  23    9                     VIEW (Cost=3 Card=55 Bytes=2530)
  24   23                       FILTER
  25   24                         CONNECT BY (WITHOUT FILTERING)
  26   25                           TABLE ACCESS (FULL) OF 'PLATFORM_TYPE' (TABLE) (Cost=3 Card=55 Bytes=1485)
  27    8                   TABLE ACCESS (BY INDEX ROWID) OF 'COUNTRY' (TABLE) (Cost=1 Card=1 Bytes=15)
  28   27                     INDEX (UNIQUE SCAN) OF 'NS_COUNTRY_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
  29    4           TABLE ACCESS (BY INDEX ROWID) OF 'PLATFORM' (TABLE) (Cost=1 Card=1 Bytes=12)
  30   29             INDEX (UNIQUE SCAN) OF 'NS_PLATFORM_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
  31    3         PARTITION RANGE (ITERATOR) (Cost=2 Card=1)
  32   31           INDEX (RANGE SCAN) OF 'OBSERVATION_IDX1' (INDEX) (Cost=2 Card=1)

Explain Plan

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

SQL*Plus: Release 10.1.0.5.0 - Production on Fri Nov 9 15:15:40 2007

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 1000;
SQL> explain plan for
  2  select
  3        p.platform_code
  4      , oo.cnt parameter_period_count
  5      , oo.platform_subtype
  6      , oo.platform_type
  7      , oo.country
  8      , o.shape.sdo_point.y last_parameter_report_lat
  9      , CASE                                                                                                                              WHEN o.shape.sdo_point.x >= 0                                                                                                   THEN o.shape.sdo_point.x + 30                                                                                                 ELSE (o.shape.sdo_point.x + 390)                                                                                                END as lon_390
 10      , o.shape.sdo_point.x last_parameter_report_lon
 11     , o.observation_date last_parameter_report_date
 12     from
 13            OSMC.observation o
 14         ,  OSMC.platform p
 15         ,(
 16           select p.id
 17                , trunc(o.observation_date) ob_date
 18                , max(o.observation_date) max_ob_time
 19                , pr.name
 20                , ptv.name platform_subtype
 21                , ptv.platform_type
 22                , c.name country
 23                , count(*) cnt
 24           from OSMC.observation o
 25               ,OSMC.platform p
 26               ,OSMC.platform_type_association pta
 27               ,OSMC.platform_type_view ptv
 28               ,OSMC.observation_value ov
 29               ,OSMC.parameter pr
 30               ,OSMC.country c
 31           where o.observation_date  >= to_date('2007-09-18 00:00:00','YYYY-MM-DD HH24:MI:SS')
 32             and o.observation_date  <  to_date('2007-09-19 00:00:00','YYYY-MM-DD HH24:MI:SS')
 33             and ov.observation_date  >= to_date('2007-09-18 00:00:00','YYYY-MM-DD HH24:MI:SS')
 34             and ov.observation_date  <  to_date('2007-09-19 00:00:00','YYYY-MM-DD HH24:MI:SS')
 35             and o.observation_date = ov.observation_date
 36             and o.id = ov.observation_id
 37             and o.platform_id = p.id
 38             and ov.parameter_id = pr.id
 39             and p.country_id = c.id
 40             and p.id = pta.platform_id
 41             and pta.platform_type_id = ptv.id
 42             and pr.name = 'SST'
 43     -- specify a parameter name
 44     --        and SDO_ANYINTERACT(o.shape,
 45     --            SDO_GEOMETRY(2003, 8307, NULL,
 46     --              SDO_ELEM_INFO_ARRAY(1,1003,3),
 47     --              SDO_ORDINATE_ARRAY(-45,-45, 45,45))
 48     --            ) = 'TRUE'
 49     -- specify a rectangle with lower-left, upper-right coordinates
 50     AND o.shape.sdo_point.x >= -166.28159531157152
 51     AND o.shape.sdo_point.x <= -141.0356702312569
 52     AND o.shape.sdo_point.y >= -60.009277233033764
 53     AND o.shape.sdo_point.y <= -44.61119292862682
 54     --        and c.name = 'UNITED STATES'
 55     -- specify a country name
 56             and ptv.platform_type = 'DRIFTING BUOYS'
 57     -- specify any platform type (e.g. VOSCLIM, SHIPS, etc.)
 58           group by p.id
 59                  , trunc(o.observation_date)
 60                  , pr.name
 61                  , ptv.platform_type
 62                  , c.name
 63                  , ptv.name
 64         ) oo
 65     where oo.id = o.platform_id
 66       and oo.max_ob_time = o.observation_date
 67       and oo.id = p.id
 68       and o.platform_id = p.id
 69     order by oo.platform_subtype,p.platform_code;

Explained.

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                           |     1 |   134 | 15191   (5)| 00:03:03 |       |       |
|   1 |  SORT ORDER BY                          |                           |     1 |   134 | 15191   (5)| 00:03:03 |       |       |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID     | OBSERVATION               |     1 |    24 |     3   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                         |                           |     1 |   134 | 15190   (5)| 00:03:03 |       |       |
|   4 |     NESTED LOOPS                        |                           |     1 |   110 | 15187   (5)| 00:03:03 |       |       |
|   5 |      VIEW                               |                           |     1 |    98 | 15186   (5)| 00:03:03 |       |       |
|*  6 |       FILTER                            |                           |       |       |            |          |       |       |
|   7 |        HASH GROUP BY                    |                           |     1 |   126 | 15186   (5)| 00:03:03 |       |       |
|   8 |         NESTED LOOPS                    |                           |     1 |   126 | 15185   (5)| 00:03:03 |       |       |
|*  9 |          HASH JOIN                      |                           |     1 |   111 | 15184   (5)| 00:03:03 |       |       |
|  10 |           NESTED LOOPS                  |                           |     1 |    65 | 15180   (5)| 00:03:03 |       |       |
|  11 |            NESTED LOOPS                 |                           |     1 |    58 | 15179   (5)| 00:03:03 |       |       |
|  12 |             NESTED LOOPS                |                           |     1 |    49 | 15178   (5)| 00:03:03 |       |       |
|  13 |              NESTED LOOPS               |                           |     2 |    78 | 15176   (5)| 00:03:03 |       |       |
|  14 |               PARTITION RANGE SINGLE    |                           |   426 | 10650 | 13896   (6)| 00:02:47 |    15 |    15 |
|* 15 |                TABLE ACCESS FULL        | OBSERVATION               |   426 | 10650 | 13896   (6)| 00:02:47 |    15 |    15 |
|  16 |               PARTITION RANGE SINGLE    |                           |     1 |    14 |     3   (0)| 00:00:01 |    15 |    15 |
|* 17 |                INDEX RANGE SCAN         | NS_OBSERVATION_VALUES_PK  |     1 |    14 |     3   (0)| 00:00:01 |    15 |    15 |
|* 18 |              TABLE ACCESS BY INDEX ROWID| PARAMETER                 |     1 |    10 |     1   (0)| 00:00:01 |       |       |
|* 19 |               INDEX RANGE SCAN          | PARAMETER_IDX1            |     1 |       |     0   (0)| 00:00:01 |       |       |
|  20 |             TABLE ACCESS BY INDEX ROWID | PLATFORM                  |     1 |     9 |     1   (0)| 00:00:01 |       |       |
|* 21 |              INDEX UNIQUE SCAN          | NS_PLATFORM_PK            |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 22 |            INDEX RANGE SCAN             | NS_PLATFORM_TYPE_ASSOC_PK |     1 |     7 |     1   (0)| 00:00:01 |       |       |
|  23 |           VIEW                          |                           |    55 |  2530 |     3   (0)| 00:00:01 |       |       |
|* 24 |            FILTER                       |                           |       |       |            |          |       |       |
|  25 |             CONNECT BY WITHOUT FILTERING|                           |       |       |            |          |       |       |
|  26 |              TABLE ACCESS FULL          | PLATFORM_TYPE             |    55 |  1485 |     3   (0)| 00:00:01 |       |       |
|  27 |          TABLE ACCESS BY INDEX ROWID    | COUNTRY                   |     1 |    15 |     1   (0)| 00:00:01 |       |       |
|* 28 |           INDEX UNIQUE SCAN             | NS_COUNTRY_PK             |     1 |       |     0   (0)| 00:00:01 |       |       |
|  29 |      TABLE ACCESS BY INDEX ROWID        | PLATFORM                  |     1 |    12 |     1   (0)| 00:00:01 |       |       |
|* 30 |       INDEX UNIQUE SCAN                 | NS_PLATFORM_PK            |     1 |       |     0   (0)| 00:00:01 |       |       |
|  31 |     PARTITION RANGE ITERATOR            |                           |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 32 |      INDEX RANGE SCAN                   | OBSERVATION_IDX1          |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------------------

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

   6 - filter("PR"."NAME"='SST')
   9 - access("PTA"."PLATFORM_TYPE_ID"="ID")
  15 - filter("O"."SYS_NC00009$"<=(-141.0356702312569) AND "O"."OBSERVATION_DATE">=TO_DATE('2007-09-18 00:00:00',
              'yyyy-mm-dd hh24:mi:ss') AND "O"."SYS_NC00010$"<=(-44.61119292862682) AND "O"."SYS_NC00010$">=(-60.009277233033764) AND
              "O"."OBSERVATION_DATE"<TO_DATE('2007-09-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "O"."SYS_NC00009$">=(-166.28159531157152))
  17 - access("O"."OBSERVATION_DATE"="OV"."OBSERVATION_DATE" AND "O"."ID"="OV"."OBSERVATION_ID")
       filter("OV"."OBSERVATION_DATE">=TO_DATE('2007-09-18 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "OV"."OBSERVATION_DATE"<TO_DATE('2007-09-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  18 - filter("OV"."PARAMETER_ID"="PR"."ID")
  19 - access("PR"."NAME"='SST')
  21 - access("O"."PLATFORM_ID"="P"."ID")
  22 - access("P"."ID"="PTA"."PLATFORM_ID")
  24 - filter(ANY='DRIFTING BUOYS' AND TO_DATE('2007-09-18 00:00:00', 'yyyy-mm-dd hh24:mi:ss')<TO_DATE('2007-09-19
              00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND (-166.28159531157152)<=(-141.0356702312569) AND
              (-60.009277233033764)<=(-44.61119292862682) AND TO_DATE('2007-09-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss')>TO_DATE('2007-09-18
              00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  28 - access("P"."COUNTRY_ID"="C"."ID")
  30 - access("OO"."ID"="P"."ID")
  32 - access("O"."PLATFORM_ID"="P"."ID" AND "OO"."MAX_OB_TIME"="O"."OBSERVATION_DATE")
       filter("OO"."ID"="O"."PLATFORM_ID")

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

67 rows selected.

Results

SQL> select
  2    p.platform_code
  3  , oo.cnt parameter_period_count
  4  , oo.platform_subtype
  5  , oo.platform_type
  6  , oo.country
  7  , o.shape.sdo_point.y last_parameter_report_lat
  8  , CASE WHEN o.shape.sdo_point.x >= 0 THEN o.shape.sdo_point.x + 30 ELSE (o.shape.sdo_point.x + 390) END as lon_390
  9  , o.shape.sdo_point.x last_parameter_report_lon
 10  , o.observation_date last_parameter_report_date
 11  from
 12         OSMC.observation o
 13      ,  OSMC.platform p
 14      ,(
 15        select p.id
 16             , trunc(o.observation_date) ob_date
 17             , max(o.observation_date) max_ob_time
 18             , pr.name
 19             , ptv.name platform_subtype
 20             , ptv.platform_type
 21             , c.name country
 22             , count(*) cnt
 23        from OSMC.observation o
 24            ,OSMC.platform p
 25            ,OSMC.platform_type_association pta
 26            ,OSMC.platform_type_view ptv
 27            ,OSMC.observation_value ov
 28            ,OSMC.parameter pr
 29            ,OSMC.country c
 30        where o.observation_date  >= to_date('2007-09-18 00:00:00','YYYY-MM-DD HH24:MI:SS')
 31          and o.observation_date  <  to_date('2007-09-19 00:00:00','YYYY-MM-DD HH24:MI:SS')
 32          and ov.observation_date  >= to_date('2007-09-18 00:00:00','YYYY-MM-DD HH24:MI:SS')
 33          and ov.observation_date  <  to_date('2007-09-19 00:00:00','YYYY-MM-DD HH24:MI:SS')
 34          and o.observation_date = ov.observation_date
 35          and o.id = ov.observation_id
 36          and o.platform_id = p.id
 37          and ov.parameter_id = pr.id
 38          and p.country_id = c.id
 39          and p.id = pta.platform_id
 40          and pta.platform_type_id = ptv.id
 41          and pr.name = 'SST'
 42  -- specify a parameter name
 43  --        and SDO_ANYINTERACT(o.shape,
 44  --            SDO_GEOMETRY(2003, 8307, NULL,
 45  --              SDO_ELEM_INFO_ARRAY(1,1003,3),
 46  --              SDO_ORDINATE_ARRAY(-45,-45, 45,45))
 47  --            ) = 'TRUE'
 48  -- specify a rectangle with lower-left, upper-right coordinates
 49  AND o.shape.sdo_point.x >= -166.28159531157152
 50  AND o.shape.sdo_point.x <= -141.0356702312569
 51  AND o.shape.sdo_point.y >= -60.009277233033764
 52  AND o.shape.sdo_point.y <= -44.61119292862682
 53  --        and c.name = 'UNITED STATES'
 54  -- specify a country name
 55          and ptv.platform_type = 'DRIFTING BUOYS'
 56  -- specify any platform type (e.g. VOSCLIM, SHIPS, etc.)
 57        group by p.id
 58               , trunc(o.observation_date)
 59               , pr.name
 60               , ptv.platform_type
 61               , c.name
 62               , ptv.name
 63      ) oo
 64  where oo.id = o.platform_id
 65    and oo.max_ob_time = o.observation_date
 66    and oo.id = p.id
 67    and o.platform_id = p.id
 68  order by oo.platform_subtype,p.platform_code;


PLATFORM_CODE COUNT PLATFORM_SUBTYPE          PLATFORM_TYPE           COUNTRY       LAT    LON_390         LON  LAST_PARAMETER REPORT
------------- ----- ------------------------- -------------- ---------------- --------- ----------------------  ---------------------
16560            16 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES    -54.13     223.77     -166.23  2007/09/18  23:30:00
16563            43 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES    -53.33     246.94     -143.06  2007/09/18  23:40:00
17521            57 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES   -51.567    245.052    -144.948  2007/09/18  23:29:00
17521            57 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES    -51.57     245.05     -144.95  2007/09/18  23:29:00
17523            44 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES    -46.37     223.92     -166.08  2007/09/18  23:20:00
17524            39 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES    -45.53     229.52     -160.48  2007/09/18  23:30:00
55627            42 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES    -45.23     238.63     -151.37  2007/09/18  23:10:00
55627            42 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES   -45.227    238.632    -151.368  2007/09/18  23:10:00
55802            42 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES    -45.77     244.23     -145.77  2007/09/18  23:10:00
55917            41 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES    -45.99     241.66     -148.34  2007/09/18  23:00:00
55930            45 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES    -45.86     247.16     -142.84  2007/09/18  23:10:00
55930            45 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES   -45.862    247.162    -142.838  2007/09/18  23:10:00
55937            62 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES   -47.519    225.016    -164.984  2007/09/18  23:10:00
55937            62 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES    -47.52     225.02     -164.98  2007/09/18  23:10:00
55940            43 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES    -50.56     234.05     -155.95  2007/09/18  23:20:00
56539            44 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    AUSTRALIA           -53     225.47     -164.53  2007/09/18  23:20:00
56539            44 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    AUSTRALIA       -53.004    225.473    -164.527  2007/09/18  23:20:00
74543            59 DRIFTING BUOYS (GENERIC)  DRIFTING BUOYS    UNITED STATES    -50.91     234.76     -155.24  2007/09/18  23:20:00

Check

select id,platform_code from osmc.platform where platform_code = '16560';


        ID PLATFORM_CODE
---------- --------------------
     10280 16560                 

1 rows selected
SQL> alter session set nls_date_format = 'syyyy/mm/dd hh24:mi:ss';

Session altered.

SQL> select     o.observation_date
  2           , o.ID
  3           , o.platform_id
  4           , p.name
  5           , ov.value
  6           , o.datasource_id
  7       from osmc.observation o,osmc.observation_value ov,osmc.parameter p
  8      where o.platform_id = 10280
  9        and p.name = 'SST'
 10       and o.observation_date  >= to_date('2007-09-18 00:00:00','YYYY-MM-DD HH24:MI:SS')
 11       and o.observation_date  <  to_date('2007-09-19 00:00:00','YYYY-MM-DD HH24:MI:SS')
 12       and ov.observation_date  >= to_date('2007-09-18 00:00:00','YYYY-MM-DD HH24:MI:SS')
 13       and ov.observation_date  <  to_date('2007-09-19 00:00:00','YYYY-MM-DD HH24:MI:SS')
 14       and o.observation_date = ov.observation_date
 15       and o.id = ov.observation_id
 16       and o.shape.sdo_point.x >= -166.28159531157152
 17       and o.shape.sdo_point.x <= -141.0356702312569
 18       and o.shape.sdo_point.y >= -60.009277233033764
 19       and o.shape.sdo_point.y <= -44.61119292862682
 20       and ov.parameter_id = p.id
 21  --group by o.observation_date, o.platform_id, p.name
 22     order by o.observation_date;

OBSERVATION_DATE             ID PLATFORM_ID NAME                      VALUE DATASOURCE_ID
-------------------- ---------- ----------- -------------------- ---------- -------------
 2007/09/18 15:30:00   57086287       10280 SST                         4.6    3
 2007/09/18 16:30:00   57066890       10280 SST                         4.9    1
 2007/09/18 16:30:00   57086288       10280 SST                         4.9    3
 2007/09/18 17:30:00   57073739       10280 SST                           5    1
 2007/09/18 17:30:00   57086289       10280 SST                           5    3
 2007/09/18 18:30:00   57078922       10280 SST                         5.1    1
 2007/09/18 18:30:00   57086290       10280 SST                         5.1    3
 2007/09/18 19:30:00   57078858       10280 SST                         5.1    1
 2007/09/18 19:30:00   57086291       10280 SST                         5.1    3
 2007/09/18 20:30:00   57082051       10280 SST                         5.2    1
 2007/09/18 20:30:00   57086292       10280 SST                         5.2    3
 2007/09/18 21:30:00   57082091       10280 SST                         5.3    1
 2007/09/18 21:30:00   57086293       10280 SST                         5.3    3
 2007/09/18 22:30:00   57082071       10280 SST                         5.4    1
 2007/09/18 22:30:00   57086294       10280 SST                         5.4    3
 2007/09/18 23:30:00   57086295       10280 SST                         5.4    3

16 rows selected.

Links

OSMC

Personal tools