Iterating Oracle collections of objects with out exploding them

Question

I'm using Oracle object data types to represent a timespan or period. And I've got to do a bunch of operations that involve working with collections of periods. Iterating over collections in SQL is significantly faster than in PL/SQL.

CREATE TYPE PERIOD AS OBJECT (
  beginning DATE,
  ending    DATE,
  ... some member functions...);

CREATE TYPE PERIOD_TABLE AS TABLE OF PERIOD;

-- what I would like to do: where t.column_value is still a period type
SELECT (t.column_value).range_intersect(period2)
FROM TABLE(period_table1) t
WHERE pa_contains(period_table1, (t.column_value).prev()) = 0
  AND pa_contains(period_table1, (t.column_value).next()) = 1

The problem is that the TABLE() function explodes the objects into scalar values, and I really need the objects instead. I could use the scalar values to recreate the objects but this would incur the overhead of re-instantiating the objects. And the period is designed to be subclassed so there would be additional difficulty trying to figure out what to initialize it as.

Is there another way to do this in SQL that doesn't destroy my objects?

Answer 1

Hi Scott,

If I understand your question correctly, the problem lies with the behaviour of the TABLE operator that doesn't return a table of objects (complete with attribute and member functions) but instead a simple "scalar" table (only attributes are accessible).

I'm not sure you can do what you describe with the TABLE operator. Here is a workaround with a temporary table instead of your nested table (Oracle 10.2.0.1):

SQL> CREATE OR REPLACE TYPE PERIOD AS OBJECT (
  2    beginning DATE,
  3    ending    DATE,
  4    MEMBER FUNCTION range_intersect (p_period period) RETURN VARCHAR2
  5  );
  6  /     
Type created

SQL> CREATE OR REPLACE TYPE BODY period IS
  2     MEMBER FUNCTION range_intersect(p_period period) RETURN VARCHAR2 IS
  3     BEGIN
  4        RETURN CASE
  5                  WHEN p_period.beginning <= ending
  6                       AND p_period.ending >= beginning
  7                  THEN 'Y'
  8                  ELSE 'N'
  9               END;
 10     END range_intersect;
 11  END;
 12  /     
Type body created

SQL> CREATE GLOBAL TEMPORARY TABLE period_table_tmp (
  2     per period
  3  );
Table created

With this setup, I can query the table period_table_tmp in SQL as if it were a collection of period object (i.e. I can see the member functions):

SQL> INSERT INTO period_table_tmp
  2     VALUES (period(DATE '2010-01-01', DATE '2010-01-31'));
1 row inserted

SQL> INSERT INTO period_table_tmp
  2     VALUES (period(DATE '2010-02-01', DATE '2010-02-28'));     
1 row inserted

SQL> SELECT t.per.beginning, t.per.ending,
  2         t.per.range_intersect(period(DATE '2010-01-01',
  3                                      DATE '2010-01-15')) intersec
  4    FROM period_table_tmp t;

PER.BEGINNING PER.ENDING  INTERSEC
------------- ----------- ---------
01/01/2010    31/01/2010  Y
01/02/2010    28/02/2010  N

Answer 2

Sorry this was a really tough question. But I finally found a way to do this using some tools I had created earlier. The trick ended up being to iterate over a nested table of number to get each element.

So the first piece was a series generator that I had shamelessly borrowed from Postgres. There are other ways to generate numbers but this is pretty efficient.

CREATE OR REPLACE FUNCTION generate_series(
  p_start         NUMBER,
  p_end           NUMBER
) RETURN NUMBER_TABLE PIPELINED IS
BEGIN
  FOR i IN p_start .. p_end LOOP
    PIPE ROW(i);
  END LOOP;
  RETURN;
END;

And the second piece is the ability to subscript a collection item in SQL.

CREATE OR REPLACE FUNCTION get_item(p1 PERIOD_TABLE, idx NUMBER)
RETURN PERIOD IS
BEGIN
  RETURN p1(idx);
END;

And finally putting it all together:

FUNCTION range_intersect(
  p1 PERIOD_TABLE,
  p2 PERIOD_TABLE
) RETURN PERIOD_TABLE IS
  v_return    PERIOD_TABLE;
  v_len1      NUMBER(8);
  v_len2      NUMBER(8);
BEGIN
  v_len1 := p1.last;
  v_len2 := p2.last;

  WITH pa1 AS (
    SELECT get_item(p1, column_value) AS period
    FROM TABLE(generate_series(1, v_len1))
  ),
  pa2 AS (
    SELECT get_item(p2, column_value) AS period
    FROM TABLE(generate_series(1, v_len2))
  )     
  SELECT period(start_time, MIN(end_time)) 
  BULK COLLECT INTO v_return
  FROM (
    SELECT (pa1.period).first() AS start_time
    FROM pa1
    WHERE contains(p1, (pa1.period).prev()) = 0
      AND contains(p2, (pa1.period).first()) = 1

    UNION ALL

    SELECT (pa2.period).first() AS start_time
    FROM pa2
    WHERE contains(p1, (pa2.period).prev()) = 0
      AND contains(p2, (pa2.period).first()) = 1
  ) s_start
  ... snip...

Not easy, but doable.