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
