Home » RDBMS Server » Performance Tuning » Index for Order by clause
Index for Order by clause [message #255148] Mon, 30 July 2007 16:59 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I have to tune following sql which has cost 1830. I am running oracle 9i and table and indexes are analyzed.

[B]SELECT b.s_name, D.login, d.f_name, d.l_name, e.desc
FROM CT_E_Stk_MAP a, ct_stk b, e_info c, user d, e_code e 
WHERE a.ct_stk_id = b.ct_stk_id 
  AND a.user = c.user
  AND a.user= d.id 
  AND c.e_c3 = e.c_value 
  AND e.name = 'EO06Y' 
ORDER BY stk_name[/B]

[U]Table has following index[/U]
[B]ct_stk_id ==> is Pk (unique Index) for ct_stk b table and fk for CT_E_Stk_MAP a table 
user ==> is a PK (unique Index) for e_info c table and fk for CT_E_Stk_MAP a table [/B]
I have also created index for e.name and b.stk_name but it not effective so dropped.

[U]Table has following total records[/U]
[B]USER ==>	     573421	
CT_E_Stk_MAP ==>	121	
ct_stk ==>	       3110	
E_INFO==>             75945
E_CODE==>  	       7141[/B]

[U]After Index for both the table, following is from Auto Trace[/U]	
recursive calls	0
db block gets	0
consistent gets	11254
physical reads	9985


Please let me know if you need more onfo.

thanks,

Re: Index for Order by clause [message #255150 is a reply to message #255148] Mon, 30 July 2007 17:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
does the following perform any better (or worse)?
SELECT b.s_name, D.login, d.f_name, d.l_name, e.desc
FROM ct_stk b,  user d, e_code e 
WHERE e.name = 'EO06Y'
  AND  EXISTS (select '1' from  CT_E_Stk_MAP a ,  e_info c
                          where a.ct_stk_id = b.ct_stk_id 
                           AND  a.user = c.user
                           AND  a.user = d.id 
                           AND  c.e_c3 = e.c_value
              )
ORDER BY stk_name

[Updated on: Mon, 30 July 2007 18:00] by Moderator

Report message to a moderator

Re: Index for Order by clause [message #255170 is a reply to message #255150] Mon, 30 July 2007 23:03 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How about giving us an Explain Plan.
Re: Index for Order by clause [message #255221 is a reply to message #255170] Tue, 31 July 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And Oracle version with 4 decimals.

Regards
Michel
Re: Index for Order by clause [message #255345 is a reply to message #255170] Tue, 31 July 2007 08:56 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks. We have Oracle 9i R2 with latest patch applied on sun solaris.

[U][B]Explain plan[/B][/U]
[B][U]Operation[/U] [U]Object Name [/U][U]Rows[/U] [U]Bytes[/U] [U]Cost[/U][/B]
SELECT STATEMENT Optimizer Mode=CHOOSE 2 K 1831
SORT ORDER BY 2 K 346 K 1831
HASH JOIN 2 K 346 K 1756
TABLE ACCESS FULL E_CODE 420 11 K 10
HASH JOIN 3 K 314 K 1745
TABLE ACCESS FULL ct_stk 127 4 K 2
HASH JOIN 3 K 213 K 1742
HASH JOIN 3 K 167 K 1302
TABLE ACCESS FULL CT_E_Stk_MAP 3 K 57 K 5
TABLE ACCESS FULL USER 573 K 19 M 1264
TABLE ACCESS FULL E_INFO 76 K 1 M 432

Thanks, 
Re: Index for Order by clause [message #255356 is a reply to message #255150] Tue, 31 July 2007 09:12 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks, it has same cost from explain plan.
Re: Index for Order by clause [message #255362 is a reply to message #255345] Tue, 31 July 2007 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Do you think it is formatted?
I just see a mess.

Regards
Michel
Re: Index for Order by clause [message #255380 is a reply to message #255362] Tue, 31 July 2007 11:00 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Sorry, didn't know how to format here so i copied in excel and pasted here.
Re: Index for Order by clause [message #255385 is a reply to message #255380] Tue, 31 July 2007 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
didn't know how to format here so i copied in excel and pasted here.

Use notepad and insert spaces.

Regards
Michel
Re: Index for Order by clause [message #255399 is a reply to message #255385] Tue, 31 July 2007 12:26 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thank you sir. I hope note pad will work the best.

Operation				Object Name	Rows	Bytes	Cost

SELECT STATEMENT Optimizer Mode=CHOOSE			  2 K	 	1831
  SORT ORDER BY						  2 K	346 K	1831
    HASH JOIN						  2 K	346 K	1756
      TABLE ACCESS FULL			E_CODE		420	 11 K	10
      HASH JOIN						  3 K	314 K	1745
        TABLE ACCESS FULL		ct_stk		127	  4 K	2
        HASH JOIN					  3 K	213 K	1742
          HASH JOIN					  3 K	167 K	1302
            TABLE ACCESS FULL		CT_E_Stk_MAP 	  3 K	 57 K	5
            TABLE ACCESS FULL		USER		573 K	 19 M	1264
          TABLE ACCESS FULL		E_INFO		 76 K	  1 M	432

Re: Index for Order by clause [message #255409 is a reply to message #255399] Tue, 31 July 2007 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Good. And then?

Regards
Michel
Re: Index for Order by clause [message #255412 is a reply to message #255409] Tue, 31 July 2007 14:24 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Michel,
I didn't understand your question mark for and then?
Could you please explain?
Re: Index for Order by clause [message #255415 is a reply to message #255412] Tue, 31 July 2007 14:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And then what is the problem?

Regards
Michel
Re: Index for Order by clause [message #255433 is a reply to message #255415] Tue, 31 July 2007 20:11 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Just qurious about overall cost as query is not that much slow.
Re: Index for Order by clause [message #255452 is a reply to message #255148] Tue, 31 July 2007 23:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
COST is only relevant when comparing SQL statements that produce the EXACT same results.

It is meaningless to compare SQL_A with a cost of 1776 to SQL_B with a cost 1492 when they produce DIFFERENT result set.

It is the same as asking, "which is better 3 or red?".

If you don't believe me, just Ask Tom!

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:313416745628
Re: Index for Order by clause [message #255717 is a reply to message #255452] Wed, 01 August 2007 10:14 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks. I am not comparing two different queries which produce two different resultset.
I am just comparing cost. My question is if i can't avoid to use ORDER BY clause then how i can tune the query?
Re: Index for Order by clause [message #255720 is a reply to message #255148] Wed, 01 August 2007 10:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
In some/many/most cases, SQL performance may be improved by having an index on the columns in the WHERE clause.
Re: Index for Order by clause [message #255724 is a reply to message #255720] Wed, 01 August 2007 10:33 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks once again. I have created index on where clause and as well as ORDER BY clause but cost was almost same so I just dropped. I have also updated statistics after creating index.
Re: Index for Order by clause [message #255814 is a reply to message #255724] Wed, 01 August 2007 17:38 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If this is the query:
SELECT b.s_name, D.login, d.f_name, d.l_name, e.desc
FROM CT_E_Stk_MAP a, ct_stk b, e_info c, user d, e_code e 
WHERE a.ct_stk_id = b.ct_stk_id 
  AND a.user = c.user
  AND a.user= d.id 
  AND c.e_c3 = e.c_value 
  AND e.name = 'EO06Y' 
ORDER BY stk_name


If all of the joins are on unique keys, and the SQL returns fewer than 50K rows, then it should benefit from the following:
Index E_CODE(NAME)
Index E_INFO(E_C3)
Index CT_E_STK_MAP(CT_STK_ID)
Index CT_STK(CT_STK_ID)
Index USER(ID)

If the joins are on Non-Unique keys, or if the SQL returns more than 50K rows, then all bets are off. The indexes MIGHT help, but the Full Table Scans may be the best bet.

Don't forget to gather statistics for the indexes, otherwise Oracle will make bad decisions.

Ross Leishman
Re: Index for Order by clause [message #257652 is a reply to message #255814] Wed, 08 August 2007 22:57 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks.
Previous Topic: Error during Merge Statment
Next Topic: dead ORADISM process
Goto Forum:
  


Current Time: Tue Dec 03 15:32:43 CST 2024