Re: Indexing View Columns?

> Maybe, maybe not. It would be interesting and helpful to learn your
> WHERE conditions for implicit assignments.
ok, complete example here. Please use the tables from post 4 as prerequesites; in addition, this table is created:

TABLE EMPLOYEE_CONTRACTS:
===

- ident_id NUMBER
- contract_start DATE
- contract_end DATE

Now, for the implicit assignments (create view):

  SELECT ec.ident_id, 1421
  FROM emloyee_contracts ec
  WHERE contract_end IS NULL
  /* Dynamic group of all indefinitely employed people */ UNION ALL
  SELECT ec.ident_id, 210
  FROM emloyee_contracts ec LEFT
  JOIN identity i ON i.id=ec.ident_id
  WHERE UPPER(i.name) LIKE 'S%'
  /* Dynamic group of all people whose name starts with 's' */ UNION ALL
  SELECT i.ident_id, 19920
  FROM identity i LEFT
  JOIN emloyee_contracts ec ON i.id=ec.ident_id LEFT   JOIN explicit_assign ea ON ea.ident_id = i.id   WHERE EXISTS ea.group_id AND ec.contract_end < SYSDATE   /* Dynamic group of all people who are not employed any more and

     still have an explicit group membership somewhere */

> Btw, another solution might be to encode your implicit assignments
> into trigger logic and write assignments into the assignment table
> whenever you insert a new identity.  Advantage: querying will be fast
> or at least can be tuned more easily, downside is that you need manual
> intervention whenever your implicit assignments change.  But if that
> does not happen too often it might be an option nevertheless.
Thanks, yes, I had thought of that as well, however, since we use not only Oracle but also MariaDB and PostGreSQL, I'd have to recode  the trigger code for all these RDBMS - everytime. So my aim is at not using code at all, but to limit everything to VIEWs where coding is somewhat similar in all three RDBMS.

Thanks again, hth, newuser2k8 Received on Mon Mar 14 2011 - 08:50:37 CDT