CODEWITHSUNDEEP
sqloracle-database
Rohit
Rohit

Reputation: 63

Self join SQL is taking too much time to execute

Below SQL is taking too much time to execute.Dont know where is am doing wrong but yes getting proper result.can i further simplify this sql. This is oracle db and jmc_job_step table contains huge records.

select *
 from
        jmc_job_run_id jobrunid0_ 
    inner join
        jmc_job_step jobsteps1_ 
            on jobrunid0_.id=jobsteps1_.job_run_id
    where
        (
            jobsteps1_.creation_date in (
                select
                    min(jobstep2_.creation_date) 
                from
                    jmc_job_step jobstep2_ 
                where
                    jobrunid0_.id=jobstep2_.job_run_id 
                group by
                    jobstep2_.job_run_id ,
                    jobstep2_.job_step_no
            ) 

            )
            or jobsteps1_.job_step_progress_value in (
                select
                    max(jobstep3_.job_step_progress_value) 
                from
                    jmc_job_step jobstep3_ 
                where
                    jobrunid0_.id=jobstep3_.job_run_id 
                group by
                    jobstep3_.job_run_id ,
                    jobstep3_.job_step_no
            )
        ) 
    order by
        jobrunid0_.job_start_time desc

Upvotes: 0

Views: 91

Answers (1)

Littlefoot
Littlefoot

Reputation: 142788

This is useless; it says "I don't care what those columns contain", but - yet - you give the database engine to check those values anyway.

    (
        upper(jobrunid0_.tenant_id) like '%'|| null
    ) 
    and (
       upper(jobrunid0_.job_run_id) like '%'||null||'%'
    )

Upvotes: 1

Related Questions