Re: OCP - Performance Tuning

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:b15h9a$lqq$1$8300dec7_at_news.demon.co.uk...
>
>
> I've just been sent the following question from an OCP test,
> and I can't decide whether this is a subtle trap, or a naive
> interpretation of the manuals on the part of the examiner.
>
> I haven't been told whether this is a 'single answer only'
> question, or 'perm any N'. But I assume from the grammar
> that precisely one answer is supposed to be correct.

That would be my reading.

>
> If you were taking the exam, which one would you put ?
>
> (NB - I didn't say "which one is correct?").
>
> 4. The cost-based optimizer can choose between a nested
> loops join and a sort merge join operation. All tables are
> analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS.
> Which execution plan will be the result?
>
> a. The sort-merge join.
> b. The nested loops join.
> c. This depends on some sort parameter values.
> d. This depends on the number of rows in each table.

Hmmm. I suspect I'd go for C) on the grounds that on the information given you can't (AFAIK) definitely state A) or B), and that for D) one might argue that it depends (sort of) upon the number of *blocks* accessed rather than the number of rows.

Or possibly E) "where's my hash join then thats what I want to know?"

Presumably the naive manual reading that you refer to is the bit that says in effect increasing sort_area_size makes sorts look cheaper.

--
Niall Litchfield
Oracle DBA
Audit Commission UK