Table access Full X Indices

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Wed, 13 Jul 2005 6:57 pm

I am running the following query:
Code: Select all
SELECT   e.status, w.prioridade, w.nome, e.usuario_ant_rep, e.data_ini,
         e.data_fim, e.cod_workflow, e.prazo, e.unidade_prazo, e.cod_etapa,
         te.descricao, e.cod_tipoetapa, w.cod_tipoworkflow, e.cod_usuario
    FROM etapa e, workflow w, tipo_etapa te
   WHERE e.cod_usuario = 1
     AND (e.status = 'não' OR e.status = 'PRO')
     AND w.cod_workflow = e.cod_workflow
     AND e.cod_tipoetapa = te.cod_tipoetapa
     AND te.tipo = 1
the following indices in database: ETAPA_COD_USUARIO-> on top of table COD_USUARIO ETAPA_STATUS-> STEP over STEP TIPO_ETAPA_TIPO table STATUS-> on top of the table TYPE TIPO_ETAPA in Showplan, I noticed that the query is TABLE_ACCESS_FULL in tables STEP, WORKFLOW and TIPO_ETAPA and does not use the above indexes.

Why the query does not use indexes ETAPA_COD_USUARIO, ETAPA_STATUS and TIPO_ETAPA_TIPO?
rogers
Location: RJ

Poston Mon, 01 Aug 2005 11:54 am

Your optimizer is cost or rule?
If it's COST, the stats are updated?

Have cases that he thinks faster not to use the indexes. An example of this could be that index in the STATUS field (YES or). I don't know how many options there may be in this field, but I think not many.

Try to force the use of the index to see if it will improve with HINTS:
Code: Select all
SELECT  /*+ INDEX (seu_campo seu_indice) */
   e.status, w.prioridade, w.nome, e.usuario_ant_rep, e.data_ini,
         e.data_fim, e.cod_workflow, e.prazo, e.unidade_prazo, e.cod_etapa,
         te.descricao, e.cod_tipoetapa, w.cod_tipoworkflow, e.cod_usuario
    FROM etapa e, workflow w, tipo_etapa te
   WHERE e.cod_usuario = 1
     AND (e.status = 'não' OR e.status = 'PRO')
     AND w.cod_workflow = e.cod_workflow
     AND e.cod_tipoetapa = te.cod_tipoetapa
     AND te.tipo = 1
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: Bing [Bot] and 4 guests