query plans and clustering

Hi,
We are migrating our 817 system to a 9i and curent are running the two in parallel. 817 is use rule and 9i is cost. A particular query is running significantly slower in the 9i than in the 817. Tables are the same with same indexes. Statistics are current in 9i though they are estimates at 15%
The plans for the query:

9i                     ROWS BYTES   COST
  SORT AGGREGATE		1 	35
    MERGE JOIN			4K	137K	35769
      SORT JOIN			166K	3M	14725
        TABLE ACCESS BY LOCAL INDEX ROWID	BILL_INVOICE_DETAIL
			166K	3M	11988
          INDEX RANGE SCAN	BILL_INVOICE_DETAIL_PK
          			666K	 	3191
      FILTER
        SORT JOIN
          TABLE ACCESS FULL	ADJ	1M	13M	3180

817
SELECT STATEMENT		1 	33 	244
  SORT AGGREGATE		1 	33
    NESTED LOOPS		1 	33 	244
      TABLE ACCESS BY LOCAL INDEX ROWID	BILL_INVOICE_DETAIL
      				138 	2K	161
        INDEX RANGE SCAN	BID_PK
        			138 	 	132
      TABLE ACCESS BY INDEX ROWID	ADJ
      				1M	13M	1
        INDEX RANGE SCAN	ADJ_PK
        			1M	 	3

 I can see that the 9i query does a full table scan on table ADJ and sort for each BILL_INVOICE_DETAIL record. Apparently it is not using the index on ADJ. So I look at the clustering of the 2 indexes on the 2 tables.

 9i

 index                  Analyze date            cluster factor Rows
 BILL_INVOICE_DETAIL_PK	01-SEP-2005 	1	30118585	925811672
 ADJ_PK	                25-AUG-2005 	1	454516	    1129200

 817
 BID_PK	08-AUG-2005 	1	120985008    1126568577.80971
 ADJ_PK	26-AUG-2005 	1	67849	    1143265.66360053

 Indexes BILL_INVOICE_DETAIL_PK and BID_PK are the same except that BILL_INVOICE_DETAIL_PK has a unique constraint  It seems that the 9i query skips using the index because of the high clustering.

 Now I think the clustering factor has to do with the relation of the data blocks to the index blocks. Therefor the 9i query needs to pick up more blocks for a given key block.

 I would think clustering is much like traffic syncing. Whereas a road's lights can be best synced for either North or south you cant have both. The same I would think is with clustering. The data can be ordered to emulate one index over another

 My question is how can I effect the clustering without changing the indexes? Or am i interpreting this incorrectly? Received on Thu Sep 01 2005 - 11:25:09 CDT