Re: Indexing View Columns?
Date: Thu, 10 Mar 2011 11:15:26 -0800 (PST)
Message-ID: <67351fac-26fd-4687-9797-d4bbc99676eb_at_p12g2000vbo.googlegroups.com>
_at_joel garry: don't think so. It does not involve normalization at this stage; there ist no static modeling involved here. but thanks anyway.
_at_steve howard:
> Why is it not an option?
Here goes.
a) create type:
CREATE TYPE GROUP_MEMBERSHIP_TYPE IS OBJECT (ident_id NUMBER, group_id
NUMBER);
b) create result set type:
CREATE TYPE GROUP_MEMBERSHIP_SET AS TABLE OF GROUP_MEMBERSHIP_TYPE;
c) Write a function:
GET_GROUP_MEMBERSHIP(GROUP_ID IN NUMBER) RETURN GROUP_MEMBERSHIP_SET;
Now I can select like this:
SELECT * FROM TABLE(GET_GROUP_MEMBERSHIP(1512)); which will return the
appropriate rows. This solution I implemented with EXECUTE IMMEDIATE
dynamic SQL; the SQL works flawlessly.
However with the tables from 4th comment:
## SELECT ea.ident_id, ea.group_id, g.name
## FROM EXPLICIT_ASSIGN ea LEFT
## JOIN GROUPS g ON g.id=ea.group_id
## WHERE g.id IN (SELECT 1,2,3,4,5,6,7 FROM DUAL)
-- WORKS!
## SELECT ia.ident_id, ia.group_id, g.name
## FROM table(GET_GROUP_MEMBERSHIP(???????????)) ia LEFT
## JOIN GROUPS g ON g.id=ia.group_id
## WHERE g.id = IN (SELECT 1,2,3,4,5,6,7 FROM DUAL)
-- See?
- how to handle subqueries?
- how to unite with explicit assignment?
- how to reuse in other SQL statements?
We'll always have to feed the desired groups into the WHERE clause AND the function parameter, even if the parameter is a table of number as well... With this solution I always have to treat explicit and dynamic group memberships differently on database level.
Don't get me wrong: The solution with the view from first post works technically. It's just damn slow with a growing number of UNION ALL statements. But perhaps it's really not solvable on dbms level.
Thanks, NewUser2k8 Received on Thu Mar 10 2011 - 13:15:26 CST