Re: Need Suggestion on the following Query and SQEXPLAIN...it is
REBELLO, Rulesh Felix said:
> ....and this query is suppposed to run every 5 minutes....
Table schemas?
Why not rewrite as:
select distinct a.c78_loc, a.user_id, b.user_email, a.c78_status,a.c78h_tags, a.created_by
from c78h a, c78_users b, err_maint c
where c.entry_no = a.c78_loc
and a.user_id = b.user_id
and c.status = "A"
and a.c78_status IN ("O", "C", "D", "I", "K", "N", "R")
and (a.c78h_tags not in ("S", "H") or a.c78h_tags is null )
Are you sure the statistics are OK, because the index choice on A looks a
little weird to me?
Is there an index on b(user_id)?
I guess it's the filtering on A that's killing you.
> Informix 9.21UC2 on SCO
>
>
>
> Table a is c78h rowcount 110910
>
> Table b is c78_users rowcount 374
>
> Table c is err_maint rowcount 166610
>
> QUERY:
> ------
> select distinct a . c78_loc , a . user_id , b . user_email , a .
> c78_status , a . c78h_tags , a . created_by from c78h a , c78_users b
> ,
> err_maint c where c . entry_no = a . c78_loc and a . user_id = b .> user_id
> and c . status = "A" and a . c78_status = "O" and ( a . c78h_tags not
> in
> (
> "S" , "H" ) or a . c78h_tags is null ) union select distinct a .
> c78_loc
> ,
> a . user_id , b . user_email , a . c78_status , a . c78h_tags , a .
> created_by from c78h a , c78_users b , err_maint c where c . entry_no
> =
> a
> . c78_loc and a . user_id = b . user_id and c . status = "A" and a .
> c78_status = "C" and ( a . c78h_tags not in ( "S" , "H" ) or a .
> c78h_tags
> is null ) union select distinct a . c78_loc , a . user_id , b .
> user_email
> , a . c78_status , a . c78h_tags , a . created_by from c78h a ,
> c78_users
> b , err_maint c where c . entry_no = a . c78_loc and a . user_id = b .
> user_id and c . status = "A" and a . c78_status = "D" and ( a .
> c78h_tags
> not in ( "S" , "H" ) or a . c78h_tags is null ) union select distinct
> a
> .
> c78_loc , a . user_id , b . user_email , a . c78_status , a .
> c78h_tags
> ,
> a . created_by from c78h a , c78_users b , err_maint c where c .
> entry_no
> = a . c78_loc and a . user_id = b . user_id and c . status = "A" and a
> .
> c78_status = "I" and ( a . c78h_tags not in ( "S" , "H" ) or a .
> c78h_tags
> is null ) union select distinct a . c78_loc , a . user_id , b .
> user_email
> , a . c78_status , a . c78h_tags , a . created_by from c78h a ,
> c78_users
> b , err_maint c where c . entry_no = a . c78_loc and a . user_id = b .
> user_id and c . status = "A" and a . c78_status = "K" and ( a .
> c78h_tags
> not in ( "S" , "H" ) or a . c78h_tags is null ) union select distinct
> a
> .
> c78_loc , a . user_id , b . user_email , a . c78_status , a .
> c78h_tags
> ,
> a . created_by from c78h a , c78_users b , err_maint c where c .
> entry_no
> = a . c78_loc and a . user_id = b . user_id and c . status = "A" and a
> .
> c78_status = "N" and ( a . c78h_tags not in ( "S" , "H" ) or a .
> c78h_tags
> is null ) union select distinct a . c78_loc , a . user_id , b .
> user_email
> , a . c78_status , a . c78h_tags , a . created_by from c78h a ,
> c78_users
> b , err_maint c where c . entry_no = a . c78_loc and a . user_id = b .
> user_id and c . status = "A" and a . c78_status = "R" and ( a .
> c78h_tags
> not in ( "S" , "H" ) or a . c78h_tags is null )
>
> Estimated Cost: 30703
> Estimated # of Rows Returned: 10315
>
> 1) c78.b: SEQUENTIAL SCAN
>
> 2) c78.a: INDEX PATH
>
> Filters: (c78.a.c78h_tags NOT IN ('S' , 'H' )OR c78.a.c78h_tags IS
> NULL )
>
> (1) Index Keys: user_id c78_status c78h_tags (Serial, fragments:
> ALL)
> Lower Index Filter: (c78.a.user_id = c78.b.user_id AND
> c78.a.c78_status = 'O' )
> NESTED LOOP JOIN
>
> 3) c78.c: INDEX PATH
>
> (1) Index Keys: entry_no status (Key-Only) (Serial, fragments: ALL)
> Lower Index Filter: (c78.c.entry_no = c78.a.c78_loc AND
> c78.c.status
> = 'A' )
> NESTED LOOP JOIN
>
>
> Union Query:
> ------------
>
> 1) c78.a: INDEX PATH
>
> Filters: (c78.a.c78h_tags NOT IN ('S' , 'H' )OR c78.a.c78h_tags IS
> NULL )
>
> (1) Index Keys: c78_status (Serial, fragments: ALL)
> Lower Index Filter: c78.a.c78_status = 'C'
>
> 2) c78.b: INDEX PATH
>
> (1) Index Keys: user_id (Serial, fragments: ALL)
> Lower Index Filter: c78.a.user_id = c78.b.user_id
> NESTED LOOP JOIN
>
> 3) c78.c: INDEX PATH
>
> (1) Index Keys: entry_no status (Key-Only) (Serial, fragments: ALL)
> Lower Index Filter: (c78.c.entry_no = c78.a.c78_loc AND
> c78.c.status
> = 'A' )
> NESTED LOOP JOIN
>
>
> Union Query:
> ------------
>
> 1) c78.b: SEQUENTIAL SCAN
>
> 2) c78.a: INDEX PATH
>
> Filters: (c78.a.c78h_tags NOT IN ('S' , 'H' )OR c78.a.c78h_tags IS
> NULL )
>
> (1) Index Keys: user_id c78_status c78h_tags (Serial, fragments:
> ALL)
> Lower Index Filter: (c78.a.user_id = c78.b.user_id AND
> c78.a.c78_status = 'D' )
> NESTED LOOP JOIN
>
> 3) c78.c: INDEX PATH
>
> (1) Index Keys: entry_no status (Key-Only) (Serial, fragments: ALL)
> Lower Index Filter: (c78.c.entry_no = c78.a.c78_loc AND
> c78.c.status
> = 'A' )
> NESTED LOOP JOIN
>
>
> Union Query:
> ------------
>
> 1) c78.a: INDEX PATH
>
> Filters: (c78.a.c78h_tags NOT IN ('S' , 'H' )OR c78.a.c78h_tags IS
> NULL )
>
> (1) Index Keys: c78_status (Serial, fragments: ALL)
> Lower Index Filter: c78.a.c78_status = 'I'
>
> 2) c78.b: INDEX PATH
>
> (1) Index Keys: user_id (Serial, fragments: ALL)
> Lower Index Filter: c78.a.user_id = c78.b.user_id
> NESTED LOOP JOIN
>
> 3) c78.c: INDEX PATH
>
> (1) Index Keys: entry_no status (Key-Only) (Serial, fragments: ALL)
> Lower Index Filter: (c78.c.entry_no = c78.a.c78_loc AND
> c78.c.status
> = 'A' )
> NESTED LOOP JOIN
>
>
> Union Query:
> ------------
>
> 1) c78.b: SEQUENTIAL SCAN
>
> 2) c78.a: INDEX PATH
>
> Filters: (c78.a.c78h_tags NOT IN ('S' , 'H' )OR c78.a.c78h_tags IS
> NULL )
>
> (1) Index Keys: user_id c78_status c78h_tags (Serial, fragments:
> ALL)
> Lower Index Filter: (c78.a.user_id = c78.b.user_id AND
> c78.a.c78_status = 'K' )
> NESTED LOOP JOIN
>
> 3) c78.c: INDEX PATH
>
> (1) Index Keys: entry_no status (Key-Only) (Serial, fragments: ALL)
> Lower Index Filter: (c78.c.entry_no = c78.a.c78_loc A