Home » RDBMS Server » Performance Tuning » Help Please
Help Please [message #278643] Mon, 05 November 2007 04:26 Go to next message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Dear All,

Can any one help me in figuring out the probelm with the table index? There is a table called account_link and there are four indexes associated with the table. Iam running an select query and trying to find out the time taken. I found time taken when i pass an index hint to the select query is more than the time taken without index hint in the query.

The other table marketing_lead is properly indexed. i doubt on the account_link table index.


IndexName                 columns
ALK_PK	     ORG_NO, CRACCT_NO, APP_NO, MKT_LEAD_NO	
ALK_UK	     ORG_NO, CRACCT_NO,RAC_ASSO_ROLE_NO,PRAC_ASSO_SEQ_NO
ALK_UK2	     ORG_NO, APP_NO, MKT_LEAD_NO	
ALK_INDX1    MKT_LEAD_NO		



the query which i run (without passing index)is

SELECT count(al.app_no)
    FROM account_link al,
         marketing_lead ml
   WHERE al.org_no in (SELECT org_no FROM product_account)
     AND al.cracct_no in (SELECT cracct_no FROM product_account)
     AND al.prac_asso_role_no = 1
     AND al.org_no = ml.org_no
     AND al.mkt_lead_no = ml.mkt_lead_no;



time taken is 15 seconds for 585000 records.

query with ALK_UK index hint

SELECT /*+ index (ALK_UK) */ count(al.app_no)
    FROM account_link al,
         marketing_lead ml
   WHERE al.org_no in (SELECT org_no FROM product_account)
     AND al.cracct_no in (SELECT cracct_no FROM product_account)
     AND al.prac_asso_role_no = 1
     AND al.org_no = ml.org_no
     AND al.mkt_lead_no = ml.mkt_lead_no;


Time taken is 17 seconds

query with ALK_PK index hint

SELECT /*+ index (ALK_PK) */ count(al.app_no)
    FROM account_link al,
         marketing_lead ml
   WHERE al.org_no in (SELECT org_no FROM product_account)
     AND al.cracct_no in (SELECT cracct_no FROM product_account)
     AND al.prac_asso_role_no = 1
     AND al.org_no = ml.org_no
     AND al.mkt_lead_no = ml.mkt_lead_no;


Time taken : 16.586 seconds

Iam using SQL developer to find out the time consumed for each and every query.

Thanks
Balaji

[Updated on: Mon, 05 November 2007 04:29]

Report message to a moderator

Re: Help Please [message #278651 is a reply to message #278643] Mon, 05 November 2007 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I found time taken when i pass an index hint to the select query is more than the time taken without index hint in the query.

Then don't use the hint. Why do you want to use it?

By the way, I don't see any difference between the time 15, 17, 16.586 is the same thing from a client point of view, just in the margin of error from network, client workload and so on variations.

Regards
Michel
Re: Help Please [message #278684 is a reply to message #278651] Mon, 05 November 2007 07:02 Go to previous messageGo to next message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Hi Michael,

Thank you for the response. I believe in the production DB we have more number of records and hence i thought to use some of the indexes already available to make the query execute faster.


Regards,
Balaji
Re: Help Please [message #278705 is a reply to message #278684] Mon, 05 November 2007 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Using index does not mean query is faster.
You have to use index when it is correct to use it.
Just keep your statistics up to date and let the optimizer does its job. Then come back if it is wrong.

Regards
Michel

[Updated on: Mon, 05 November 2007 15:54]

Report message to a moderator

Re: Help Please [message #278817 is a reply to message #278643] Mon, 05 November 2007 15:27 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Is it possible to create FKs from ACCOUNT_LINK to product_account table for org_no and cracct_no columns?

In that case you will be able to remove both IN subselects.

Michael
Re: Help Please [message #278822 is a reply to message #278643] Mon, 05 November 2007 15:46 Go to previous message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
SELECT count(al.app_no)
FROM account_link al
WHERE al.org_no in (SELECT org_no FROM product_account)
  AND al.cracct_no in (SELECT cracct_no FROM product_account)
  AND al.prac_asso_role_no = 1
  AND al.org_no IN (SELECT ml.org_no from marketing_lead ml
                    WHERE al.mkt_lead_no = ml.mkt_lead_no
                   )
/


How does the query above perform?
I hope product_account.org_no, product_account.cracct_no, al.prac_asso_role_no, al.org, al.mkt_lead_no & ml.mkt_lead_no are indexed.
Previous Topic: Help needed in querying View
Next Topic: sql slow
Goto Forum:
  


Current Time: Wed Dec 07 04:43:43 CST 2016

Total time taken to generate the page: 0.11136 seconds