Re: Complex outer join query

Since the ACCOUNT table is outer-joined to the PAT table, you have to use (+) for the criteria
AND UPPER(A.SHORT_NAME) LIKE '1-7%'
like this:
AND UPPER(A.SHORT_NAME) LIKE '1-7%' (+) HTH,
Ranga Chakravarthi

"Nisse" <kvigerum_at_hotmail.com> wrote in message news:ff428a25.0201030252.78164e74_at_posting.google.com...
> 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,
> CA.ACCOUNT_SHARE,
> CA.CONTACT_PERSON,
> P.PRODUCT_ID,
> P.SHORT_NAME PRODUCT_SHORT_NAME,
> PT.SHORT_NAME PART_TYPE_SHORT_NAME
> FROM CLIENT C ,
> ACCOUNT A, CLIENT_ACCOUNT CA,
> PRODUCT P, PART_TYPE PT, PRODUCT_ACC_TYPE_REL PAT
> WHERE C.CLIENT_ID = CA.CLIENT_ID(+)
> AND CA.ACCOUNT_ID = A.ACCOUNT_ID(+)
> AND PT.PART_TYPE_ID(+) = CA.PART_TYPE_ID
> AND A.PRODUCT_ACC_TYPE_REL_ID = PAT.PRODUCT_ACC_TYPE_REL_ID(+)
> AND P.PRODUCT_ID(+) = PAT.PRODUCT_ID
> AND UPPER(C.SURNAME_OR_COMPANYNAME) LIKE '%'
> AND ROWNUM <= 250
>
> The query didn&#8217;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&#8217;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 Sun Jan 06 2002 - 22:23:34 CET