Solved

Dataware house query tuning

Posted on 2016-11-17
9
114 Views
Last Modified: 2016-11-19
Hi Team,

Need help on the below query . I attached the excel sheet which contains the data and the expected output.  For the requirement in the excel sheet , I have written the below query , I need help on other best alternatives of writing the below query , since the fact  table contains millions of rows.


Requirement for the data in the excel sheet:
Write a parameterized SQL query to find which store made highest sales of ‘Iphone-6’ till end of Oct-2016 as per the given format in ‘Expected Result’.FACT_SALES’ has millions of records, hence expecting the SQL query is fine-tuned and shouldn’t be give any performance issues.

below is the query i have written for the above requirement, any help in this regard is really appreciated.

With sales_data as (
Select 
fs.store_id, fs.prod_id , fs.zone_id,sum(qty_sold) qty_sold,
dense_rank() over (order by max(qty_sold) desc ) rk
from fact_sales fs  INNER JOIN DIM_PROD dp on (dp.prod_id=fs.prod_id)
where to_Char(fs.sale_date,'MON-YYYY') <=  to_char(to_date('&1','YYYYMMDD'),'MON-YYYY')
and dp.prod_name='&2'
group by fs.store_id, fs.prod_id , fs.zone_id
)
Select dp.prod_name, ds.store_name , dz.zone_code , sd.qty_sold * dp.Unit_Price_Rs HIGHEST_SALE ,(dp.Unit_Price_Rs * (sd.qty_sold * dp.Unit_Price_Rs)) Total_Unit_Price_Rs
from sales_data sd
JOIN DIM_STORES ds on (ds.store_id=sd.store_id and ds.zone_id=sd.zone_id)
JOIN DIM_ZONE dz on (dz.zone_id=ds.zone_id)
JOIN DIM_PROD dp on (dp.prod_id=sd.prod_id)
where rk=1;

Open in new window

0
Comment
Question by:sam_2012
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41892123
where to_Char(fs.sale_date,'MON-YYYY') <=  to_char(to_date('&1','YYYYMMDD'),'MON-YYYY')

NEVER use  function on data to suit the where clause; instead adjust the where clause to suit the data

ALSO, when dealing with dates don't compare them to char values, compare dates to dates
0
 

Author Comment

by:sam_2012
ID: 41892402
Hi Portlet Paul,

Can you help me on the   below condition ,  how to change it or better way of writing the condition below

where to_Char(fs.sale_date,'MON-YYYY') <=  to_char(to_date('&1','YYYYMMDD'),'MON-YYYY')
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41892410
if you give me a sample of the data, and an example of the date parameter, and the expected result I can give you a more considered answer.

perhaps in the meantime try this:
                  where fs.sale_date <  add_months(TRUNC(to_date('&1','YYYYMMDD').'MONTH'),1)

e.g. if the parameter is 2016-11-17, the data selected would be less than 2016-12-01

to_date('20161117','YYYYMMDD') = 2016-11-17
TRUNC(    2016-11-17   , 'MONTH') = 2016-11-01
add_months(    2016-11-01   ,1) = 2016-12-01
so:

where fs.sale_date < 2016-12-01
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sam_2012
ID: 41892413
The data parameter wouldbe entered as OCT-2016 i.e MON-YYYY in this format
IPhone_Sales.xlsx
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41892434
just change the text pattern of to_date to match your parameter

If your parameter is always in that format:

WHERE fs.sale_date < add_months(to_date('OCT-2016', 'MON-YYYY'),1)

which INCLUDES October 2016 in the data to search

===
nb: I do NOT recommend using language dependent month names but that is your decisions
, and if you do change, just change to_date to suit
0
 

Author Comment

by:sam_2012
ID: 41892445
Will this condition have better performance on the fact table with million rows.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41892476
I cannot test any assertion I make here - only you can do that.

I also can only make assumptions about the indexing in your data, and this is critical to the execution efficiency.

So I cannot KNOW it will be quicker, but it SHOULD be quicker.

You should also learn to use explain plans. These are the best tool to use to investigate what indexes are being used (or not) and to identify performance problems.

So a long answer. Yes it will be better, but maybe there are still problems.
0
 

Author Closing Comment

by:sam_2012
ID: 41894126
awesome. thank you
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41894479
Great. Pleased it helped.

Keep this in mind: avoid using functions on data to suit a where clause condition
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
A great marketing strategy is diverse.  Read about the not so popular, yet effective, marketing tactics you can start using today!
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

695 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