여러 표에 걸친 합성 지수와 동등합니까?

여러 표에 걸친 합성 지수와 동등합니까?

저는 다음과 비슷한 테이블 구조를 가지고 있습니다.

create table MAIL (
  ID        int,
  FROM      varchar,
  SENT_DATE date
);

create table MAIL_TO (
  ID      int,
  MAIL_ID int,
  NAME      varchar
);

다음 쿼리를 실행해야 합니다.

select m.ID 
from MAIL m 
  inner join MAIL_TO t on t.MAIL_ID = m.ID
where m.SENT_DATE between '07/01/2010' and '07/30/2010'
  and t.NAME = 'someone@example.com'

두 조건이 모두 인덱스를 사용할 수 있도록 인덱스를 설계할 수 있는 방법이 있습니까?MAIL에 인덱스를 붙이면.MAIL_TO의 SENT_DATE 및 인덱스.NAME, 데이터베이스는 인덱스 또는 둘 다가 아닌 다른 인덱스 중 하나를 사용하도록 선택합니다.첫 번째 조건으로 필터링한 후 데이터베이스는 두 번째 조건에 대한 결과를 항상 전체 스캔해야 합니다.

Oracle은 두 인덱스를 모두 사용할 수 있습니다.두 개의 지수가 정확하지 않을 뿐입니다.

다음을 고려합니다. 쿼리 계획에서 인덱스를 고려합니다.mail.sent_date첫째, 그것은 무엇을 얻습니까?mail? 이 모든 것이 다.mail.id어디에mail.sent_date당신이 제시한 범위 안에 있습니다.where조항, 그렇죠?

그래서.mail_to일람표를 붙여mail.id모래를mail.name당신은 당신의 것을 포기했습니다.where절이 시점에서 Oracle은 일치하는 테이블을 스캔하는 것이 좋다고 판단합니다.mail_to.mail_ids 에 인덱스를 사용하기 보다는mail_to.name.

varchar의 인덱스는 항상 문제가 있으며 Oracle은 전체 테이블 스캔을 매우 선호합니다.그러나 Oracle이 실제로 사용하고자 하는 열이 포함된 인덱스를 제공하고 전체 테이블 행과 통계에 따라 Oracle을 사용할 수 있습니다.이것이 인덱스입니다.

 create index mail_to_pid_name on mail_to( mail_id, name ) ; 

인덱스가 다음 위치에서 작동합니다.name오라클은 이름만 찾는 게 아니라 이름만 찾는 것이기 때문에mail_id 그리고.name.

반대로, 비용 기반 분석기가 테이블로 이동하는 것이 더 저렴하다고 판단할 경우mail_to우선, 그리고 당신의 인덱스를 사용합니다.mail_to.name, 그것은 무엇을 얻습니까?한 다발의mail_to_.mail_id올려다 보다mail. 다음과 같은 ID와 특정 sent_date가 있는 행을 찾아야 합니다.

 create index mail_id_sentdate on mail( sent_date, id ) ; 

이 경우에 제가 말씀드린 것은sent_date지수 1위, 그리고id둘째.(이것이 더 직관적입니다.)

다시 한 번 말씀드리지만, 지수를 생성할 때는 열뿐만 아니라where절 뿐만 아니라 가입 조건에 있는 열도 포함됩니다.

---

갱신하다

jthg: 네, 항상 데이터가 배포되는 방식에 따라 달라집니다.테이블에 있는 행의 개수에 대해서도 마찬가지입니다. 만약 매우 많다면 Oracle은 테이블 스캔 및 해시 조인을 수행하고, 매우 적으면 테이블 스캔을 수행합니다.두 지수 중 하나의 순서를 반대로 설정할 수도 있습니다.두 번째 인덱스에서 sent_date를 먼저 지정함으로써 다음과 같은 인덱스에 대한 대부분의 요구를 제거합니다.sent_date.

구체화된 뷰를 사용하면 엄격한 구체화된 뷰 기준을 충족한다고 가정할 때 값을 인덱싱할 수 있습니다.

어떤 기준이 더 선택적입니까?날짜 범위나 수신인?수신인이 맞겠네요.선택성이 높은 경우 날짜 인덱스는 신경 쓰지 말고 발견된 메일 ID를 기준으로 데이터베이스에서 검색하도록 하십시오.그러나 인덱스 테이블MAIL아직 안되면 id에.

반면, 일부 현대의 옵티마이저들은 두 인덱스를 모두 사용하여 두 테이블을 스캔하고 두 결과를 병합하기 위해 조인 열의 해시 값을 구축합니다.오라클이 언제 이런 전략을 택할지 확신할 수 없습니다.SQL Server가 다른 엔진에 비해 해시 조인을 자주 하는 경향이 있다는 것을 이제야 깨달았습니다.

구체화된 뷰에 대한 요구사항이 충족되지 않는 상황에서는 다음과 같은 두 가지 옵션이 있습니다.

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) 트리거의 성능/관리 오버헤드를 원하지 않고 다음 날 보고에만 이 작업이 필요한 경우, 테이블을 잘라내고 전체 보기를 구체화된 테이블로 선택하는 야간 프로세스 및 뷰를 생성할 수 있습니다.

십여 개의 표에 걸쳐 결합이 필요한 평탄한 관계형 데이터를 색인화하는 데 성공했습니다.보고 시간을 몇 시간에서 몇 초로 단축할 수 있습니다.값비싼 쿼리이기는 하지만 사용 기간이 줄어든 경우 작업을 오프 시간으로 설정할 수 있습니다.

쿼리가 일반적으로 특정 달에 대한 것이라면 데이터를 월별로 분할할 수 있습니다.

언급URL : https://stackoverflow.com/questions/3373907/equivalent-of-a-composite-index-across-multiple-tables