You need to post the explain plan and the tkprof. Although just looking at
the SQL I would say the problem is at:
AND (Upper(Agent.LastName) like
'ADA%' or Upper(Agent.FirstName) like 'ADA%')
You are forcing Oracle to scan the entire agent table to do the comparison
(due to the upper function). You could create a function based index on
those columns and analyze the table and it should work a lot faster. (look
up function based indexes in the docs or www.oracle.asktom.com) You don't
give us any idea what the version is, which is important.
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
<rajesh_at_solutionsoftware.com> wrote in message
news:aa8abed4.0304072132.65cddb37_at_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 - 09:29:31 CDT
