Very Computer

by **Nicklas Hjalmarsso** » Sun, 31 Dec 1899 09:00:00

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.

by **Connor McDonal** » Sun, 31 Dec 1899 09:00:00

> 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.

Until 8i, a function on an indexed column will prohibit the use of the
index. Thus the view (which has trunc) will not allow it...

To avoid it, you could have have both the date and the trunc'd date in
the view, or just retain the original time and trunc it as your select.

HTH
--
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"

"Some days you're the pigeon, and some days you're the statue."

Very Computer

optimizing a view

optimizing a view

optimizing a view