OraFAQ Forum: Performance Tuning » Index not used by SQL

Home » RDBMS Server » Performance Tuning » Index not used by SQL

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

Index not used by SQL [message #65551]

Mon, 25 October 2004 21:11

Milind Deshpande
Messages: 93
Registered: May 2004

Member

Hi All,

I have a SQL statement like this:-

select distinct
/* [[[[[[CTA.CROW.Company.Affiliation.Search]]]]]] */
BUS.BUS_NAME_ID BusinessNameId,
ENT.ENTITY_ID EntityId,
ENT.TRUE_NAME TrueName,
BUS.BUS_NAME Name,
BN.BUS_NAME_TYPE NameType,
BUS.BUS_NAME_TYPE_CD NameTypeId,
JUR.JURIS_SHORT_NAME Jurisdiction,
ET.ENTITY_TYPE_SHORT EntityType,
ENT.ENTITY_TYPE_CD EntityTypeId,
ES.ENTITY_STATUS Status,
ENT.FORMATION_DATE FormationDate,
NVL(ENT.FEDERAL_TAX_ID, ' ') FederalID
from
ARV_AFFL_MEMBERSHIP AFM,
ARV_BUSINESS_NAME BUS,
ARV_ENTITY ENT,
ARV_REPRESENTATION REP,
ARV_ENTITY_STATUS ES,
ARV_JURISDICTION JUR,
ARV_BUS_NAME_TYPE BN,
ARV_ENTITY_TYPE ET
where
AFM.ENTITY_ID = ENT.ENTITY_ID and
ENT.ENTITY_ID = BUS.ENTITY_ID and
BUS.BUS_NAME_TYPE_CD = BN.BUS_NAME_TYPE_CD and
BUS.BUS_NAME_TYPE_CD in (5001, 5003) and
ENT.ENTITY_ID = REP.ENTITY_ID(+) and
ENT.DOM_JURIS_ID = JUR.JURIS_ID and
ENT.ENTITY_TYPE_CD = ET.ENTITY_TYPE_CD and
ENT.ENTITY_STATUS_CD = ES.ENTITY_STATUS_CD
AND ENT.ENTITY_STATUS_CD = 2007 --byCompanyStatus

order by Name

Whenever I apply the condition on Entity_status_cd in the query there is a Full Table scan on the Tentity table.Entity_status_cd is an indexed column then also the optimiser is not using the Index.I have analyzed the Index as well as the Table.But,whenever I create a new Index on this column by dropping the older one the optimiser uses it. Why it is so.Is there any changes required in the INIT.ora or there is anything else.Pls. help me on this cause this kind of a problem I am facing with lot of queries.

Thanks in advance

Milind

Report message to a moderator

Re: Index not used by SQL [message #65552 is a reply to message #65551]

Mon, 25 October 2004 22:00

Frank
Messages: 7901
Registered: March 2000

Senior Member

If you create a new index (i.e. NOT analyzed ?!) then the index is used; whenever you analyze the index, the optimizer refuses to use it.
I would say that the optimizer decides that the cardinality of the index is such that a full table scan is more efficient.
What percentage of all entity_status_code = 2007 ??

(index does not equal fast; FTS is not always bad)

hth

Report message to a moderator

Re: Index not used by SQL [message #65557 is a reply to message #65551]

Tue, 26 October 2004 03:18

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

Senior Member
Account Moderator

>>But,whenever I create a new Index on this column by dropping the older one the optimiser uses it

When you drop the old index all stats gathered by analyze command are gone.
So you need to analyze the the index again.
an FTS (full table scan) is not always harmfull.

Report message to a moderator

Previous Topic:	Pl. clear my douts of nested loop join.
Next Topic:	Leading hint

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 13:39:31 CDT 2025