Trying to avoid a double full scan with aggregates in query

Question

I have a statistics query I'm trying to run against the below table:

CREATE TABLE "*****"."RUN_D" 
(   
"WHSE_CODE" CHAR(12 BYTE), 
"RUN_NUM" NUMBER(*,0), 
"SHEET_NUM" NUMBER(*,0), 
"SHEET_SEQ" NUMBER(*,0), 
"COUNT_DATE" DATE, 
"COUNTED" CHAR(1 BYTE), 
"ITEM_NUM" CHAR(30 BYTE), 
"BIN_CODE" CHAR(10 BYTE), 
"STOCK_UOM" CHAR(10 BYTE), 
"ON_HAND" NUMBER(12,3), 
"SNAP_COST" NUMBER(15,5), 
"COUNTED_OH" NUMBER(12,3), 
"COUNTER_NAME" CHAR(20 BYTE), 
"USER_CODE" CHAR(30 BYTE), 
"COUNT_VARIANCE" CHAR(1 BYTE), 
"ORG_CODE" CHAR(2 BYTE), 
"DIV_CODE" CHAR(8 BYTE), 
"LOT_CODE" CHAR(20 BYTE), 
"UPDATED" CHAR(1 BYTE)
)

I have the following query which works correctly but EXPLAIN PLAN is telling me that it requires a double table scan, which I'm trying to avoid.

Query:

SELECT 
  tot.run_num, tot.whse_code, TRUNC(tot.count_date) AS Count_Date,
  CAST((vary.Variance_Count / COUNT(tot.item_num) * 100) AS DECIMAL(10,2)) AS Percentage_Count_Variance,  
  CAST((vary.Variance_Cost / SUM(tot.snap_cost * tot.on_hand) * 100) AS DECIMAL(10,2)) AS Variance_Percentage,  
  CAST(vary.Variance_Cost AS DECIMAL(10,2)) AS Total_Value_Variance
FROM run_d tot
JOIN 
(SELECT run_num, COUNT(item_num) AS Variance_Count, SUM(snap_cost * (counted_oh - on_hand)) AS Variance_Cost
 FROM run_d 
 WHERE counted_oh - on_hand != 0
 GROUP BY run_num) vary ON tot.run_num = vary.run_num 
WHERE TRUNC(tot.count_date) BETWEEN TO_DATE('07/01/2014','MM/DD/YYYY') AND TO_DATE('07/21/2014','MM/DD/YYYY')
GROUP BY tot.run_num, tot.whse_code, vary.Variance_Count, vary.Variance_Cost,   TRUNC(tot.count_date)
ORDER BY tot.whse_code, tot.run_num

Is there a more efficient way that I can write this query, most specifically to avoid the double table scan?

Answer 1

No, I cannot see a better way. The group-by constraints are different for the selects, so there is no way to merge them in to one.

The remainder of your code looks good, except I am concerned about your % calculations.... are they accurate?

(vary.Variance_Count / COUNT(tot.item_num) * 100) AS DECIMAL(10,2)

That looks like it will be integer arithmetic all the way through, I would instead write it as:

(100.0 * vary.Variance_Count / COUNT(tot.item_num)) AS DECIMAL(10,2)