Complex outer join query

We are running a quite complex query that doesn't behave as we wish. We want to get a list of clients and if the client has an account then we want to see the information about the account and the product that the account is connected to. The reason for using the index hint is that we want to be sure that the result always is returned in the order surname, first_name. We are also using the index hint so that we can use this as a list search that is first get the 250 first results and if the query would return more then we would start the next query from client_id 250

Create unique index I_sur_first on client (surname_or_companyname, first_name, client_id)

select /*+ USE_NL(C, a, CA, P,PT,PAT) INDEX(c i_sur_first ) */

C.CIVIC_REG_NO, 
C.CLIENT_ID, 
C.FIRST_NAME, 
C.SURNAME_OR_COMPANYNAME, 
A.ACCOUNT_ID, 
A.SHORT_NAME ACCOUNT_SHORT_NAME,
A.FRIENDLY_NAME, 

PRODUCT P, PART_TYPE PT, PRODUCT_ACC_TYPE_REL PAT
 WHERE C.CLIENT_ID = CA.CLIENT_ID(+) 
 AND CA.ACCOUNT_ID = A.ACCOUNT_ID(+) 

The query didn’t return the respected result until we added the hint use_nl. Very strange since nested loops should be slower then hash join(?) Anyway this query takes 100 msecs so that is ok. But when we are adding a search criteria from any other table then client for instance
AND UPPER(A.SHORT_NAME) LIKE '1-7%'
then the performance is getting really bad. And it takes 6-7 minutes to get the result. If I change the hint to use_hash in the second query then the result takes 3 secs still slow but it also returns the wrong result. I tried to add the ORDERED hint but it didn’t help.

Does anyone have any suggestion about how to solve this problem? Is the search order incorrect, could we use an other hint or is it just not possible to do this?

Thanks in advance / Nisse Received on Thu Jan 03 2002 - 11:52:18 CET