Re: Indexing View Columns?
Date: Mon, 14 Mar 2011 19:21:28 +0100
Message-ID: <8u74liForfU1_at_mid.individual.net>
On 14.03.2011 14:50, NewUser2008 wrote:
> Hi, thank you for your reply.
>
>> 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%'
In Oracle you could try an FBI on UPPER(identity.name) or even a combined index on (identity.id, UPPER(identity.name)).
> /* 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 */
Why do you not have "ea.group_id IS NOT NULL"? Wouldn't that be more straightforward? Again you could try combined indexes (emloyee_contracts.od, emloyee_contracts.contract_end) and (explicit_assign.id, explicit_assign.group_id).
Question is though whether the other products will give you similar indexing capabilities. In case not you are back to square 1. It may still be easier to write 3 versions of the trigger(s)... In any case I would first try to optimize individual queries and then look at the union.
Cheers
robert
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Mon Mar 14 2011 - 13:21:28 CDT