Home » SQL & PL/SQL » SQL & PL/SQL » Can you Tune this Query?
Can you Tune this Query? [message #195944] Tue, 03 October 2006 04:41 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi all,

This is my original query. In what way we can improve the performance?
SELECT 	DISTINCT A.COL1, 
	B.COL2, 
	C.COL3, 
	D.COL4 
FROM 
	TAB1 A, 
	TAB2 B, 
	TAB3 C, 
	TAB4 D
WHERE
	A.COL1 = B.COL1 AND
	A.COL1 = C.COL1 AND
	A.COL2 = D.COL2
	A.COL3 = D.COL3

Where A.COL1, B.COL1, C.COL1, D.COL1 are primary keys in the corresponding table.

No. of records in each table given blelow.

TAB1=12826465
TAB2=3239928
TAB3=11336
TAB4=62324

Thanks,
Thangam
Re: Can you Tune this Query? [message #195945 is a reply to message #195944] Tue, 03 October 2006 04:48 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The first question I would ask is: if these columns are really primary keys on the tables, why do you need a "select distinct" ? "Select distinct" implies you are getting duplicates, which means some of the joins are non-unique.

The "select distinct" will require a sort of the data, which will add enormously to the time taken. Also, if there are any many to many joins, this could hugely increase the volume of data being retrieved (for example, if two rows in tab1 match 3 rows in tab2, that will produce 6 rows). The sort and de-duplication phase would then take even longer.

Look again at your data model and try to write the query in such a way that the data is returned naturally without any duplicates and you can drop the "select distinct".
Re: Can you Tune this Query? [message #195947 is a reply to message #195945] Tue, 03 October 2006 04:58 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Well, I know that sorting will degrade the performance. But did you closely look into my query. i'm joining the table TAB1 and TAB4 with NON PRIMARY key. In the table TAB4, COL1 is the primary key not others.

Thanks,
Thangam
Re: Can you Tune this Query? [message #195952 is a reply to message #195944] Tue, 03 October 2006 05:17 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
That is really my point. Having to use "select distinct" is nearly always the sign of an inadequate table design. What is the extent of the record duplication that takes place when you join A to D ? Try running the query as a join of A and D without the "select distinct" and compare that to the number of rows returned with the "select distinct".

You could force D to be distinct on its columns by copying it to another table first. This shouldn't take long, given that it's only 60000 rows.

create table temp_tab as
select * from
(select *, row_number() over (partition by col2, col3 order by col4) as rn
from tab4)
where rn=1


Obviously, you should also have run run dbms_stats/analyze on all the tables and indexes. It may also help to have an index on the col2 and col3 columns on tab4.
Re: Can you Tune this Query? [message #195967 is a reply to message #195952] Tue, 03 October 2006 06:30 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi Cthulhu,
Thanks for your reply. Apart from this get rid of distinct, is there any way to enhance the performance?

Thanks,
Thangam
Re: Can you Tune this Query? [message #195969 is a reply to message #195944] Tue, 03 October 2006 06:40 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Lots of things potentially e.g.

a) make sure all your database stats on all tables are up to date

b) get the explain plan for the query and find out what type of joins it's doing. Perhaps tune it using some hints.

c) use parallelization, if your system supports it.

d) tune some of your SGA parameters.

This is a big area and you'll have to do a fair amount of research.
Re: Can you Tune this Query? [message #195983 is a reply to message #195944] Tue, 03 October 2006 08:10 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could create indexes on:
TAB1 (Col1,col2,col3)
TAB2 (Col1,Col2)
TAB3 (Col1,Col3)
TAB4 (Col2,Col3,Col4)

This would let the optimiser user Index Fast Full scans instead of Full Table Scans, which may well be faster if there are a lot of other columns in the tables.
Previous Topic: Connect by prior and SUM
Next Topic: ORA-13249
Goto Forum:
  


Current Time: Fri Dec 09 19:16:08 CST 2016

Total time taken to generate the page: 0.10869 seconds