相当于跨多个表的复合索引?

一 materialized view 如果满足严格的物化视图条件,将允许您索引值。

Oracle可以使用这两个索引。你只是没有 正确的 两个指标。

考虑:如果查询计划在 mail.sent_date 首先,它是从哪里得到的 mail ?它得到所有 mail.id S在哪里 邮寄日期 在你给的范围内 where 子句,是吗?

所以它去 mail_to 一份清单 邮递员身份证 S和 mail.name 你放弃了你的 在哪里? 条款。此时,Oracle决定最好扫描表以进行匹配 mail_to.mail_id 而不是在上使用索引 mail_to.name .

varchars上的索引总是有问题的,Oracle更喜欢全表扫描。但是如果我们给Oracle一个包含列的索引, 欲望 要使用,根据表的总行数和统计信息,我们可以让它使用它。这是索引:

 create index mail_to_pid_name on mail_to( mail_id, name ) ; 

这适用于 name 不是,因为甲骨文不仅仅是在寻找一个名字,而是在寻找一个 mail_id 和 一 名称 .

相反,如果基于成本的分析器确定去表更便宜 梅尔托 首先,使用索引 邮递员姓名 ,坐着能得到什么?一串 mail_to_.mail_id 向上看 邮件 . 它需要找到具有这些ID的行 和 某些发送日期,因此:

 create index mail_id_sentdate on mail( sent_date, id ) ; 

注意,在这种情况下,我 sent_date 索引中的第一个,以及 id 第二。(这更直观。)

同样,关键是:在创建索引时,不仅要考虑 在哪里? 子句,也包括联接条件中的列。

更新

JTHG:是的,它总是取决于数据的分布方式。以及表中有多少行:如果非常多,Oracle将执行表扫描和哈希联接,如果非常少,则执行表扫描。您可以颠倒这两个索引的顺序。通过将发送日期放在第二个索引的第一位,我们消除了对索引的大部分需求 哨兵日 .

哪个标准更具选择性?日期范围还是收件人?我猜是收件人。如果这是高度选择性的,那么不需要考虑日期索引,只需要让数据库根据找到的邮件ID进行搜索。但索引表 MAIL 如果还没有的话。

另一方面,一些现代的优化器甚至会同时使用这两个索引,扫描两个表,然后构建连接列的哈希值来合并两个表的结果。我不确定Oracle是否以及何时会选择这种策略。我刚刚意识到,与其他引擎相比,SQL Server更倾向于进行哈希连接。

如果您的查询通常是针对特定月份的,那么您可以 partition 每月的数据。

在具体化视图不满足要求的情况下,有以下两种选择:

1)您可以创建一个交叉引用表,并使用触发器更新它。

这些概念与Oracle相同,但目前我只安装了SQL Server来运行测试,请参阅以下设置:

create table MAIL (
  ID        INT IDENTITY(1,1),
  [FROM]      VARCHAR(200),
  SENT_DATE DATE,
  CONSTRAINT PK_MAIL PRIMARY KEY (ID)
);

create table MAIL_TO (
  ID      INT IDENTITY(1,1),
  MAIL_ID INT,
  [NAME]     VARCHAR (200),
  CONSTRAINT PK_MAIL_TO PRIMARY KEY (ID)
);

ALTER TABLE [dbo].[MAIL_TO]  WITH CHECK ADD  CONSTRAINT [FK_MAILTO_MAIL] FOREIGN KEY([MAIL_ID])
REFERENCES [dbo].[MAIL] ([ID])
GO

ALTER TABLE [dbo].[MAIL_TO] CHECK CONSTRAINT [FK_MAILTO_MAIL]
GO


CREATE TABLE CompositeIndex_MailSentDate_MailToName ( 
[MAIL_ID] INT,
[MAILTO_ID] INT,
SENT_DATE DATE,
MAILTO_NAME VARCHAR(200),
CONSTRAINT PK_CompositeIndex_MailSentDate_MailToName PRIMARY KEY (MAILTO_ID,MAIL_ID)
)

GO

CREATE NONCLUSTERED INDEX IX_MailSent_MailTo ON dbo.CompositeIndex_MailSentDate_MailToName (SENT_DATE,MAILTO_NAME)
CREATE NONCLUSTERED INDEX IX_MailTo_MailSent ON dbo.CompositeIndex_MailSentDate_MailToName (MAILTO_NAME,SENT_DATE)
GO

CREATE TRIGGER dbo.trg_MAILTO_Insert
ON dbo.MAIL_TO  
AFTER INSERT AS  
BEGIN 
 INSERT INTO dbo.CompositeIndex_MailSentDate_MailToName ( MAIL_ID, MAILTO_ID, SENT_DATE, MAILTO_NAME )
 SELECT mailTo.MAIL_ID,mailTo.ID,m.SENT_DATE,mailTo.NAME
 FROM
 inserted mailTo
 INNER JOIN dbo.MAIL m ON m.ID = mailTo.MAIL_ID
END
GO


CREATE TRIGGER dbo.trg_MAILTO_Delete
ON dbo.MAIL_TO  
AFTER DELETE AS  
BEGIN 
 DELETE mailToDelete
 FROM
 dbo.MAIL_TO mailToDelete
 INNER JOIN deleted ON mailToDelete.ID = deleted.ID
END
GO

CREATE TRIGGER dbo.trg_MAILTO_Update
ON dbo.MAIL_TO  
AFTER UPDATE AS  
BEGIN 
 UPDATE compositeIndex
 SET
 compositeIndex.MAILTO_NAME = updates.NAME
 FROM
 dbo.CompositeIndex_MailSentDate_MailToName compositeIndex
 INNER JOIN inserted updates ON updates.ID = compositeIndex.MAILTO_ID
END
GO

CREATE TRIGGER dbo.trg_MAIL_Update
ON dbo.MAIL  
AFTER UPDATE AS  
BEGIN 
 UPDATE compositeIndex
 SET
 compositeIndex.SENT_DATE = updates.SENT_DATE
 FROM
 dbo.CompositeIndex_MailSentDate_MailToName compositeIndex
 INNER JOIN inserted updates ON updates.ID = compositeIndex.MAIL_ID
END
GO


INSERT INTO dbo.MAIL ( [FROM], SENT_DATE )
SELECT 'SenderA','2018-10-01'
UNION ALL SELECT 'SenderA','2018-10-02'

INSERT INTO dbo.MAIL_TO ( MAIL_ID, NAME )
SELECT 1,'CustomerA'
UNION ALL SELECT 1,'CustomerB'
UNION ALL SELECT 2,'CustomerC'
UNION ALL SELECT 2,'CustomerD'
UNION ALL SELECT 2,'CustomerE'


SELECT * FROM dbo.MAIL
SELECT * FROM dbo.MAIL_TO
SELECT * FROM dbo.CompositeIndex_MailSentDate_MailToName

然后您可以使用 dbo.CompositeIndex_MailSentDate_MailToName 表以联接到其余数据。这在插入和更新率较低但查询需求较高的环境中很有用。因此实现触发器的相对开销很小。

这有一个优点,即可以进行实时的事务更新。

2)如果不需要触发器的性能/管理开销,而只需要在第二天报告时使用,则可以创建一个视图和一个夜间进程,该进程将截断表并将整个视图选择为物化表。

我已经成功地使用它索引了需要跨十几个表连接的扁平关系数据。将报告时间从几小时缩短到几秒。虽然这是一个昂贵的查询,但是如果您有减少使用的时间段,您可以将作业设置为非工作时间。