OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Urgent:-SQL Help

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

Urgent:-SQL Help [message #65520]

Tue, 12 October 2004 05:07

Milind Deshpande
Messages: 93
Registered: May 2004

Member

Hi All,

Kindly help me with this SQL which is not at all performing well.There are no Full Table Scans and Indexes are being used by the query.Can anybody help me in rewriting this SQL or suggest any changes or any hint.

Pls. help me on this it 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

Previous Topic:	Server gets slow after some days of fresh import
Next Topic:	Oracle instance running on a system with low open file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 23:11:32 CST 2024