Which query is better inner join or in???

[gandolf989]

I am currently working on a java application running against a 9.2.0.7 database running on windows 2000 with 2 gigs of ram. The java application dynamically creates queries based on a collection of stuff the a user has combined together. Currently the queries look something like this.

SQL> SELECT COUNT(*) FROM(
  2  SELECT detail.pkey    AS keyval,
  3         detail.p_date  AS grp0
  4    FROM detail
  5   WHERE TO_CHAR(detail.pkey) IN
  6         ( SELECT detail_pkey
  7             FROM agg_tbl
  8            WHERE agg_id = '0b080e0000010876d429ad0a1428098500' ));

The TO_CHAR function on the primary key of the detail table is completely unnecessary, but since the Java programmer would not remove it I created a function based index on that column for TO_CHAR, which helped a little.

Also the number of rows in agg_tbl will grow and shrink rapidly while the application is running. Someone might drag an object onto a chart or destroy a chart and cause the agg_tbl to grow or shrink by anywhere from 100 to 100,000 rows.

Most of the time the application does a full table scan on the detail table, which causes the application to slow down. I want to rewrite the query to the following.

SQL> SELECT COUNT(*) FROM(
  2  SELECT detail.pkey    AS keyval,
  3         detail.p_date  AS grp0
  4    FROM ( SELECT detail_pkey
  5             FROM agg_tbl
  6            WHERE comp_id = '0b080e0000010876d429ad0a1428098500' ) agg_tbl
  7   INNER JOIN detail
  8      ON detail.pkey = agg_tbl.detail_pkey);

I want to remove the TO_CHAR, that should have never been there, move the agg_tbl to the front of the from list because it controls what data gets returned and use an inner join instead of the in clause, so that it would be a correlated query rather than a non-correlated query. But the results seem rather similar. In SQL Plus it is taking .08 seconds. But there are times where it takes 10 - 15 seconds.

When I run the queries in SQL Plus they run fine, but when they run through the Java app they are painfully slow. I included a sql trace to show the difference. The performance of the app can vary greatly depending on how stale the stats are.

Does anyone have any suggestion as to how I should structure the queries so that the response time will be consistantly good?

Thanks.