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

Home -> Community -> Usenet -> c.d.o.server -> Re: selects on views are slow while on tables fast

Re: selects on views are slow while on tables fast

From: Vincent Ventrone <vav_at_mitre.org>
Date: Tue, 19 Oct 1999 13:55:54 -0400
Message-ID: <380CB0AA.F0310E3B@mitre.org>

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 01730
Received on Tue Oct 19 1999 - 12:55:54 CDT

Original text of this message

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