Help Please [message #278643] |
Mon, 05 November 2007 04:26 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #278684 is a reply to message #278651] |
Mon, 05 November 2007 07:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: Help Please [message #278822 is a reply to message #278643] |
Mon, 05 November 2007 15:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
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.
|
|
|