Frequency-Based Ranges – Generalized with SQL Macro

In the previous post I used MATCH_RECOGNIZE to convert a list of concrete event times to frequency-based ranges.
The same technique can be applied with different tables, so I wrapped the query in a Table SQL Macro.

The parameters are the table, the “type” column, the “time” column, and (optionally) the period used for filtering.
I assumed the frequencies are always in minutes. The macro can be further generalized to support different time resolutions.

Here is the code:

create or replace function freq_view
(
    i_table     in dbms_tf.table_t,
    i_columns   in dbms_tf.columns_t,
    i_from_time in date default date '1970-01-01',
    i_to_time   in date default date '9999-12-31'
) return varchar2 sql_macro as
begin
    return 'select event_type ' || i_columns(1) || ',
       to_char(from_time,''yyyy-mm-dd'') "DATE",
       to_char(from_time,''hh24:mi'') ||
         nvl2(to_time,
              '' - '' || to_char(to_time,''hh24:mi'')
                    || '' every '' || minutes || '' minutes (''
                    || number_of_events || '' events)'',
              null) event_times
from (
  select ' || i_columns(1) || ' as event_type, trunc(' || i_columns(2) || ',''mi'') as event_time
  from   i_table
  where  ' || i_columns(2) || ' between i_from_time and i_to_time
) match_recognize (
    partition by event_type
    order by event_time
    measures X.event_time from_time,
             last(Y.event_time) to_time,
             round((first(Y.event_time)-X.event_time)*24*60) minutes,
             count(*) number_of_events
    pattern (X Y{2,} | X)
    define
      Y as event_time-prev(event_time) = first(Y.event_time)-X.event_time
)
order by event_type, from_time';
end freq_view;
/

Let’s use it for the same data set used in the original post:

SQL> break on event_type skip 1 dup
SQL> select * from freq_view(events,columns(event_type,event_time),timestamp'2022-07-29 08:00:00');

EVENT_ DATE           EVENT_TIMES
------ -------------- ----------------------------------------------
A      2022-07-29     08:10 - 08:50 every 10 minutes (5 events)
A      2022-07-29     08:58 - 09:22 every 8 minutes (4 events)
A      2022-07-29     09:33
A      2022-07-29     09:42 - 10:52 every 10 minutes (8 events)
A      2022-07-29     10:59 - 11:11 every 4 minutes (4 events)
A      2022-07-29     11:21 - 12:01 every 10 minutes (5 events)

B      2022-07-29     08:15 - 09:55 every 20 minutes (6 events)
B      2022-07-29     10:10
B      2022-07-29     10:25 - 12:05 every 25 minutes (5 events)

C      2022-07-29     08:02 - 08:32 every 2 minutes (16 events)
C      2022-07-29     08:49 - 09:05 every 2 minutes (9 events)
C      2022-07-29     09:08 - 09:20 every 4 minutes (4 events)
C      2022-07-29     09:22 - 10:16 every 2 minutes (28 events)
C      2022-07-29     11:46 - 12:20 every 2 minutes (18 events)


14 rows selected.

And now let’s use it for something else – the DBMS_SCHEDULER executions from the last 3 hours:

SQL> select * from freq_view(user_scheduler_job_run_details,columns(job_name,log_date),sysdate-3/24);

JOB_NAME     DATE            EVENT_TIMES
------------ --------------- ------------------------------------------------
JOB_AAA      2022-07-30      08:45 - 10:45 every 60 minutes (3 events)
JOB_BBB      2022-07-30      08:10 - 10:10 every 60 minutes (3 events)
JOB_CCC      2022-07-30      08:07 - 11:02 every 5 minutes (36 events)
JOB_DDD      2022-07-30      10:00
JOB_EEE      2022-07-30      08:06 - 11:05 every 1 minutes (180 events)

Subtleties – Part 3 (more workarounds for COLLECT DISTINCT in PL/SQL)

In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL, and saw two workarounds – using dynamic SQL and using the SET function.
In Part 2 we saw that the SET function can operate on Nested Table but not on Varray.
In this post we’ll see two more workarounds.

Inline View

We can first remove the duplicates in an inline view, and then use the “simple” COLLECT function on the outcome of the inline view:

create type integer_vt as varray(100) of integer
/

var rc refcursor
begin
  open :rc for
    select person_id,cast(collect(project_id) as integer_vt) project_id_list
    from (
      select distinct person_id,project_id from project_assignments
    )
    group by person_id
    order by person_id;
end;
/

PL/SQL procedure successfully completed. 

print rc

PERSON_ID PROJECT_ID_LIST
---------- -----------------------------------
       101 INTEGER_VT(2, 3, 1)
       102 INTEGER_VT(2)
       103 INTEGER_VT(3)

MULTISET

We can use the MULTISET operator instead of the COLLECT function. Note that this solution is not identical to the previous ones – we use a separate query for each person as the input to the MULTISET operator, and we get results also for people with no project assignments at all:

begin
    open :rc for
        select p.person_id,
               cast(multiset (select distinct project_id
                     from   project_assignments a
                     where  a.person_id = p.person_id) as integer_vt) project_id_list
        from   people p
        order  by person_id;
end;
/

PL/SQL procedure successfully completed. 

print rc

PERSON_ID PROJECT_ID_LIST
---------- ------------------------------
       101 INTEGER_VT(1, 2, 3)
       102 INTEGER_VT(2)
       103 INTEGER_VT(3)
       104 INTEGER_VT()

Conclusion

There is no one “correct” solution. Under different circumstances, different solutions differ in complexity, performance, etc. We should use the one that is best for the specific case.
And if we need to use a collection type, then NESTED TABLE is by far my first choice. I would use the much more limited type VARRAY only if I have a really good reason to do so.

PL/SQL Functions in the WITH Clause and Read (In)Consistency

A question was raised in Martin Widlake’s session this morning in OUG Ireland 2016. The (excellent) session was about the performance of calling PL/SQL functions from SQL, and Martin scared the audience (or at least tried to scare…) with the fact that read consistency is not kept (with respect to the base SQL statement) when the PL/SQL functions are called from that SQL statement.
The question that was raised was whether this is also true when the PL/SQL functions are embedded in the WITH clause – a new 12c feature about which you can read in detail in Write Less with More – Part 8.

Here is a small example that shows that read consistency is not kept in this case as well.

create table t (x number);

with function f return number is
    c number;
  begin
    select count(*) into c from t;
    dbms_lock.sleep(10);
    return c;
  end f;
select f from dual
union all
select f from dual
/

Now, right after starting executing the previous query, from another session we do the following:

insert into t (x) values (1);
commit;

and the result that we get from the query is:

0
1

<

Identity Columns and Table Locks

In my “write less with more” presentation today at OUG Ireland 2016, I showed the ability to change an identity column from GENERATED BY DEFAULT AS IDENTITY to GENERATED ALWAYS AS IDENTITY with the START WITH LIMIT VALUE option. You can read about this option (and about identity columns in general) in detail in Write Less with More – Part 2.
Jonathan Lewis raised a good question – if the identity column is not indexed, will this operation lock the entire table?
I didn’t know the answer so I checked it later, and the answer is that is locks the entire table in exclusive mode.
And… this is true also if the identity column is indexed.

ilOUG Tech Days – My Experience

Last week I participated in “ilOUG Tech Days 2015” – a two day conference of the Israeli Oracle User Group. It took place in a hotel in Haifa, and hosted more than 100 attendees and an impressive league of international and local speakers.
It was a really great event in my opinion, well organized and executed by ilOUG management, especially Ami Aharonovich and Liron Amitzi (you can read Liron’s post about the conference here).
I gave two presentations and attended many good lectures and keynotes presented by Bryn Llewellyn, Keith Laker, Heli Helskyaho, Jonathan Lewis, Tom Kyte, Joze Senegacnik and Ami Aharonovich. I wish I could attend more, but I couldn’t be in multiple places at the same time…

My presentations were about “Indexes and Indexing in Oracle 12c” and “Deep Dive into Oracle 12c Pattern Matching”. In the latter I had a very special guest in the room – Keith Laker from Oracle, the product manager of the feature I was just presenting. It was very cool Keith was there, and also beneficial, as I could get his help answering some questions, like:

  • Is the MATCH_RECOGNIZE clause part of ANSI SQL? (answer: not yet)
  • Does it require a specific Oracle edition or option? (answer: no, it is part of all Oracle editions with no extra cost)
  • Are there any differences in the feature between 12.1.0.1 and 12.1.0.2? (answer: no)

Both my presentations were brand new for this conference, and I feel they still need some “tuning” for the future, especially the one about indexing in 12c, which contains more content than can be delivered in just 45 minutes.
My presentations are available to download from the Presentations page.
I will write more about them later.

Other highlights:

ilOUG Day

ilOUG, the Israeli Oracle User Group, is introducing a new and refreshing concept for its SIG meetings. On Monday, March 23rd 2015, there will be for the first time a meeting for the entire technological community, unlike the past meetings that were focused each time on a specific interest group (and therefore also small in many cases). In the upcoming event there will be 15 lectures in 5 parallel tracks:

  • Database Administration
  • Database Development
  • Engineered Systems, Hardware & OS
  • Development Technologies and Tools
  • Big Data & BI

There is great variety, and event participants can freely switch tracks at any point, so I’m sure anyone can find something of interest (some of you may even find it hard to choose between simultaneous presentations). In addition, such a community wide event allows more mingling and networking than previous smaller events.

I will present the lecture Write Less (Code) with More (Oracle 12c New Features) in the Database Development track. Here is the abstract:

Oracle 12c introduced many new features that allow us developers to write less code than in previous releases, and become more efficient and productive.
Some features, such as Row Limiting and Lateral Inline Views, enhance the SQL language, so SQL statements can become much shorter and more readable than before.
Other features, such as Temporal Validity and In-Database Archiving, offer built-in functionality that previously had to be implemented by the application.
Attend this session to learn about several of these new features, and see many useful examples.

The meeting will take place at the Dan Panorama hotel in Tel Aviv, from 2pm. Participation is free of charge, but requires registration in advance.

For full details see: http://www.iloug.org.il/ilOUGDay/?page=Agenda2

 

Lecture on EBR

In the next meeting of Oracle and ilOUG DBA Forum, on December 10th, I will talk about Edition-Based Redefinition, a powerful and fascinating feature of Oracle (added in version 11.2), that enables application upgrades with zero downtime, while the application is actively used and operational.
I will explain how to use it, give some tips from my hands-on experience with EBR, and present examples.

Course Addendum

I lectured about Oracle SQL and PL/SQL in a course lately. As always, some issues that had not been part of the planned scope were raised and discussed, so I thought to write short posts about some of them, for the benefit of the participants and everyone else that may find it relevant.

These issues are: