| SQL performance tuning [message #214795] |
Thu, 18 January 2007 02:14  |
rawat_me
Messages: 45
Registered: September 2005
|
Member |
|
|
Hi Gurus,
I Have the following query which i want to tune.
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 20 Million
reocrd and detail_record has around 5 Million record.
Also list_home and list_copy contains around 1 Million record.
Where p_id is the input from the screen.
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.
DR
|
|
|
|
| Re: SQL performance tuning [message #214818 is a reply to message #214795] |
Thu, 18 January 2007 03:35  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Both of those IN clauses are going to cause Full Table scans on million row tables, which isn't going to make anything any quicker.
If there is in index on Unit_id on table List_Home, and on Unit_c on table List_Copy, then you could use Exists :a.add,
a.stamp_updt,
b.cur_typ_c
from record_master a,
Detail_record b
where a.id = p_id
and exists (select null from list_home l where l.unit_id = a.unit_id)
and exists (select null from list_copy c where c.unit_c a.unit_c)
and a.id_c = b.id_c |
|
|
|
| Re: SQL performance tuning [message #214992 is a reply to message #214818] |
Thu, 18 January 2007 20:53 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| JRowbottom wrote on Thu, 18 January 2007 20:35 | Both of those IN clauses are going to cause Full Table scans on million row tables,...
|
Not necessarily these days. The optimizer will often rewrite such a query to be the equivalent of an EXISTS. It's unlikely in this case though - more probably it will produce a HASH SEMI-JOIN, which may actually be the best solution depending on the number of distinct unit_id and unit_c values.
Ross Leishman
|
|
|
|
| Re: SQL performance tuning [message #214993 is a reply to message #214818] |
Thu, 18 January 2007 20:55 |
rawat_me
Messages: 45
Registered: September 2005
|
Member |
|
|
Hi,
But in your query the index on the master table record_master
will not be picked.
Also what if list_home and list_copy has only few thousand records.In that case i think IN is fine and the query will pick the index as well.
Thanks
|
|
|
|
|
|
|
|
|
|
| Re: SQL performance tuning [message #215289 is a reply to message #215023] |
Sat, 20 January 2007 10:11 |
rawat_me
Messages: 45
Registered: September 2005
|
Member |
|
|
Hi All
I am attaching the Explain plan for the Test Query .
=========================================================
SELECT STATEMENT Cost = 11
SORT AGGREGATE
HASH JOIN SEMI
HASH JOIN SEMI
HASH JOIN
TABLE ACCESS FULL record_master
TABLE ACCESS FULL Detail_record
TABLE ACCESS FULL list_copy
TABLE ACCESS FULL list_home
=======================================================
The record_master has 1 Million record and detail_record has
5000 records.
The list_copy and list_home have only 1000 records.
Any help is most welcome.
rawat
|
|
|
|
| Re: SQL performance tuning [message #215309 is a reply to message #214795] |
Sat, 20 January 2007 19:25 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| rawat_me wrote on Thu, 18 January 2007 19:14 | 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
|
You didn't explain the right query. I was willing to overlook the missing SELECT keyword in the original query, but your EXPLAIN PLAN shows a SORT AGGREGATE step, which means it included a SELECT COUNT(*) or similar.
- Post the actual query
- Post the actual Explain Plan of that query
- Enclose both of them in [code] and [/code] tags so that we can read them properly in our browser.
- Tell us what p_id is in the original query. A column? A PL/SQL variable?
- How many of the 1M rows in the master do you expect to be needed in the query.
- Double-check! Confirm you have done ALL of the above before posting back.
Have you tried analyzing your tables? If p_id is a PL/SQL variable, I'm surprised Oracle is not using an index.
Ross Leishman
|
|
|
|
| Re: SQL performance tuning [message #215313 is a reply to message #215309] |
Sat, 20 January 2007 22:09 |
rawat_me
Messages: 45
Registered: September 2005
|
Member |
|
|
Hi
This is the code
=======================================================
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
=======================================================
Explain plan i am attaching in the file.
p_id is the input parameter ( Variable ) .
Average 2-3K records are coming for the query but taking around 14-15 Min.
Thanks.
|
|
|
|
|
|
| Re: SQL performance tuning [message #215356 is a reply to message #215341] |
Sun, 21 January 2007 19:48 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
That COST of 11 tells me that Oracle thinks the tables are very small. There's no way a full scan of a 1M row table would cost that little.
Gather statistics on all tables like I suggested earlier.
If there are only 2-3K rows with a.id = p_id, then you will need an index on record_master.id
If Detail_record contains only 5000 rows, you won't need an index. But you seem to be confused about this, because earlier you told us it had 5M rows. If that is the case, you'll need an index on Detail_record.id_c
If list_home and list_copy contain only 1000 rows each, they will not need an index either. However, if - as you indicated in your earlier post - they have 1M rows, you will need indexes on list_home.unit_id and list_copy.unit_c
Work out from this which indexes you will need, build them, gather statistics on all tables, and then re-explain your plan.
It should look something like:
NESTED LOOPS (SEMI)
NESTED LOOPS (SEMI)
NESTED LOOPS
TABLE ACCESS (BY INDEX ROWID) record_master
INDEX RANGE SCAN record_master(id)
TABLE ACCESS (BY INDEX ROWID) Detail_record
INDEX RANGE SCAN Detail_record(id_c)
TABLE ACCESS (BY INDEX ROWID) list_home
INDEX RANGE SCAN list_home(unit_id)
TABLE ACCESS (BY INDEX ROWID) list_copy
INDEX RANGE SCAN list_home(unit_c)
HASH JOIN (SEMI)
HASH JOIN (SEMI)
HASH JOIN
TABLE ACCESS (BY INDEX ROWID) record_master
INDEX RANGE SCAN record_master(id)
TABLE ACCESS (FULL) Detail_record
TABLE ACCESS (FULL) list_home
TABLE ACCESS (FULL) list_copy
Do not index record_master.unit_id or record_master.unit_c - it will just confuse the optimizer.
Ross Leishman
|
|
|
|
| Re: SQL performance tuning [message #215357 is a reply to message #215356] |
Sun, 21 January 2007 20:17 |
rawat_me
Messages: 45
Registered: September 2005
|
Member |
|
|
Hi Ross,
I will do as per your suggestion and see if it works.
Can you please clarify is there any need of the index on all four columns because at present there is an index
on the table record_master whose order is
( id, unit_id , unit_c, id_c ) .
Thanks
DR
|
|
|
|
| Re: SQL performance tuning [message #215361 is a reply to message #215357] |
Sun, 21 January 2007 21:27 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It wont hurt. If the number of rows to be returned from the master was greater, it would actually help. With your volumes I doubt you'll notice.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
Umm, no. They're in separate sub-queries. They're not joined.