Query on 10-day joined table

From NGDCWiki

Jump to: navigation, search

Contents

Comparison Query

  • In this example:
    • The user wants to know how many times individual Ships belonging to the United States reported Air Temperature on February 4, 2008 in the area bounded by the geographic area between -45,45 longitude and -45,45 latitude.
    • The user wants to see each ship's platform code, the count for how many times it reported air temperature, the type of ship, the associated country, and its location (lat,long) and the time the ship last reported air temperature.

Query used when joining tables

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('2008-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.observation_date  <  to_date('2008-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and ov.observation_date  >= to_date('2008-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and ov.observation_date  <  to_date('2008-02-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;

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                      2008/02/04 18:00;00      
4XFX                 2                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      38.9                      -40.5                      2008/02/04 12:00;00      
9VAY4                7                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      38.4                      -44.7                      2008/02/04 20:00;00      
KABL                 2                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      18.7                      39.5                       2008/02/04 12:00;00      
MLXD5                2                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      -34.7                     28.4                       2008/02/04 12:00;00      
P3GY9                4                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      1                         -6.7                       2008/02/04 18:00;00      
S6TY                 1                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      44.8                      -16.6                      2008/02/04 12:00;00      
SYAQ                 1                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      -25.6                     -38.3                      2008/02/04 14:00;00      
V7DI7                2                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      30.3                      32.3                       2008/02/04 12:00;00      
VRZN9                2                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      27.8                      33.7                       2008/02/04 12:00;00      
WADP                 1                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      -5.6                      -33.7                      2008/02/04 12:00;00      
WAHV                 1                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      36.9                      2.3                        2008/02/04 18:00;00      
WDB9444              10                     SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      38.4                      -41.4                      2008/02/04 21:00;00      
WDC6923              2                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      34.5                      21.6                       2008/02/04 12:00;00      
WDD6126              8                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      43.7                      -32.2                      2008/02/04 21:00;00      
ZCIJ7                1                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      36.3                      -8.9                       2008/02/04 00:00;00      
3FSA4                1                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      43.9                      -43.5                      2008/02/04 12:00;00      
A8CF2                2                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      4                         -8.1                       2008/02/04 12:00;00      
C6QK                 1                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      36.8                      -44.4                      2008/02/04 18:00;00      
ELPX7                2                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      9.8                       -28.1                      2008/02/04 12:00;00      
ELQQ4                1                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      -2.6                      -36.8                      2008/02/04 12:00;00      
LAOW5                1                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      44.4                      -18.5                      2008/02/04 00:00;00      
V7BW7                2                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      13.3                      42.9                       2008/02/04 10:00;00      
WCBP                 4                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      35.9                      -28.5                      2008/02/04 18:00;00      
WFQB                 4                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      -20.6                     -4.2                       2008/02/04 18:00;00      
WRFJ                 4                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      35.7                      29.3                       2008/02/04 18:00;00      

26 rows selected

Explain plan

explain plan for
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('2008-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.observation_date  <  to_date('2008-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and ov.observation_date  >= to_date('2008-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and ov.observation_date  <  to_date('2008-02-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;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2327470598

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                              |     1 |   136 |    24  (17)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY                           |                              |     1 |   136 |    24  (17)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID            | OBSERVATION                  |     1 |    24 |     3   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                          |                              |     1 |   136 |    23  (14)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                         |                              |     1 |   112 |    20  (15)| 00:00:01 |       |       |
|   5 |      VIEW                                |                              |     1 |   100 |    19  (16)| 00:00:01 |       |       |
|*  6 |       FILTER                             |                              |       |       |            |          |       |       |
|   7 |        SORT GROUP BY                     |                              |     1 |   153 |    19  (16)| 00:00:01 |       |       |
|*  8 |         HASH JOIN                        |                              |     1 |   153 |    18  (12)| 00:00:01 |       |       |
|   9 |          NESTED LOOPS                    |                              |     1 |   107 |    13   (0)| 00:00:01 |       |       |
|  10 |           NESTED LOOPS                   |                              |     1 |   100 |    12   (0)| 00:00:01 |       |       |
|  11 |            NESTED LOOPS                  |                              |     1 |    83 |    11   (0)| 00:00:01 |       |       |
|  12 |             NESTED LOOPS                 |                              |     1 |    74 |    10   (0)| 00:00:01 |       |       |
|  13 |              NESTED LOOPS                |                              |     1 |    64 |     9   (0)| 00:00:01 |       |       |
|* 14 |               TABLE ACCESS BY INDEX ROWID| OBSERVATION                  |     3 |   141 |     3   (0)| 00:00:01 |       |       |
|* 15 |                DOMAIN INDEX              | OBSERVATION_SPX              |       |       |            |          |       |       |
|  16 |               PARTITION RANGE SINGLE     |                              |     1 |    17 |     2   (0)| 00:00:01 |    17 |    17 |
|* 17 |                INDEX RANGE SCAN          | OBSERVATION_VALUE_PK         |     1 |    17 |     2   (0)| 00:00:01 |    17 |    17 |
|* 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           | PLATFORM_PK                  |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 22 |            TABLE ACCESS BY INDEX ROWID   | COUNTRY                      |     1 |    17 |     1   (0)| 00:00:01 |       |       |
|* 23 |             INDEX UNIQUE SCAN            | COUNTRY_PK                   |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 24 |           INDEX RANGE SCAN               | PLATFORM_TYPE_ASSOCIATION_PK |     1 |     7 |     1   (0)| 00:00:01 |       |       |
|  25 |          VIEW                            |                              |    55 |  2530 |     4  (25)| 00:00:01 |       |       |
|  26 |           SORT ORDER BY                  |                              |    55 |  1705 |     4  (25)| 00:00:01 |       |       |
|* 27 |            FILTER                        |                              |       |       |            |          |       |       |
|  28 |             CONNECT BY WITHOUT FILTERING |                              |       |       |            |          |       |       |
|  29 |              TABLE ACCESS FULL           | PLATFORM_TYPE                |    55 |  1705 |     3   (0)| 00:00:01 |       |       |
|  30 |      TABLE ACCESS BY INDEX ROWID         | PLATFORM                     |     1 |    12 |     1   (0)| 00:00:01 |       |       |
|* 31 |       INDEX UNIQUE SCAN                  | PLATFORM_PK                  |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 32 |     INDEX RANGE SCAN                     | OBSERVATION_IDX1             |     1 |       |     2   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------------

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

   6 - filter("PR"."NAME"='ATMP' AND "C"."NAME"='UNITED STATES')
   8 - access("PTA"."PLATFORM_TYPE_ID"="ID")
  14 - filter("O"."OBSERVATION_DATE">=TO_DATE('2008-02-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "O"."OBSERVATION_DATE"<TO_DATE('2008-02-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  15 - access("MDSYS"."SDO_ANYINTERACT"("O"."SHAPE","MDSYS"."SDO_GEOMETRY"(2003,8307,NULL,"SDO_ELEM_INFO_ARRAY"(1,1003,3),"SDO_OR
              DINATE_ARRAY"((-45),(-45),45,45)))='TRUE')
  17 - access("O"."OBSERVATION_DATE"="OV"."OBSERVATION_DATE" AND "O"."ID"="OV"."OBSERVATION_ID")
       filter("OV"."OBSERVATION_DATE"<TO_DATE('2008-02-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "OV"."OBSERVATION_DATE">=TO_DATE('2008-02-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  18 - filter("OV"."PARAMETER_ID"="PR"."ID")
  19 - access("PR"."NAME"='ATMP')
  21 - access("O"."PLATFORM_ID"="P"."ID")
  22 - filter("C"."NAME"='UNITED STATES')
  23 - access("P"."COUNTRY_ID"="C"."ID")
  24 - access("P"."ID"="PTA"."PLATFORM_ID")
  27 - filter(ANY='SHIPS' AND TO_DATE('2008-02-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')<TO_DATE('2008-02-05 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND TO_DATE('2008-02-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')>TO_DATE('2008-02-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  31 - 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")

63 rows selected.

autotrace

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('2008-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.observation_date  <  to_date('2008-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and ov.observation_date  >= to_date('2008-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and ov.observation_date  <  to_date('2008-02-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;

Execution Plan
----------------------------------------------------------
Plan hash value: 2327470598

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                              |     1 |   136 |    24  (17)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY                           |                              |     1 |   136 |    24  (17)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID            | OBSERVATION                  |     1 |    24 |     3   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                          |                              |     1 |   136 |    23  (14)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                         |                              |     1 |   112 |    20  (15)| 00:00:01 |       |       |
|   5 |      VIEW                                |                              |     1 |   100 |    19  (16)| 00:00:01 |       |       |
|*  6 |       FILTER                             |                              |       |       |            |          |       |       |
|   7 |        SORT GROUP BY                     |                              |     1 |   153 |    19  (16)| 00:00:01 |       |       |
|*  8 |         HASH JOIN                        |                              |     1 |   153 |    18  (12)| 00:00:01 |       |       |
|   9 |          NESTED LOOPS                    |                              |     1 |   107 |    13   (0)| 00:00:01 |       |       |
|  10 |           NESTED LOOPS                   |                              |     1 |   100 |    12   (0)| 00:00:01 |       |       |
|  11 |            NESTED LOOPS                  |                              |     1 |    83 |    11   (0)| 00:00:01 |       |       |
|  12 |             NESTED LOOPS                 |                              |     1 |    74 |    10   (0)| 00:00:01 |       |       |
|  13 |              NESTED LOOPS                |                              |     1 |    64 |     9   (0)| 00:00:01 |       |       |
|* 14 |               TABLE ACCESS BY INDEX ROWID| OBSERVATION                  |     3 |   141 |     3   (0)| 00:00:01 |       |       |
|* 15 |                DOMAIN INDEX              | OBSERVATION_SPX              |       |       |            |          |       |       |
|  16 |               PARTITION RANGE SINGLE     |                              |     1 |    17 |     2   (0)| 00:00:01 |    17 |    17 |
|* 17 |                INDEX RANGE SCAN          | OBSERVATION_VALUE_PK         |     1 |    17 |     2   (0)| 00:00:01 |    17 |    17 |
|* 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           | PLATFORM_PK                  |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 22 |            TABLE ACCESS BY INDEX ROWID   | COUNTRY                      |     1 |    17 |     1   (0)| 00:00:01 |       |       |
|* 23 |             INDEX UNIQUE SCAN            | COUNTRY_PK                   |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 24 |           INDEX RANGE SCAN               | PLATFORM_TYPE_ASSOCIATION_PK |     1 |     7 |     1   (0)| 00:00:01 |       |       |
|  25 |          VIEW                            |                              |    55 |  2530 |     4  (25)| 00:00:01 |       |       |
|  26 |           SORT ORDER BY                  |                              |    55 |  1705 |     4  (25)| 00:00:01 |       |       |
|* 27 |            FILTER                        |                              |       |       |            |          |       |       |
|  28 |             CONNECT BY WITHOUT FILTERING |                              |       |       |            |          |       |       |
|  29 |              TABLE ACCESS FULL           | PLATFORM_TYPE                |    55 |  1705 |     3   (0)| 00:00:01 |       |       |
|  30 |      TABLE ACCESS BY INDEX ROWID         | PLATFORM                     |     1 |    12 |     1   (0)| 00:00:01 |       |       |
|* 31 |       INDEX UNIQUE SCAN                  | PLATFORM_PK                  |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 32 |     INDEX RANGE SCAN                     | OBSERVATION_IDX1             |     1 |       |     2   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------------

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

   6 - filter("PR"."NAME"='ATMP' AND "C"."NAME"='UNITED STATES')
   8 - access("PTA"."PLATFORM_TYPE_ID"="ID")
  14 - filter("O"."OBSERVATION_DATE">=TO_DATE('2008-02-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "O"."OBSERVATION_DATE"<TO_DATE('2008-02-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  15 - access("MDSYS"."SDO_ANYINTERACT"("O"."SHAPE","MDSYS"."SDO_GEOMETRY"(2003,8307,NULL,"SDO_ELEM_INFO_ARRAY"(1,1003,3),"SDO_OR
              DINATE_ARRAY"((-45),(-45),45,45)))='TRUE')
  17 - access("O"."OBSERVATION_DATE"="OV"."OBSERVATION_DATE" AND "O"."ID"="OV"."OBSERVATION_ID")
       filter("OV"."OBSERVATION_DATE"<TO_DATE('2008-02-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "OV"."OBSERVATION_DATE">=TO_DATE('2008-02-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  18 - filter("OV"."PARAMETER_ID"="PR"."ID")
  19 - access("PR"."NAME"='ATMP')
  21 - access("O"."PLATFORM_ID"="P"."ID")
  22 - filter("C"."NAME"='UNITED STATES')
  23 - access("P"."COUNTRY_ID"="C"."ID")
  24 - access("P"."ID"="PTA"."PLATFORM_ID")
  27 - filter(ANY='SHIPS' AND TO_DATE('2008-02-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')<TO_DATE('2008-02-05 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND TO_DATE('2008-02-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')>TO_DATE('2008-02-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  31 - 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")


Statistics
----------------------------------------------------------
       9269  recursive calls
          6  db block gets
     217520  consistent gets
       2890  physical reads
          0  redo size
       1656  bytes sent via SQL*Net to client
        767  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
         26  rows processed

Query on joined table

select 
  oo.platform_code
, oo.cnt parameter_period_count
, oo.platform_type platform_subtype
, oo.platform_type_parent 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 all_osmc_tsqp o,
(select o.platform_code
           , trunc(observation_date) ob_date
           , max(observation_date) max_ob_time
           , o.parameter
           , o.platform_type
           , o.platform_type_parent
           , o.country
           , count(*) cnt
      from all_osmc_tsqp o
      where o.observation_date  >= to_date('2008-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.observation_date  <  to_date('2008-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.parameter = '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 country = 'UNITED STATES'
-- specify a country name
        and platform_type_parent = 'SHIPS'
-- specify any platform type (e.g. VOSCLIM, SHIPS, etc.)
      group by o.platform_code
             , trunc(o.observation_date)
             , parameter
             , platform_type
             , platform_type_parent
             , country)oo
where oo.platform_code = o.platform_code
  and oo.max_ob_time = o.observation_date
group by   oo.platform_code
, oo.cnt
, oo.platform_type
, oo.platform_type_parent
, oo.country
, o.shape.sdo_point.y
, o.shape.sdo_point.x
, o.observation_date
order by platform_subtype,platform_code;

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                      2008/02/04 18:00:00      
4XFX                 2                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      38.9                      -40.5                      2008/02/04 12:00:00      
9VAY4                7                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      38.4                      -44.7                      2008/02/04 20:00:00      
KABL                 2                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      18.7                      39.5                       2008/02/04 12:00:00      
MLXD5                2                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      -34.7                     28.4                       2008/02/04 12:00:00      
P3GY9                4                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      1                         -6.7                       2008/02/04 18:00:00      
S6TY                 1                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      44.8                      -16.6                      2008/02/04 12:00:00      
SYAQ                 1                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      -25.6                     -38.3                      2008/02/04 14:00:00      
V7DI7                2                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      30.3                      32.3                       2008/02/04 12:00:00      
VRZN9                2                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      27.8                      33.7                       2008/02/04 12:00:00      
WADP                 1                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      -5.6                      -33.7                      2008/02/04 12:00:00      
WAHV                 1                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      36.9                      2.3                        2008/02/04 18:00:00      
WDB9444              10                     SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      38.4                      -41.4                      2008/02/04 21:00:00      
WDC6923              2                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      34.5                      21.6                       2008/02/04 12:00:00      
WDD6126              8                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      43.7                      -32.2                      2008/02/04 21:00:00      
ZCIJ7                1                      SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                      36.3                      -8.9                       2008/02/04 00:00:00      
3FSA4                1                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      43.9                      -43.5                      2008/02/04 12:00:00      
A8CF2                2                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      4                         -8.1                       2008/02/04 12:00:00      
C6QK                 1                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      36.8                      -44.4                      2008/02/04 18:00:00      
ELPX7                2                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      9.8                       -28.1                      2008/02/04 12:00:00      
ELQQ4                1                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      -2.6                      -36.8                      2008/02/04 12:00:00      
LAOW5                1                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      44.4                      -18.5                      2008/02/04 00:00:00      
V7BW7                2                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      13.3                      42.9                       2008/02/04 10:00:00      
WCBP                 4                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      35.9                      -28.5                      2008/02/04 18:00:00      
WFQB                 4                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      -20.6                     -4.2                       2008/02/04 18:00:00      
WRFJ                 4                      VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                      35.7                      29.3                       2008/02/04 18:00:00      

26 rows selected

Drill-down results


describe all_osmc_tsqp;
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- 
OBSERVATION_DATE                        DATE                                                                                                                                                                                          
PLATFORM_CODE                           VARCHAR2(20)                                                                                                                                                                                  
PLATFORM_TYPE                           VARCHAR2(40)                                                                                                                                                                                  
PLATFORM_TYPE_PARENT                    VARCHAR2(40)                                                                                                                                                                                  
PLATFORM_DESCRIPTION                    VARCHAR2(80)                                                                                                                                                                                  
PLATFORM_URL                            VARCHAR2(4000)                                                                                                                                                                                
COUNTRY_CODE                            VARCHAR2(2)                                                                                                                                                                                   
COUNTRY                                 VARCHAR2(50)                                                                                                                                                                                  
OPERATIONAL_STATUS                      VARCHAR2(4000)                                                                                                                                                                                
LATITUDE                                NUMBER(8,3)                                                                                                                                                                                   
LONGITUDE                               NUMBER(8,3)                                                                                                                                                                                   
LONGITUDE_390                           NUMBER(8,3)                                                                                                                                                                                   
SHAPE                                   SDO_GEOMETRY()                                                                                                                                                                                
PARAMETER                               VARCHAR2(20)                                                                                                                                                                                  
PARAMETER_DESCRIPTION                   VARCHAR2(50)                                                                                                                                                                                  
VALUE                                   NUMBER                                                                                                                                                                                        
UNITS                                   VARCHAR2(40)                                                                                                                                                                                  
DATASOURCE                              VARCHAR2(50)                                                                                                                                                                                  
OBSERVATION_ID                          NUMBER                                                                                                                                                                                        
OBJECTID                       NOT NULL NUMBER(38)                                                                                                                                                                                    
DELAY_HOUR                              NUMBER                                                                                                                                                                                        
DATE_ADDED                              DATE                                                                                                                                                                                          
ORGANIZATION                            VARCHAR2(80)                                                                                                                                                                                  
ORGANIZATION_URL                        VARCHAR2(4000)                                                                                                                                                                                
CONTACT_NAME                            VARCHAR2(50)                                                                                                                                                                                  
CONTACT_EMAIL                           VARCHAR2(75)                                                                                                                                                                                  

26 rows selected

select * from all_osmc_tsqp 
where platform_code = 'WDB9444'
and observation_date  >= to_date('2008-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
and observation_date  <  to_date('2008-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
and parameter = 'ATMP'
order by observation_date;

OBSERVATION_DATE     PLATFORM_CODE        PLATFORM_TYPE                            PLATFORM_TYPE_PARENT                     PLATFORM_DESCRIPTION PLATFORM_URL    CO COUNTRY         OPERATIONAL_STATUS     LATITUDE  LONGITUDE LONGITUDE_390 PARAMETER            PARAMETER_DESCRIPTIO      VALUE UNITS      DATASOURCE                                         OBSERVATION_ID   OBJECTID DELAY_HOUR DATE_ADDED           ORGANIZATION    ORGANIZATION_UR CONTACT_NAME    CONTACT_EMAIL
-------------------- -------------------- ---------------------------------------- ---------------------------------------- -------------------- --------------- -- --------------- -------------------- ---------- ---------- ------------- -------------------- -------------------- ---------- ---------- -------------------------------------------------- -------------- ---------- ---------- -------------------- --------------- --------------- --------------- ---------------
 2008/02/04 00:00:00 WDB9444              SHIPS (GENERIC)                          SHIPS                                    SEA-LAND PRIDE                       US UNITED STATES   OPERATIONAL PLATFORM       39.1      -35.7         324.3 ATMP                 AIR TEMPERATURE              17 degC       GLOBAL OCEAN DATA ASSIMILATION EXPERIMENT                70414773   27643916         48  2008/02/04 05:39:24
 2008/02/04 04:00:00 WDB9444              SHIPS (GENERIC)                          SHIPS                                    SEA-LAND PRIDE                       US UNITED STATES   OPERATIONAL PLATFORM         39      -36.9         323.1 ATMP                 AIR TEMPERATURE            16.9 degC       GTS AND GODAE                                            70424079   27708958         48  2008/02/05 03:04:09
 2008/02/04 07:00:00 WDB9444              SHIPS (GENERIC)                          SHIPS                                    SEA-LAND PRIDE                       US UNITED STATES   OPERATIONAL PLATFORM       38.9      -37.6         322.4 ATMP                 AIR TEMPERATURE            17.1 degC       GTS AND GODAE                                            70431015   27775735         48  2008/02/05 03:06:44
 2008/02/04 08:00:00 WDB9444              SHIPS (GENERIC)                          SHIPS                                    SEA-LAND PRIDE                       US UNITED STATES   OPERATIONAL PLATFORM       38.8      -37.9         322.1 ATMP                 AIR TEMPERATURE            17.1 degC       GTS AND GODAE                                            70431610   27722256         48  2008/02/05 03:07:25
 2008/02/04 09:00:00 WDB9444              SHIPS (GENERIC)                          SHIPS                                    SEA-LAND PRIDE                       US UNITED STATES   OPERATIONAL PLATFORM       38.8      -38.2         321.8 ATMP                 AIR TEMPERATURE            15.5 degC       GTS AND GODAE                                            70434095   27722883         48  2008/02/05 03:08:08
 2008/02/04 10:00:00 WDB9444              SHIPS (GENERIC)                          SHIPS                                    SEA-LAND PRIDE                       US UNITED STATES   OPERATIONAL PLATFORM       38.8      -38.4         321.6 ATMP                 AIR TEMPERATURE            15.5 degC       GTS AND GODAE                                            70436541   27720898         48  2008/02/05 03:08:56
 2008/02/04 12:00:00 WDB9444              SHIPS (GENERIC)                          SHIPS                                    SEA-LAND PRIDE                       US UNITED STATES   OPERATIONAL PLATFORM       38.7        -39           321 ATMP                 AIR TEMPERATURE              17 degC       GTS AND GODAE                                            70441236   27762750         48  2008/02/05 03:10:31
 2008/02/04 15:00:00 WDB9444              SHIPS (GENERIC)                          SHIPS                                    SEA-LAND PRIDE                       US UNITED STATES   OPERATIONAL PLATFORM       38.6      -39.8         320.2 ATMP                 AIR TEMPERATURE            17.7 degC       GTS AND GODAE                                            70450996   27814966         48  2008/02/05 03:14:18
 2008/02/04 19:00:00 WDB9444              SHIPS (GENERIC)                          SHIPS                                    SEA-LAND PRIDE                       US UNITED STATES   OPERATIONAL PLATFORM       38.4      -40.9         319.1 ATMP                 AIR TEMPERATURE              16 degC       GTS AND GODAE                                            70457034   27806496         48  2008/02/05 03:17:04
 2008/02/04 21:00:00 WDB9444              SHIPS (GENERIC)                          SHIPS                                    SEA-LAND PRIDE                       US UNITED STATES   OPERATIONAL PLATFORM       38.4      -41.4         318.6 ATMP                 AIR TEMPERATURE              16 degC       GTS AND GODAE                                            70461394   27807456         48  2008/02/05 03:18:44

10 rows selected.

Explain plan

explain plan for
select 
  oo.platform_code
, oo.cnt parameter_period_count
, oo.platform_type platform_subtype
, oo.platform_type_parent 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 all_osmc_tsqp o,
(select o.platform_code
           , trunc(observation_date) ob_date
           , max(observation_date) max_ob_time
           , o.parameter
           , o.platform_type
           , o.platform_type_parent
           , o.country
           , count(*) cnt
      from all_osmc_tsqp o
      where o.observation_date  >= to_date('2008-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.observation_date  <  to_date('2008-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.parameter = '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 country = 'UNITED STATES'
-- specify a country name
        and platform_type_parent = 'SHIPS'
-- specify any platform type (e.g. VOSCLIM, SHIPS, etc.)
      group by o.platform_code
             , trunc(o.observation_date)
             , parameter
             , platform_type
             , platform_type_parent
             , country)oo
where oo.platform_code = o.platform_code
  and oo.max_ob_time = o.observation_date
group by   oo.platform_code
, oo.cnt
, oo.platform_type
, oo.platform_type_parent
, oo.country
, o.shape.sdo_point.y
, o.shape.sdo_point.x
, o.observation_date
order by platform_subtype,platform_code;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1854789294

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                   |     1 |   115 |  1401   (3)| 00:00:20 |       |       |
|   1 |  SORT GROUP BY                          |                   |     1 |   115 |  1401   (3)| 00:00:20 |       |       |
|   2 |   NESTED LOOPS                          |                   |     1 |   115 |  1400   (3)| 00:00:20 |       |       |
|   3 |    VIEW                                 |                   |     1 |    89 |     1 (100)| 00:00:01 |       |       |
|*  4 |     FILTER                              |                   |       |       |            |          |       |       |
|   5 |      SORT GROUP BY                      |                   |     1 |   242 |     1 (100)| 00:00:01 |       |       |
|   6 |       PARTITION RANGE SINGLE            |                   |     1 |   242 |            |          |     5 |     5 |
|*  7 |        TABLE ACCESS BY LOCAL INDEX ROWID| ALL_OSMC_TSQP     |     1 |   242 |            |          |     5 |     5 |
|*  8 |         DOMAIN INDEX                    | ALL_OSMC_TSQP_SPX |       |       |            |          |       |       |
|   9 |    PARTITION RANGE ITERATOR             |                   |     1 |    26 |  1399   (3)| 00:00:20 |   KEY |   KEY |
|* 10 |     TABLE ACCESS FULL                   | ALL_OSMC_TSQP     |     1 |    26 |  1399   (3)| 00:00:20 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------------------

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

   4 - filter("PARAMETER"='ATMP' AND "COUNTRY"='UNITED STATES' AND "PLATFORM_TYPE_PARENT"='SHIPS')
   7 - filter("COUNTRY"='UNITED STATES' AND "O"."PARAMETER"='ATMP' AND "PLATFORM_TYPE_PARENT"='SHIPS' AND
              "O"."OBSERVATION_DATE">=TO_DATE('2008-02-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "O"."OBSERVATION_DATE"<TO_DATE('2008-02-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
   8 - access("MDSYS"."SDO_ANYINTERACT"("O"."SHAPE","MDSYS"."SDO_GEOMETRY"(2003,8307,NULL,"SDO_ELEM_INFO_ARRAY"(1,100
              3,3),"SDO_ORDINATE_ARRAY"((-45),(-45),45,45)))='TRUE')
  10 - filter("OO"."PLATFORM_CODE"="O"."PLATFORM_CODE" AND "OO"."MAX_OB_TIME"="O"."OBSERVATION_DATE")

28 rows selected.

autotrace

select 
  oo.platform_code
, oo.cnt parameter_period_count
, oo.platform_type platform_subtype
, oo.platform_type_parent 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 all_osmc_tsqp o,
(select o.platform_code
           , trunc(observation_date) ob_date
           , max(observation_date) max_ob_time
           , o.parameter
           , o.platform_type
           , o.platform_type_parent
           , o.country
           , count(*) cnt
      from all_osmc_tsqp o
      where o.observation_date  >= to_date('2008-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.observation_date  <  to_date('2008-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.parameter = '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 country = 'UNITED STATES'
-- specify a country name
        and platform_type_parent = 'SHIPS'
-- specify any platform type (e.g. VOSCLIM, SHIPS, etc.)
      group by o.platform_code
             , trunc(o.observation_date)
             , parameter
             , platform_type
             , platform_type_parent
             , country)oo
where oo.platform_code = o.platform_code
  and oo.max_ob_time = o.observation_date
group by   oo.platform_code
, oo.cnt
, oo.platform_type
, oo.platform_type_parent
, oo.country
, o.shape.sdo_point.y
, o.shape.sdo_point.x
, o.observation_date
order by platform_subtype,platform_code;

Execution Plan
----------------------------------------------------------
Plan hash value: 1854789294

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                   |     1 |   115 |  1401   (3)| 00:00:20 |       |       |
|   1 |  SORT GROUP BY                          |                   |     1 |   115 |  1401   (3)| 00:00:20 |       |       |
|   2 |   NESTED LOOPS                          |                   |     1 |   115 |  1400   (3)| 00:00:20 |       |       |
|   3 |    VIEW                                 |                   |     1 |    89 |     1 (100)| 00:00:01 |       |       |
|*  4 |     FILTER                              |                   |       |       |            |          |       |       |
|   5 |      SORT GROUP BY                      |                   |     1 |   242 |     1 (100)| 00:00:01 |       |       |
|   6 |       PARTITION RANGE SINGLE            |                   |     1 |   242 |            |          |     5 |     5 |
|*  7 |        TABLE ACCESS BY LOCAL INDEX ROWID| ALL_OSMC_TSQP     |     1 |   242 |            |          |     5 |     5 |
|*  8 |         DOMAIN INDEX                    | ALL_OSMC_TSQP_SPX |       |       |            |          |       |       |
|   9 |    PARTITION RANGE ITERATOR             |                   |     1 |    26 |  1399   (3)| 00:00:20 |   KEY |   KEY |
|* 10 |     TABLE ACCESS FULL                   | ALL_OSMC_TSQP     |     1 |    26 |  1399   (3)| 00:00:20 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------------------

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

   4 - filter("PARAMETER"='ATMP' AND "COUNTRY"='UNITED STATES' AND "PLATFORM_TYPE_PARENT"='SHIPS')
   7 - filter("COUNTRY"='UNITED STATES' AND "O"."PARAMETER"='ATMP' AND "PLATFORM_TYPE_PARENT"='SHIPS' AND
              "O"."OBSERVATION_DATE">=TO_DATE('2008-02-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "O"."OBSERVATION_DATE"<TO_DATE('2008-02-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
   8 - access("MDSYS"."SDO_ANYINTERACT"("O"."SHAPE","MDSYS"."SDO_GEOMETRY"(2003,8307,NULL,"SDO_ELEM_INFO_ARRAY"(1,100
              3,3),"SDO_ORDINATE_ARRAY"((-45),(-45),45,45)))='TRUE')
  10 - filter("OO"."PLATFORM_CODE"="O"."PLATFORM_CODE" AND "OO"."MAX_OB_TIME"="O"."OBSERVATION_DATE")


Statistics
----------------------------------------------------------
       2131  recursive calls
         14  db block gets
     149217  consistent gets
          3  physical reads
          0  redo size
       1656  bytes sent via SQL*Net to client
        376  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
         26  rows processed

Query using lat/long instead of spatial query

select 
  oo.platform_code
, oo.cnt parameter_period_count
, oo.platform_type platform_subtype
, oo.platform_type_parent platform_type
, oo.country
, o.latitude last_parameter_report_lat
, o.longitude last_parameter_report_lon
, o.observation_date last_parameter_report_date
from all_osmc_tsqp o,
(select o.platform_code
           , trunc(observation_date) ob_date
           , max(observation_date) max_ob_time
           , o.parameter
           , o.platform_type
           , o.platform_type_parent
           , o.country
           , count(*) cnt
      from all_osmc_tsqp o
      where o.observation_date  >= to_date('2008-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.observation_date  <  to_date('2008-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.parameter = 'ATMP'
-- specify a parameter name
        and (    latitude > -45
             and latitude < 45
             and longitude > -45
             and longitude < 45)
-- specify rectangle coordinates
        and country = 'UNITED STATES'
-- specify a country name
        and platform_type_parent = 'SHIPS'
-- specify any platform type (e.g. VOSCLIM, SHIPS, etc.)
      group by o.platform_code
             , trunc(o.observation_date)
             , parameter
             , platform_type
             , platform_type_parent
             , country)oo
where oo.platform_code = o.platform_code
  and oo.max_ob_time = o.observation_date
group by   oo.platform_code
, oo.cnt
, oo.platform_type
, oo.platform_type_parent
, oo.country
, o.latitude
, o.longitude
, o.observation_date
order by platform_subtype,platform_code;

PLATFORM_CODE        PARAMETER_PERIOD_COUNT PLATFORM_SUBTYPE                         PLATFORM_TYPE                            COUNTRY                                            LAST_PARAMETER_REPORT_LAT LAST_PARAMETER_REPORT_LON LAST_PARA
-------------------- ---------------------- ---------------------------------------- ---------------------------------------- -------------------------------------------------- ------------------------- ------------------------- ---------
15655                                     4 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                           42.5                     27.48 04-FEB-08
4XFX                                      2 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                           38.9                     -40.5 04-FEB-08
9VAY4                                     7 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                           38.4                     -44.7 04-FEB-08
KABL                                      2 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                           18.7                      39.5 04-FEB-08
MLXD5                                     2 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                          -34.7                      28.4 04-FEB-08
P3GY9                                     4 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                              1                      -6.7 04-FEB-08
S6TY                                      1 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                           44.8                     -16.6 04-FEB-08
SYAQ                                      1 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                          -25.6                     -38.3 04-FEB-08
V7DI7                                     2 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                           30.3                      32.3 04-FEB-08
VRZN9                                     2 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                           27.8                      33.7 04-FEB-08
WADP                                      1 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                           -5.6                     -33.7 04-FEB-08
WAHV                                      1 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                           36.9                       2.3 04-FEB-08
WDB9444                                  10 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                           38.4                     -41.4 04-FEB-08
WDC6923                                   2 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                           34.5                      21.6 04-FEB-08
WDD6126                                   8 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                           43.7                     -32.2 04-FEB-08
ZCIJ7                                     1 SHIPS (GENERIC)                          SHIPS                                    UNITED STATES                                                           36.3                      -8.9 04-FEB-08
3FSA4                                     1 VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                                           43.9                     -43.5 04-FEB-08
A8CF2                                     2 VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                                              4                      -8.1 04-FEB-08
C6QK                                      1 VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                                           36.8                     -44.4 04-FEB-08
ELPX7                                     2 VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                                            9.8                     -28.1 04-FEB-08
ELQQ4                                     1 VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                                           -2.6                     -36.8 04-FEB-08
LAOW5                                     1 VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                                           44.4                     -18.5 04-FEB-08
V7BW7                                     2 VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                                           13.3                      42.9 04-FEB-08
WCBP                                      4 VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                                           35.9                     -28.5 04-FEB-08
WFQB                                      4 VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                                          -20.6                      -4.2 04-FEB-08
WRFJ                                      4 VOLUNTEER OBSERVING SHIPS (GENERIC)      SHIPS                                    UNITED STATES                                                           35.7                      29.3 04-FEB-08

26 rows selected.

Explain plan

explain plan for
select 
  oo.platform_code
, oo.cnt parameter_period_count
, oo.platform_type platform_subtype
, oo.platform_type_parent platform_type
, oo.country
, o.latitude last_parameter_report_lat
, o.longitude last_parameter_report_lon
, o.observation_date last_parameter_report_date
from all_osmc_tsqp o,
(select o.platform_code
           , trunc(observation_date) ob_date
           , max(observation_date) max_ob_time
           , o.parameter
           , o.platform_type
           , o.platform_type_parent
           , o.country
           , count(*) cnt
      from all_osmc_tsqp o
      where o.observation_date  >= to_date('2008-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.observation_date  <  to_date('2008-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.parameter = 'ATMP'
-- specify a parameter name
        and (    latitude > -45
             and latitude < 45
             and longitude > -45
             and longitude < 45)
-- specify rectangle coordinates
        and country = 'UNITED STATES'
-- specify a country name
        and platform_type_parent = 'SHIPS'
-- specify any platform type (e.g. VOSCLIM, SHIPS, etc.)
      group by o.platform_code
             , trunc(o.observation_date)
             , parameter
             , platform_type
             , platform_type_parent
             , country)oo
where oo.platform_code = o.platform_code
  and oo.max_ob_time = o.observation_date
group by   oo.platform_code
, oo.cnt
, oo.platform_type
, oo.platform_type_parent
, oo.country
, o.latitude
, o.longitude
, o.observation_date
order by platform_subtype,platform_code;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 951886697

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     3 |   345 |  5878   (3)| 00:01:23 |       |       |
|   1 |  SORT GROUP BY              |               |     3 |   345 |  5878   (3)| 00:01:23 |       |       |
|   2 |   NESTED LOOPS              |               |     3 |   345 |  5877   (3)| 00:01:23 |       |       |
|   3 |    VIEW                     |               |     3 |   267 |  1704   (3)| 00:00:24 |       |       |
|*  4 |     FILTER                  |               |       |       |            |          |       |       |
|   5 |      SORT GROUP BY          |               |     3 |   267 |  1704   (3)| 00:00:24 |       |       |
|   6 |       PARTITION RANGE SINGLE|               |    17 |  1513 |  1703   (2)| 00:00:24 |     5 |     5 |
|*  7 |        TABLE ACCESS FULL    | ALL_OSMC_TSQP |    17 |  1513 |  1703   (2)| 00:00:24 |     5 |     5 |
|   8 |    PARTITION RANGE ITERATOR |               |     1 |    26 |  1391   (3)| 00:00:20 |   KEY |   KEY |
|*  9 |     TABLE ACCESS FULL       | ALL_OSMC_TSQP |     1 |    26 |  1391   (3)| 00:00:20 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------

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

   4 - filter("PARAMETER"='ATMP' AND "COUNTRY"='UNITED STATES' AND "PLATFORM_TYPE_PARENT"='SHIPS')
   7 - filter("COUNTRY"='UNITED STATES' AND "O"."PARAMETER"='ATMP' AND
              "PLATFORM_TYPE_PARENT"='SHIPS' AND "LONGITUDE">(-45) AND "LONGITUDE"<45 AND "LATITUDE"<45 AND
              "LATITUDE">(-45))
   9 - filter("OO"."PLATFORM_CODE"="O"."PLATFORM_CODE" AND "OO"."MAX_OB_TIME"="O"."OBSERVATION_DATE")

25 rows selected.

Autotrace


set autotrace trace;
select 
  oo.platform_code
, oo.cnt parameter_period_count
, oo.platform_type platform_subtype
, oo.platform_type_parent platform_type
, oo.country
, o.latitude last_parameter_report_lat
, o.longitude last_parameter_report_lon
, o.observation_date last_parameter_report_date
from all_osmc_tsqp o,
(select o.platform_code
           , trunc(observation_date) ob_date
           , max(observation_date) max_ob_time
           , o.parameter
           , o.platform_type
           , o.platform_type_parent
           , o.country
           , count(*) cnt
      from all_osmc_tsqp o
      where o.observation_date  >= to_date('2008-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.observation_date  <  to_date('2008-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.parameter = 'ATMP'
-- specify a parameter name
        and (    latitude > -45
             and latitude < 45
             and longitude > -45
             and longitude < 45)
-- specify rectangle coordinates
        and country = 'UNITED STATES'
-- specify a country name
        and platform_type_parent = 'SHIPS'
-- specify any platform type (e.g. VOSCLIM, SHIPS, etc.)
      group by o.platform_code
             , trunc(o.observation_date)
             , parameter
             , platform_type
             , platform_type_parent
             , country)oo
where oo.platform_code = o.platform_code
  and oo.max_ob_time = o.observation_date
group by   oo.platform_code
, oo.cnt
, oo.platform_type
, oo.platform_type_parent
, oo.country
, o.latitude
, o.longitude
, o.observation_date
order by platform_subtype,platform_code;

Execution Plan
----------------------------------------------------------
Plan hash value: 951886697

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     3 |   345 |  5878   (3)| 00:01:23 |       |       |
|   1 |  SORT GROUP BY              |               |     3 |   345 |  5878   (3)| 00:01:23 |       |       |
|   2 |   NESTED LOOPS              |               |     3 |   345 |  5877   (3)| 00:01:23 |       |       |
|   3 |    VIEW                     |               |     3 |   267 |  1704   (3)| 00:00:24 |       |       |
|*  4 |     FILTER                  |               |       |       |            |          |       |       |
|   5 |      SORT GROUP BY          |               |     3 |   267 |  1704   (3)| 00:00:24 |       |       |
|   6 |       PARTITION RANGE SINGLE|               |    17 |  1513 |  1703   (2)| 00:00:24 |     5 |     5 |
|*  7 |        TABLE ACCESS FULL    | ALL_OSMC_TSQP |    17 |  1513 |  1703   (2)| 00:00:24 |     5 |     5 |
|   8 |    PARTITION RANGE ITERATOR |               |     1 |    26 |  1391   (3)| 00:00:20 |   KEY |   KEY |
|*  9 |     TABLE ACCESS FULL       | ALL_OSMC_TSQP |     1 |    26 |  1391   (3)| 00:00:20 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------

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

   4 - filter("PARAMETER"='ATMP' AND "COUNTRY"='UNITED STATES' AND "PLATFORM_TYPE_PARENT"='SHIPS')
   7 - filter("COUNTRY"='UNITED STATES' AND "O"."PARAMETER"='ATMP' AND
              "PLATFORM_TYPE_PARENT"='SHIPS' AND "LONGITUDE">(-45) AND "LONGITUDE"<45 AND "LATITUDE"<45 AND
              "LATITUDE">(-45))
   9 - filter("OO"."PLATFORM_CODE"="O"."PLATFORM_CODE" AND "OO"."MAX_OB_TIME"="O"."OBSERVATION_DATE")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     136539  consistent gets
          0  physical reads
          0  redo size
       1656  bytes sent via SQL*Net to client
        376  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         26  rows processed

counts of all observations

select count(*),platform_code,max(observation_date) from
(select o.platform_code
      , o.observation_date
      from all_osmc_tsqp o
      where o.observation_date  >= to_date('2008-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
        and o.observation_date  <  to_date('2008-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
        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 country = 'UNITED STATES'
-- specify a country name
        and platform_type_parent = 'SHIPS'
-- specify any platform type (e.g. VOSCLIM, SHIPS, etc.)
      group by o.platform_code
             , o.observation_date)
             group by platform_code,trunc(observation_date);

COUNT(*)               PLATFORM_CODE        MAX(OBSERVATION_DATE)     
---------------------- -------------------- ------------------------- 
4                      15655                 2008/02/04 18:00:00      
1                      3FSA4                 2008/02/04 12:00:00      
2                      4XFX                  2008/02/04 12:00:00      
7                      9VAY4                 2008/02/04 20:00:00      
2                      A8CF2                 2008/02/04 12:00:00      
1                      C6QK                  2008/02/04 18:00:00      
2                      ELPX7                 2008/02/04 12:00:00      
1                      ELQQ4                 2008/02/04 12:00:00      
2                      KABL                  2008/02/04 12:00:00      
1                      LAOW5                 2008/02/04 00:00:00      
2                      MLXD5                 2008/02/04 12:00:00      
4                      P3GY9                 2008/02/04 18:00:00      
1                      S6TY                  2008/02/04 12:00:00      
1                      SYAQ                  2008/02/04 14:00:00      
2                      V7BW7                 2008/02/04 10:00:00      
2                      V7DI7                 2008/02/04 12:00:00      
2                      VRZN9                 2008/02/04 12:00:00      
1                      WADP                  2008/02/04 12:00:00      
1                      WAHV                  2008/02/04 18:00:00      
4                      WCBP                  2008/02/04 18:00:00      
10                     WDB9444               2008/02/04 21:00:00      
2                      WDC6923               2008/02/04 12:00:00      
8                      WDD6126               2008/02/04 21:00:00      
4                      WFQB                  2008/02/04 18:00:00      
4                      WRFJ                  2008/02/04 18:00:00      
1                      ZCIJ7                 2008/02/04 00:00:00      

26 rows selected
Personal tools