Re: optimizing a view

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Mon, 13 Dec 1999 18:52:20 +0800
Message-ID: <3854CFE4.2A5@yahoo.com>

Nicklas Hjalmarsson wrote:
>
> 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" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Mon Dec 13 1999 - 04:52:20 CST