안하는걸로 알고 있습니다.

PostgreSQL 에서도 지원하던데..  

MSSQL에서도 지원했으면 좋겠어요

잘 몰라서 그러는데,

Indexed View를 활용한 것과 효과가 다른가요???

그럼..

(BOL에서..)

인덱스된 뷰를 만들거나 인덱스된 뷰에 포함된 테이블의 행을 조작할 경우 7개의 SET 옵션에 특정 값을 할당해야 합니다. 즉, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, ANSI_WARNING SET 옵션을 ON으로 설정하고 NUMERIC_ROUNDABORT SET 옵션을 OFF로 설정해야 합니다.

위의 설정이 하나라도 다르게 설정되면 인덱스된 뷰에서 참조되는 테이블의 데이터 수정 문(INSERT, UPDATE, DELETE)이 실패하고 SQL Server에 설정 요구 사항에 위반되는 SET 옵션이 모두 표시된 오류가 나타납니다. 또한, 인덱스된 뷰에 수반되는 SELECT 문의 경우, SET 옵션의 값이 필요한 값이 아니면 SQL Server는 해당 뷰에 인덱스가 없는 것처럼 SELECT를 처리합니다. 이렇게 함으로써 위에 표시된 SET 옵션이 적용될 수 있는 경우에 쿼리 결과의 정확성을 보장합니다.

응용 프로그램이 DB-Library 연결을 사용할 경우 서버에서 7개의 모든 SET 옵션에 필요한 값이 할당되어야 합니다. (기본적으로 OLE DB 및 ODBC 연결은 ARITHABORT를 제외하면 필요한 SET 옵션을 모두 정확하게 설정합니다.)

BCP, 복제, 분산 쿼리 등 일부 작업은 위에 표시된 SET 옵션 중 하나라도 필요한 값이 아니면 인덱스된 뷰에 사용하는 해당 테이블에 대한 업데이트 실행이 실패할 수 있습니다. 대부분의 경우 ARITHABORT를 ON으로 설정하면(서버 구성 옵션의 user options를 통해) 이러한 문제를 방지할 수 있습니다.

서버의 한 데이터베이스에서 계산된 열에 대해 첫 번째로 인덱스된 뷰 또는 인덱스가 만들어지면 바로 서버 차원에서 ARITHABORT 사용자 옵션을 ON으로 반드시 설정하십시오.

인덱스된 뷰에 대한 자세한 고려 사항과 제한 사항을 보려면 비고 항목을 참조하십시오.

bol 에 나오는 옵션이야 기본으로 셋팅 되어 있습니다.

그리고 제가 보기에는 indexed view 가 그룹바이나 이런게 있을때 좀 좋더군요 좋기는 좋은데 제가 예전에 올린 영문 문서와 같이 제약사항도 많습니다. 조 아래

http://database.sarang.net/?inc=read&aid=2172&criteria=mssql&subcrit=&id=&limit=20&keyword=why+indexed&page=1

-_- 근데 오라클을 심도 있게 안해봐서 그런지 function based index 가 뭔가요?

누가 function based index 설명 좀 해주세요 ^^ 안 찾아보고 물어보는 센스 ~ 질문자는 알겠죠? 뭐하는넘인지

Function based index 란

함수를 사용한 데이터 field에 대해 인덱스를 사용하는 것을 말합니다.

예를들면

create index emp_ename_idx on emp(upper(ename));

이런식으로 인덱스를 생성하는 거죠.

create table tblz
(
idx int identity(1,1)
,col1 datetime
)

-- 10번
insert into tblz values (GETDATE())
-- 1번
insert into tblz values ('20050813 10:10')

-- 실험
select CONVERT(CHAR(10),COL1,112) from tblz

-- 펑션만들기
CREATE FUNCTION dbo.UFN_GETDATE(@time datetime)
RETURNS CHAR(10)
AS
BEGIN
     DECLARE @date CHAR(10)

    SELECT @date= CONVERT(CHAR(10),@TIME,112)
   
    RETURN( @date);
END
GO

-- 펑션테스트
select  dbo.UFN_getdate(col1) from tblz

CREATE CLUSTERED INDEX IDX_DATE
 ON TBLZ (COL1 ASC)
 WITH PAD_INDEX
 , FILLFACTOR = 90

CREATE VIEW FUNCTIONBASEDVIEW WITH SCHEMABINDING
AS
select  dbo.UFN_getdate(col1) AS IDXDATE from dbo.tblz

-- 에러
-- 서버: 메시지 4513, 수준 16, 상태 1, 프로시저 FUNCTIONBASEDVIEW, 줄 3
-- 뷰 'FUNCTIONBASEDVIEW'을(를) 스키마 바인딩할 수 없습니다. 'dbo.UFN_getdate'은(는) 스키마 바인딩되지 않았습니다.

drop view FUNCTIONBASEDVIEW

CREATE VIEW FUNCTIONBASEDVIEW WITH SCHEMABINDING
AS
select  idx, CONVERT(CHAR(10),col1,112) AS IDXDATE from dbo.tblz

-- 성공

select * from FUNCTIONBASEDVIEW

create unique clustered index FUNCTIONBASEDVIEW_idx
 on FUNCTIONBASEDVIEW (idx asc)
-- 경고: 뷰의 SELECT 목록에 집계가 아닌 식이 있어서 최적화 프로그램에서 인덱스를 사용할 수 없습니다.

-- drop index FUNCTIONBASEDVIEW.FUNCTIONBASEDVIEW_idxdate

create nonclustered index FUNCTIONBASEDVIEW_idxdate
 on FUNCTIONBASEDVIEW (idxdate asc)

set showplan_text on
go
set statistics io on
set statistics profile on
set statistics time on
select idxdate from FUNCTIONBASEDVIEW where idxdate = '20050813'
set statistics io off
set statistics profile off
set statistics time off
go

-- SQL Server 실행 시간:
--    CPU 시간 = 0ms, 경과 시간 = 0ms.
-- SQL Server 구문 분석 및 컴파일 시간:
--  CPU 시간 = 0ms, 경과 시간 = 0ms.
-- idxdate   
-- ----------
-- 20050813 
--
-- (1개 행 적용됨)
--
-- 'tblz' 테이블. 스캔 수 1, 논리적 읽기 수 2, 물리적 읽기 수 0, 미리 읽기 수 0.
--
-- SQL Server 실행 시간:
--    CPU 시간 = 0ms, 경과 시간 = 0ms.
-- Rows        Executes    StmtText                                                                                                      StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                      DefinedValues                      EstimateRows             EstimateIO               EstimateCPU              AvgRowSize  TotalSubtreeCost         OutputList     Warnings Type                           Parallel EstimateExecutions      
-- ----------- ----------- ------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------- ---------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ -------------- -------- ------------------------------ -------- ------------------------
-- 1           1           SELECT [idxdate]=[idxdate] FROM [FUNCTIONBASEDVIEW] WHERE [idxdate]=@1                                        4           1           0           NULL                           NULL                           NULL                                                                          NULL                               1.0                      NULL                     NULL                     NULL        3.7675019E-2             NULL           NULL     SELECT                         0        NULL
-- 1           1             |--Compute Scalar(DEFINE:([Expr1002]=Convert([tblz].[col1])))                                               4           2           1           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1002]=Convert([tblz].[col1]))                                    [Expr1002]=Convert([tblz].[col1])  1.0                      0.0                      0.0000001                17          3.7675019E-2             [Expr1002]     NULL     PLAN_ROW                       0        1.0
-- 1           1                  |--Clustered Index Scan(OBJECT:([pubs].[dbo].[tblz].[IDX_DATE]), WHERE:(Convert([tblz].[col1])=[@1]))  4           3           2           Clustered Index Scan           Clustered Index Scan           OBJECT:([pubs].[dbo].[tblz].[IDX_DATE]), WHERE:(Convert([tblz].[col1])=[@1])  [tblz].[col1]                      1.0                      3.7578501E-2             9.3900002E-5             15          0.0376724                [tblz].[col1]  NULL     PLAN_ROW                       0        1.0
--
-- (3개 행 적용됨)
--
--
-- SQL Server 실행 시간:
--    CPU 시간 = 0ms, 경과 시간 = 0ms.
--
-- SQL Server 실행 시간:
--    CPU 시간 = 0ms, 경과 시간 = 0ms.
--
-- SQL Server 실행 시간:
--    CPU 시간 = 0ms, 경과 시간 = 0ms.
--
-- SQL Server 실행 시간:
--    CPU 시간 = 0ms, 경과 시간 = 0ms.

set showplan_text off
go

노트패드에 붙여보세요

SQL Server에는 Function Based Index는 없습니다.

같은 기능을 구현하려면..

테이블에 Computed Column를 만들고

여기에 인덱스를 걸어주면 됩니다.

ALTER TABLE emp ADD upper_ename AS upper(ename)

컴퓨티드 컬럼만 인덱스를 줄수 있지

펑션을 준 컬럼에 대해서는 인덱스가 걸리지 않습니다.

select * from tblz

-- idx         col1                                                  
-- ----------- ------------------------------------------------------
-- 1           2005-08-12 10:42:47.513
-- 2           2005-08-12 10:42:47.780
-- 3           2005-08-12 10:42:47.937
-- 4           2005-08-12 10:42:48.107
-- 5           2005-08-12 10:42:48.263
-- 6           2005-08-12 10:42:48.420
-- 7           2005-08-12 10:42:48.590
-- 8           2005-08-12 10:42:48.747
-- 9           2005-08-12 10:42:48.920
-- 10          2005-08-12 10:42:49.090
-- 11          2005-08-12 10:42:49.263

ALTER TABLE TBLZ ADD col2 AS DBO.UFN_GETDATE(COL1)
select * from tblz

-- idx         col1                                                   col2      
-- ----------- ------------------------------------------------------ ----------
-- 1           2005-08-12 10:42:47.513                                20050812 
-- 2           2005-08-12 10:42:47.780                                20050812 
-- 3           2005-08-12 10:42:47.937                                20050812 
-- 4           2005-08-12 10:42:48.107                                20050812 
-- 5           2005-08-12 10:42:48.263                                20050812 
-- 6           2005-08-12 10:42:48.420                                20050812 
-- 7           2005-08-12 10:42:48.590                                20050812 
-- 8           2005-08-12 10:42:48.747                                20050812 
-- 9           2005-08-12 10:42:48.920                                20050812 
-- 10          2005-08-12 10:42:49.090                                20050812 

CREATE  INDEX  TBLX_IDX3 ON tblz (col2)
-- 서버: 메시지 1933, 수준 16, 상태 1, 줄 1
-- 키 열 'col2'이(가) 명확하지 않거나 정확하지 않으므로 인덱스를 만들 수 없습니다.

확정적 함수와 비확정적 함수...

표현이 맞는지 모르겠지만, 어쨌던 확정적 함수라면 인덱스 걸어줄 수 있지 않나요??

그럼..

비확정 함수는 유저 펑션이 만들어지지도 않습니다.

CREATE FUNCTION dbo.UFN_GETDATE(@time datetime)
RETURNS CHAR(10)
AS
BEGIN
     DECLARE @date CHAR(10)

    SELECT @date= CONVERT(CHAR(10),@TIME,112)
   
    RETURN( @date);
END

그렇네요..

그냥 봐서는 될 것 같은데, 안 되네요.. ^^;;

이하 샘플)

create function dbo.get(@a int, @b int)
returns int
as
begin
  return(@a + @b)
end

go

create table a(
  a int
, b int
, c as (a+b)
, d as dbo.get(a, b)
)

go

insert into a values (1,2)
insert into a values (2,3)
insert into a values (3,4)
insert into a values (4,5)
insert into a values (5,6)

select * from a

go

create index idxa on a(c)

go
create index idxa on a(d)

go

drop table a
drop function dbo.get

oracle의 function base index 는 mssql에서도 생성하여 구현하는 것이 가능합니다.

하지만...오라클과 똑같이 어떤 특정 컬럼에 대한 변환 값이나 기타 조건에 모두 적용할수 있는 것은 아닙니다.

그리고 oracle 에서는 funcation base index 를 구현할때는 특정 파라미터나 기타 전체 옵티마이져 및 통계정보및 여러 가지 인스턴스 레벨에서의 제약이 많습니다.

 물론  mssql에서도 몇가지의 제약이 있긴합니다.

같은 값들을 반환하는 이른바 확정된 함수에서는 가능하며.. 그밖에 비 확정된 함수는 function base index를 사용하는 것이 불가 합니다.

물론 서버의 셋팅값은 윗분들이 잘 적어 주셨네요... 다만 함수의 여부에 따라서 사용여부가 가능해 지겠죠... ^^

 사용하는데 따른 제약 조건이 상당히 많습니다.

이걸 mssql에서 function base index 라고 불러야 되나 하는 생각이 들지만.. 기본 사용 포멧은 비슷하다고 볼수 있게죠..

해당 컬럼의 변형에 의한 인덱스의 사용을 가능하게 해 주는 역할을 하기 때문에... 저는 그렇게 부르고 사용하고 있씁니다.

 물론 mssql 은 기본이 cbo 이기 때문에 사용이 가능합니다.

하지만... 기본적으로 이 function base index 는 사용하지 않는 것이 바람직 하겠죠... 오라클에서 사용을 권장하지는 않습니다.

그만큼 서버 내부적인 파싱비용및 기타 비용들이 많이 들기 때문에.. 가능한 해당 컬럼의 변형을 하지 않은 체로 가공하여 정상적인 인덱스를 옵티마이져가 선택하게 해 주는 것이 바른 판단입니다.

 수고하세요

sqler 님 뭘 mssql 의 function based index 라 하시는지 그리고 indexed view 를 이용한 방법이 아니고 다른 방법이라는 말씀이신지

예제는 어떻게 되는지 좀 적어주시면 감사하겠습니다.

펑션에 기본적으로 인덱스 안걸리거든요