SQL Query Performance Issue sub-query and missing parenthesis

Question

I have two Questions in the below query

When i tried running the query I am getting the below error in oracle
Performance issue i.e when i run the query without the IN clause i.e remove the {select * from cand_profile where postal_code in } in the below query it is taking 15 sec .How to fine tune this sql query ?

Query

select *
from cand_profile 
where postal_code in (
    SELECT ZIP
    FROM (
        SELECT
            dest.ID,
            dest.postal_code AS ZIP,
            ACOS(SIN(RADIANS(src.latitude))*SIN(RADIANS(dest.latitude))+COS(RADIANS(src.latitude))*COS(RADIANS(dest.latitude))*COS(RADIANS(src.longitude)-RADIANS(dest.longitude)))* 3959 AS DISTANCE
        FROM post_codes dest
            CROSS JOIN
        post_codes src
        WHERE src.ID = (
            SELECT ID
            FROM post_codes
            WHERE postal_code = '60195'
            GROUP BY ID
        ) 
        AND ( dest.ID <> src.ID OR  dest.ID = src.ID )
    )
    GROUP BY ID,ZIP,DISTANCE
    HAVING DISTANCE <= 5
    ORDER BY DISTANCE
))

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause:
*Action:

What you show in your question includes 16 left parentheses and 17 right parentheses. You have an extra right parenthesis at the end. — Mark Leiber, May 14 '15 at 19:37
For understanding about Correlated Subquery's poor performace, take a look at this http://stackoverflow.com/questions/17268848/difference-between-subquery-and-correlated-subquery?answertab=votes#tab-top . To say, the inner query will be executed every time for every row in the outer query. — The Coder, May 14 '15 at 19:38
What are you trying to accomplish with this?: AND ( dest.ID <> src.ID OR dest.ID = src.ID ) — Mark Leiber, May 14 '15 at 19:39
@GordonLinoff ha. I just might favorite this question because of that. senthil , if you are trying to include everything, you could either omit that line (because everything is already included by default), or do "(1=1)". Or do a "not null" if you are trying to filter nulls or something... — John Smith, May 14 '15 at 19:51
`dest.ID <> src.ID OR dest.ID = src.ID` is not equivalent to `1=1`; it's equivalent to `dest.ID IS NOT NULL AND src.ID IS NOT NULL` because of the way `NULL` comparisons work in Oracle. `NULL = NULL` and `NULL <> NULL` are always `false`. — Mick Mnemonic, May 14 '15 at 20:08

score 1 · Accepted Answer · 2015-05-14T20:43:29.157

1

The 'in' clause tends to be very inefficient. I would try to use a join instead:

select c.*
from cand_profile c
join (
    SELECT ZIP
    FROM (
        SELECT dest.ID,dest.postal_code AS ZIP, ACOS(SIN ( RADIANS( src.latitude) ) * SIN ( RADIANS ( dest.latitude )) 
+ COS ( RADIANS ( src.latitude)) * COS ( RADIANS ( dest.latitude )) * COS ( RADIANS( src.longitude ) - RADIANS ( dest.longitude ))) * 3959
AS DISTANCE
        FROM post_codes dest
            CROSS JOIN
        post_codes src
        WHERE src.ID = (
            SELECT ID
            FROM post_codes
            WHERE postal_code = '60195'
            GROUP BY ID
        ) 
        AND ( dest.ID <> src.ID OR  dest.ID = src.ID )
    )
    GROUP BY ID,ZIP,DISTANCE
    HAVING DISTANCE <= 5
    ORDER BY DISTANCE
) a
on postal_code = a.ZIP

edited May 14 '15 at 20:43

answered May 14 '15 at 19:32

1

@Senthil the problem might be that your original query had 1 extra parenthesis in it.. try the query now. About the (1=1), this should produce exactly the same results as the line originally had, but I am not sure what you were trying to accomplish with that line because all ID's should either = or <>.. thus it is inclusive of everything. – May 14 '15 at 20:45
- john ,I tried the above query which took in 15.16 sec . Also tried using (1=1) which also give me 15 sec .. – Senthil May 14 '15 at 20:47
@Senthil 15 seconds is not terribly slow.. But it's hard to find out exactly what is taking the most time, without seeing an execution plan (my guess would be the "distance" calculation though; there is a lot of processing going on there) – May 14 '15 at 20:52
ORA-01428: argument '1.00000000000000000000000000000000000001' is out of range 01428. 00000 - "argument '%s' is out of range" *Cause: *Action: – Senthil May 18 '15 at 16:14

SQL Query Performance Issue sub-query and missing parenthesis

1 Answers1