Home » RDBMS Server » Performance Tuning » URGENT:-Query not performing well
URGENT:-Query not performing well [message #65516] Mon, 11 October 2004 22:55 Go to next message
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.
Re: URGENT:-Query not performing well [message #65521 is a reply to message #65516] Tue, 12 October 2004 05:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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.
could you show your explain plan? [message #65527 is a reply to message #65521] Fri, 15 October 2004 03:53 Go to previous message
explain_plan
Messages: 1
Registered: October 2004
Junior Member
could you show your explain plan?
Previous Topic: cache data problem
Next Topic: Demonstrating if Insertion is Faster in Partitioned or Non-Partitioned Table
Goto Forum:
  


Current Time: Thu Mar 28 16:02:11 CDT 2024