Hi,
I have a peculiar problem. Not sure if it is a problem, but will be helpful if someone explains. I have two queries:
Query1
Select Obj.Value, Count(Distinct Activity_Date) as cnt
From PM.TIMESHEET_DET Act
Inner Join (Select * From PM.OBJ_DATA Where Attribute_ID='49lh78209720001000030ag19l')Obj On Act.Assignee_ID=Obj.Object_ID
Inner Join (Select * From Per_All_People_F Where Trunc(Sysdate) Between Trunc(Effective_Start_Date) and Trunc(Effective_End_Date))Papf On to_number(Obj.Value)=Papf.Person_ID
Where Is_Filled='N'
And Activity_Date Between '12-DEC-2011' And '16-DEC-2011'
Group By Obj.Value
Query2
Select Obj.Value, Count(Distinct Activity_Date) as cnt
From PM.TIMESHEET_DET Act
Inner Join (Select * From PM.OBJ_DATA Where Attribute_ID='49lh78209720001000030ag19l')Obj On Act.Assignee_ID=Obj.Object_ID
--Inner Join (Select * From Per_All_People_F Where Trunc(Sysdate) Between Trunc(Effective_Start_Date) and Trunc(Effective_End_Date))Papf On to_number(Obj.Value)=Papf.Person_ID
Where Is_Filled='N'
And Activity_Date Between '12-DEC-2011' And '16-DEC-2011'
Group By Obj.Value
In query 2, i have commented the second inner join. Query 2 executes in 10 seconds, but Query 1 takes more than 15 mins to run.
Query 1 plan
Plan
SELECT STATEMENT ALL_ROWS
Cost: 4,681 Bytes: 23,280 Cardinality: 194
7 SORT GROUP BY Cost: 4,681 Bytes: 23,280 Cardinality: 194
6 TABLE ACCESS BY INDEX ROWID TABLE PM.TIMESHEET_DET Cost: 328 Bytes: 370 Cardinality: 10
5 NESTED LOOPS Cost: 4,680 Bytes: 23,280 Cardinality: 194
3 HASH JOIN Cost: 3,432 Bytes: 1,577 Cardinality: 19
1 INDEX FULL SCAN INDEX (UNIQUE) HR.PER_PEOPLE_F_PK Cost: 98 Bytes: 693 Cardinality: 33
2 TABLE ACCESS FULL TABLE PM.OBJ_DATA Cost: 3,334 Bytes: 657,944 Cardinality: 10,612
4 INDEX RANGE SCAN INDEX PM.ATD_ASSIGNEE_ID Cost: 51 Cardinality: 9,334
Query 2 plan
Plan
SELECT STATEMENT ALL_ROWS
Cost: 75,391 Bytes: 964,161 Cardinality: 9,739
5 SORT GROUP BY Cost: 75,391 Bytes: 964,161 Cardinality: 9,739
4 HASH JOIN Cost: 74,946 Bytes: 3,746,556 Cardinality: 37,844
1 TABLE ACCESS FULL TABLE PM.OBJ_DATA Cost: 3,334 Bytes: 657,944 Cardinality: 10,612
3 TABLE ACCESS BY INDEX ROWID TABLE PM.TIMESHEET_DET Cost: 71,612 Bytes: 1,375,771 Cardinality: 37,183
2 INDEX RANGE SCAN INDEX PM.ATD_ACTIVITY_DATE Cost: 201 Cardinality: 74,367
If you see query 2 cost is higher than query 1, but how does it execute faster than query 1. My understanding may be wrong, so pls help me in understanding this.
Thanks
Jek