Java Development Tools

Question on query hints. I am doing queries of very large tables, using only indexes in the view criteria. Unfortunately in some cases, 'tkprof' and SQL_TRACE is showing that the optimizer is still choosing full table scans, resulting in extremely poor performance.

I am able to correct this behavior by including a FIRST_ROWS query optimizer hint on the view object. This causes an index scan, with performance as expected. I can verify this by issueing the query directly from SQLplus.

The problem arises with the query constructed from BC4J. It is wrapping the query with a 'SELECT /*+ FIRST_ROWS */ count(1) FROM (SELECT ...) ESTCOUNT', but not including the hint on the inner select. The inner select is executed with a full table scan.

How do I have BC4J include this? Can I turn off getEstimatedRowCount()?

Below is a snippet of both traces (column names changed):
-------------------------------------
********************************************************************************

SELECT /*+ FIRST_ROWS */
Accounts.column1, Accounts.column2, Accounts.column3
FROM
ACCOUNTS Accounts WHERE (column4 IS NULL) AND ( (
(Accounts.column1 LIKE 'this%') ) )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 22 0.01 0.01 1 56 0 22
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 26 0.01 0.01 1 56 0 22

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 55 (TEST)

Rows Row Source Operation
------- ---------------------------------------------------
11 TABLE ACCESS BY INDEX ROWID ACCOUNTS
15 INDEX RANGE SCAN (object id 176490)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: HINT: FIRST_ROWS
11 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'ACCOUNTS'
15 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ACC_IDX'
(UNIQUE)

********************************************************************************
********************************************************************************

SELECT /*+ FIRST_ROWS */ count(1)
FROM
(SELECT Accounts.column1, Accounts.column2, Accounts.column3
FROM
ACCOUNTS Accounts WHERE (column4 IS NULL) AND ( (
(Accounts.column1 LIKE 'this%') ) )) ESTCOUNT


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 2.38 27.87 16685 23594 5 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2.38 27.87 16685 23594 5 1

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 55 (CDS)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
538 TABLE ACCESS FULL ACCOUNTS


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: HINT: FIRST_ROWS
1 SORT (AGGREGATE)
538 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'ACCOUNTS'

********************************************************************************
-------------------------------------