Re: Indexing View Columns?
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Mon, 14 Mar 2011 07:07:46 -0700 (PDT)
Message-ID: <6396bd24-d3c7-4c51-8771-d8628d1ab114_at_a21g2000prj.googlegroups.com>
On Mar 10, 11:33 am, NewUser2008 <mcse..._at_gmail.com> wrote:
> Hi,
>
> thanks for your reply. Nah, sorry, it's not that easy. I'm trying to
> find an alternative group management method on database level that is
> able to manage both explicit group assignment and implicit assignment
> via an sql statement.
>
> For instance (simplified tables here):
>
> TABLE IDENTITY:
> ===
> - id
> - name
>
> TABLE GROUPS
> ===
> - id
> - name
>
> TABLE EXPLICIT_ASSIGN
> ===
> - ident_id
> - group_id
>
> VIEW IMPLICIT_ASSIGN -- simplified code...
> ===
> SELECT ident_id, 512 FROM employeeData WHERE -- several conditions
> UNION ALL
> SELECT ident_id, 13012 FROM someOtherTable WHERE -- several conditions
> UNION ALL
> SELECT ident_id, 4099 FROM thirdTable WHERE -- several conditions
>
> with the purpose of handling those groups seamlessly:
>
> VIEW ALL_GROUPS
> ===
> SELECT * FROM EXPLICIT_ASSIGN
> UNION ALL SELECT * FROM IMPLICIT_ASSIGN
>
> So one only needs to know the group number to get its members. The
> purpose of not using higher level scripting is that I
> a) can use this technique on the database level within triggers and
> stuff and
> b) feed it SUBQUERIES like SELECT * FROM all_groups WHERE group_id IN
> (...)
>
> Thanks, NewUser2k8
Date: Mon, 14 Mar 2011 07:07:46 -0700 (PDT)
Message-ID: <6396bd24-d3c7-4c51-8771-d8628d1ab114_at_a21g2000prj.googlegroups.com>
On Mar 10, 11:33 am, NewUser2008 <mcse..._at_gmail.com> wrote:
> Hi,
>
> thanks for your reply. Nah, sorry, it's not that easy. I'm trying to
> find an alternative group management method on database level that is
> able to manage both explicit group assignment and implicit assignment
> via an sql statement.
>
> For instance (simplified tables here):
>
> TABLE IDENTITY:
> ===
> - id
> - name
>
> TABLE GROUPS
> ===
> - id
> - name
>
> TABLE EXPLICIT_ASSIGN
> ===
> - ident_id
> - group_id
>
> VIEW IMPLICIT_ASSIGN -- simplified code...
> ===
> SELECT ident_id, 512 FROM employeeData WHERE -- several conditions
> UNION ALL
> SELECT ident_id, 13012 FROM someOtherTable WHERE -- several conditions
> UNION ALL
> SELECT ident_id, 4099 FROM thirdTable WHERE -- several conditions
>
> with the purpose of handling those groups seamlessly:
>
> VIEW ALL_GROUPS
> ===
> SELECT * FROM EXPLICIT_ASSIGN
> UNION ALL SELECT * FROM IMPLICIT_ASSIGN
>
> So one only needs to know the group number to get its members. The
> purpose of not using higher level scripting is that I
> a) can use this technique on the database level within triggers and
> stuff and
> b) feed it SUBQUERIES like SELECT * FROM all_groups WHERE group_id IN
> (...)
>
> Thanks, NewUser2k8
Your initial post shows the data coming from the same table and now your update shows multiple tables as the source. An accurate description of the problem always helps. Why is the data in multiple tables? What is the difference?
From the information posted it looks like each group was placed into its own table rather than a single table with views or FGAC (VPD) used to provide group members acess to only their data. But obviouls the presented information is still only a partial picture.
If the additional posted suggestions do not provide your answer then more information might help.
HTH -- Mark D Powell -- Received on Mon Mar 14 2011 - 09:07:46 CDT