CODEWITHSUNDEEP
sqloracle-databaseoracle10g
atif
atif

Reputation: 1147

Slow query with multiple joins

I have following query which I am trying to optimize, the query runs in 3 seconds if I remove the join condition I.ident_id in (select distinct(ident_id) from MISSING_Images miss ) from below query but with this I takes 3 mins to execute. 

SELECT IDENT_ID
   FROM tbl_IDENT I
            JOIN tbl_TA AN ON (AN.IDENT_ID = I.IDENT_ID and AN.anchor_point = I.c_id)
            JOIN tbl_AB A ON (A.A_ID = I.A_ID)
            JOIN tbl_FULL_ARCHIVE FT ON (FT.ARCHIVE_ID = I.ARCHIVE_ID)
            WHERE (I.DATA_STATUS = 'ACTIVE' or I.DATA_STATUS = 'INACTIVE')
            AND 
            (
             I.FD = 'Y'
                OR


 I.ident_id in (select distinct(ident_id) from MISSING_Images miss where substr(miss.NAME, 0, INSTR(miss.NAME, '.',-1,1)) in (SELECT substr(IMG_NAME, 0, INSTR(IMG_NAME, '.',-1,1)) FROM IMAGES ))

);

select distinct(ident_id) from MISSING_Images miss return 2000 records and main tbl_IDENT has over 100,000 record. I have the index created on I.ident_id

Any hint how to improve it. I am using oracle 10g.

Upvotes: 0

Views: 115

Answers (4)

Multisync
Multisync

Reputation: 8797

You may try to replace 

I.ident_id in (select distinct(ident_id) from MISSING_Images miss)

with

EXISTS (select 1 from MISSING_Images miss where miss.ident_id = I.ident_id)

And create an index on MISSING_Images.ident_id

EDIT: The most direct solution will be:

EXISTS (select 1 from MISSING_Images miss 
        where miss.ident_id = I.ident_id
          and exists (select 1 from images img 
                      where substr(img.IMG_NAME, 0, INSTR(img.IMG_NAME, '.',-1,1))
                            = substr(miss,NAME, 0, INSTR(IMG_NAME, '.',-1,1))
                     )
        )

And create index-based indexes: 

create index indx_name1 on images(substr(IMG_NAME, 0, INSTR(IMG_NAME, '.',-1,1))); 
create index indx_name2 on MISSING_Images(substr(miss.NAME, 0, INSTR(miss.NAME, '.',-1,1)));

Take a note that such indexes can have a bad impact on insert/update operations on the undelying objects and require some additional space. In addition to that they don't work well with nulls.

Other choices:

EXISTS (select 1 from MISSING_Images miss join images img 
                        on substr(img.IMG_NAME, 0, INSTR(img.IMG_NAME, '.',-1,1))
                            = substr(miss,NAME, 0, INSTR(IMG_NAME, '.',-1,1))
        where miss.ident_id = I.ident_id
       )


EXISTS (select 1 from (select miss.ident_id MISSING_Images miss join images img 
                        on substr(img.IMG_NAME, 0, INSTR(img.IMG_NAME, '.',-1,1))
                            = substr(miss,NAME, 0, INSTR(IMG_NAME, '.',-1,1))
                       ) sub        
        where sub.ident_id = I.ident_id
       )

Upvotes: 2

Exhausted
Exhausted

Reputation: 1885

you can even combine the where filters in the join conditions like below

SELECT IDENT_ID
   FROM tbl_IDENT I
            JOIN tbl_TA AN ON (AN.IDENT_ID = I.IDENT_ID and AN.anchor_point = I.c_id)
            and I.DATA_STATUS IN ('ACTIVE', 'INACTIVE') AND
            I.FD = 'Y'
                OR
             I.ident_id in (select distinct(ident_id) from MISSING_Images miss)
            JOIN tbl_AB A ON (A.A_ID = I.A_ID)
            JOIN tbl_FULL_ARCHIVE FT ON (FT.ARCHIVE_ID = I.ARCHIVE_ID);

and even you can have exists clause with that (as @Multisync said) might result in better performance.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Create an index on MISSING_Images(ident_id).

I would also suggest that you write the where clause as:

        WHERE I.DATA_STATUS IN ('ACTIVE', 'INACTIVE') AND
              (I.FD = 'Y' OR
               I.ident_id in (select ident_id from MISSING_Images miss)
              )

The distinct is unnecessary in an in subquery. Also, in is easier to read (and write and maintain) then a list of individual comparisons. The index should take care of performance.

Upvotes: 1

Sebas
Sebas

Reputation: 21532

Try a union instead, to begin with?

SELECT IDENT_ID
FROM tbl_IDENT I
    JOIN tbl_TA AN ON AN.IDENT_ID = I.IDENT_ID AND AN.anchor_point = I.c_id
    JOIN tbl_AB A ON A.A_ID = I.A_ID
    JOIN tbl_FULL_ARCHIVE FT ON FT.ARCHIVE_ID = I.ARCHIVE_ID
WHERE 
    (I.DATA_STATUS = 'ACTIVE' OR I.DATA_STATUS = 'INACTIVE')
AND I.FD = 'Y'
UNION
SELECT IDENT_ID
FROM tbl_IDENT I
    JOIN tbl_TA AN ON AN.IDENT_ID = I.IDENT_ID AND AN.anchor_point = I.c_id
    JOIN tbl_AB A ON A.A_ID = I.A_ID
    JOIN tbl_FULL_ARCHIVE FT ON FT.ARCHIVE_ID = I.ARCHIVE_ID
    JOIN MISSING_Images miss ON I.ident_id = miss.ident_id
WHERE 
    I.DATA_STATUS = 'ACTIVE' 
OR I.DATA_STATUS = 'INACTIVE'
;

More improvements can surely be provided, but for that I'd need an explain plan of the query...

Upvotes: 1

Related Questions