Indexing View Columns?
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