Informix IDS 11系统管理(918考试)认证指南,第8部分:面向管理员的SQL特性(7)

索引自连接

索引自连接是这样一类索引，如果复合索引中的主要列具有大量重复内容，而非主要列具有较好的选择性，则将对查询进行优化。

在 IDS 的早期版本中，优化器将对符合搜索条件的复合索引进行全方位扫描，或者，如果主要列都不满足条件，则执行连续扫描。

索引自连接将对高度重复的主要列搜索惟一组合，然后对搜索得到的惟一组合执行较小的查询，对更具选择性的非主要列进行过滤。该表在逻辑上执行自连接。

除索引自连接以外，还提供了两种新的指令访问方法：

• INDEX_SJ：强制优化器执行索引自连接
• AVOID_INDEX_SJ：阻止优化器执行索引自连接

例如，以下表为例，其中的 col1 和 col2 列具有大量重复，col3 选择性较好，而这三个列（col1、col2、col3）之上定义了一个复合索引（idx1）：

                    
        CREATE TABLE tab1 (
        col1  int,
        col2  int,
        col3  int);
        
        CREATE INDEX idx1 ON tab1(col1,col2,col3);

执行以下查询：

        SELECT * FROM TAB1
        WHERE col1 >= 1  AND col1 <= 2
        AND   col2 >= 2  AND col2 <= 4
        AND   col3 >= 40 AND col3 <= 50;
      

首先，将对主要列 col1 和 col2（高度重复）执行索引扫描，以获得惟一组合。然后对于得到的惟一组合，使用过滤器 (col1 = col1, col2 = col2, col3 >= 40, col3 <= 50) 执行索引扫描。可以将这种方法视作使用相同索引执行两次索引扫描。

                    
        QUERY:
        ------
        SELECT * FROM TAB1
        WHERE col1 >= 1  AND col1 <= 2
        AND   col2 >= 2  AND col2 <= 4
        AND   col3 >= 40 AND col3 <= 50
        
        Estimated Cost: 37
       Estimated # of Rows Returned: 66
        
        1) informix.tab1: INDEX PATH
        
        (1) Index Keys: col1 col2 col3   (Key-Only)  (Serial, fragments: ALL)
        Index Self Join Keys (col1 col2 )
        Lower bound: informix.tab1.col1 >= 1 AND (informix.tab1.col2 >= 2 )
        Upper bound: informix.tab1.col1 <= 2 AND (informix.tab1.col2 <= 4 )
        
        Lower Index Filter: (informix.tab1.col1 = informix.tab1.col1 
        AND informix.tab1.col2 = informix.tab1.col2 ) 
        AND informix.tab1.col3 >= 40
        
        Upper Index Filter: informix.tab1.col3 <= 50
        Index Key Filters:  (informix.tab1.col2 <= 4 ) AND
        (informix.tab1.col2 >= 2 )
        
        
        Query statistics:
        -----------------
        
        Table map :
        ----------------------------
        Internal name     Table name
        ----------------------------
        t1                tab1
        
        type     table  rows_prod  est_rows  rows_scan  time       est_cost
        -------------------------------------------------------------------
        scan     t1     66         66        66         00:00:00   37 
                

                    
        QUERY:
        ------
        SELECT {+AVOID_INDEX_SJ(TAB1 IDX1)} * FROM TAB1
        WHERE col1 >= 1 AND col1 <= 2
        AND   col2 >= 2 AND col2 <= 4
        AND   col3 >= 40 AND col3 <= 50
        
        
        DIRECTIVES FOLLOWED:
        AVOID_INDEX_SJ ( tab1 idx1 )
        DIRECTIVES NOT FOLLOWED:
        
        Estimated Cost: 208144 
       Estimated # of Rows Returned: 66
        
        1) informix.tab1: INDEX PATH
        
        (1) Index Keys: col1 col2 col3   (Key-Only)  (Serial, fragments: ALL)
        Lower Index Filter: informix.tab1.col1 >= 1 
        AND (informix.tab1.col2 >= 2 ) 
        AND (informix.tab1.col3 >= 40 )
        
        Upper Index Filter: informix.tab1.col1 <= 2 
        AND (informix.tab1.col3 <= 50 ) 
        AND (informix.tab1.col2 <= 4 )
        
        Index Key Filters:  (informix.tab1.col3 <= 50 ) AND
        (informix.tab1.col2 <= 4 ) AND
        (informix.tab1.col2 >= 2 ) AND
        (informix.tab1.col3 >= 40 )
        
        
        Query statistics:
        -----------------
        
        Table map :
        ----------------------------
        Internal name     Table name
        ----------------------------
        t1                tab1
        
        type     table  rows_prod  est_rows  rows_scan  time       est_cost
        -------------------------------------------------------------------
        scan     t1     66         66        3500011    00:00:13   208144