Home » RDBMS Server » Performance Tuning » Compare current row values with previous one until mismatch (Oracle 10g)
| Compare current row values with previous one until mismatch [message #529579] |
Wed, 02 November 2011 01:09  |
 |
asakchris
Messages: 4
Registered: November 2011
|
Junior Member |
|
|
We have employee salary table which will have salary of an employee on daily basis (only working days). Below is the table structure:
emp_salary
----------
emp_id NUMBER(15) NOT NULL
effective_date DATE NOT NULL
salary NUMBER(15) NOT NULL
Primary key - emp_id, effective_date
This table is yearly partitioned
I have to find out how long the salary is not changed for an employee from given date, and last salary. I am using below query to do this:
WITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary
)
THEN 0
ELSE 1
END
) changed_ind
FROM emp_salary
WHERE emp_id = 12345
AND effective_date <= TO_DATE ('07/10/2011', 'mm/dd/yyyy')
ORDER BY effective_date DESC)
SELECT COUNT (1) not_changed_count, MIN (salary) last_salary
FROM salary_tab
WHERE effective_date > (SELECT MAX (effective_date)
FROM salary_tab
WHERE changed_ind = 1);
The cost of this query is 1677 and it is taking around 60 msec to complete. When I run this query for around 2000 employees in a loop it is taking around 3 minutes to complete.
Is there any way we can optimize this query?
The main bottleneck of this query is in the with clause where I am processing the entire history instead of stopping after first change.
|
|
|
|
|
|
| Re: Compare current row values with previous one until mismatch [message #529609 is a reply to message #529595] |
Wed, 02 November 2011 03:03  |
|
asakchris
Messages: 4
Registered: November 2011
|
Junior Member |
|
|
Below are the create table and insert scripts:
CREATE TABLE emp_salary
(
emp_id NUMBER(15) CONSTRAINT emp_salary_01 NOT NULL,
effective_date DATE CONSTRAINT emp_salary_02 NOT NULL,
salary NUMBER(15)
);
ALTER TABLE emp_salary ADD (
CONSTRAINT emp_salary_pk
PRIMARY KEY
(emp_id, effective_date));
INSERT INTO emp_salary VALUES (1, TO_DATE ('10/31/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (1, TO_DATE ('10/28/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (1, TO_DATE ('10/27/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (1, TO_DATE ('10/26/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (1, TO_DATE ('10/25/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (1, TO_DATE ('10/24/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (1, TO_DATE ('10/21/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (1, TO_DATE ('10/20/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (1, TO_DATE ('10/19/2011', 'mm/dd/yyyy'), 240);
INSERT INTO emp_salary VALUES (1, TO_DATE ('10/18/2011', 'mm/dd/yyyy'), 240);
INSERT INTO emp_salary VALUES (1, TO_DATE ('10/17/2011', 'mm/dd/yyyy'), 230);
INSERT INTO emp_salary VALUES (2, TO_DATE ('10/31/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (2, TO_DATE ('10/28/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (2, TO_DATE ('10/27/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (2, TO_DATE ('10/26/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (2, TO_DATE ('10/25/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (2, TO_DATE ('10/24/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (2, TO_DATE ('10/21/2011', 'mm/dd/yyyy'), 245);
INSERT INTO emp_salary VALUES (2, TO_DATE ('10/20/2011', 'mm/dd/yyyy'), 244);
INSERT INTO emp_salary VALUES (2, TO_DATE ('10/19/2011', 'mm/dd/yyyy'), 242);
INSERT INTO emp_salary VALUES (2, TO_DATE ('10/18/2011', 'mm/dd/yyyy'), 240);
INSERT INTO emp_salary VALUES (2, TO_DATE ('10/17/2011', 'mm/dd/yyyy'), 240);
INSERT INTO emp_salary VALUES (3, TO_DATE ('10/31/2011', 'mm/dd/yyyy'), 250);
INSERT INTO emp_salary VALUES (3, TO_DATE ('10/28/2011', 'mm/dd/yyyy'), 240);
INSERT INTO emp_salary VALUES (3, TO_DATE ('10/27/2011', 'mm/dd/yyyy'), 240);
INSERT INTO emp_salary VALUES (3, TO_DATE ('10/26/2011', 'mm/dd/yyyy'), 240);
INSERT INTO emp_salary VALUES (3, TO_DATE ('10/25/2011', 'mm/dd/yyyy'), 240);
INSERT INTO emp_salary VALUES (3, TO_DATE ('10/24/2011', 'mm/dd/yyyy'), 240);
INSERT INTO emp_salary VALUES (3, TO_DATE ('10/21/2011', 'mm/dd/yyyy'), 240);
INSERT INTO emp_salary VALUES (3, TO_DATE ('10/20/2011', 'mm/dd/yyyy'), 240);
INSERT INTO emp_salary VALUES (3, TO_DATE ('10/19/2011', 'mm/dd/yyyy'), 240);
INSERT INTO emp_salary VALUES (3, TO_DATE ('10/18/2011', 'mm/dd/yyyy'), 240);
INSERT INTO emp_salary VALUES (3, TO_DATE ('10/17/2011', 'mm/dd/yyyy'), 240);
For emp_id 1, if we ran this query for 10/31/2011, then it has to compare the 10/31 salary with 10/29 and do the same until the salary mismatches. In this case, salary salary mismatch occurs on 10/20, so the stale salary period is from 10/31 to 10/21 which is 7 days.
Below query will give that result:
WITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary
)
THEN 0
ELSE 1
END
) changed_ind
FROM emp_salary
WHERE emp_id = 1
AND effective_date <= TO_DATE ('10/31/2011', 'mm/dd/yyyy')
ORDER BY effective_date DESC)
SELECT COUNT (1) not_changed_count, MIN (salary) last_salary,
MIN (effective_date) stale_start_date,
MAX (effective_date) stale_end_date
FROM salary_tab
WHERE effective_date > (SELECT MAX (effective_date)
FROM salary_tab
WHERE changed_ind = 1);
NOT_CHANGED_COUNT LAST_SALARY STALE_STA STALE_END
----------------- ----------- --------- ---------
7 250 21-OCT-11 31-OCT-11
[Updated on: Wed, 02 November 2011 03:31] Report message to a moderator |
|
|
|
| Re: Compare current row values with previous one until mismatch [message #529627 is a reply to message #529609] |
Wed, 02 November 2011 04:11 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> def dt=10/31/2011
SQL> def emp=1
SQL> with
2 data as (
3 select emp_id, effective_date, salary,
4 row_number() over (order by effective_date desc) rn
5 from emp_salary
6 where emp_id = &emp and effective_date <= to_date('&dt','MM/DD/YYYY')
7 )
8 select rn nb_rows, salary,
9 effective_date startdt, connect_by_root effective_date enddt
10 from data
11 where connect_by_isleaf = 1
12 connect by prior rn = rn - 1 and prior salary = salary
13 start with rn = 1
14 /
NB_ROWS SALARY STARTDT ENDDT
---------- ---------- ----------- -----------
8 250 20-OCT-2011 31-OCT-2011
Regards
Michel
|
|
|
|
|
|
|
|
| Re: Compare current row values with previous one until mismatch [message #529653 is a reply to message #529649] |
Wed, 02 November 2011 05:18 |
|
asakchris
Messages: 4
Registered: November 2011
|
Junior Member |
|
|
I am using below query to get data for list of employees:
WITH salary_tab AS
(SELECT emp_id, effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary
)
THEN 0
ELSE 1
END
) changed_ind
FROM emp_salary
WHERE emp_id IN (1, 2, 3)
AND effective_date <= TO_DATE ('10/31/2011', 'mm/dd/yyyy')
ORDER BY effective_date DESC)
SELECT emp_id, COUNT (1) not_changed_count, MIN (salary) last_salary,
MIN (effective_date) stale_start_date,
MAX (effective_date) stale_end_date
FROM salary_tab tab_main
WHERE effective_date >
(SELECT MAX (effective_date)
FROM salary_tab tab_inn
WHERE tab_inn.emp_id = tab_main.emp_id AND changed_ind = 1)
GROUP BY emp_id;
EMP_ID NOT_CHANGED_COUNT LAST_SALARY STALE_STA STALE_END
---------- ----------------- ----------- --------- ---------
1 7 250 21-OCT-11 31-OCT-11
2 5 250 25-OCT-11 31-OCT-11
Are there any ways to improve the performance?
|
|
|
|
| Re: Compare current row values with previous one until mismatch [message #529655 is a reply to message #529653] |
Wed, 02 November 2011 05:25 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Either your queries are wrong, either you don't use the test case you provided.
SQL> with
2 data as (
3 select emp_id, effective_date, salary,
4 row_number() over (partition by emp_id order by effective_date desc) rn
5 from emp_salary
6 where effective_date <= to_date('&dt','MM/DD/YYYY')
7 )
8 select emp_id, rn nb_rows, salary,
9 effective_date startdt, connect_by_root effective_date enddt
10 from data
11 where connect_by_isleaf = 1
12 connect by prior rn = rn - 1 and prior salary = salary and prior emp_id = emp_id
13 start with rn = 1
14 order by emp_id
15 /
EMP_ID NB_ROWS SALARY STARTDT ENDDT
---------- ---------- ---------- ----------- -----------
1 8 250 20-OCT-2011 31-OCT-2011
2 6 250 24-OCT-2011 31-OCT-2011
3 1 250 31-OCT-2011 31-OCT-2011
3 rows selected.
Regards
Michel
[Updated on: Wed, 02 November 2011 05:26] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sun Nov 24 12:27:11 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
