Better way of writing this sql statement?

From: <stefan454_at_hotmail.com>
Date: 9 Aug 2005 23:24:27 -0700
Message-ID: <1123655067.481926.127530@z14g2000cwz.googlegroups.com>

Hello,

Question is there any better way of writing the statement below?

Regards
Stefan

• The current_timestamp and the in list of location_id can be different. Up to 350 location_id can come as inparameter SELECT l.location_id, o.type1, o.type2, COUNT(*) FROM location l JOIN object_location ol ON l.location_id = ol.location_id JOIN object o ON ol.object_id = o.object_id WHERE (ol.object_id, ol.registration_timestamp) IN (SELECT ol2.object_id, max(ol2.registration_timestamp) FROM object_location ol2 WHERE ol2.location_id IN (1000431, 1000432, 1000433, 1000434) AND ol2.registration_timestamp < current_timestamp GROUP BY ol2.object_id) AND l.type1 = 1 GROUP BY l.location_id, o.type1, o.type2 order by 1

--Primary key is in place and the fk is indexed

CREATE TABLE location
(

  LOCATION_ID    	   INTEGER              NOT NULL,
  LOCATION_TYPE  	   INTEGER              NOT NULL,
  NAME                     VARCHAR2(50)         NOT NULL,
  DESCRIPTION              VARCHAR2(200),
  SOURCE_ID                VARCHAR2(100)        NOT NULL,
  TYPE1		           INTEGER              NOT NULL,
  TYPE2		           INTEGER,
  TYPE3                    INTEGER,
  TYPE4                    INTEGER,
  TYPE5                    INTEGER,
  TYPE6                    NUMBER               NOT NULL,
  VERSION                  INTEGER              NOT NULL

);
Approximate 1500 records

CREATE TABLE OBJECT
(

  OBJECT_ID       INTEGER            NOT NULL,
  TYPE1           INTEGER            NOT NULL,
  TYPE2           INTEGER            NOT NULL,
  NAME            VARCHAR2(50)       NOT NULL,
  TYPE3           INTEGER            NOT NULL,
  FLAG1           INTEGER            NOT NULL,
  FLAG2           INTEGER            NOT NULL,
  QUANTITY1       NUMBER             NOT NULL,
  QUANTITY2       NUMBER             NOT NULL,
  TYPE4           VARCHAR2(50)       NOT NULL,
  VERSION         INTEGER            NOT NULL

);
Approximate 25000 records

CREATE TABLE OBJECT_LOCATION
(

  OBJECT_LOCATION_ID     INTEGER                NOT NULL,
  LOCATION_ID            INTEGER                NOT NULL,
  OBJECT_ID              INTEGER                NOT NULL,
  REGISTRATION_TIMESTAMP TIMESTAMP(6)           NOT NULL,
  TYPE1                  VARCHAR2(20),
  STATUS                 INTEGER,
  QUANTITY               INTEGER,
  VERSION                INTEGER

);
Approximate 6 miljon records

• Index1 LOCATION_ID, OBJECT_ID, REGISTRATION_TIMESTAMP
• Index2 OBJECT_ID, REGISTRATION_TIMESTAMP

Received on Wed Aug 10 2005 - 01:24:27 CDT