Re: Indexing View Columns?
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Fri, 11 Mar 2011 06:12:13 -0800 (PST)
Message-ID: <4e3a444a-52ba-4d78-8024-aa79eb6e3bca_at_dn9g2000vbb.googlegroups.com>
On 10 Mrz., 20:15, NewUser2008 <mcse..._at_gmail.com> wrote:
> _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?
>
> a) how to handle subqueries?
> b) how to unite with explicit assignment?
> c) 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.
Date: Fri, 11 Mar 2011 06:12:13 -0800 (PST)
Message-ID: <4e3a444a-52ba-4d78-8024-aa79eb6e3bca_at_dn9g2000vbb.googlegroups.com>
On 10 Mrz., 20:15, NewUser2008 <mcse..._at_gmail.com> wrote:
> _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?
>
> a) how to handle subqueries?
> b) how to unite with explicit assignment?
> c) 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.
Maybe, maybe not. It would be interesting and helpful to learn your WHERE conditions for implicit assignments.
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.
Kind regards
robert Received on Fri Mar 11 2011 - 08:12:13 CST