Início / dba / Perguntas / 300714
Accepted
grochu
Asked: 2021-10-07 01:26:48 +0800 CST

Qual é a melhor maneira de solicitar a tabela particionada?

  • 772

Qual é a maneira mais eficiente de obter dados ordenados de uma tabela particionada?

Eu tenho esta consulta:

SELECT TOP (4000)
*
FROM dbo.MS
ORDER BY MANDT
, MBLNR
, MJAHR
, ZEILE

A tabela é particionada por MBLNR.

Plano de execução: https://www.brentozar.com/pastetheplan/?id=Bkhv5g5EF

Existe alguma opção para se livrar do Sort do plano de execução?

Criar declaração de tabela:

CREATE TABLE [dbo].[MS]
(
    [MANDT] [NVARCHAR](3) NOT NULL
    , [MBLNR] [NVARCHAR](10) NOT NULL
    , [MJAHR] [NVARCHAR](4) NOT NULL
    , [ZEILE] [NVARCHAR](4) NOT NULL
    , [LINE_ID] [NVARCHAR](6) NOT NULL
    , [PARENT_ID] [NVARCHAR](6) NOT NULL
    , [LINE_DEPTH] [NVARCHAR](2) NOT NULL
    , [MAA_URZEI] [NVARCHAR](4) NOT NULL
    , [BWART] [NVARCHAR](3) NOT NULL
    , [XAUTO] [NVARCHAR](1) NOT NULL
    , [MATNR] [NVARCHAR](18) NOT NULL
    , [WERKS] [NVARCHAR](4) NOT NULL
    , [LGORT] [NVARCHAR](4) NOT NULL
    , [CHARG] [NVARCHAR](10) NOT NULL
    , [INSMK] [NVARCHAR](1) NOT NULL
    , [ZUSCH] [NVARCHAR](1) NOT NULL
    , [ZUSTD] [NVARCHAR](1) NOT NULL
    , [SOBKZ] [NVARCHAR](1) NOT NULL
    , [LIFNR] [NVARCHAR](10) NOT NULL
    , [KUNNR] [NVARCHAR](10) NOT NULL
    , [KDAUF] [NVARCHAR](10) NOT NULL
    , [KDPOS] [NVARCHAR](6) NOT NULL
    , [KDEIN] [NVARCHAR](4) NOT NULL
    , [PLPLA] [NVARCHAR](10) NOT NULL
    , [SHKZG] [NVARCHAR](1) NOT NULL
    , [WAERS] [NVARCHAR](5) NOT NULL
    , [DMBTR] [DECIMAL](13, 2) NOT NULL
    , [BNBTR] [DECIMAL](13, 2) NOT NULL
    , [BUALT] [DECIMAL](13, 2) NOT NULL
    , [SHKUM] [NVARCHAR](1) NOT NULL
    , [DMBUM] [DECIMAL](13, 2) NOT NULL
    , [BWTAR] [NVARCHAR](10) NOT NULL
    , [MENGE] [DECIMAL](13, 3) NOT NULL
    , [MEINS] [NVARCHAR](3) NOT NULL
    , [ERFMG] [DECIMAL](13, 3) NOT NULL
    , [ERFME] [NVARCHAR](3) NOT NULL
    , [BPMNG] [DECIMAL](13, 3) NOT NULL
    , [BPRME] [NVARCHAR](3) NOT NULL
    , [EBELN] [NVARCHAR](10) NOT NULL
    , [EBELP] [NVARCHAR](5) NOT NULL
    , [LFBJA] [NVARCHAR](4) NOT NULL
    , [LFBNR] [NVARCHAR](10) NOT NULL
    , [LFPOS] [NVARCHAR](4) NOT NULL
    , [SJAHR] [NVARCHAR](4) NOT NULL
    , [SMBLN] [NVARCHAR](10) NOT NULL
    , [SMBLP] [NVARCHAR](4) NOT NULL
    , [ELIKZ] [NVARCHAR](1) NOT NULL
    , [SGTXT] [NVARCHAR](50) NOT NULL
    , [EQUNR] [NVARCHAR](18) NOT NULL
    , [WEMPF] [NVARCHAR](12) NOT NULL
    , [ABLAD] [NVARCHAR](25) NOT NULL
    , [GSBER] [NVARCHAR](4) NOT NULL
    , [KOKRS] [NVARCHAR](4) NOT NULL
    , [PARGB] [NVARCHAR](4) NOT NULL
    , [PARBU] [NVARCHAR](4) NOT NULL
    , [KOSTL] [NVARCHAR](10) NOT NULL
    , [PROJN] [NVARCHAR](16) NOT NULL
    , [AUFNR] [NVARCHAR](12) NOT NULL
    , [ANLN1] [NVARCHAR](12) NOT NULL
    , [ANLN2] [NVARCHAR](4) NOT NULL
    , [XSKST] [NVARCHAR](1) NOT NULL
    , [XSAUF] [NVARCHAR](1) NOT NULL
    , [XSPRO] [NVARCHAR](1) NOT NULL
    , [XSERG] [NVARCHAR](1) NOT NULL
    , [GJAHR] [NVARCHAR](4) NOT NULL
    , [XRUEM] [NVARCHAR](1) NOT NULL
    , [XRUEJ] [NVARCHAR](1) NOT NULL
    , [BUKRS] [NVARCHAR](4) NOT NULL
    , [BELNR] [NVARCHAR](10) NOT NULL
    , [BUZEI] [NVARCHAR](3) NOT NULL
    , [BELUM] [NVARCHAR](10) NOT NULL
    , [BUZUM] [NVARCHAR](3) NOT NULL
    , [RSNUM] [NVARCHAR](10) NOT NULL
    , [RSPOS] [NVARCHAR](4) NOT NULL
    , [KZEAR] [NVARCHAR](1) NOT NULL
    , [PBAMG] [DECIMAL](13, 3) NOT NULL
    , [KZSTR] [NVARCHAR](1) NOT NULL
    , [UMMAT] [NVARCHAR](18) NOT NULL
    , [UMWRK] [NVARCHAR](4) NOT NULL
    , [UMLGO] [NVARCHAR](4) NOT NULL
    , [UMCHA] [NVARCHAR](10) NOT NULL
    , [UMZST] [NVARCHAR](1) NOT NULL
    , [UMZUS] [NVARCHAR](1) NOT NULL
    , [UMBAR] [NVARCHAR](10) NOT NULL
    , [UMSOK] [NVARCHAR](1) NOT NULL
    , [KZBEW] [NVARCHAR](1) NOT NULL
    , [KZVBR] [NVARCHAR](1) NOT NULL
    , [KZZUG] [NVARCHAR](1) NOT NULL
    , [WEUNB] [NVARCHAR](1) NOT NULL
    , [PALAN] [DECIMAL](11, 0) NOT NULL
    , [LGNUM] [NVARCHAR](3) NOT NULL
    , [LGTYP] [NVARCHAR](3) NOT NULL
    , [LGPLA] [NVARCHAR](10) NOT NULL
    , [BESTQ] [NVARCHAR](1) NOT NULL
    , [BWLVS] [NVARCHAR](3) NOT NULL
    , [TBNUM] [NVARCHAR](10) NOT NULL
    , [TBPOS] [NVARCHAR](4) NOT NULL
    , [XBLVS] [NVARCHAR](1) NOT NULL
    , [VSCHN] [NVARCHAR](1) NOT NULL
    , [NSCHN] [NVARCHAR](1) NOT NULL
    , [DYPLA] [NVARCHAR](1) NOT NULL
    , [UBNUM] [NVARCHAR](10) NOT NULL
    , [TBPRI] [NVARCHAR](1) NOT NULL
    , [TANUM] [NVARCHAR](10) NOT NULL
    , [WEANZ] [NVARCHAR](3) NOT NULL
    , [GRUND] [NVARCHAR](4) NOT NULL
    , [EVERS] [NVARCHAR](2) NOT NULL
    , [EVERE] [NVARCHAR](2) NOT NULL
    , [IMKEY] [NVARCHAR](8) NOT NULL
    , [KSTRG] [NVARCHAR](12) NOT NULL
    , [PAOBJNR] [NVARCHAR](10) NOT NULL
    , [PRCTR] [NVARCHAR](10) NOT NULL
    , [PS_PSP_PNR] [NVARCHAR](8) NOT NULL
    , [NPLNR] [NVARCHAR](12) NOT NULL
    , [AUFPL] [NVARCHAR](10) NOT NULL
    , [APLZL] [NVARCHAR](8) NOT NULL
    , [AUFPS] [NVARCHAR](4) NOT NULL
    , [VPTNR] [NVARCHAR](10) NOT NULL
    , [FIPOS] [NVARCHAR](14) NOT NULL
    , [SAKTO] [NVARCHAR](10) NOT NULL
    , [BSTMG] [DECIMAL](13, 3) NOT NULL
    , [BSTME] [NVARCHAR](3) NOT NULL
    , [XWSBR] [NVARCHAR](1) NOT NULL
    , [EMLIF] [NVARCHAR](10) NOT NULL
    , [EXBWR] [DECIMAL](13, 2) NOT NULL
    , [VKWRT] [DECIMAL](13, 2) NOT NULL
    , [AKTNR] [NVARCHAR](10) NOT NULL
    , [ZEKKN] [NVARCHAR](2) NOT NULL
    , [VFDAT] [NVARCHAR](8) NOT NULL
    , [CUOBJ_CH] [NVARCHAR](18) NOT NULL
    , [EXVKW] [DECIMAL](13, 2) NOT NULL
    , [PPRCTR] [NVARCHAR](10) NOT NULL
    , [RSART] [NVARCHAR](1) NOT NULL
    , [GEBER] [NVARCHAR](10) NOT NULL
    , [FISTL] [NVARCHAR](16) NOT NULL
    , [MATBF] [NVARCHAR](18) NOT NULL
    , [UMMAB] [NVARCHAR](18) NOT NULL
    , [BUSTM] [NVARCHAR](4) NOT NULL
    , [BUSTW] [NVARCHAR](4) NOT NULL
    , [MENGU] [NVARCHAR](1) NOT NULL
    , [WERTU] [NVARCHAR](1) NOT NULL
    , [LBKUM] [DECIMAL](13, 3) NOT NULL
    , [SALK3] [DECIMAL](13, 2) NOT NULL
    , [VPRSV] [NVARCHAR](1) NOT NULL
    , [FKBER] [NVARCHAR](16) NOT NULL
    , [DABRBZ] [NVARCHAR](8) NOT NULL
    , [VKWRA] [DECIMAL](13, 2) NOT NULL
    , [DABRZ] [NVARCHAR](8) NOT NULL
    , [XBEAU] [NVARCHAR](1) NOT NULL
    , [LSMNG] [DECIMAL](13, 3) NOT NULL
    , [LSMEH] [NVARCHAR](3) NOT NULL
    , [KZBWS] [NVARCHAR](1) NOT NULL
    , [QINSPST] [NVARCHAR](1) NOT NULL
    , [URZEI] [NVARCHAR](4) NOT NULL
    , [J_1BEXBASE] [DECIMAL](13, 2) NOT NULL
    , [MWSKZ] [NVARCHAR](2) NOT NULL
    , [TXJCD] [NVARCHAR](15) NOT NULL
    , [EMATN] [NVARCHAR](18) NOT NULL
    , [J_1AGIRUPD] [NVARCHAR](1) NOT NULL
    , [VKMWS] [NVARCHAR](2) NOT NULL
    , [HSDAT] [NVARCHAR](8) NOT NULL
    , [BERKZ] [NVARCHAR](1) NOT NULL
    , [MAT_KDAUF] [NVARCHAR](10) NOT NULL
    , [MAT_KDPOS] [NVARCHAR](6) NOT NULL
    , [MAT_PSPNR] [NVARCHAR](8) NOT NULL
    , [XWOFF] [NVARCHAR](1) NOT NULL
    , [BEMOT] [NVARCHAR](2) NOT NULL
    , [PRZNR] [NVARCHAR](12) NOT NULL
    , [LLIEF] [NVARCHAR](10) NOT NULL
    , [LSTAR] [NVARCHAR](6) NOT NULL
    , [XOBEW] [NVARCHAR](1) NOT NULL
    , [GRANT_NBR] [NVARCHAR](20) NOT NULL
    , [ZUSTD_T156M] [NVARCHAR](1) NOT NULL
    , [SPE_GTS_STOCK_TY] [NVARCHAR](1) NOT NULL
    , [KBLNR] [NVARCHAR](10) NOT NULL
    , [KBLPOS] [NVARCHAR](3) NOT NULL
    , [XMACC] [NVARCHAR](1) NOT NULL
    , [VGART_MKPF] [NVARCHAR](2) NOT NULL
    , [BUDAT_MKPF] [NVARCHAR](8) NOT NULL
    , [CPUDT_MKPF] [NVARCHAR](8) NOT NULL
    , [CPUTM_MKPF] [NVARCHAR](6) NOT NULL
    , [USNAM_MKPF] [NVARCHAR](12) NOT NULL
    , [XBLNR_MKPF] [NVARCHAR](16) NOT NULL
    , [TCODE2_MKPF] [NVARCHAR](20) NOT NULL
    , [VBELN_IM] [NVARCHAR](10) NOT NULL
    , [VBELP_IM] [NVARCHAR](6) NOT NULL
    , [SGT_SCAT] [NVARCHAR](16) NOT NULL
    , [SGT_UMSCAT] [NVARCHAR](16) NOT NULL
    , [SGT_RCAT] [NVARCHAR](16) NOT NULL
    , [/BEV2/ED_KZ_VER] [NVARCHAR](1) NOT NULL
    , [/BEV2/ED_USER] [NVARCHAR](12) NOT NULL
    , [/BEV2/ED_AEDAT] [NVARCHAR](8) NOT NULL
    , [/BEV2/ED_AETIM] [NVARCHAR](6) NOT NULL
    , [DISUB_OWNER] [NVARCHAR](10) NOT NULL
    , [OINAVNW] [DECIMAL](13, 2) NOT NULL
    , [OICONDCOD] [NVARCHAR](2) NOT NULL
    , [CONDI] [NVARCHAR](2) NOT NULL
    , [WRF_CHARSTC1] [NVARCHAR](18) NOT NULL
    , [WRF_CHARSTC2] [NVARCHAR](18) NOT NULL
    , [WRF_CHARSTC3] [NVARCHAR](18) NOT NULL
    , [/BEV2/ED_AEDAT_SIMP_DT] AS (TRY_CONVERT([DATE], [/BEV2/ED_AEDAT], (112)))
    , [BUDAT_MKPF_SIMP_DT] AS (TRY_CONVERT([DATE], [BUDAT_MKPF], (112)))
    , [CPUDT_MKPF_SIMP_DT] AS (TRY_CONVERT([DATE], [CPUDT_MKPF], (112)))
    , [DABRBZ_SIMP_DT] AS (TRY_CONVERT([DATE], [DABRBZ], (112)))
    , [DABRZ_SIMP_DT] AS (TRY_CONVERT([DATE], [DABRZ], (112)))
    , [HSDAT_SIMP_DT] AS (TRY_CONVERT([DATE], [HSDAT], (112)))
    , [VFDAT_SIMP_DT] AS (TRY_CONVERT([DATE], [VFDAT], (112)))
    , [/BEV2/ED_AETIM_SIMP_TM] AS (TRY_CONVERT([TIME](0), CASE [/BEV2/ED_AETIM]
                                                          WHEN N'240000' THEN
                                                              N'23:59:59'
                                                          ELSE
                                                              STUFF(STUFF([/BEV2/ED_AETIM], (3), (0), N':'), (6), (0), N':')
                                                          END, (112))
                                  )
    , [CPUTM_MKPF_SIMP_TM] AS (TRY_CONVERT([TIME](0), CASE [CPUTM_MKPF]
                                                      WHEN N'240000' THEN
                                                          N'23:59:59'
                                                      ELSE
                                                          STUFF(STUFF([CPUTM_MKPF], (3), (0), N':'), (6), (0), N':')
                                                      END, (112))
                              )
    , CONSTRAINT [dbo_MS~0]
          PRIMARY KEY CLUSTERED
          (
              [MANDT] ASC
              , [MBLNR] ASC
              , [MJAHR] ASC
              , [ZEILE] ASC
          )
);

Função de partição:

CREATE PARTITION FUNCTION [PF_dbo_MS_SIMP] (NVARCHAR(10))
AS RANGE RIGHT FOR VALUES (
N'426811'
, N'426862'
, N'426916'
, N'426968'
, N'427022'
, N'427072'
, N'427124'
, N'427182'
, N'427238'
, N'427293'
, N'427349'
, N'427404'
, N'427459'
, N'427513'
, N'427569'
, N'427625'
, N'427680'
, N'427733'
, N'427784'
, N'427840'
, N'427893'
, N'427948'
, N'428002'
, N'428053'
, N'428105'
, N'428162'
, N'428216'
, N'428272'
, N'428328'
, N'428384'
, N'428437'
, N'428489'
, N'428547'
, N'428601'
, N'428659'
, N'428715'
, N'428769'
, N'428822'
, N'428878'
, N'428932'
, N'428986'
, N'429041'
, N'429089'
, N'429146'
, N'429204'
, N'429260'
, N'429313'
, N'429366'
, N'429418'
, N'429472'
, N'429528'
, N'429583'
, N'429638'
, N'429691'
, N'429745'
, N'429799'
, N'429856'
, N'429909'
, N'429961'
, N'430012'
, N'430063'
, N'430118'
, N'430171'
, N'430224'
, N'430276'
, N'430326'
, N'430381'
, N'430434'
, N'430489'
, N'430539'
, N'430591'
, N'430644'
, N'430698'
, N'430753'
, N'430805'
, N'430856'
, N'430906'
, N'430961'
, N'516464'
, N'516561'
, N'516664'
, N'516766'
, N'516868'
, N'516970'
, N'517071'
, N'517173'
, N'517274'
, N'517376'
, N'517478'
, N'517578'
, N'517678'
, N'517778'
, N'517879'
, N'517913'
);
partitioning sql-server-2016

1 respostas

  1. Best Answer

    O particionamento adiciona o número da partição como uma chave principal implícita aos índices particionados.

    Seu índice clusterizado em:

    [MANDT] ASC, [MBLNR] ASC, [MJAHR] ASC, [ZEILE] ASC

    torna-se:

    [<partition_id>], [MANDT] ASC, [MBLNR] ASC, [MJAHR] ASC, [ZEILE] ASC

    Esse índice não pode entregar o pedido necessário para:

    SELECT TOP (4000) *
FROM dbo.MS
ORDER BY MANDT, MBLNR, MJAHR, ZEILE

    As linhas são ordenadas apenas da maneira que você deseja em cada partição .

    Por exemplo, isso retornaria linhas dentro da partição nº 1 sem uma classificação:

    SELECT TOP (4000) *
FROM dbo.MS
WHERE $PARTITION.PF_dbo_MS_SIMP (MBLNR) = 1
ORDER BY MANDT, MBLNR, MJAHR, ZEILE

    Seu índice clusterizado atual é compatível com esta ordem:

    SELECT TOP (4000) * 
FROM dbo.MS
ORDER BY 
    $PARTITION.PF_dbo_MS_SIMP (MBLNR),  -- Partition ID
    MANDT, MBLNR, MJAHR, ZEILE;

    Não há soluções perfeitas para isso.

    1. Você pode alterar seu arranjo de particionamento para atender a essa consulta. Seria uma mudança grande e corajosa. Você precisaria testar o novo esquema com sua carga de trabalho e acordos de carregamento/arquivamento de dados.

    2. Você pode criar um índice secundário não particionado e esperar que o otimizador escolha um plano de pesquisa de marcador para sua consulta, mas você tem um índice que não está alinhado com a tabela base.

      Por exemplo:

      CREATE UNIQUE NONCLUSTERED INDEX name_me 
ON dbo.MS (MANDT, MBLNR, MJAHR, ZEILE) 
ON [PRIMARY];

    3. Você pode gerar uma consulta por partição como a mostrada acima (incluindo a parte superior (4000)), unir todas elas e, em seguida, selecionar a parte superior (4000) do resultado da união por partição. Isso exigiria um pouco de SQL dinâmico (supondo que suas partições pudessem mudar com o tempo).

    O seguinte mostra o código estático para a opção 2 para apenas três partições:

    SELECT TOP (4000) * FROM
(
    SELECT * FROM 
    (
        SELECT TOP (4000) *
        FROM dbo.MS
        WHERE $PARTITION.PF_dbo_MS_SIMP (MBLNR) = 1
        ORDER BY MANDT, MBLNR, MJAHR, ZEILE
    ) AS P1
    UNION ALL
    SELECT * FROM 
    (
        SELECT TOP (4000) *
        FROM dbo.MS
        WHERE $PARTITION.PF_dbo_MS_SIMP (MBLNR) = 2
        ORDER BY MANDT, MBLNR, MJAHR, ZEILE
    ) AS P2
    UNION ALL
    SELECT * FROM 
    (
        SELECT TOP (4000) *
        FROM dbo.MS
        WHERE $PARTITION.PF_dbo_MS_SIMP (MBLNR) = 3
        ORDER BY MANDT, MBLNR, MJAHR, ZEILE
    ) AS P3
) AS M
ORDER BY MANDT, MBLNR, MJAHR, ZEILE;

    O plano de execução é:

    plano de união de fusão

    Cada 'varredura' é uma varredura de uma única partição (a varredura tem um predicado de busca). Observe a falta de tipos.

    Este plano não é bloqueante e as concatenações de mesclagem preservam a ordem. Isso significa que no máximo 4.000 linhas serão lidas no total. O plano não lê até 4.000 linhas de cada partição.

    • 6

relate perguntas