Re: Indexing View Columns?

From: joel garry <joel-garry_at_home.com>
Date: Thu, 10 Mar 2011 09:20:10 -0800 (PST)
Message-ID: <5506cccf-08b9-4927-b33c-a106e9817c7d_at_b13g2000prf.googlegroups.com>

On Mar 10, 7: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

I think you need to go back to the design stage and review the normalization. You are fighting relational theory here, the test_table is just a pile of data with many overlaid definitions, as exemplified by your mysterious unioned where clauses. There might be a pivot table operation buried in there somewhere, but I can't really tell.

jg

--
_at_home.com is bogus.
Give a man a fish, and you've fed him for a day.
Give a man millions of dead sardines, and you've got a problem.
http://latimesblogs.latimes.com/lanow/2011/03/officials-fear-bacteria-from-rotting-fish-could-imperil-more-sea-life-in-redondo-beach.html

Received on Thu Mar 10 2011 - 11:20:10 CST