Re: Indexing View Columns?

From: NewUser2008 <mcseven_at_gmail.com>
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?

  1. how to handle subqueries?
  2. how to unite with explicit assignment?
  3. 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

Original text of this message