OraFAQ Forum: Performance Tuning » URGENT:-Query not performing well

Home » RDBMS Server » Performance Tuning » URGENT:-Query not performing well

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

URGENT:-Query not performing well [message #65516]

Mon, 11 October 2004 22:55

Milind Deshpande
Messages: 93
Registered: May 2004

Member

Hi All,

I am having this query which is not performing well and taking long time to execute. I have seen the execution plan which is perfect and using Indexes and there are no Full Table Scans.

Can anybody help me in rewriting this query so that query performs well in the database.Pls. this is urgent.

select
/* [[[[[[Customer.AccountSearchDetail]]]]]] */
BusinessNameId,
CompressedName,
NameType,
OrganizationId,
EntityStatusCode,
EntityId,
EntJurisShortName,
JurisParentId,
DomJurisId,
EntityStatus,
CustomerId,
CustomerStatus,
CustAddress
from(
select /*+FIRST_ROWS DOMAIN_INDEX_NO_SORT */
NS.BUSINESS_NAME_ID BusinessNameId,
NS.COMPRESSED_NAME CompressedName,
NS.NAME_TYPE NameType,
K.ORGANIZATION_ID OrganizationId,
E.ENTITY_STATUS_CD EntityStatusCode,
E.ENTITY_ID EntityId,
J.JURIS_SHORT_NAME EntJurisShortName,
J.PARENT_ID JurisParentId,
E.DOM_JURIS_ID DomJurisId,
ES.ENTITY_STATUS EntityStatus,
null CustomerId,
null CustomerStatus,
null CustAddress
from
AV_ACCOUNT_NAME_SEARCH NS,
ARV_BUSINESS_NAME BN,
ARV_ENTITY E,
ARV_ENTITY_STATUS ES,
ARV_JURISDICTION J,
AV_ORGANIZATION_KEYS K
where
NS.SEARCH_TYPE = 'HQ' and
NS.BUSINESS_NAME_ID = BN.BUS_NAME_ID and
BN.ENTITY_ID = E.ENTITY_ID and
E.ENTITY_STATUS_CD = ES.ENTITY_STATUS_CD and
E.DOM_JURIS_ID = J.JURIS_ID and
NS.ONEWORLD_ID = K.ONEWORLD_ID

union all

select /*+FIRST_ROWS DOMAIN_INDEX_NO_SORT */
NS.BUSINESS_NAME_ID BusinessNameId,
NS.COMPRESSED_NAME CompressedName,
NS.NAME_TYPE NameType,
K.ORGANIZATION_ID OrganizationId,
null EntityStatusCode,
null EntityId,
null EntJurisShortName,
null JurisParentId,
null DomJurisId,
null EntityStatus,
C.CUSTOMER_ID CustomerId,
C.ACCOUNT_STATUS CustomerStatus,
decode(CA.CITY, null, decode(CA.STATE, null, '--', '--, ' || CA.STATE),
decode(CA.STATE, null, trim(CA.CITY) || ', --', trim(CA.CITY) || ', ' || CA.STATE)) CustAddress
from
AV_ACCOUNT_NAME_SEARCH NS,
AV_ORGANIZATION_KEYS K,
AV_CUSTOMER C,
AV_CUSTOMER_ADDRESS CA
where
NS.SEARCH_TYPE = 'HQ' and
NS.ONEWORLD_ID = K.ONEWORLD_ID and
K.ORGANIZATION_ID = C.ORGANIZATION_ID and
C.CUSTOMER_ID = CA.CUSTOMER_ID and
CA.ADDRESS_TYPE = 'Mailing')AccountDetails
where 1 = 1 and
AccountDetails.CompressedName like ARFN_CTD_COMPRESS('S') || '%' and
AccountDetails.CompressedName like '%' || ARFN_CTD_COMPRESS('S') || '%' and
(AccountDetails.EntityStatusCode = 2002 or AccountDetails.CustomerStatus = 'Active') and
trim(AccountDetails.NameType) not in ('FORMER')

Thanks in advance.

Milind.

Report message to a moderator

Re: URGENT:-Query not performing well [message #65521 is a reply to message #65516]

Tue, 12 October 2004 05:16

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Is it a necessary to use FIRST_ROWS? ( depends on your case).
except for a very few cases, FIRST_ROWS will not be useful (search operations. say like in google. You are most interested in the first page of hits in search. the following pages can take some time to be returned).

And using any hints is going to confuse CBO and if you are using a CBO, sometimes FULL TABLE SCANS are good and better than indexed scans.
Oracle recomends to leave these decision making to CBO.

Report message to a moderator

could you show your explain plan? [message #65527 is a reply to message #65521]

Fri, 15 October 2004 03:53

explain_plan
Messages: 1
Registered: October 2004

Junior Member

could you show your explain plan?

Report message to a moderator

Previous Topic:	cache data problem
Next Topic:	Demonstrating if Insertion is Faster in Partitioned or Non-Partitioned Table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 23:31:53 CST 2024