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?