Hi,

Thanks in advance for taking the time to read this long and cumbersome post.

Requirements for an application are as follows:

Users will input search criteria when searching for a customer that could include all or none of the following:
1. Base customer information
2. Customer address information
3. Customer contact information

Addresses and contact have a many-to-one relationship with customers. So there could be multiple contacts and address with the same customer ID. When the user's input this search criteria, the query needs to hit ALL of the address and contact records for a customer, BUT only return 1 customer record per customer match, and also page the records into a web browser. Currently in the app. This is accomplished by creating a customer_search view which joins to all of the relevant information (customer, address, contact) into one place. Which of course returns duplicate customerID rows due to the relationship of the tables. The duplicates are then filtered out with an IN clause with a MIN and a GROUP BY. The sql looks something like below, please keep in mind that do to the architecture of the application the results HAVE to be pre-paged.

SELECT *
FROM customer_search c
WHERE c.customerid || c.addressid || c.contactid IN
(SELECT min(c.customerid || c.addressid || c.contactid)
FROM customer_search c2
WHERE UPPER(c2.Street) LIKE :Street AND
UPPER(c2.City) LIKE :City
-- blah blah blah all filter criteria goes here
GROUP BY c2.customerid)
ORDER BY UPPER(c.customercode) ASC, UPPER(c.customername) ASC)
WHERE rownum >= :startRow AND rownum <= :endRow

The problem with this is that, in production, when we are dealing with 300000 customers or so, this query takes FOREVER because of oracle's apparent inability to nest queries effeciently.

Any optimization ideas? THANKS AGAIN.