Re: Most efficient way to create a view with current values

From: John K. Hinsdale <hin_at_alma.com>
Date: Mon, 23 Jul 2007 13:08:15 -0700
Message-ID: <1185221295.752979.223310@22g2000hsm.googlegroups.com>

On Jul 23, 3:11 pm, Bram Pouwelse <b..._at_pouwelse.com> wrote:

> I ... want to create a view which returns the Info of the
> device and the last known status
>
> The tables:
> DEVICE
> Id
> Name
> Code
>
> STATUS
> Time
> Status
> Device_id
>
> The view
> CURRENT_DEVICE_STATUS
> Name | Code | Time | Status
> d1 | 1234 | 13.44| x
> d3 | 4833 | 12.54| s
>
> current view code:
>
> CREATE OR REPLACE VIEW CURRENT_DEVICE_STATUS
> (
> Name, Code, Time, Status
> )
> AS
> (
> SELECT
> d.Name,
> d.Code,
> (SELECT max(Time) keep (dense_rank first order by Time desc)
Time
> FROM status WHERE device_id= d.id),
> (SELECT max(Status) keep (dense_rank first order by Time desc)
Status
> FROM status WHERE device_id= d.id)
>
> FROM Device d
> );
>
> Is there a more efficient way to create a view like this?

Hi Bram,

My own style, both for predictable performance and for clarity, is to put the aggregating subquery in the FROM clause, and join it, e.g.:

    SELECT D.name, D.code, S.time, S.status     FROM device D, status S,

           ( SELECT device_id, MAX(time) AS latest_time
             FROM status GROUP BY device_id
           ) LATEST
    WHERE D.id = S.device_id
      AND S.device_id = LATEST.device_id
      AND S.time = LATEST.latest_time

As long as both tables are indexed on the device ID this query should run fine even as the tables grow.

Also, the above query has the behavior -- which may be desirable or undesirable -- that if there is a "tie" for which status is the latest, i.e., because they were logged at the "same" time per Oracle's DATE granularity, then *both* statuses will be output. This may be desirable if there is to be no arbitrary tie-breaking, or undesirable if you need your view to reliably output one status row per device.

Also, your subquery in the SELECT clause was referred to as an "inline view." That is incorrect; an "inline view" is a subquery in the FROM clause (as above). A query in the SELECT or WHERE clause is called a "scalar subquery." They are different animals.

Scalar subquery: can return at most one row (else: error!), can appear as if it were a single-valued expression (scalar) in many places where you would use one: in SELECT, WHERE, etc. Introduced relatively recently in Oracle 9.

Inline view: acts like a "table" in the from clause, or more accurately a view that you are creating "on the fly", hence the name. Been around for ages (since Oracle 7). One "advantage" of using inline view in a case like above is that if for whatever reason your subquery returns multiple rows it does not trigger the Oracle error:

    ORA-01427: single-row subquery returns more than one row

but instead simply produces more rows of output. Again this may be desirable or undesirable. To trigger this error, run:

    SELECT ( SELECT 'x' FROM dual

             UNION
             SELECT 'y' FROM dual ) AS foo

    FROM dual;

Hope that helps.

        John Hinsdale Received on Mon Jul 23 2007 - 15:08:15 CDT