如何理解AND-EQUAL！

最新推荐文章于 2024-10-16 11:14:53 发布

clt3617

最新推荐文章于 2024-10-16 11:14:53 发布

阅读量181

点赞数

文章标签：数据库

当我第一次看到hint相关内容中讲到AND_EQUAL时（

The `AND_EQUAL` hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes.

）感觉没有读懂，不知道这里提到的merge到底指的什么意思，但是当看到RBO Path 9: Single-Column Indexes章节中的解释时对AND_EQUAL已经非常清楚了，原来是对单列index scan找到满足条件的rowid的merge（If the WHERE clauses uses columns of many single-column indexes, then Oracle executes the statement by performing a range scan on each index to retrieve the rowids of the rows that satisfy each condition. Oracle then merges the sets of rowids to obtain a set of rowids of rows that satisfy all conditions. Oracle then accesses the table using these rowids.）

[@more@]

AND_EQUAL

The `AND_EQUAL` hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes.

and_equal_hint::=

Text description of the illustration and_equal_hint.gif

where:

table specifies the name or alias of the table associated with the indexes to be merged.
index specifies an index on which an index scan is to be performed. You must specify at least two indexes. You cannot specify more than five.

--===============================================

RBO Path 9: Single-Column Indexes

This access path is available if the statement's WHERE clause uses the columns of one or more single-column indexes in equality conditions. For multiple single-column indexes, the conditions must be combined with AND operators.

If the WHERE clause uses the column of only one index, then Oracle executes the statement by performing a range scan on the index to retrieve the rowids of the selected rows, and then accesses the table by these rowids.

For example:

In the following statement, there is an index on the job column of the emp table:

SELECT * 
    FROM emp 
    WHERE job = 'ANALYST';

The EXPLAIN PLAN output for this statement might look like this:

OPERATION                 OPTIONS        OBJECT_NAME 
----------------------------------------------------- 
SELECT STATEMENT 
  TABLE ACCESS            BY ROWID       EMP 
    INDEX                 RANGE SCAN     JOB_INDEX

job_index is the index on emp.job.

If the WHERE clauses uses columns of many single-column indexes, then Oracle executes the statement by performing a range scan on each index to retrieve the rowids of the rows that satisfy each condition. Oracle then merges the sets of rowids to obtain a set of rowids of rows that satisfy all conditions. Oracle then accesses the table using these rowids.

Oracle can merge up to five indexes. If the WHERE clause uses columns of more than five single-column indexes, then Oracle merges five of them, accesses the table by rowid, and then tests the resulting rows to determine whether they satisfy the remaining conditions before returning them.

In the following statement, there are indexes on both the job and deptno columns of the emp table:

SELECT * 
    FROM emp 
    WHERE job = 'ANALYST' 
      AND deptno = 20;

The EXPLAIN PLAN output for this statement might look like this:

OPERATION                 OPTIONS        OBJECT_NAME 
----------------------------------------------------- 
SELECT STATEMENT 
  TABLE ACCESS            BY ROWID       EMP 
    AND-EQUAL 
      INDEX               RANGE SCAN     JOB_INDEX 
      INDEX               RANGE SCAN     DEPTNO_INDEX

The AND-EQUAL operation merges the rowids obtained by the scans of the job_index and the deptno_index, resulting in a set of rowids of rows that satisfy the query.

测试例子：

SQL> select *from tt where id=1 and name='a';

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 896727551

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

---------

ime |

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

---------

| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 0

0:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| TT | 1 | 6 | 2 (0)| 0

0:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_TT_ID | 1 | | 1 (0)| 0

0:00:01 |

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

---------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("NAME"='a')
2 - access("ID"=1)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
333 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL> select /*+ rule */ *from tt where id=1 and name='a';

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 2967672675

-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | AND-EQUAL | |
|* 2 | INDEX RANGE SCAN| IDX_TT_ID |
|* 3 | INDEX RANGE SCAN| IDX_TT_NAME |
-----------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)
3 - access("NAME"='a')

Note
-----
- rule based optimizer used (consider using cbo)

SQL> select table_name,index_name from user_indexes where table_name='TT';

TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
TT IDX_TT_ID
TT IDX_TT_NAME

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1003236/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1003236/