Query tuning

From: <rajesh_at_solutionsoftware.com>
Date: 7 Apr 2003 22:32:39 -0700
Message-ID: <aa8abed4.0304072132.65cddb37@posting.google.com>

Hi,
Here is a query that I am executing in my application and it takes around 10 minutes to execute. Absolutely no idea how to proceed with fine tuning.
The query is:

---

select

        distinct Agent.AgentId, Policy.appnumber, Agent.FullName, Customer.FullName,

	Address.Address1 ||', '|| Address2 Address, ContactNo.ContactNo, 
	Policy.Appdate, MT1.Description Status, GetPolicyStatusDate(
policy.policyid, 0, 'Issued') EFFECTIVEDATE,
	MT2.Description PaymentMode, Policy.ModalPremium,

policy.annualpremium
From

        Agent, Customer, Policy, Address, ContactNo, PolicyCustomer, AgentPolicy, MToption MT1, MTOption MT2
Where

	Agent.AgentId = AgentPolicy.AgentID 
	and Policy.PolicyId = Agentpolicy.PolicyId 
	and decode(AgentPolicy.IssplitAgent, Null, 0,
AgentPolicy.IssplitAgent) = 0
	and Policy.StatusId = MT1.optionID and Policy.PaymentModeId =
MT2.OptionID
	and Policycustomer.policyid  = Policy.PolicyID 
	and Policycustomer.IsPrimary  = 1 
	and PolicyCustomer.CustomerID = Customer.CustomerID  
	and Customer.CustomerID = Address.PersonID (+) 
	and Address.IsPrimary (+) = 1 and Address.Forwhom (+) = 4 
	and Customer.CustomerID = ContactNo.PersonID (+) and
ContactNo.IsPrimary (+) = 1
	and ContactNo.Forwhom (+) = 4  AND  (Upper(Agent.LastName) like

'ADA%' or Upper(Agent.FirstName) like 'ADA%') order by

        Agent.FullName
Records in each of the table are:

---

Agent - Contains around 5000 records
Customer - Contains around 100000 records Policy - Contains around 100000 records Address - Contains around 100000 records ContactNo - Contains around 100000 records PolicyCustomer - Contains around 100000 records AgentPolicy - Contains around 100000 records MToption MT1 - Contains around 400 records MTOption MT2 - Contains around 400 records

At this juncture, I have absolutely no idea as to what is the direction in which I should proceed if I have to fine tune the query. Please help...
Regards,
Rajesh Received on Tue Apr 08 2003 - 00:32:39 CDT