Re: "select *" MUCH quicker than "select <field> from...": explanation ?

Quick guess would be the first query you performed (SELECT FHC_ID FROM <VIEW>) had to read from disk (physical IO). While your second query (SELECT * FROM <VIEW>) had it easy reading just from the buffer cache. It probably didn't have anything to do with your SELECT list at all. You could have found the opposite results if you executed these statements in reverse order.

These are all of course assuming that nothing else has changed in between queries like your plan. A presence of a concatenated index in (OIU_ID, FHC_ID) might invoke an FFS.

Start using at least SET AUTOTRACE ON for now. And all the other trace tools. These would tell you what's happening under the hood.

spendius_at_muchomail.com (Spendius) wrote in message news:<aba30b75.0407061025.441cc6f4_at_posting.google.com>...
> hi,
> I've noticed, as I was trying to tune a query that performs
> a SELECT on a single field in a view, that if I replace this
> call to a single field by a '*' or the list of other/all fields
> existing in this view I get a quicker answer in SQL*Plus. The
> statement is very simple and looks like
> > SELECT fhc_id
> > FROM <view>
> > WHERE oiu_id = 32
> If I replace 'fhc_id' by several or all columns of the views the
> return is immediate (it takes about a second when only the 'fhc_id'
> column is selected). On the other hand the definition of the view
> is rather complex, and it behaves exactly the same if I perform
> the query directly as it is defined in the view.
> Any idea/enlightenment about what makes Oracle respond really
> quicker when you select several columns rather than one only ?
> Thanks.
> Spendius
Received on Tue Jul 06 2004 - 21:00:13 CDT