Hi all,
I am doing some experiments with analytic functions. I use Oracle 11.2 XE and the demo HR schema. I'm trying to code a query that gives me the running total:
-- analytic version
SELECT employee_id, last_name, first_name, salary,
sum(salary)
OVER (ORDER BY last_name, first_name) running_total
FROM employees
order by last_name, first_name;
I wanted to compare it with a query using subqueries:
WITH emp_ordered AS (
SELECT ROWNUM AS ct, a.
FROM (
SELECT e.employee_id, e.last_name, e.first_name, e.salary FROM employees e ORDER BY 2,3
) a
)
SELECT eo.employee_id, eo.last_name, eo.first_name, eo.salary,
(SELECT SUM(salary) FROM emp_ordered WHERE ct <= eo.ct) AS running_total
FROM emp_ordered AS eo
ORDER BY 5;
When I put a hint to avoid materializing the factored subquery (hoping in a little boost in performance):
WITH emp_ordered AS (
SELECT /*+ inline */ ROWNUM AS ct, a.
FROM (
SELECT e.employee_id, e.last_name, e.first_name, e.salary FROM employees e ORDER BY 2,3
) a
)
SELECT eo.employee_id, eo.last_name, eo.first_name, eo.salary,
(SELECT SUM(salary) FROM emp_ordered WHERE ct <= eo.ct) AS running_total
FROM emp_ordered AS eo
ORDER BY 5;
I obtained a different cost (less than without the hint), but I recently realized that I cannot count on it to say it performs better.
BUT...
using the hint I got a TOTALLY DIFFERENT result set! Only the 1st row is correct and all the other106 rows have the same running_total value (the max one).
How could it be possible? I always thought that hints were "safe": using them wrongly I could wait an hour more, but not receive a different result!
Thank you again for the help you would give to me,
regards,
Filippo