主页 / dba / 问题 / 318389
Accepted
Sicilian-Najdorf
Asked: 2022-10-19 23:04:48 +0800 CST

寻找不等式的谓词 - 残差谓词

  • 772

有人可以指导我更好地理解具有多个不等式谓词的索引搜索。考虑下表：

CREATE TABLE [dbo].[table_1](
[DBINVHDID] [int] IDENTITY(1,1) NOT NULL,
[TYPE] [nvarchar](1) NULL,
[INV_NO] [nvarchar](13) NULL,
[ACCOUNT] [nvarchar](10) NULL,
[NAME] [nvarchar](40) NULL,
[ADDR1] [nvarchar](25) NULL,
[ADDR2] [nvarchar](25) NULL,
[ADDR3] [nvarchar](25) NULL,
[CONTACT] [nvarchar](25) NULL,
[GST_NO] [nvarchar](15) NULL,
[CR_TERMS] [nvarchar](10) NULL,
[OREF] [nvarchar](20) NULL,
[YREF] [nvarchar](20) NULL,
[SALESREP] [nvarchar](20) NULL,
[DISCOUNT] [decimal](18, 2) NULL,
[TRANSPORT] [decimal](18, 2) NULL,
[INS] [decimal](18, 2) NULL,
[GST] [decimal](18, 2) NULL,
[TOTAL] [decimal](18, 2) NULL,
[DATE] [smalldatetime] NULL,
[LINE] [smallint] NULL,
[PSLIP] [nvarchar](10) NULL,
[VAT] [nvarchar](1) NULL,
[STORE] [nvarchar](3) NULL,
[STKSTR] [nvarchar](3) NULL,
[POST] [smalldatetime] NULL,
[PAID] [decimal](18, 2) NULL,
[WEIGHT] [decimal](18, 4) NULL,
[INSURE] [nvarchar](15) NULL,
[HAND] [decimal](18, 2) NULL,
[CODC] [decimal](18, 2) NULL,
[TOPAY] [decimal](18, 2) NULL,
[IND1] [nvarchar](1) NULL,
[IND2] [nvarchar](1) NULL,
[IND3] [nvarchar](1) NULL,
[COST] [decimal](18, 2) NULL,
[REPC] [nvarchar](6) NULL,
[DISPATCH] [nvarchar](1) NULL,
[TILLNO] [nvarchar](2) NULL,
[USER] [nvarchar](6) NULL,
[RCOMM] [decimal](18, 2) NULL,
[DISP1] [nvarchar](1) NULL,
[DISP2] [nvarchar](7) NULL,
[PRNSEQ] [int] NULL,
[TIME] [nvarchar](20) NULL,
[ROUND] [decimal](18, 2) NULL,
[MESSAGE1] [nvarchar](50) NULL,
[MESSAGE2] [nvarchar](50) NULL,
[MESSAGE3] [nvarchar](50) NULL,
[PRINT] [nvarchar](1) NULL,
[PDATE] [smalldatetime] NULL,
[FILENO] [nvarchar](20) NULL,
[sched6] [nvarchar](1) NULL,
[BUYACC] [nvarchar](10) NULL,
[DBCLW] [nvarchar](20) NULL,
[PRINTCODE] [nvarchar](3) NULL,
[INV_HOLD] [nvarchar](1) NULL,
[CURRATE] [decimal](18, 4) NULL,
[CURCODE] [nvarchar](3) NULL,
[DEPOS] [smalldatetime] NULL,
[ROUTE] [nvarchar](5) NULL,
[DEL_METHOD] [nvarchar](1) NULL,
[DROPNO] [int] NULL,
[SEATNO] [int] NULL,
[DDATE] [smalldatetime] NULL,
[COMM] [nvarchar](20) NULL,
[ROT] [nvarchar](1) NULL,
[KNAPP] [nvarchar](1) NULL,
[OSEC] [nvarchar](1) NULL,
[OSECT] [nvarchar](3) NULL,
[UPDEXT] [nvarchar](1) NULL,
[EORDREFNO] [nvarchar](32) NULL,
[FSI] [nvarchar](1) NULL,
[MESSAGE_LINES] [nvarchar](500) NULL)

这是索引：

CREATE INDEX [Index_1] ON table_1 
([PRINT], [DEL_METHOD], [TYPE], [INV_NO], [DBINVHDID], [OSECT], [OSEC]) 
INCLUDE 
( [BUYACC], [CR_TERMS], [DISP1], [NAME], [ROUTE], [SEATNO], [STORE])

这是查询：

SELECT 
    [DBINVHDID],[STORE], [INV_NO], [DISP1],[SEATNO], [BUYACC],
    [NAME],[ROUTE], [CR_TERMS], [DEL_METHOD] 
FROM table_1 
WHERE 
    [PRINT] = 'N' 
    AND ([TYPE] = '3' OR [TYPE]='5') 
    AND [DEL_METHOD] = 'C' 
    AND [OSECT] <> 'KNP' 
    AND [OSEC] <> '1' 
ORDER BY [INV_NO] ASC

查询计划：

在此处输入图像描述

我遇到的问题是在不等式上有一个 SEEK 谓词和一个 PREDICATE：

在此处输入图像描述

如何重新创建此索引以便没有残留谓词？

最初，索引是使用定义中更左侧的 OSECT 和 OSEC 列创建的，这会导致残留 IO 警告和大量读取 - 然后我将这些列移动到清除 IO 问题的相等列之后。

我想了解的是如何让这个索引完全满足查询而没有剩余谓词？

查询可以重写吗？

此外，还有一个过多的内存授予警告 - SQL 正在为此查询分配过多的内存 - STATS 是最新的 FULLSCAN

过度授权是否与剩余谓词有关？

sql-server

2 个回答

  1. Best Answer

    如何重新创建此索引以便没有残留谓词？

    您似乎可能需要身份是唯一的。这需要强制执行：

    CREATE UNIQUE CLUSTERED INDEX CX ON dbo.table_1 (DBINVHDID);

    然后创建一个过滤索引：

    CREATE INDEX [Index_1] 
ON table_1 ([PRINT], [DEL_METHOD], [TYPE], [INV_NO]) 
INCLUDE (
    [DBINVHDID], [OSECT], [OSEC], [STORE], [DISP1], [SEATNO], [BUYACC],
    [NAME], [ROUTE], [CR_TERMS])
WHERE 
    OSECT <> N'KNP'
    AND OSEC <> N'1';

    将查询表示为：

    SELECT 
    U.[DBINVHDID],
    U.[STORE],
    U.[INV_NO],
    U.[DISP1],
    U.[SEATNO], 
    U.[BUYACC],
    U.[NAME],
    U.[ROUTE],
    U.[CR_TERMS],
    U.[DEL_METHOD] 
FROM 
(
    SELECT T1.*
    FROM dbo.table_1 AS T1
    WHERE
        T1.[PRINT] = N'N'
        AND T1.DEL_METHOD = N'C'
        AND T1.[TYPE] = N'3'

    UNION ALL

    SELECT T2.*
    FROM dbo.table_1 AS T2
    WHERE
        T2.[PRINT] = N'N'
        AND T2.DEL_METHOD = N'C'
        AND T2.[TYPE] = N'5'
) AS U
WHERE
    U.OSECT <> N'KNP'
    AND U.OSEC <> N'1'
ORDER BY
    U.INV_NO ASC;

    这将给出一个没有残差谓词和排序的执行计划：

    没有残差或排序的计划

    如果这些条件在不同的查询中可能不同，您可以省略索引的过滤部分。这两个不等式谓词将被应用为残差谓词OSECT。OSEC

    过度授权是否与剩余谓词有关？

    内存授予用于排序。它估计与需要排序的行数和大小成正比。谓词（可搜索和残差）确实会影响基数估计。你的桌子似乎也是空的。在这种情况下，SQL Server 通常会发出无意义的“内存过多”警告，即使内存预留很小。

    没有排序，就没有内存要求。

    您不应在master数据库中创建表。

    您的单个表是一堆无组织的数据。具有多个表来表示不同实体的关系设计可能更容易查询。您应该考虑重新设计。

    相关阅读：

    • 8

  2. 您的查询具有 sargable 条件，例如 <>、OR 运算符。这些运算符对查询成本有不良影响。为了降低查询成本，您可以通过将查询分为 2 个查询来删除“或”条件，如下所示：

    SELECT 
    [DBINVHDID],[STORE], [INV_NO], [DISP1],[SEATNO], [BUYACC],
    [NAME],[ROUTE], [CR_TERMS], [DEL_METHOD] 
FROM table_1 
WHERE 
    [PRINT] = 'N' 
    AND ([TYPE] = '3') 
    AND [DEL_METHOD] = 'C' 
    AND [OSECT] <> 'KNP' 
    AND [OSEC] <> '1'
UNION 
SELECT 
    [DBINVHDID],[STORE], [INV_NO], [DISP1],[SEATNO], [BUYACC],
    [NAME],[ROUTE], [CR_TERMS], [DEL_METHOD] 
FROM table_1 
WHERE 
    [PRINT] = 'N' 
    AND ([TYPE]='5') 
    AND [DEL_METHOD] = 'C' 
    AND [OSECT] <> 'KNP' 
    AND [OSEC] <> '1' 
ORDER BY [INV_NO] ASC

    为了更好地理解，请查看此链接： https ://medium.com/codex/sargable-predicates-and-null-values-in-sql-server-c43ec3d8b108

    • 1

相关问题