Otimizar Select no Oracle

[cfa]

Boa tarde galera,

Estou iniciando no Oracle, estou utilizando Oracle 9i, Minha dúvida é a seguinte:

eu tenho 3 tabelas: infracao, multa, lote.

Estou efetuando este select:

SELECT

i.nr_serie, i.nr_faixa, i.nr_aiip, i.ds_local, i.dt_infr,i.hr_infr,

i.nr_imov, i.nr_lote, i.qt_vel_cons, i.qt_vel_per, i.qt_vel_af,

m.nr_serie, m.nr_faixa, m.nr_aiip, m.cd_enquadr, m.dt_notif, m.nr_renainf,

l.nr_lote, l.cd_cliente, l.tp_lote

FROM

infracao i, multa m, lote l

WHERE

i.cd_cliente = 6875 and i.cd_cliente = m.cd_cliente

and i.nr_serie = m.nr_serie and i.nr_faixa = m.nr_faixa

and i.nr_aiip = m.nr_aiip and i.cd_cliente = l.cd_cliente

and i.nr_lote = l.nr_lote and l.tp_lote <> 1

and m.nr_renainf is not null and m.dt_notif is null

and i.dt_ender <> '01/01/1900' and i.cd_cancel is null

and i.dt_audit is null and i.cd_usuar_aud is null

and i.id_empfoto is not null

Mas sem brincadeira, ele está levando mais de 10 minutos!

Gostaria de saber que estou fazendo de errado ou se alguém tem alguma recomendação!?

valeu!

[Denis Courcy]

Oi, cfa!

Sua pesquisa pode estar gerando um TABLE SCAN, então verifique se há indices para i.cd_cliente, m.cd_cliente, m.nr_serie, i.nr_serie, m.nr_faixa, i.cd_cliente, l.cd_cliente, i.nr_lote e l.nr_lote, que parecem ser as chaves candidatas (primárias e estrangeiras) para os relacionamentos. Se não houver índices para os relacionamentos, crie-os.

[cfa]

Olá Denis,

Estava pesquisando e tentei efetuar esta mesma consulta utilizando o INNER JOIN, mas está dando o seguinte erro: "ORA-00933: comando SQL não encerrado adequadamente".

Segue o meu SELECT para orientação!

SELECT i.nr_serie, i.nr_faixa, i.nr_aiip, i.ds_local, i.dt_infr, i.hr_infr, i.nr_imov, i.nr_lote, i.qt_vel_cons, i.qt_vel_per, i.qt_vel_af,

m.nr_serie, m.nr_faixa, m.nr_aiip, m.cd_enquadr, m.dt_notif, m.nr_renainf,

l.nr_lote, l.cd_cliente, l.tp_lote

FROM infracao i

INNER JOIN multa m

on i.cd_cliente = m.cd_cliente

on i.nr_serie = m.nr_serie

on i.nr_faixa = m.nr_faixa

on i.nr_aiip = m.nr_aiip

INNER JOIN lote l

on i.nr_lote = l.nr_lote

on i.cd_cliente = l.cd_cliente

on l.tp_lote <> 1

on m.nr_renainf is not null

on m.dt_notif is null

on i.dt_ender <> '01/01/1900'

on i.cd_cancel is null

on i.dt_audit is null

on i.cd_usuar_aud is null

on i.id_empfoto is not null;

Sou realmente bem iniciante em banco de dados, estou estudando e pesquisando, peço que o pessoal do fórum tenha paciência, desde já obrigado pela ajuda!

valeu!

[Denis Courcy]

Oi 'cfa'

Você poderia, por favor, passar a estrutura destas tabelas, incluindo seus índices para que eu possa auxiliá-lo ce forma correta de montar esta estrutura? A razão deste pedido é que não estou conseguindo determinar as chaves candidatas aos relacionamentos e, por conseqüência, não consigo orientá-lo.

[cfa]

Oi 'cfa'

Você poderia, por favor, passar a estrutura destas tabelas, incluindo seus índices para que eu possa auxiliá-lo ce forma correta de montar esta estrutura? A razão deste pedido é que não estou conseguindo determinar as chaves candidatas aos relacionamentos e, por conseqüência, não consigo orientá-lo.

Olá Denis, desculpe a demora da resposta!

Segue conforme solicitado, as tabelas são relativamente grandes!

TABELA: INFRACAO

ColumnName DataType PrimaryKey NotNull Flags Default Value Comment AutoInc

QT_VAL_AFER BIGINT

QT_VAL_CONS BIGINT

QT_VAL_PER BIGINT

CD_USUAR_CAN VARCHAR(12)

NR_SEQ_PROPR BIGINT

ID_FIS_JUR VARCHAR(1)

DT_LIMITE_JARI DATETIME(7)

TP_PGU BIGINT

DT_ATUALIZA DATETIME(7)

NM_BAIRRO VARCHAR(25)

NR_CHASSI VARCHAR(21)

SG_UFPLACA VARCHAR(2)

ID_ASSINOU DECIMAL(22, 1)

DT_APROV DATETIME(7)

CD_USUAR_APR VARCHAR(10)

ID_INFRAT DECIMAL(22, 1)

DS_LINHA VARCHAR(30)

CD_EMPRES BIGINT

DS_REFER VARCHAR(40)

NR_KMINFR DECIMAL(22, 12)

CD_RODOV VARCHAR(6)

CD_MUNIC_MUL BIGINT

TP_VEIC BIGINT

CD_ESPEC BIGINT

CD_MARCA BIGINT

NR_RENAVAN BIGINT

NR_CPF DECIMAL(22, 16)

QT_PERM_BAFO DECIMAL(22, 12)

NR_EQUIP VARCHAR(15)

DS_MED_BAFO VARCHAR(6)

DT_ENDER DATETIME(7)

NR_RADAR VARCHAR(15)

QT_PESO_AF DECIMAL(22, 5)

QT_VEL_PER DECIMAL(22, 5)

QT_VEL_AF DECIMAL(22, 16)

DT_INCLUS DATETIME(7)

DT_ALTER DATETIME(7)

CD_MUNICMUL BIGINT

CD_PLACAMUL VARCHAR(7)

CD_USUAR VARCHAR(10)

CD_REGIAO VARCHAR(5)

NR_LAUDO VARCHAR(15)

DT_LAUDO DATETIME(7)

ID_EMPFOTO BIGINT 0

DT_LIMITE_REC DATETIME(7)

DS_OBSERV VARCHAR(100)

QT_PESO_PER DECIMAL(22, 5)

QT_VEL_CONS DECIMAL(22, 5)

DT_LIMITE_CON DATETIME(7)

NR_DIGVER DECIMAL(22, 5)

CD_BAIXA_AIT DECIMAL(22, 5)

CD_PELOTAO VARCHAR(5)

CD_COR BIGINT

NR_REGENV DECIMAL(22, 5)

CD_USUAR_AUD VARCHAR(10)

DT_AUDIT DATETIME(7)

CD_CATEG BIGINT

NM_INFRAT VARCHAR(40)

CD_CLIENTE DECIMAL(22, 5)

NR_SERIE VARCHAR(1)

NR_FAIXA DECIMAL(22, 5)

NR_AIIP BIGINT

CD_CLIENTE_L DECIMAL(22, 5)

CD_CANCEL DECIMAL(22, 5)

DT_CANCEL DATETIME(7)

CD_MUNIC BIGINT

CD_PLACA VARCHAR(7)

DS_LOCAL VARCHAR(80)

CD_LOGR DECIMAL(22, 5)

NR_IMOV BIGINT

CD_BAIRRO DECIMAL(22, 5)

NR_LOTE BIGINT

CD_AGENTE BIGINT

DS_MARCA VARCHAR(35)

DS_COR VARCHAR(25)

DT_INFR DATETIME(7)

HR_INFR DATETIME(7)

NR_CDFOTO DECIMAL(22, 5)

ID_DIRFOTO DECIMAL(22, 5)

NR_SEQFOTO DECIMAL(22, 5)

DS_MOD_BAFO VARCHAR(6)

IndexName IndexType Columns

IX_CLI_PLC_END Index CD_CLIENTE

CD_PLACA

DT_ENDER

IX_INFRACAO Index NR_AIIP

IX_INFRACAO_01 Index CD_PLACA

DT_INFR

HR_INFR

IX_INFRACAO_02 Index CD_CLIENTE

NR_SERIE

NR_FAIXA

NR_AIIP

DT_ENDER

DT_CANCEL

IX_INFRACAO_03 Index CD_CLIENTE

NR_LOTE

CD_CANCEL

DT_ENDER

TABELA: LOTE

ColumnName DataType PrimaryKey NotNull Flags Default Value Comment AutoInc

ID_ARQECTBOL CHAR(12)

CD_USUAR_CAN VARCHAR(12)

DT_ATUALIZA DATETIME(7)

NR_LOTE BIGINT PK NN

CD_CLIENTE DECIMAL(22, 5) PK NN

TP_LOTE DECIMAL(22, 5)

DT_INIC DATETIME(7)

DT_FECH DATETIME(7)

DT_CANCEL DATETIME(7)

DT_ENDER DATETIME(7)

CD_TIPO VARCHAR(4)

NR_ARQUIVO BIGINT

QT_LOTE DECIMAL(22, 5)

DT_PUBLIC DATETIME(7)

DT_EMIS DATETIME(7)

DT_ENVAR DATETIME(7)

DT_ENVECT DATETIME(7)

DT_ENVPOST DATETIME(7)

ID_ARQECT VARCHAR(12)

DT_NOT DATETIME(7)

DT_DEVECT DATETIME(7)

DT_RETAIT DATETIME(7)

CD_USUAR VARCHAR(10)

ID_ARQ VARCHAR(12)

DT_VALID DATETIME(7)

DT_AUDIT DATETIME(7)

DT_RETEND DATETIME(7)

CD_ORIGEM VARCHAR(3)

DT_AUTO DATETIME(7)

DT_BOLETO DATETIME(7)

DT_ENVARBOL DATETIME(7)

DT_DEVECTBOL DATETIME(7)

DT_ENVECTBOL DATETIME(7)

IndexName IndexType Columns

PRIMARY PRIMARY NR_LOTE

CD_CLIENTE

TABELA: MULTA

ColumnName DataType PrimaryKey NotNull Flags Default Value Comment AutoInc

DT_RET_END DATETIME(7)

DT_RET_NOT DATETIME(7)

DT_ATUALIZA DATETIME(7)

DT_RET_BOL DATETIME(7)

SG_CORREIO VARCHAR(6)

NR_LOTEAR BIGINT

NR_ARBOL BIGINT

DT_ENVARBOL DATETIME(7)

DT_DEVARBOL DATETIME(7)

DT_ENTARBOL DATETIME(7)

CD_MTVDEVBOL BIGINT

DT_LANCARBOL DATETIME(7)

NR_LOTEARBOL BIGINT

NR_ETQ_ECT BIGINT

FL_DTARQ DATETIME(7)

NR_RENAINF BIGINT

NR_COMP_ECT_B VARCHAR(10)

NR_COMP_ECT_N VARCHAR(10)

NR_RENAINF_OLD BIGINT

DT_ESUS_REN DATETIME(7)

CD_CLIENTE DECIMAL(22, 5)

NR_SERIE VARCHAR(1)

NR_FAIXA DECIMAL(22, 5)

NR_AIIP BIGINT

CD_ENQUADR BIGINT

NR_NOTIF BIGINT

VL_MULTA DECIMAL(22, 12)

DT_PONTUA DATETIME(7)

CD_BAIXA DECIMAL(22, 5)

DT_NOTIF DATETIME(7)

DT_BOLETO DATETIME(7)

DT_VENC DATETIME(7)

NR_AR BIGINT

DT_DEVOL_AR DATETIME(7)

DT_ENTREAR DATETIME(7)

NR_GPRODESP BIGINT

ID_RET_PRODESP DECIMAL(22, 5)

NR_SEQ_DETRAN DECIMAL(22, 5)

CD_MTVDEV BIGINT

CD_SIGAR VARCHAR(2)

NM_RECEB VARCHAR(40)

NR_RGRECEB VARCHAR(12)

DT_LANCAR DATETIME(7)

CD_USUAR VARCHAR(10)

DT_POST DATETIME(7)

ID_COMPANH DECIMAL(22, 5)

QT_PTOACUM DECIMAL(22, 5)

FL_REINC VARCHAR(1)

NR_ANOBOL DECIMAL(22, 5)

IndexName IndexType Columns

IX_MULTA_00 Index CD_CLIENTE

NR_NOTIF

IX_MULTA_01 Index CD_CLIENTE

NR_SERIE

NR_FAIXA

NR_AIIP

CD_ENQUADR

NR_GPRODESP