Dear all,

I came across a question I was not able to answer. I create a VIEW in
Oracle 10g like this:
-- ---------------------------
CREATE OR REPLACE VIEW TEST_VIEW (
col1, col2, col3
) AS
SELECT id col1, 1 col2, nicedate col3 FROM test_table
UNION ALL SELECT id col1, 2 col2, nicedate col3 FROM test_table
UNION ALL SELECT id col1, 3 col2, nicedate col3 FROM test_table
UNION ALL SELECT id col1, 4 col2, nicedate col3 FROM test_table
UNION ALL SELECT id col1, 5 col2, nicedate col3 FROM test_table
UNION ALL SELECT id col1, 6 col2, nicedate col3 FROM test_table
-- ---------------------------

Apparently each line will have a different WHERE-Clause (which I
omitted here)
and a "hard coded" second column. With 10 lines there is no problem so
far,
however, if I need 10k of these statements, response time takes
minutes when
questioning with """SELECT * FROM TEST_VIEW WHERE col2 = 3012""";

probably b/c the database will create a result set from all statement
lines
within the view and will then be filtering the rows.

Is there any way to go around this issue? Like indexing the col2 or
giving
it a hint on which UNION line to use?

A stored function and dynamic SQL is not an option, then I could use
my
business logic to do the same.

Thanks, NewUser2k8

On Thu, 10 Mar 2011 04:50:11 -0800, NewUser2008 wrote:

Indexing column in a view? Sure, as soon as it is possible to assign
rowid to the rows of a view. Not a problem, as long as the view is
materialized.
I would strongly advise to consider alternative design options.



--
http://mgogala.byethost5.com

On Mar 10, 8:30*am, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:
NewUser2k8, as Mladen said you can only build indexes on materialized
views, which are really tables, that get updated at periodic times
based on the material view definition.

What is the purpose of this view? If assigning a specific value to
column2 based on the value of another column in the row then could you
use a CASE statement instead?

1 select fld2,
2 case when fld2 = 4 then 'SALES'
3 when fld2 = 5 then 'RETAIL PARTS'
4 when fld2 = 6 then 'FIELD TECH'
5 when fld2 = 7 then 'WEB SALE'
6 end as "SALES CREDIT"
7* from marktest
SQL> /

FLD2 SALES CREDIT
---------- ------------
4 SALES
5 RETAIL PARTS
6 FIELD TECH
7 WEB SALE

HTH -- Mark D Powell --

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

On Mar 10, 7:33*am, NewUser2008 <mcse... (AT) gmail (DOT) com> wrote:
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
--
@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/lano...ndo-beach.html

On Mar 10, 7:50*am, NewUser2008 <mcse... (AT) gmail (DOT) com> wrote:

Why is it not an option?

@joel garry: don't think so. It does not involve normalization at this
stage; there ist no static modeling involved here. but thanks anyway.

@steve howard:
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.

Thanks, NewUser2k8

On 10 Mrz., 20:15, NewUser2008 <mcse... (AT) gmail (DOT) com> wrote:
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

Hi, thank you for your reply.

from post 4 as prerequesites; in addition, this table
is created:

TABLE EMPLOYEE_CONTRACTS:
===
- ident_id NUMBER
- contract_start DATE
- contract_end DATE

Now, for the implicit assignments (create view):

SELECT ec.ident_id, 1421
FROM emloyee_contracts ec
WHERE contract_end IS NULL
/* Dynamic group of all indefinitely employed people */
UNION ALL
SELECT ec.ident_id, 210
FROM emloyee_contracts ec LEFT
JOIN identity i ON i.id=ec.ident_id
WHERE UPPER(i.name) LIKE 'S%'
/* Dynamic group of all people whose name starts with 's' */
UNION ALL
SELECT i.ident_id, 19920
FROM identity i LEFT
JOIN emloyee_contracts ec ON i.id=ec.ident_id LEFT
JOIN explicit_assign ea ON ea.ident_id = i.id
WHERE EXISTS ea.group_id AND ec.contract_end < SYSDATE
/* Dynamic group of all people who are not employed any more and
still have an explicit group membership somewhere */

not only Oracle but also MariaDB and PostGreSQL, I'd have to re-
code the trigger code for all these RDBMS - everytime. So my aim
is at not using code at all, but to limit everything to VIEWs where
coding is somewhat similar in all three RDBMS.

Thanks again, hth, newuser2k8

On Mar 10, 11:33*am, NewUser2008 <mcse... (AT) gmail (DOT) com> wrote:
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 --