Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Query tuning

Posted on 2014-02-23
4
Medium Priority
?
222 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 1200 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 1200 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month12 days, 7 hours left to enroll

564 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