I'm running the following query on our corporate database, it's extremely slow since the database is constantly under very high load. It takes several hours to run:
LOCKING ROW FOR ACCESS SELECT
When bit2.Sales_Quantity IS NULL THEN 1
ELSE bit2.Sales_Quantity END) as Picked,
bpr.Base_Product_Number = bit2.Base_Product_Number
INNER JOIN VWI0CAL_SMALL_CALENDAR cal
ON bit2.Transaction_Date = cal.Calendar_Date
FULL OUTER JOIN
PZ.sub_group_id = bpr.Product_Sub_Group_Code
Year_Week_Number = '201121'
bit2.Sales_Value > 0
Query looks ok. Few of the basic performace checks can be:
1.Stats should be up to data for joining columns and WHERE clause columns.
2.Joining column should share same data type.
3.Joining columns shouldn't be heavily skewed.
4.Check for the possiblity of adding PPI on column - Year_Week_Number
1. If you are only concerned about the sold products and why a FULL OUTER JOIN to the Lokup table. Just to get the time Zones you could probably use a derived table off the lookup table with just two attributes (Product_Sub_group_Code and Picking_Zone_Id)
Then you can use a inner join to the Sales table.
2. As you said that the Sales table has billions of records and you are just using 2-3 columns in the select and couple in the joins. I would suggest that you create a derived table off the sales table with just these 5-6 columns u need and then join it to the other tables.
Yes, I agree with Manik. If you are doing a join on Huge table, it is better to write a subquery fetching only the required columns on unique combination. Or creating a derived table from it. This will definetly reduce the cost of the query and increase the performance.
Also collecting stats on the tables will increase the performance.
The Full Outer Join is probably removed by the optimizer and the SUM might be put in a DerivedTable, but without Explain and some info about PI/partitioning it's hard to tell.