Search optimisation over oracle DB

We have set of customer tables like customer, customer address, customer-communication, customer profession (oracle 11g).

Front end will provide few details like 'email-id', 'f-name', 'city', 'state' to search in the DB and return matching customer details.

all tables contains more than 10 coulmns and when the matching emailid, f-name, city, state are found we need to return the details for that customer.

the return details are spread over all 4 tables and we need to join all 4 tables to get the details to return. (ex: we need to retun cust-id, appt num, city, state, phone numbers, profession deatisl)

comm-metchod - can have like email, phone, fax and

comm-method-values - will contain have thier values

also customer can have multiple addresses(with different address-id) and professions!

Issue here is the data size and join is taking more time. we are considering to have materialsed views but still the size of view will grow incredibly when a customer can multiple addresses, email addressed and professions.

looking for suggestion to solve the search complexity.

table are like -

Tab-name:CUSTOMER

Cust_id

F-Name

L-Name

Pref-Name

prefix

suffix

gender

blah1

blah2

blah3

Tab-name:CUST_ADDR

Cust-id

Cust-addr-id

street

appt num

City

State

Country

blah4

blah5

blah6

Tab-name:CUST_COMMUNICATION

cust-id

Cust-addr-id

comm-method

comm-method-value

blah7

blah8

Tab-name:CUST_PROFESSION

cust-id

profession-code

profession description

Comments-txt

blah9

blah10

You need to plan out what queries will be permitted; that is what are the minimum required fields and what indexes are there to support them.

I can't imagine a search on just city or state would provide a useful result. You'd have too many matches.

Email-Id will be very selective, so you use one query if they supply an email id. The query will drive from the small number of matched customer_communication records, up to the parent customer and then down to the other child records.

First-Name plus Last-Name will be acceptably selective, so use a second query if they supply these but NOT an email_id (which would be using the first query) First-Name plus city plus state may be acceptable, so a third query for that Last-Name plus city plus state may be acceptable, so a fourth query for that

Most of the indexes will probably be cached in memory. You'll probably get down to a handful of physical IOs per query. If you need to get it lower than that, then look into materialized views or table clusters (ie one cluster on CUST_ID with all the tables stored as part of that cluster).