Re: Help with query

"Chuck" <skilover_nospam_at_bluebottle.com> a écrit dans le message de news: Zoieh.21$bj5.15_at_trnddc07...
| -----BEGIN PGP SIGNED MESSAGE-----
| Hash: SHA1
|
| Bruman wrote:
| > Michel Cadot wrote:>
| >> Here's another query which works even if the interval
| >> between 2 rows is not 10 minutes:
| >>
| >> select begin_time,
| >> last_value(end_time)
| >> over (order by begin_time
| >> range between current row
| >> and interval '1:59:59' hour to second following)
| >> end_time,
| >> sum(undoblks)
| >> over (order by begin_time
| >> range between current row
| >> and interval '1:59:59' hour to second following)
| >> undoblks
| >> from v$undostat
| >> order by begin_time
| >> /
| >>
| >> Regards
| >> Michel Cadot
| >
| > I like it. Is there a good source on Analytic Functions other than the
| > Oracle documentation? Some of us dunderheads have trouble following
| > the little charts and need examples to understand what is going on.
| >
|
| Not sure why but it's not returning the same results as my query that
| doesn't use the analytic functions. I'm just not familiar enough with
| the analytic functions to debug it yet. Like Bruman, I'm looking for a
| good reference on them. The Oracle documentation seem to be written for
| someone who already knows them, not someone who's trying to learn them.
|
| SELECT a.begin_time,
| MAX (b.end_time) end_time,
| SUM (b.undoblks) * 8 / 1024 undo_mb
| FROM v$undostat a JOIN v$undostat b
| ON b.begin_time >= a.begin_time
| AND b.begin_time < a.begin_time + 2/24
| GROUP BY a.begin_time
| ORDER BY 1 DESC;
| -----BEGIN PGP SIGNATURE-----
| Version: GnuPG v1.4.5 (MingW32)
|
| iEYEARECAAYFAkV5srkACgkQzIf+rZpn0oQKzQCfeIoow6xua0BDEPZJRH2ovk+R
| kz8AoJDKqNHhPwu6SI/37rQKjHw+8xBF
| =NgiV
| -----END PGP SIGNATURE-----
The different results come from you query a v$ view. v$ views are special, there is no read consistency on these views, so your self-join may not work on the same row set for both v$undostat. If you first copy v$undostat content in a table and then you'll get the same result.

Regards
Michel Cadot Received on Fri Dec 08 2006 - 13:52:12 CST