Indexing View Columns?

From: NewUser2008 <mcseven_at_gmail.com>
Date: Thu, 10 Mar 2011 04:50:11 -0800 (PST)
Message-ID: <0171e84e-44f6-40d8-bf20-24f267f62285_at_d12g2000prj.googlegroups.com>



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 Received on Thu Mar 10 2011 - 06:50:11 CST

Original text of this message