Solved

Query tuning

Posted on 2014-02-23
4
212 Views
Last Modified: 2014-02-26
Hi ,

I have the below requirement , I have 5 tables called as customer , persondet , userdet .

Customer can be account holder , Sales Person , internal customer.

persondet : contains the peronal det of the customer

appuser : contains the user details mapped for each customer

userdet : contains the user details of customer who is authorised to view the sales.

salesdet : contains the comments about the sales made to the customer.

Structure  of the tabes is a sbelow:

Customer:
Customer_id ,
Is_internal,
customer_name

salesdet:
comment_id ,
comments,
account_holder_id,(accountholder customer id from customer table)
sales_person_id,(salesperson customer id from customer table)
internalcust_id , (internal customer id from customer table)
accountholder_contact_id, (account holder contact id from persondet table)
salesperson_contact_id, (salesperson contact id from persondet table)
internalcust_contact_id (internal customer contact id from persondet table)

userdet:
user_id (user id (appuser_id frin appuser table)
fname,
lname

persondet:
contact_id,
fname,
lname,

appuser :
customer_id,
appuser_id

appuser.appuser_id is the foreign key in userdet table.

I need to fetch the details of each sales comment in salesdet table for each customer type i.e accountholder, salesperson, internal.  If it is internal , I need get the user name from userdet table else from persondet table .

the salesdet table contains the  account_holder_id,sales_person_id,internalcust_id,accountholder_contact_id,
salesperson_contact_id,
internalcust_contact_id
, these column contain the values from customer and persondet. Below is the query that i have written to get the results , I have been asked not to use multiple outer joins .

Query:
SELECT com.comment,
    cust_internal.customer_name,
      case when cust_internal.is_internal='T' Then
        (Select ud.fname from appuser au , userdet ud where au.customer_id=cust_internal.customer_id
        and au.user_id=ud.user_id)
    else
        con_internal.fname
      end  internalfirstname,
    case when cust_internal.is_internal='T' Then
        (Select ud.lname from appuser au , userdet ud where au.customer_id=cust_internal.customer_id
        and au.user_id=ud.user_id)
    else
        con_internal.lname
      end  internallastname,
  cust_salesperson.customer_name ,
    con_salesperson.first_name ,
    con_salesperson.last_name,
    cust_accountholder.customer_name ,
    con_accountholder.first_name ,
    con_accountholder.last_name    
  FROM salesdet com
  lEFT OUTER JOIN customer cust_internal
  ON(com.account_holder_id = cust_internal.Customer_id)
  lEFT OUTER JOIN customer cust_salesperson
  ON(com.sales_person_id = cust_salesperson.Customer_id)
  lEFT OUTER JOIN customer cust_accountholder
  ON(com.account_holder_id = cust_accountholder.Customer_id)
  LEFT OUTER JOIN salesdet con_internal
  ON(com.internalcust_contact_id = con_internal.contact_id)
  LEFT OUTER JOIN salesdet con_salesperson
  ON(com.salesperson_contact_id = con_salesperson.contact_id)
  LEFT OUTER JOIN salesdet  con_accountholder
  ON(com.accountholder_contact_id = con_accountholder.contact_id)
 
  I need to re - modify the logic for getting this data, any help in this query is really appreciated
0
Comment
Question by:sam_2012
  • 2
  • 2
4 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 39880656
>, I have been asked not to use multiple outer joins
why?

you can use INNER JOIN only if you can be sure that in the related table there will be matching record(s) always  ... if that is not the case (in a optional relationship), the outer join is requires.

so, who is asking you to avoid the outer joins?
0
 

Author Comment

by:sam_2012
ID: 39881454
our technical team has requested. I need to use this query for an view definition. Is it possible to achieve the same using inline queries?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 39881831
>our technical team has requested
with some specific reason?
knowing that  LEFT JOIN often cannot be avoided?

anyhow, please check for each "LEFT JOIN", if the relation between the tables is "mandatory" or "optional".

if it's mandatory, you can safely change from LEFT JOIN to INNER JOIN, for example:
FROM salesdet com
  LEFT OUTER JOIN customer cust_internal
ON(com.account_holder_id = cust_internal.Customer_id) 

Open in new window

I think that every sales shall have a account holder/customer record, so I would think that for example there, you can safely change to INNER JOIN there. but YOU have to check that, by doing this query:
select com.* FROM salesdet com
  LEFT OUTER JOIN customer cust_internal
ON(com.account_holder_id = cust_internal.Customer_id)
WHERE  cust_internal.Customer_id IS NULL

Open in new window

if that query returns any records, the relation is NOT mandatory, hence you cannot safely change to inner join.

ON(com.account_holder_id = cust_internal.Customer_id)
0
 

Author Closing Comment

by:sam_2012
ID: 39889062
awesome
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now