0 Replies Latest reply: Nov 21, 2013 8:41 AM by Prateek02 RSS

    Query runs for large amount of time

    Prateek02


      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