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

From: <sybrandb_at_hccnet.nl>
Date: Mon, 23 Jul 2007 21:19:30 +0200
Message-ID: <blv9a3lb8di5f32m45s3eg2v6cfsntnqce@4ax.com>

On Mon, 23 Jul 2007 21:11:21 +0200, Bram Pouwelse <bram_at_pouwelse.com> wrote:

>I have a table with Devices and a Status table and 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
>
>The device table contains a few thousand devices and each device has a
>few status changes a day,
>
>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?
>
>Bram.

yes of course there is, not using analytical functions. Also I would contest the second inline view is correct.

select d.name,

          d. code,
         d.time,
        d.status

from status d
where time=
(select max(time)
 from status d1
 where d1.id= d.id
)

-- 
     
Sybrand Bakker
Senior Oracle DBA

Received on Mon Jul 23 2007 - 14:19:30 CDT