Indices para serem criados apos atualização para a fase 02 (patch 99000)
Base Connector SQL:
-- Índices para TBDFENSUFAILURE
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBDFENSUFAILURE]')
AND name = N'IX_TBTBDFENSUFAILURE_1'
)
CREATE NONCLUSTERED INDEX [IX_TBTBDFENSUFAILURE_1]
ON [DBO].[TBDFENSUFAILURE]
(
[NSU] ASC,
[CNPJBASE] ASC,
[CNPJORDEM] ASC,
[CNPJDV] ASC
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
-- Índices para TBDFERESULT
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBDFERESULT]')
AND name = N'IX_TBDFERESULT_1'
)
CREATE NONCLUSTERED INDEX [IX_TBDFERESULT_1]
ON [DBO].[TBDFERESULT]
(
[NSU] ASC,
[CNPJBASE] ASC,
[CNPJORDEM] ASC,
[CNPJDV] ASC
)
INCLUDE ([REPROCESS])
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBDFERESULT]')
AND name = N'IIX_TBDFERESULT_3'
)
CREATE NONCLUSTERED INDEX [IIX_TBDFERESULT_3]
ON [DBO].[TBDFERESULT]
(
[NSU] ASC
)
INCLUDE ([REPROCESS])
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
-- Índices para TBDOCNUMBERFAILURE
IF NOT EXISTS (
SELECT 1
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBDOCNUMBERFAILURE]')
AND name = N'IX_TBDOCNUMBERFAILURE_1'
)
CREATE NONCLUSTERED INDEX [IX_TBDOCNUMBERFAILURE_1]
ON [DBO].[TBDOCNUMBERFAILURE]
(
CNPJBASE ASC,
CNPJORDEM ASC,
CNPJDV ASC,
SERIE ASC,
VERIFYDATE DESC
)
INCLUDE (
ID,
CNPJSTR,
DOCUMENTNUMBER,
DOCUMENTKEY,
EMISSIONDATE,
EMISSIONTYPE,
CSTAT,
STATISTICSID,
MODEL,
STATUS
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
-- Índice 2: pesquisa por CNPJ, série, número do documento e modelo
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBDOCNUMBERFAILURE]')
AND name = N'IX_TBDOCNUMBERFAILURE_2'
)
CREATE NONCLUSTERED INDEX [IX_TBDOCNUMBERFAILURE_2]
ON [DBO].[TBDOCNUMBERFAILURE]
(
CNPJBASE ASC,
CNPJORDEM ASC,
CNPJDV ASC,
SERIE ASC,
DOCUMENTNUMBER ASC,
MODEL ASC
)
INCLUDE (ID)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
-- Índice 3: status + VerifyDate (ordem DESC para VerifyDate)
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBDOCNUMBERFAILURE]')
AND name = N'IX_TBDOCNUMBERFAILURE_3'
)
CREATE NONCLUSTERED INDEX [IX_TBDOCNUMBERFAILURE_3]
ON [DBO].[TBDOCNUMBERFAILURE]
(
STATUS ASC,
VERIFYDATE DESC
)
INCLUDE (
ID,
CNPJBASE,
CNPJORDEM,
CNPJDV,
SERIE,
DOCUMENTNUMBER
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
-- Índices para TBLOGDOCUMENT
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBLOGDOCUMENT]')
AND name = N'IX_TBLOGDOCUMENT_2'
)
CREATE NONCLUSTERED INDEX [IX_TBLOGDOCUMENT_2]
ON [DBO].[TBLOGDOCUMENT]
(
[CNPJEMITBASE] ASC,
[CNPJEMITORDEM] ASC,
[CNPJEMITDV] ASC,
[MODEL] ASC,
[SERIE] ASC,
[DOCUMENTNUMBER] ASC,
[EMISSIONTYPE] ASC
)
INCLUDE (
[LOGDOCID],
[JOBID],
[HIDDEN],
[DOCUMENTSTATUS],
[DOCUMENTDATE],
[EMISSIONDATE],
[ALLOWDELETE],
[ACCESSKEY],
[CNPJEMITSTR],
[ERPCONTROL],
[DEST],
[TOTALVALUE],
[TOTALVALUEDFE],
[XPED],
[DESTNAME]
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBLOGDOCUMENT]')
AND name = N'IIX_TBLOGDOCUMENT_3'
)
CREATE NONCLUSTERED INDEX [IIX_TBLOGDOCUMENT_3]
ON [DBO].[TBLOGDOCUMENT]
(
[JOBID] ASC,
[CNPJEMITBASE] ASC,
[CNPJEMITORDEM] ASC,
[CNPJEMITDV] ASC,
[MODEL] ASC,
[SERIE] ASC,
[DOCUMENTNUMBER] ASC
)
INCLUDE (
[LOGDOCID],
[HIDDEN],
[EMISSIONTYPE],
[DOCUMENTSTATUS],
[LASTINTERACTION]
)
WHERE [HIDDEN] = 0
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBLOGDOCUMENT]')
AND name = N'IX_TBLOGDOCUMENT_8'
)
CREATE NONCLUSTERED INDEX [IX_TBLOGDOCUMENT_8]
ON [DBO].[TBLOGDOCUMENT]
(
[SERIE] ASC,
[DOCUMENTNUMBER] ASC
)
INCLUDE (
[LOGDOCID],
[JOBID],
[CNPJEMITBASE],
[CNPJEMITORDEM],
[CNPJEMITDV]
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBLOGDOCUMENT]')
AND name = N'IX_TBLOGDOCUMENT_9'
)
CREATE NONCLUSTERED INDEX [IX_TBLOGDOCUMENT_9]
ON [DBO].[TBLOGDOCUMENT]
(
[LOGDOCID] ASC
)
INCLUDE (
[DOCUMENTNUMBER],
[DOCUMENTDATE],
[EMISSIONDATE],
[ACCESSKEY],
[JOBID],
[HIDDEN],
[DOCUMENTSTATUS],
[CNPJEMITSTR],
[XPED],
[DESTNAME],
[SERIE],
[LASTINTERACTION],
[LASTREJECTION]
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBLOGDOCUMENT]')
AND name = N'IX_TBLOGDOCUMENT_10'
)
CREATE NONCLUSTERED INDEX [IX_TBLOGDOCUMENT_10]
ON [DBO].[TBLOGDOCUMENT]
(
[JOBID] ASC,
[EMISSIONTYPE] ASC,
[MODEL] ASC,
[SERIE] ASC,
[DOCUMENTNUMBER] ASC,
[CNPJEMITBASE] ASC
)
INCLUDE (
[ACCESSKEY],
[DOCUMENTSTATUS],
[HIDDEN],
[ALLOWDELETE],
[DOCUMENTDATE],
[CNPJEMITSTR],
[CNPJEMITDV]
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
-- Constraint única em TBNDDJOBFILIAL (UC_FILIAL)
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBNDDJOBFILIAL]')
AND name = N'UC_FILIAL'
)
ALTER TABLE [DBO].[TBNDDJOBFILIAL]
ADD CONSTRAINT [UC_FILIAL] UNIQUE NONCLUSTERED (
[CNPJBASE] ASC,
[CNPJORDEM] ASC,
[CNPJDV] ASC,
[ENTITYTYPE] ASC
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
) ON [PRIMARY];
-- Índices para TBPIX
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBPIX]')
AND name = N'IX_TBPIX_4'
)
CREATE NONCLUSTERED INDEX [IX_TBPIX_4]
ON [DBO].[TBPIX]
(
[PROCESSSTATUS] ASC,
[STARTDATE] ASC
)
INCLUDE (
[ID],
[ACCESSKEY],
[CNPJSTR],
[LOGDOCID],
[STATUSDESCRIPTION],
[ENDDATE]
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBPIX]')
AND name = N'IX_TBPIX_5'
)
CREATE NONCLUSTERED INDEX [IX_TBPIX_5]
ON [DBO].[TBPIX]
(
[DOCUMENTNUMBER] ASC,
[CNPJBASE] ASC,
[CNPJORDEM] ASC,
[CNPJDV] ASC,
[EMISSIONTYPE] ASC,
[MODEL] ASC,
[SERIE] ASC
)
INCLUDE (
[ID],
[ACCESSKEY],
[CNPJSTR],
[PROCESSSTATUS],
[STARTDATE],
[ENDDATE]
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBPIX]')
AND name = N'IX_TBPIX_6'
)
CREATE NONCLUSTERED INDEX [IX_TBPIX_6]
ON [DBO].[TBPIX]
(
[ACCESSKEY] ASC
)
INCLUDE (
[ID],
[PROCESSSTATUS],
[CNPJSTR]
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
-- Índices para TBREGISTERCONSULT
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBREGISTERCONSULT]')
AND name = N'IX_TBREGISTERCONSULT_1'
)
CREATE NONCLUSTERED INDEX [IX_TBREGISTERCONSULT_1]
ON [DBO].[TBREGISTERCONSULT]
(
[CNPJDESTBASE] ASC,
[CNPJDESTORDEM] ASC,
[CNPJDESTDV] ASC,
[CONSULTDATE] DESC
)
INCLUDE (
[ID],
[XNOME],
[CNPJDESTSTR],
[CPFDEST],
[IE],
[STATUS],
[DOMAIN],
[USERNAME]
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
-- Índices para TBSTATISTICS
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBSTATISTICS]')
AND name = N'TBSTATISTICS_IDX1'
)
CREATE NONCLUSTERED INDEX [TBSTATISTICS_IDX1]
ON [DBO].[TBSTATISTICS]
(
[DOCTYPE] ASC,
[MODEL] ASC,
[SERIE] ASC,
[DOCNUM] ASC,
[CNPJBASE] ASC,
[CNPJORDEM] ASC,
[CNPJDV] ASC,
[KIND] ASC,
[TPAMB] ASC
)
INCLUDE (
[ACCESSKEY],
[STATISTICSDATE],
[EMISSIONDATE],
[PROCESSED],
[ABORTED],
[CUF],
[EMISSIONTYPE],
[STATISTICSID]
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBSTATISTICS]')
AND name = N'TBSTATISTICS_IDX3'
)
CREATE NONCLUSTERED INDEX [TBSTATISTICS_IDX3]
ON [DBO].[TBSTATISTICS]
(
[DOCTYPE] ASC,
[TPAMB] ASC,
[STATISTICSDATE] ASC,
[EMISSIONDATE] ASC
)
INCLUDE (
[ACCESSKEY],
[MODEL],
[SERIE],
[DOCNUM],
[CNPJBASE],
[CNPJORDEM],
[CNPJDV],
[KIND],
[PROCESSED],
[ABORTED]
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBSTATISTICS]')
AND name = N'TBSTATISTICS_IDX4'
)
CREATE NONCLUSTERED INDEX [TBSTATISTICS_IDX4]
ON [DBO].[TBSTATISTICS]
(
[DOCTYPE] ASC,
[CNPJBASE] ASC,
[CNPJORDEM] ASC,
[CNPJDV] ASC,
[SERIE] ASC,
[DOCNUM] ASC,
[CUF] ASC,
[TPAMB] ASC
)
INCLUDE (
[ACCESSKEY],
[STATISTICSDATE],
[EMISSIONDATE],
[PROCESSED],
[ABORTED]
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[TBSTATISTICS]')
AND name = N'TBSTATISTICS_IDX5'
)
CREATE NONCLUSTERED INDEX [TBSTATISTICS_IDX5]
ON [DBO].[TBSTATISTICS]
(
[CNPJBASE] ASC,
[CNPJORDEM] ASC,
[CNPJDV] ASC,
[SERIE] ASC,
[DOCNUM] ASC,
[DOCTYPE] ASC,
[TPAMB] ASC
)
INCLUDE (
[MODEL],
[KIND],
[CUF],
[EMISSIONTYPE],
[EMISSIONDATE],
[STATISTICSDATE],
[ABORTED],
[PROCESSED],
[ACCESSKEY],
[CNPJSTR],
[CNPJDESTSTR],
[CNPJDESTBASE],
[CNPJDESTORDEM],
[CNPJDESTDV]
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
Base Connector Oracle:
--------------------------------------------------------------------------------
-- TBDFENSUFAILURE
--------------------------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TBTBDFENSUFAILURE_1
ON TBDFENSUFAILURE (
NSU,
CNPJBASE,
CNPJORDEM,
CNPJDV
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
--------------------------------------------------------------------------------
-- TBDFERESULT
--------------------------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TBDFERESULT_1
ON TBDFERESULT (
NSU,
CNPJBASE,
CNPJORDEM,
CNPJDV,
REPROCESS
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IIX_TBDFERESULT_3
ON TBDFERESULT (
NSU,
REPROCESS
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
--------------------------------------------------------------------------------
-- TBDOCNUMBERFAILURE
--------------------------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TBDOCNUMBERFAILURE_1
ON TBDOCNUMBERFAILURE (
CNPJBASE,
CNPJORDEM,
CNPJDV,
SERIE,
VERIFYDATE DESC
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TBDOCNUMBERFAILURE_2
ON TBDOCNUMBERFAILURE (
CNPJBASE,
CNPJORDEM,
CNPJDV,
SERIE,
DOCUMENTNUMBER,
MODEL
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TBDOCNUMBERFAILURE_3
ON TBDOCNUMBERFAILURE (
STATUS,
VERIFYDATE DESC
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
--------------------------------------------------------------------------------
-- TBLOGDOCUMENT
--------------------------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TBLOGDOCUMENT_2
ON TBLOGDOCUMENT (
CNPJEMITBASE,
CNPJEMITORDEM,
CNPJEMITDV,
MODEL,
SERIE,
DOCUMENTNUMBER,
EMISSIONTYPE
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IIX_TBLOGDOCUMENT_3
ON TBLOGDOCUMENT (
JOBID,
CNPJEMITBASE,
CNPJEMITORDEM,
CNPJEMITDV,
MODEL,
SERIE,
DOCUMENTNUMBER
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TBLOGDOCUMENT_8
ON TBLOGDOCUMENT (
SERIE,
DOCUMENTNUMBER
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TBLOGDOCUMENT_10
ON TBLOGDOCUMENT (
JOBID,
EMISSIONTYPE,
MODEL,
SERIE,
DOCUMENTNUMBER,
CNPJEMITBASE
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
--------------------------------------------------------------------------------
-- TBPIX
--------------------------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TBPIX_4
ON TBPIX (
PROCESSSTATUS,
STARTDATE
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TBPIX_5
ON TBPIX (
DOCUMENTNUMBER,
CNPJBASE,
CNPJORDEM,
CNPJDV,
EMISSIONTYPE,
MODEL,
SERIE
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TBPIX_6
ON TBPIX (
ACCESSKEY
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
--------------------------------------------------------------------------------
-- TBREGISTERCONSULT
--------------------------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TBREGISTERCONSULT_1
ON TBREGISTERCONSULT (
CNPJDESTBASE,
CNPJDESTORDEM,
CNPJDESTDV,
CONSULTDATE DESC
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
--------------------------------------------------------------------------------
-- TBSTATISTICS
--------------------------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX TBSTATISTICS_IDX1
ON TBSTATISTICS (
DOCTYPE,
MODEL,
SERIE,
DOCNUM,
CNPJBASE,
CNPJORDEM,
CNPJDV,
KIND,
TPAMB
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX TBSTATISTICS_IDX3
ON TBSTATISTICS (
DOCTYPE,
TPAMB,
STATISTICSDATE,
EMISSIONDATE
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX TBSTATISTICS_IDX4
ON TBSTATISTICS (
DOCTYPE,
CNPJBASE,
CNPJORDEM,
CNPJDV,
SERIE,
DOCNUM,
CUF,
TPAMB
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX TBSTATISTICS_IDX5
ON TBSTATISTICS (
CNPJBASE,
CNPJORDEM,
CNPJDV,
SERIE,
DOCNUM,
DOCTYPE,
TPAMB
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
Base Cold SQL:
-- Índice para validação de documentos por série + número + ID
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[cold]')
AND name = N'IX_TABELA_IDE_SERIE_IDE_NNF_IDE_ID'
)
CREATE NONCLUSTERED INDEX [IX_TABELA_IDE_SERIE_IDE_NNF_IDE_ID]
ON [DBO].[cold]
(
[IDE_SERIE] ASC,
[IDE_NNF] ASC,
[IDE_ID] ASC
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
-- Índice para validação de CT-e por série + número + ID
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[cold]')
AND name = N'IX_TABELA_IDE_SERIE_IDE_NCT_IDE_ID'
)
CREATE NONCLUSTERED INDEX [IX_TABELA_IDE_SERIE_IDE_NCT_IDE_ID]
ON [DBO].[cold]
(
[IDE_SERIE] ASC,
[IDE_NCT] ASC,
[IDE_ID] ASC
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
-- Índice para validação de MDF-e por série + número + ID
IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[DBO].[cold]')
AND name = N'IX_TABELA_IDE_SERIE_IDE_NMDF_IDE_ID'
)
CREATE NONCLUSTERED INDEX [IX_TABELA_IDE_SERIE_IDE_NMDF_IDE_ID]
ON [DBO].[cold]
(
[IDE_SERIE] ASC,
[IDE_NMDF] ASC,
[IDE_ID] ASC
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];
Base Cold Oracle:
Subtituir o tabela pelo nome da tabela do cold
--------------------------------------------------------------------------------
-- TABELA NF-e - IDE_SERIE + IDE_NNF + IDE_ID
--------------------------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TABELA_SERIE_NNF_ID
ON TABELA (
IDE_SERIE,
IDE_NNF,
IDE_ID
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
--------------------------------------------------------------------------------
-- TABELA CTE - IDE_SERIE + IDE_NCT + IDE_ID
--------------------------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TABELA_SERIE_NCT_ID
ON TABELA (
IDE_SERIE,
IDE_NCT,
IDE_ID
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/
--------------------------------------------------------------------------------
-- TABELA MDFE - IDE_SERIE + IDE_NMDF + IDE_ID
--------------------------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX IX_TABELA_SERIE_NMDF_ID
ON TABELA (
IDE_SERIE,
IDE_NMDF,
IDE_ID
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/