| 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
|
|
|
|
| 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
|
|
|
|
| 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
