Trying to avoid a double full scan with aggregates in query

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?

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)

• I swapped my % calc with yours and got the same values. Why do you think they're incorrect? – Michael McGriff Jul 29 '14 at 18:56
• I have searched for references for my concerns, but can't find a decent oracle-specific one. My concern is that all your % values will be exact decimals (no fractional part). If your current % values are all exact integers (e.g. 5.00 instead of 5.12) then I would be concerned. – rolfl Jul 29 '14 at 22:09
• all the % values come out with the proper precision (5.12 in your example) which I've verified by doing the calculations manually. Thank you for checking over my query. – Michael McGriff Jul 30 '14 at 12:28