Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: selects on views are slow while on tables fast
Peter Shankey wrote:
>
> version 7.3.3
> 5 tables have views created. The views are very simple, all the views
> are is just one table except a couple of the columns use the nvl
> function. A query run against the tables and using 'explain plan' shows
> a cost of 37. The query uses all the indexes as it should. The same
> query run against the views of the tables returns a cost of 21997. The
> query uses alot of full table scans. Why is this happening?? I thought
I think what is happening is pretty simple -- when you apply a function (like NVL), or an arithmetic operation (like "+"), to one of the columns in the WHERE clause in a query against an Oracle database the optimizer will *not* use an index defined on that column, so instead you get full table scans. The same thing happens if columns or values in the WHERE clause are of different datatypes, since Oracle will need to implicitly apply a function to one of the columns to convert it. --
Vincent Ventrone | The MITRE Corp. DBA, Dept. R101 | M/S C020 vav_at_mitre.org | 202 Burlington Rd. (781) 271-7048 | Bedford, MA 01730Received on Tue Oct 19 1999 - 12:55:54 CDT