Re: Help with query

DA Morgan wrote:
> Chuck wrote:
> I'm trying to come up with a query of v$undostat that will show the
> total # of undo blocks used during any two hour period. The output
> should look like this..
>
> begin_time end_time undoblks
> ========== =========== ===========
> 09:00 11:00 123
> 09:10 11:10 145
> 09:20 11:20 1032
>
> You get the picture. It's essentially the same thing that "Select
> begin_time, end_time, undoblks from v$undostat" would show if each row
> was 2 hours apart instead of 10 minutes apart.
>
> TIA

> I just threw this together off the top of my head so it may well
> have more than a few problems.

> col begin_time format a10
> col end_time format a10

> SELECT TO_CHAR(begin_time, 'HH24:MI') BEGIN_TIME,
> LEAD(TO_CHAR(end_time, 'HH24:MI'), 11, 0) OVER (ORDER BY begin_time)
> END_TIME,
> undoblks
> FROM gv$undostat;

> but hopefully will point you in one direction.

Thanks Daniel. I was not familiar with LEAD/OVER. It's a much more elegant solution then my self join that I posted at the same time as yours. You pointed me in the right direction. I looked it up in the SQL reference and tweaked it to do what I want and it executes in a fraction of the time. Received on Thu Dec 07 2006 - 12:03:53 CST