6
\$\begingroup\$

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?

\$\endgroup\$
5
\$\begingroup\$

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)
\$\endgroup\$
  • \$\begingroup\$ I swapped my % calc with yours and got the same values. Why do you think they're incorrect? \$\endgroup\$ – Michael McGriff Jul 29 '14 at 18:56
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – rolfl Jul 29 '14 at 22:09
  • \$\begingroup\$ 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. \$\endgroup\$ – Michael McGriff Jul 30 '14 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.