执行计划之CONCATENATION

CREATE TABLE T_CONCAT

    (ID NUMBER,

    NAME VARCHAR2(30),

    TYPE VARCHAR2(30));

INSERT INTO T_CONCAT

  SELECT ROWNUM, OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS;

SQL> CREATE INDEX IND_CONCAT_NAME

  2  ON T_CONCAT (NAME);

索引已创建。

SQL> CREATE INDEX IND_CONCAT_TYPE

  2  ON T_CONCAT(TYPE);

索引已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_CONCAT')

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*)

  FROM T_CONCAT

 WHERE NAME = 'T_CONCAT'

    OR TYPE = 'DATABASE LINK';  2    3    4

Execution Plan

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

Plan hash value: 1182419877

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

| Id  | Operation			 | Name 	   | Rows  | Bytes | Cost (%CPU)| Time	   |

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

|   0 | SELECT STATEMENT		 |		   |	 1 |	34 |	 9   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE 		 |		   |	 1 |	34 |		|	   |

|   2 |   BITMAP CONVERSION COUNT	 |		   |  1882 | 63988 |	 9   (0)| 00:00:01 |

|   3 |    BITMAP OR			 |		   |	   |	   |		|	   |

|   4 |     BITMAP CONVERSION FROM ROWIDS|		   |	   |	   |		|	   |

|*  5 |      INDEX RANGE SCAN		 | IND_CONCAT_TYPE |	   |	   |	 6   (0)| 00:00:01 |

|   6 |     BITMAP CONVERSION FROM ROWIDS|		   |	   |	   |		|	   |

|*  7 |      INDEX RANGE SCAN		 | IND_CONCAT_NAME |	   |	   |	 3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   5 - access("TYPE"='DATABASE LINK')

   7 - access("NAME"='T_CONCAT')

Statistics

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

	  1  recursive calls

	  0  db block gets

	  5  consistent gets

	  0  physical reads

	  0  redo size

	422  bytes sent via SQL*Net to client

	419  bytes received via SQL*Net from client

	  2  SQL*Net roundtrips to/from client

	  0  sorts (memory)

	  0  sorts (disk)

	  1  rows processed

SQL> SELECT  /*+ USE_CONCAT */  COUNT(*)

  FROM T_CONCAT

 WHERE NAME = 'T_CONCAT'

    OR TYPE = 'DATABASE LINK';  2    3    4

Execution Plan

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

Plan hash value: 1333442903

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

| Id  | Operation		      | Name		| Rows	| Bytes | Cost (%CPU)| Time	|

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

|   0 | SELECT STATEMENT	      | 		|     1 |    34 |    49   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE 	      | 		|     1 |    34 |	     |		|

|   2 |   CONCATENATION 	      | 		|	|	|	     |		|

|   3 |    TABLE ACCESS BY INDEX ROWID| T_CONCAT	|     2 |    68 |     4   (0)| 00:00:01 |

|*  4 |     INDEX RANGE SCAN	      | IND_CONCAT_NAME |     2 |	|     3   (0)| 00:00:01 |

|*  5 |    TABLE ACCESS BY INDEX ROWID| T_CONCAT	|  1881 | 63954 |    45   (0)| 00:00:01 |

|*  6 |     INDEX RANGE SCAN	      | IND_CONCAT_TYPE |  1881 |	|     6   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   4 - access("NAME"='T_CONCAT')

   5 - filter(LNNVL("NAME"='T_CONCAT'))

   6 - access("TYPE"='DATABASE LINK')

Statistics

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

	  1  recursive calls

	  0  db block gets

	  6  consistent gets

	  0  physical reads

	  0  redo size

	422  bytes sent via SQL*Net to client

	419  bytes received via SQL*Net from client

	  2  SQL*Net roundtrips to/from client

	  0  sorts (memory)

	  0  sorts (disk)

	  1  rows processed