OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Help Please

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help Please [message #278643]

Mon, 05 November 2007 04:26

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

Michel Cadot
Messages: 68664
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

Report message to a moderator

Re: Help Please [message #278684 is a reply to message #278651]

Mon, 05 November 2007 07:02

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

Report message to a moderator

Re: Help Please [message #278705 is a reply to message #278684]

Mon, 05 November 2007 07:41

Michel Cadot
Messages: 68664
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

michael_bialik
Messages: 621
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

Report message to a moderator

Re: Help Please [message #278822 is a reply to message #278643]

Mon, 05 November 2007 15:46

BlackSwan
Messages: 26766
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.

Report message to a moderator

Previous Topic:	Help needed in querying View
Next Topic:	sql slow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 25 01:58:47 CDT 2024