Re: Oracle 8i and poor query performance in some queries

Uzytkownik "Heikki Siltala" <heikki.siltala_at_stakes.fi> napisal w wiadomosci news:26e49b9f.0310212331.b261d7b_at_posting.google.com...
> "Noel" <tbal_at_go2.pl> wrote in message

news:<bn3dah$jso$1_at_inews.gazeta.pl>...
>
> > Does:
> > select id from a1
> > minus
> > select id from a2;
> > takes so much time, eigher??
>
> Tried this one:
>
> select count(*) from (
> select id from a1
> minus
> select id from a2
> );
>
> It executes in 0,08 seconds! And the row counts in plan seem more
> realistic now. The total cost is 560, compared to earlier 109, but the
> query executes 25212,5 times faster than the earlier one. Quite a
> peformance gain!
>
> Now the problem seems to be an optimizer problem.

No, no optimizer.
Your sql statetment put Oracle in big trouble. select count(*)
 from a1 where id not in
(select id from a2);

You ask Oracle to count all rows in table a1 where id from table a1 don't exists in table a2.
Neigher id of a1 nor id of a2 are indexed. You didn't help Oracle to do a job, you didn't give any hint to do this, exept analizing table statistic.

Example:
Well, im from Poland.
You asking me how 'hate' is in polish and you want me to find it in polish-english dictionary.
So i must search whole entries to find 'nienawisc'. But i don't know if 'hate' is correct english word.
And after few weeks im able to reply. With a lot of luck i could find it faster.
But if 'hate' isn't correct, i spend half of year and finally tell you 'There is not such a word'.
But if you would tell me to search in english-polish dictionary i would reply after a view seconds :-)

--

That's why that query with 'minus' run fast in that case.
You told Oracle to compare two sets, and Oracle smart enough sorted results
after executing both subqueries.

--
Noel