I have been going nuts trying to figure this out...Ill just write the queries and results Im getting. All tables on JOINS are referencing their PKs and FKS (properly created/indexed) :

tables overview :

HD_OCORRENCIAS - 500.000 rows (calls/incidents table)
HD_STATUS - 10 rows (calls status table)
ORDEM_SERVICO_DIVISAO - 20 rows (calls support areas table)
HD_PROBLEMAS - 250 rows (types of calls table)
CLIENTES - 200.000 rows (clients table)


very slow query (the one I need to run on the system) :

SELECT
HD_OCORRENCIAS.NUMERO
FROM HD_OCORRENCIAS
INNER JOIN HD_STATUS ON HD_OCORRENCIAS.STATUS = HD_STATUS.STAT_ID
INNER JOIN ORDEM_SERVICO_DIVISAO ON HD_OCORRENCIAS.SISTEMA = ORDEM_SERVICO_DIVISAO.IDDIVISAO
INNER JOIN HD_PROBLEMAS ON HD_OCORRENCIAS.PROBLEMA = HD_PROBLEMAS.PROB_ID
INNER JOIN CLIENTES ON HD_OCORRENCIAS.IDCLIENTE = CLIENTES.IDCLIENTE

WHERE (HD_STATUS.STAT_PAINEL = 2)

plan used :

PLAN JOIN (CLIENTES NATURAL, HD_OCORRENCIAS INDEX (FK_HD_OCORRENCIAS_CLIENTE), HD_STATUS INDEX (PK_HD_STATUS), ORDEM_SERVICO_DIVISAO INDEX (PK_ORDEM_SERVICO_DIVISAO), HD_PROBLEMAS INDEX (PK_HD_PROBLEMAS))

executed 7.000ms (full table scan on clientes)


now, if I remove the clientes join... :

SELECT
HD_OCORRENCIAS.NUMERO
FROM HD_OCORRENCIAS
INNER JOIN HD_STATUS ON HD_OCORRENCIAS.STATUS = HD_STATUS.STAT_ID
INNER JOIN ORDEM_SERVICO_DIVISAO ON HD_OCORRENCIAS.SISTEMA = ORDEM_SERVICO_DIVISAO.IDDIVISAO
INNER JOIN HD_PROBLEMAS ON HD_OCORRENCIAS.PROBLEMA = HD_PROBLEMAS.PROB_ID
--INNER JOIN CLIENTES ON HD_OCORRENCIAS.IDCLIENTE = CLIENTES.IDCLIENTE

WHERE (HD_STATUS.STAT_PAINEL = 2)

plan used :

PLAN JOIN (HD_STATUS INDEX (HD_STATUS_IDX1), HD_OCORRENCIAS INDEX (FK_HD_OCORRENCIAS_STAT), ORDEM_SERVICO_DIVISAO INDEX (PK_ORDEM_SERVICO_DIVISAO), HD_PROBLEMAS INDEX (PK_HD_PROBLEMAS))

very fast, almost instantly.


now...ill keep the clientes table on the query. and I will remove ORDEM_SERVICO_DIVISAO and HD_PROBLEMAS :

query :

SELECT
HD_OCORRENCIAS.NUMERO
FROM HD_OCORRENCIAS
INNER JOIN HD_STATUS ON HD_OCORRENCIAS.STATUS = HD_STATUS.STAT_ID
--INNER JOIN ORDEM_SERVICO_DIVISAO ON HD_OCORRENCIAS.SISTEMA = ORDEM_SERVICO_DIVISAO.IDDIVISAO
--INNER JOIN HD_PROBLEMAS ON HD_OCORRENCIAS.PROBLEMA = HD_PROBLEMAS.PROB_ID
INNER JOIN CLIENTES ON HD_OCORRENCIAS.IDCLIENTE = CLIENTES.IDCLIENTE

WHERE (HD_STATUS.STAT_PAINEL = 2)

plan used :

PLAN JOIN (HD_STATUS INDEX (HD_STATUS_IDX1), HD_OCORRENCIAS INDEX (FK_HD_OCORRENCIAS_STAT), CLIENTES INDEX (PK_CLIENTES))


very fast again.



I havent got a clue to whats happening here. Why the optmizer is going for CLIENTES NATURAL, and why if I remove some of the joins is starts using the regular FK index??