Thanks Mark,

I apologise if my description caused confusion.
But yes, you're right. The conditions that define
group membership can come from any table in
the system; the system being a huge monolithic
data storage for a several thousand employees
company.

So to clear things up: An implicit group never exists
as single records [identity <==> group]; it just exists
as constraint definition set on _any_ system tables,
with the requirement that it returns an identity ID and
a group ID.

Since the group ID is unique to each of these constraint
sets, I did not have a better idea except hard coding it
into the SELECT-Statement or the resulting view
respectively.

I hope the view definition from my previous post gives
a good example of what I'm trying to accomplish:
At some point I'd like to:
--> SELECT ident_id, group_id FROM AllGroups

Which results in a 2-column result set like this:
1000, 512 -- from implicit group 512
201, 512 -- from implicit group 512
201, 4099 -- from implicit group 4099
2409, 4099 -- from implicit group 4099
201, 102 -- from explicit group 102 given there were such an
assignment
201, 516 -- from explicit group 516 given there were such an
assignment

Obviously, I now could limit the statement to only
the desired group number(s):
--> SELECT ident_id, group_id FROM AllGroups WHERE group_id IN (512)

Which results in a 2-column result set like this:
1000, 512 -- from implicit group 512
201, 512 -- from implicit group 512

And no, it does not have anything to do with FGAC
or a VPD. I do not want to limit database users to
access only some information; this mechanism will
be implemented into the business logic, since other
RDBMS do not support it like Oracle does.

Thanks again for your help,
NewUser2008

On 14.03.2011 14:50, NewUser2008 wrote:
In Oracle you could try an FBI on UPPER(identity.name) or even a
combined index on (identity.id, UPPER(identity.name)).

Why do you not have "ea.group_id IS NOT NULL"? Wouldn't that be more
straightforward? Again you could try combined indexes
(emloyee_contracts.od, emloyee_contracts.contract_end) and
(explicit_assign.id, explicit_assign.group_id).

Question is though whether the other products will give you similar
indexing capabilities. In case not you are back to square 1. It may
still be easier to write 3 versions of the trigger(s)... In any case I
would first try to optimize individual queries and then look at the union.

Cheers

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On Mar 10, 12:15*pm, NewUser2008 <mcse... (AT) gmail (DOT) com> wrote:

Well, you can upgrade. http://blogs.oracle.com/optimizer/20...orization.html

jg
--
@home.com is bogus.
http://www.computerworlduk.com/news/...ft-with-mysql/

On 15 Mrz., 22:25, joel garry <joel-ga... (AT) home (DOT) com> wrote:
solve my difficulties since they do not result from slow singluar
queries. All the UNION ALL contents from above are in fact supported
by appropriate indices; what makes the query slow is that the view
will always be executed completely; if there are 2000 small, but fast
queries being executed the whole result becomes slow anyway. So, I'd
need a way to tell the DBMS only to execute the appropriate UNION ALL
line(s) which by design is not possible because the required line
number will first be available _after_ the execution of the statement.

I thought that the explain plan somehow would give a hint on which
part shall be executed, but apparently it does not. So unless there is
a way to feed parts of a "WHERE" clause into a function parameter set
automatically (maybe only via highly specialized triggers) I fear I
cannot solve my dilemma...

Thanks again, NewUser2k8

On Mar 17, 8:41*am, NewUser2008 <mcse... (AT) gmail (DOT) com> wrote:
You CAN get there, you just need to figure out exactly what it is you
are looking for... If you were to give us a scaled down example of
what your real query looks like, you may get better information as we
can only guess at what is the real problem you are trying to solve.

GIVEN:
SQL> select * from abc;

ID NICEDATE A
---------- --------- ----------
1000 07-MAR-11 ABC
1001 06-MAR-11 ABD
1002 05-MAR-11 ABE
1003 04-MAR-11 ABF
1004 03-MAR-11 ABG
1005 02-MAR-11 ABH
1006 01-MAR-11 ABI
1007 28-FEB-11 ABJ
1008 27-FEB-11 ABK
1009 26-FEB-11 ABL
1010 25-FEB-11 ABM
1011 24-FEB-11 ABM
1012 23-FEB-11 ABN

13 rows selected.

You can use RANK
SQL> select id, rank() over (order by a) rank, nicedate,a from abc;

ID RANK NICEDATE A
---------- ---------- --------- ----------
1000 1 07-MAR-11 ABC
1001 2 06-MAR-11 ABD
1002 3 05-MAR-11 ABE
1003 4 04-MAR-11 ABF
1004 5 03-MAR-11 ABG
1005 6 02-MAR-11 ABH
1006 7 01-MAR-11 ABI
1007 8 28-FEB-11 ABJ
1008 9 27-FEB-11 ABK
1009 10 26-FEB-11 ABL
1010 11 25-FEB-11 ABM
1011 11 24-FEB-11 ABM
1012 13 23-FEB-11 ABN

SQL> select id, (rank() over (order by a))+100 rank, nicedate,a from
abc;

ID RANK NICEDATE A
---------- ---------- --------- ----------
1000 101 07-MAR-11 ABC
1001 102 06-MAR-11 ABD
1002 103 05-MAR-11 ABE
1003 104 04-MAR-11 ABF
1004 105 03-MAR-11 ABG
1005 106 02-MAR-11 ABH
1006 107 01-MAR-11 ABI
1007 108 28-FEB-11 ABJ
1008 109 27-FEB-11 ABK
1009 110 26-FEB-11 ABL
1010 111 25-FEB-11 ABM
1011 111 24-FEB-11 ABM
1012 113 23-FEB-11 ABN

13 rows selected.
OR You can use ROWNUM???

SQL> select id col1,rownum col2,nicedate col3 from
(select id, nicedate from abc where a='ABD'
union all
select id, nicedate from abc where a='ABC')
/

COL1 COL2 COL3
--------- ---------- ---------
1001 1 06-MAR-11
1000 2 07-MAR-11