Hi All,
Below is the extract query in one of SDE tasks in OBIA ETL.
Select
-----
-----
FROM
apps.PA_TASKS LEVEL1,
apps.PA_TASKS LEVEL2,
apps.PA_TASKS LEVEL3,
apps.PER_ALL_PEOPLE_F P1,
apps.PER_ALL_PEOPLE_F P2,
apps.PER_ALL_PEOPLE_F P3
WHERE
LEVEL3.PARENT_TASK_ID = LEVEL2.TASK_ID AND
LEVEL2.PARENT_TASK_ID = LEVEL1.TASK_ID AND
P1.PERSON_ID(+)= LEVEL1.TASK_MANAGER_PERSON_ID AND
P2.PERSON_ID(+)= LEVEL2.TASK_MANAGER_PERSON_ID AND
P3.PERSON_ID(+)= LEVEL3.TASK_MANAGER_PERSON_ID AND
(LEVEL1.LAST_UPDATE_DATE> TO_DATE ('11/20/2013 21:59:59', 'MM/DD/YYYY HH24:MI:SS')
OR LEVEL2.LAST_UPDATE_DATE> TO_DATE('11/20/2013 21:59:59', 'MM/DD/YYYY HH24:MI:SS')
OR LEVEL3.LAST_UPDATE_DATE> TO_DATE('11/20/2013 21:59:59', 'MM/DD/YYYY HH24:MI:SS')
OR P1.LAST_UPDATE_DATE> TO_DATE('11/20/2013 21:59:59', 'MM/DD/YYYY HH24:MI:SS')
OR P2.LAST_UPDATE_DATE> TO_DATE('11/20/2013 21:59:59', 'MM/DD/YYYY HH24:MI:SS')
OR P3.LAST_UPDATE_DATE> TO_DATE('11/20/2013 21:59:59', 'MM/DD/YYYY HH24:MI:SS'))
We are trying to optimisie this query and when generateing explain plan for this query we are seeing that full scan of the bigger of the table PA_Tasks is being carried out which is a huge table with 30.6 million records whereas other table has only 80k records.
All the columns except for the last_update_date have indexes on them. Even when trying to force the indexes on PA_TASKS table column explain plan is showing full scan of the table.
Please suggest if someone has better ways to handle this query.
Appreciate your help
Thanks
Prateek