Oracle 23c: Managing Flashback Database Logs Outside the Fast Recovery Area
Overview: In previous Database releases, you could store flashback database logs only in the fast recovery area Oracle 23c Database introduces the ... Read More
Learn more about why Eclipsys has been certified as a Great Place to Work in Canada, Best Workplaces in Ontario and Technology, and named Canada’s Top SME Employer for 3 years!
Learn more!In this blog, I’ll demonstrate the use of SUM and AVG functions with INTERVAL datatype
Prerequisites:
The example in this blog requires the following table
drop table if exists trips purge; create table trips ( id number, start_time timestamp, end_time timestamp, duration interval day to second generated always as (end_time - start_time) virtual ); begin insert into trips (id, start_time, end_time) values (1, timestamp '2024-01-20 08:45:00.0', timestamp '2024-01-20 18:01:00.0'); insert into trips (id, start_time, end_time) values (2, timestamp '2024-01-22 09:00:00.0', timestamp '2024-01-22 17:00:00.0'); insert into trips (id, start_time, end_time) values (3, timestamp '2024-01-25 08:00:00.0', timestamp '2024-01-25 17:45:00.0'); insert into trips (id, start_time, end_time) values (4, timestamp '2024-01-27 07:00:00.0', timestamp '2024-01-27 16:00:00.0'); insert into trips (id, start_time, end_time) values (5, timestamp '2024-01-28 07:00:00.0', timestamp '2024-01-28 16:00:00.0'); insert into trips (id, start_time, end_time) values (6, timestamp '2024-01-29 07:00:00.0', timestamp '2024-01-29 16:00:00.0'); insert into trips (id, start_time, end_time) values (7, timestamp '2024-01-30 07:00:00.0', timestamp '2024-01-30 16:00:00.0'); insert into trips (id, start_time, end_time) values (8, timestamp '2024-01-31 07:00:00.0', timestamp '2024-01-31 16:00:00.0'); commit; end; /
1. If we use SUM or AVG functions on an INTERVAL datatype on pre-23c Oracle database, we will get an error as shown below.
2. Oracle 23c database allows the use of SUM and AVG functions with INTERVAL datatype
select sum(duration),avg(duration) from trips;
– We can also use SUM and AVG as analytics functions with INTERVAL datatype
select id,start_time,end_time,duration, sum(duration) over (order by id rows unbounded preceding) as DUR_RUN_TOTAL, avg(duration) over (order by id rows unbounded preceding) as DUR_RUN_AVG from trips;
Overview: In previous Database releases, you could store flashback database logs only in the fast recovery area Oracle 23c Database introduces the ... Read More
Overview: Oracle 23c Database introduces a new feature to control whether a user or session is enabled for read-write operations, irrespective of the ... Read More