CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user1758616
user1758616

Reputation: 87

Performance impact due to Join

I have two tables PRODUCT and ACCOUNT.

PRODUCT table columns 

product_id   (PK)                      
subscription_id

ACCOUNT table columns

account_nbr
subscription_id  
  (account_nbr and subscription_id are primary key columns in account table)
... Other columns

I have to find account_nbr and subscription_id for a product_id.

I get product_id as input. Using it I can get the subscription_id from PRODUCT table and using subscription_id I can get the account_nbr value from ACCOUNT table.

Instead of getting the info in two queries, can it be done in one query?

Something like the below:

select distinct a.acct_nbr,p.subscription_id 
from ACCOUNT  a,PRODUCT p
where v.product_id = ' val 1' and 
v.subscription_id  = p.subscription_id

Will the performance of the above query be low compared to two separate queries?

Upvotes: 0

Views: 578

Answers (1)

Ben
Ben

Reputation: 52863

I have to find account_nbr and subscription_id for a product_id.

So, you're correct in your approach you need to JOIN the two result-sets together:

select p.account_nbr, p.subscription_id
  from account a
  join product p
    on a.subscription_id = p.subscription_id
 where p.product_id = :something

Some points:

  1. You have an alias v in your query; I don't know where this came from.
  2. Learn to use the ANSI join syntax; if you make a mistake it's a lot more obvious.
  3. You're selecting a.acct_nbr, which doesn't exist as-is.
  4. There's no need for a DISTINCT. ACCOUNT_NBR and PRODUCT_ID are the primary key of ACCOUNT.

Will the performance of the above query be low compared to two separate queries?

Probably not. If your query is correct and your tables are correctly indexed it's highly unlikely that whatever you've coded could beat the SQL engine. The database is designed to select data quickly.

Upvotes: 3

Related Questions