Re: Explain Plan For Query

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Sat, 13 Apr 2002 01:56:29 +1000
Message-ID: <a9706b$3cb$1@lust.ihug.co.nz>

It doesn't necessarily "favour" full scans. It just means the plan is optimised for returning the complete resultset, rather than for getting the quickest possible return of the first part of a resultset (first_rows). All that means is that the optimizer will spend longer choosing the perfect plan.

ALL_ROWS is recommended for data warehouses, because slight imperfections in the plan can, in those environments, result in extra *hours* of execution time. Data warehouses tend to do a lot of table scans. Therefore, I suppose, the erroneous syllogism that ALL_ROWS=>full scans.

The difference is in immediate response time, or the "feel" of how responsive an application is. But there's certainly no intrinsic favouring of scans over index access with the optimizer mode choice.

Regards
HJR

--
-----------------------------------------------
Resources for Oracle : http://www.hjrdba.com
===============================

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
news:3cb69f2b$0$232$ed9e5944_at_reading.news.pipex.net...


> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message

> news:3vgbbucc07vml3rnn5fsqejt5am8eds5nl_at_4ax.com...

> > The default optimizer goal for pl/sql is ALL_ROWS

>

> I knew that.

>

> > ALL_ROWS

> > retrieves the complete resultset in SGA

> > favors full tablescans.

>

> But I didn't know that. Is this difference documented/demonstrated

> somewhere?

>

>

> --

> Niall Litchfield

> Oracle DBA

> Audit Commission UK

> *****************************************

> Please include version and platform

> and SQL where applicable

> It makes life easier and increases the

> likelihood of a good answer

>

> ******************************************

>

>

Received on Fri Apr 12 2002 - 10:56:29 CDT