Informix에서 함수 인덱스 찾기

인포믹스에서 함수 인덱스를 찾는 방법에 대한 질문이 있어서 찾아보았습니다.

우선 카탈로그 테이블인 sysindexes 부터 살펴보니 함수 인덱스임을 표시하는 컬럼은 없었습니다.

그래서 sysindexes 테이블의 함수 인덱스 정보가 다른 인덱스와 차이가 있는지 확인해봤습니다.

 

먼저 테이블과 함수 인덱스를 만들었습니다. 내용은 IBM 문서를 참고했습니다.

www.ibm.com/developerworks/data/library/techarticle/dm-0712wilcox/index.html

$ dbaccess stores_demo -

Database selected.

> CREATE TABLE circles ( radius FLOAT );

Table created.

> CREATE FUNCTION circleArea( radius FLOAT ) RETURNS float
>     WITH (NOT VARIANT);
>
>     RETURN 3.14159 * radius * radius;
> END FUNCTION;

Routine created.

> CREATE INDEX areaOfCircleIndex on circles( circleArea( radius ) );

Index created.

 

해당 인덱스에 대한 sysindexes 테이블의 정보를 확인해보았습니다.

> select * from sysindexes where idxname='areaofcircleindex';



idxname    areaofcircleindex
owner      informix
tabid      162
idxtype    D
clustered
part1      0
part2      0
part3      0
part4      0
part5      0
part6      0
part7      0
part8      0
part9      0
part10     0
part11     0
part12     0
part13     0
part14     0
part15     0
part16     0
levels     1
leaves     1.000000000000
nunique    0.00
clust      0.00

1 row(s) retrieved.

특이하게도 일반 인덱스와 달리 part1 값이 0으로 표시됩니다. 인덱스를 만들 때 컬럼 순서를 바꾸고 일반 컬럼과 혼용해도 동일한 결과를 보여줍니다. 그럼 part1 값이 0이면 함수 인덱스인가? 라는 생각이 들어서 정말 그런지 실행해보았습니다.

> select owner, idxname from sysindexes where tabid > 99 and idxname[1,1] <> ' ' and part1 = 0;



owner    informix
idxname  ucnameindex

owner    informix
idxname  ts_data_location_spix

owner    informix
idxname  areaofcircleindex

3 row(s) retrieved.

 

데모용 데이터베이스인 stores_demo에서 수행해보니 opclass에 대한 인덱스와 함수 인덱스 두개가 보입니다. 정확히 함수 인덱스만 나오는 것은 아니고 일반적인 타입의 인덱스는 제외되는 것 같습니다. 어설프지만 확인은 가능하네요.

그래서 좀 더 정확한 방법에 대한 조언을 구하기위해 IBM Community에 질문을 올렸습니다. 바로 Art Kagel씨와 Paul Watson씨가 명쾌한 답을 주시는군요. 답은 sysindices 카탈로그 테이블에 있었습니다.

> select idxname from sysindices where indexkeys::lvarchar matches '*<[0-9]*>*';



idxname  ucnameindex

idxname  areaofcircleindex

2 row(s) retrieved.

> select idxname, indexkeys from sysindices where indexkeys::lvarchar matches '*<[0-9]*>*';



idxname    ucnameindex
indexkeys  2 [1], <1757>(3) [1], 1 [1]

idxname    areaofcircleindex
indexkeys  <1758>(1) [1]

2 row(s) retrieved.

sysindices 테이블의 indexkeys 컬럼 값에 함수 인덱스에 대한 정보가 있더군요. opclass에 대한 정보도 있는데 이부분은 저도 잘은 모르고 이 내용과는 벗어나는 내용이라 넘어가겠습니다. indexkeys 컬럼에도 딱 이 인덱스가 함수 인덱스다! 라고 알려주는 플래그 값이 있는건 아니지만, 함수 인덱스인 경우 <> 사이에 procid 값 정보가 있습니다. 이 정보로 인덱스가 함수 인덱스임을 알 수 있는 것입니다.

아래는 IBM Knowledge Center의 sysindices 테이블에 대한 설명입니다.

---

The fields within the indexkeys columns have the following significance:

• The procid (as in sysprocedures) exists only for a functional index on return values of a function defined on columns of the table.
• The list of columns (col1, col2, ... , coln) in the second field identifies the columns on which the index is defined. The maximum is language-dependent: up to 341 for an SPL or Java™ UDR; up to 102 for a C UDR.
• The opclassid identifies the secondary access method that the database server used to build and to search the index. This is the same as the sysopclasses.opclassid value for the access method.
•  

---

IBM Community에서 좋은 것을 배웠네요.