Creating a [materialised]view from generic data in Oracle/Mysql

Question

I have a generic datamodel with 3 tables

CREATE TABLE Properties 
(
  propertyId int(11) NOT NULL AUTO_INCREMENT,
  name varchar(80) NOT NULL
)
CREATE TABLE Customers
(
  customerId int(11) NOT NULL AUTO_INCREMENT,
  customerName varchar(80) NOT NULL
)
CREATE TABLE PropertyValues
(
  propertyId int(11) NOT NULL,
  customerId int(11) NOT NULL,
  value varchar(80) NOT NULL
)
INSERT INTO Properties VALUES (1, 'Age');
INSERT INTO Properties VALUES (2, 'Weight');
INSERT INTO Customers VALUES (1, 'Bob');
INSERT INTO Customers VALUES (2, 'Tom');
INSERT INTO PropertyValues VALUES (1, 1, '34');
INSERT INTO PropertyValues VALUES (2, 1, '80KG');
INSERT INTO PropertyValues VALUES (1, 2, '24');
INSERT INTO PropertyValues VALUES (2, 2, '53KG');

What I would like to do is create a view that has as columns all the ROWS in Properties and has as rows the entries in Customers. The column values are populated from PropertyValues. e.g.

 customerId Age   Weight
 1          34    80KG
 2          24    53KG

I'm thinking I need a stored procedure to do this and perhaps a materialised view (the entries in the table "Properties" change rarely). Any tips?

Answer 1

It's easy enough to generate a view with dynamic SQL:

create or replace procedure gen_view
as
    cols_stmt varchar2(32767);
    from_stmt varchar2(32767);
    subq_name varchar2(30);
begin
    for r in ( select * from properties
               order by propertyid )
    loop
        subq_name := 'pv_'||trim(to_char(r.propertyid)); 
        cols_stmt := cols_stmt || ', '|| subq_name ||'.value as '||r.name;
        from_stmt := from_stmt || ' left join ( select value, customerid from propertyvalues where propertyid = '
            ||trim(to_char(r.propertyid))||') '||subq_name
            ||' on '||subq_name||'.customerid = customers.customerid';
    end loop;
    execute immediate 'create or replace view eav_view as select customers.customerid, customers.customername'
                        || cols_stmt
                        || ' from customers '
                        || from_stmt;
end gen_view;
/

Here's it working:

SQL> exec gen_view

PL/SQL procedure successfully completed.

SQL> select * from eav_view
  2  /

CUSTOMERID
----------
CUSTOMERNAME
--------------------------------------------------------------------------------
AGE
--------------------------------------------------------------------------------
WEIGHT
--------------------------------------------------------------------------------
         1
Bob
34
80KG

         2
Tom
24
53KG


SQL>

Let's create a new property and insert values for it for some of the customers...

SQL> insert into properties values (3, 'FavouriteIceCream')
  2  /

1 row created.

SQL> insert into propertyvalues values (3, 1, 'Cherry Garcia')
  2  /

1 row created.

SQL> exec gen_view

PL/SQL procedure successfully completed.

SQL> select * from eav_view
  2  /

CUSTOMERID
----------
CUSTOMERNAME
--------------------------------------------------------------------------------
AGE
--------------------------------------------------------------------------------
WEIGHT
--------------------------------------------------------------------------------
FAVOURITEICECREAM
--------------------------------------------------------------------------------
         1
Bob
34
80KG
Cherry Garcia

         2
Tom
24
53KG

SQL>

"I'm thinking I need a stored procedure to do this and perhaps a materialised view (the entries in the table "Properties" change rarely)."

The problem is, Properties are going to change, and I'm guessing you will have no oversight of when that happens. So you are going to find it very hard to apply the changes to a materialized view. This matters because changing the projection of a materialized view necessitates dropping it. So it's quite difficult to do this without an interruption to service. A similar consideration applies to the regular view , but the outage is almost zero.

If you do want to convert the view statement into a materialized view note that Oracle doesn't seem to like the ANSI-92 syntax when it comes to materialized views (it hurls ORA-12054). I'm not sure why that should be, but the problem went away when I changed to the older joining technique, which is annoying because the outer join syntax is clunkier.

A solution without the need to re-create database objects would be to use the dynamic SQL in a function which returns a Ref Cursor, which maps to a JDBC ResultSet:

create or replace function get_eav_view
    return sys_refcursor
as
    cols_stmt varchar2(32767);
    from_stmt varchar2(32767);
    subq_name varchar2(30);
    return_value sys_refcursor;
begin
    for r in ( select * from properties
               order by propertyid )
    loop
        subq_name := 'pv_'||trim(to_char(r.propertyid)); 
        cols_stmt := cols_stmt || ','|| subq_name ||'.value as '||r.name;
        from_stmt := from_stmt || ' left join ( select value, customerid from propertyvalues where propertyid = '
            ||trim(to_char(r.propertyid))||') '||subq_name
            ||' on '||subq_name||'.customerid = customers.customerid';
    end loop;
    open return_value for
            'select customers.customerid, customers.customername'
                    || cols_stmt
                    || ' from customers '
                    || from_stmt;
    return return_value;
end get_eav_view;
/

This will always return the latest projection:

SQL> var rc refcursor
SQL> exec :rc := get_eav_view

PL/SQL procedure successfully completed.

SQL> print rc

CUSTOMERID
----------
CUSTOMERNAME
--------------------------------------------------------------------------------
AGE
--------------------------------------------------------------------------------
WEIGHT
--------------------------------------------------------------------------------
FAVOURITEICECREAM
--------------------------------------------------------------------------------
         1
Bob
34
80KG
Cherry Garcia

         2
Tom
24
53KG


SQL>     

Now, if we add a new property it gets picked up immediately:

SQL>  insert into properties values (4, 'StarSign')
   2  /

1 row created.

SQL>  insert into propertyvalues values (4, 2, 'Aries')
  2  /

1 row created.

SQL> exec :rc := get_eav_view

PL/SQL procedure successfully completed.

SQL> print rc

CUSTOMERID
----------
CUSTOMERNAME
--------------------------------------------------------------------------------
AGE
--------------------------------------------------------------------------------
WEIGHT
--------------------------------------------------------------------------------
FAVOURITEICECREAM
--------------------------------------------------------------------------------
STARSIGN
--------------------------------------------------------------------------------
         1
Bob
34
80KG
Cherry Garcia


         2
Tom
24
53KG

Aries


SQL>