Home » RDBMS Server » Performance Tuning » Index not used by SQL
Index not used by SQL [message #65551] Mon, 25 October 2004 21:11 Go to next message
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
Re: Index not used by SQL [message #65552 is a reply to message #65551] Mon, 25 October 2004 22:00 Go to previous messageGo to next message
Frank
Messages: 7880
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
Re: Index not used by SQL [message #65557 is a reply to message #65551] Tue, 26 October 2004 03:18 Go to previous message
Mahesh Rajendran
Messages: 10694
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.
Previous Topic: Pl. clear my douts of nested loop join.
Next Topic: Leading hint
Goto Forum:
  


Current Time: Tue Sep 22 07:34:03 CDT 2020