Sql-Server-2016

訂購分區表的最佳方式是什麼？

  • October 6, 2021

從分區表中獲取有序數據的最有效方法是什麼？

我有這個查詢：

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

表由 MBLNR 分區。

執行計劃：https ://www.brentozar.com/pastetheplan/?id=Bkhv5g5EF

是否有任何選項可以從執行計劃中擺脫排序？

創建表語句：

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
         )
);

分區功能：

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'
);

分區將分區號作為隱含的前導鍵添加到分區索引。

您的聚集索引：

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

變成：

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

該索引無法傳遞所需的訂單：

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

在每個分區中，行僅按您想要的方式排序。

例如，這將返回分區 #1 中沒有排序的行：

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

您目前的聚集索引確實支持此順序：

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

沒有任何完美的解決方法。

  1. 您可以更改分區安排以適應此查詢。那將是一個巨大而勇敢的改變。您需要使用您的工作負載和數據載入/歸檔安排來測試新方案。
  2. 您可以創建一個未分區的二級索引，並希望優化器為您的查詢選擇一個書籤查找計劃，但是您有一個與基表不對齊的索引。

例如：

CREATE UNIQUE NONCLUSTERED INDEX name_me 
ON dbo.MS (MANDT, MBLNR, MJAHR, ZEILE) 
ON [PRIMARY];
  1. 您可以生成一個如上所示的每個分區查詢（包括頂部（4000）），將它們全部合併在一起，然後從每個分區聯合的結果中選擇頂部（4000）。這將需要一些動態 SQL（假設您的分區可能會隨著時間而改變）。

下面顯示了僅針對三個分區的選項 2 的靜態程式碼：

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;

執行計劃是：

合併工會計劃

每個“掃描”都是對單個分區的掃描（掃描有一個搜尋謂詞）。注意缺少排序。

該計劃是非阻塞的，並且合併連接是保持順序的。這意味著總共最多讀取 4000 行。該計劃不會從每個分區讀取多達 4000 行。

引用自：https://dba.stackexchange.com/questions/300714

相關問答

Sql-Server

觸發分區表和性能

  • July 7, 2022
檢視問答
Sql-Server

大型數據庫分區和僅更新數據的備份（部分備份）

  • October 3, 2021
檢視問答
Sql-Server

如何減少日期分區日誌表上非聚集索引的碎片

  • January 5, 2021
檢視問答
Sql-Server-2016

是否可以在 MSSQL 2016 上編輯分區函式？

  • November 28, 2020
檢視問答
Sql-Server

每個分區/文件組多個文件？

  • December 31, 2019
檢視問答
Sql-Server

如何防止 SELECT 上的分區列儲存死鎖

  • August 6, 2019
檢視問答