Solved

Query tuning

Posted on 2014-02-23
4
215 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 143

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 143

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

828 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