Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> optimizing a view

optimizing a view

From: Nicklas Hjalmarsson <hjalmarssn_at_logica.com>
Date: Mon, 13 Dec 1999 17:19:44 +0100
Message-ID: <8336f2$as4@romeo.logica.co.uk>


Is it possible to have a query go through an index when selecting from a view?

If someone can look at thios example
?
CREATE TABLE TEST   TIME DATE,
  VALUE NUMBER ) ; CREATE INDEX cc_test_time_ndx ON TEST(time)   TABLESPACE index_data ;

CREATE OR REPLACE VIEW TESTVIEW (TIME,
SUM_VAL ) AS select trunc(time,'MI') time ,sum(value) sum_val from test group by trunc(time,'MI');

When I select directy from the table it works fine. select trunc(time,'MI') time ,sum(value) sum_val from test where time < SYSDATE -1 group by trunc(time,'MI');

But when I use the view it makes a full tableacan. select * from testview where time < SYSDATE -1;

I cannot make the query directly towards the table but its ok redifine the view or a create another index. Received on Mon Dec 13 1999 - 10:19:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US