Re: Optimization question: Unrolling subquery into IN clause

From: Wolfram Roesler <wr_at_spam.la>
Date: Mon, 19 Oct 2009 06:22:41 +0000 (UTC)
Message-ID: <Xns9CA95539F12C9wrgrpde_at_news.albasani.net>

madhu <madhusreeram_at_gmail.com> wrote in news:80c66c85-6c41-47c5-b9c2- 03bc2058c766_at_j19g2000yqk.googlegroups.com:

> Try adding a hint - /*+ index(tab1 index_name_on_col2) */, so Oracle
> chooses the index on col2.
> So the query would be:
> SELECT /*+ index(tab1 index_name_on_col2) */ several_columns
> ...

I tried that, and was astonished to find out that it made things even worse. The whole query takes 15 seconds with that hint. Oracle chooses to use that index automatically when the query is issued by the application using bind variables, which is why the application is even slower than the ad-hoc SQL query entered in sqlplus.

Thanks for your help
W. Rösler Received on Mon Oct 19 2009 - 01:22:41 CDT