Hi,
I'm not sure of the words I could use to explain what I want to do. It's some kind of copy on write, or a layer over a table...
Well, to make it simple, let's say I have one table EMP containing people's informations. This table is updated by another program once every night.
In order to override some data in that table, I also have a table LAYER.
CREATE TABLE EMP (
id NUMBER,
lastname VARCHAR2(30),
firstname VARCHAR2(30),
CONSTRAINT emp_pk PRIMARY KEY (id)
) ;
CREATE TABLE LAYER (
id NUMBER,
lastname VARCHAR2(30),
firstname VARCHAR2(30),
CONSTRAINT layer_pk PRIMARY KEY (id)
) ;
INSERT INTO emp VALUES (1,'Hughes','Alan');
INSERT INTO emp VALUES (2,'Col','Robert');
INSERT INTO emp VALUES (3,'Chain','Vicky');
INSERT INTO layer VALUES(2,'Kowl', null);
Now, in order to get the fixed data: 2 Kowl Robert, I thought about using this kind of view:
CREATE VIEW v_emp AS SELECT
e.id
, COALESCE(l.lastname, e.lastname) lastname
, COALESCE(l.firstname, e.firstname) firstname
FROM emp e LEFT JOIN LAYER l ON e.id=l.id ;
Unfortunately, performance when querying on firstname and/or lastname are not so good (my EMP table contains about 500,000 rows, query on a name takes 0.30 secs). Creating indices on both firstname and lastname columns doesn't help.
Since I can't create index on COALESCE(l.lastname, e.lastname), I'm quite stuck...
I've tried to create index on COALESCE(null, e.lastname) with no luck. I couldn't try BITMAP index because I don't have partitioning enabled, but I'm not sure it helps neither.
Do you have an idea to improve performance, or maybe should I use another table design?
Using a materialized view surely could help, but I'd like to avoid it in this case.
Thank you for your help gurus !
EDIT:
Sample queries:
SQL> select * from emp ;
ID LASTNAME FIRSTNAME
---------- ---------- ----------
1 Hughes Alan
2 Col Robert
3 Chain Vicky
SQL> select * from layer ;
ID LASTNAME FIRSTNAME
---------- ---------- ----------
2 Kowl
SQL>
SQL>
SQL>
SQL> select * from v_emp where lastname='Kowl' ;
ID LASTNAME FIRSTNAME
---------- ---------- ----------
2 Kowl Robert
SQL> select * from v_emp where lastname='Col' ;
no rows selected
Added some sample queries