[SQL2014] Function based index (본문 하단에 실전예저 포함)

출처 http://www.sqlines.com/oracle/function_based_indexes

SQL Server:

  -- Define a table
  CREATE TABLE cities 
  (
     name VARCHAR(90),
     state CHAR(2),
     notes VARCHAR(90)
  );
 
  -- Insert some data
  INSERT INTO cities VALUES ('Houston', 'TX', '4th largest city in the US, and the largest city in Texas');
  INSERT INTO cities VALUES ('Seattle', 'WA', 'the largest city in the Pacific Northwest');
 
    -- Create index (non-function based) on name column
  CREATE INDEX cities_name_idx ON cities (name);

UPPER function is not required to return the correct results, so index on name column can be used:

  SELECT name, state, notes
  FROM cities
  WHERE name = 'HOUSTON';

Output:

name	state	notes
Houston	TX	4th largest city in the US, and the largest city in Texas

Implementing a Function-Based Index

But let's use UPPER function as an example how to implement a function-based index using a computed column and index on it in SQL Server:

SQL Server:

  -- Define a table with a computed column
  CREATE TABLE cities 
  (
     name VARCHAR(90),
     state CHAR(2),
     notes VARCHAR(90),
     upper_name AS UPPER(name)
  );
 
  -- Insert some data
  INSERT INTO cities VALUES ('Houston', 'TX', '4th largest city in the US, and the largest city in Texas');
  INSERT INTO cities VALUES ('Seattle', 'WA', 'the largest city in the Pacific Northwest');
 
  -- Create an index on computed column that acts as the function-based index
  CREATE UNIQUE INDEX cities_fn_idx ON cities (upper_name);

Note that SQL Server allows you to not specify columns list (name, state, notes) although INSERT omits the value for the computed column.

Now SQL Server uses the index on the computed column even if you still use UPPER(name) function in the query, not only upper_name column:

SQL Server:

  SELECT name, state, notes
  FROM cities
  WHERE UPPER(name) = 'HOUSTON';

Execution plan:

There are just 2 rows in the table, so we used UNIQUE index to force SQL Server to use it, and demonstrate that a index on a computed column can be used even if the query still uses a function in WHERE.

-----------------------------------------------------------------

실전예제

# 테이블 생성

CREATE TABLE [dbo].[Z_TB_COM101_0319](

[PLAY_DATE] [varchar](8) NOT NULL,

[THEA_CD] [varchar](4) NOT NULL,

[SCREEN_CD] [varchar](3) NOT NULL,

[SEQ] [int] NOT NULL,

        [PLAY_STRT_TIMS] [varchar](4) NULL,

        [PLAY_DATE_STRT_TIMS] AS PLAY_DATE+PLAY_STRT_TIMS     --> FBI 을 위한 computed 컬럼

)

#인덱스 생성

SET ANSI_PADDING ON

GO

/****** Object:  Index [IX_TB_COM101_02]    Script Date: 2015-03-19 오후 3:11:48 ******/

CREATE INDEX [IX_Z_TB_COM101_0319_03] ON [dbo].Z_TB_COM101_0319

(

[PLAY_DATE_STRT_TIMS] ASC

)

GO

# 쿼리문 비교

   ※ 해당 쿼리문은 전체 28000 건 중 28000건을 조회한다. (FULL SCAN)

   1. FBI 생성 전

SELECT DISTINCT 

             MVE_CD

    ,THEA_CD

  FROM TB_COM101 WITH(NOLOCK)    

WHERE PLAY_DATE + PLAY_STRT_TIMS >= CONVERT(VARCHAR(8), DATEADD(DAY, -1, GETDATE()), 112) + '240100'--PLAY_DATE >= CONVERT(VARCHAR(8), DATEADD(DAY, 0, GETDATE()), 112)

  2. FBI 생성 후

SELECT DISTINCT 

             MVE_CD

    ,THEA_CD

  FROM TB_COM101 WITH(NOLOCK)    

WHERE PLAY_DATE + PLAY_STRT_TIMS >= CONVERT(VARCHAR(8), DATEADD(DAY, -1, GETDATE()), 112) + '240100'--PLAY_DATE >= CONVERT(VARCHAR(8), DATEADD(DAY, 0, GETDATE()), 112)

# 결론

   FULL SCAN 쿼리문에서는 computed column 을 만들어내기 위한 계산 과정이 추가되기 때문에 성능이 오히려 저하된다. 즉, 상황에 맞게 FBI 를 사용해야 하며 반드시 쿼리 TRACE를 통해 확인해야 한다.