SQL Performance query

[rawatds]

Hi Gurus,

I Have the following query which i want to tune.

Select
a.add,
a.stamp_updt,
b.cur_typ_c
from record_master a,
Detail_record b
where a.id = p_id
and a.unit_id in (select unit_id from list_home)
and a.unit_c in (select unit_c from list_copy)
and a.id_c = b.id_c


Where record_master is the driving table containing 1 Million reocrd and detail_record has around 1000 record.
Also list_home and list_copy contains around 800 record.

Where p_id is the input from the screen i.e it is variable.
At present the Query is taking around 15 minutes.

There is a index in the table record_master whose order is
( id, unit_id , unit_c, id_c ) .

I am unable to see any improvement in the query because of IN clause in the Query.

Any suggestion is most useful.

Thanks
DR

 

[SantaMufasa]

DR,

My suggestions are:

1) Create separate indexes for "P_ID", "DETAIL_RECORD.ID_C", "MASTER_RECORD.ID_C", "LIST_HOME.UNIT_ID", and "LIST_COPY.UNIT_C".

2) Use the following code, which employs the "EXISTS" operator, which is much more efficient than the "IN" operator:

Select a.add, a.stamp_updt, b.cur_typ_c 
  from record_master a, Detail_record b 
 where a.id = p_id
   and [b]EXISTS (select 'x' from list_home
                where a.unit_id = unit_id)
   and EXISTS (select 'x' from list_copy
                where a.unit_id = unit_id)[/b]
   and a.id_c = b.id_c;

Let us know how much improvement you encounter.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[rawatds]

Hi Santa,
The records in the table LIST_HOME and LIST_COPY are inserted as a DISTINCT value from some other tables.

So is there any worth to create index on these tables.

Thanks
DR

 

[SantaMufasa]

To answer the question about the worth of indexes on those two tables, DR, I'll ask a question of you: "If you had two SQL reference manuals, one of 1000 pages, and the other of 800 pages, and if you had to look up information about "INDEXES" in those to manuals, which would be quicker:

1) finding the applicable pages by reading both books cover-to-cover, or

2) looking up the applicable pages in the index?"

I believe that unless the reference manuals were brief brochures, that using indexes would probably be faster for finding specific information.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

To answer the question about the worth of indexes on those two tables, DR, I'll ask a question of you: "If you had two SQL reference manuals, one of 1000 pages, and the other of 800 pages, and if you had to look up information about "INDEXES" in those to manuals, which would be quicker:

1) finding the applicable pages by reading both books cover-to-cover, or

2) looking up the applicable pages in the index?"

I believe that unless the reference manuals were brief brochures, that using indexes would probably be faster for finding specific information.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]