Re: FOR ALL COLUMNS -- why??

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 18 Sep 2004 06:08:51 +0000 (UTC)
Message-ID: <cigjdj$c6l$1@hercules.btinternet.com>

It isn't just the access method that matters, the number of rows acquired is also important because it may affect the choice of access path into the next table.

If the optimizer knows that it gets just 20 rows from "whatever" because one of the predicates was "gender = 'F'" then it may decide a nested loop into the next table is good, if it know that there will be 380 rows because the predicate was "gender = 'M'", it may decide that a hash join is a better option.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 2nd





"Domenic" <domenicg_at_hotmail.com> wrote in message
news:c7e08a19.0409171851.63f47d5a_at_posting.google.com...


> Now that I'm looking at dbms_stats, what is the benefit of calculating

> histograms on columns that aren't indexed?  Skew or no skew -- there

> is only one path.

>

> So if I have a "gender" column and 95% are males and 5% are females,

> if it was indexed then this makes sense:

>

> SELECT * FROM whatever WHERE gender = 'F' --> index scan

> SELECT * FROM whatever WHERE gender = 'M' --> full table scan

>

> But if there's no index, what's the point?

>

> Does anyone know of an example?

>

> Domenic.

Received on Sat Sep 18 2004 - 01:08:51 CDT