【SQL】查找数据的方式 (二)

如果表查询中所有的列都包括在了索引中,就有可能使用FFS,当然也可以使用hint来选择FFS。下面看看使用不同方式访问索引的例子:

SQL> create index idx_un_tset_ownid_dataid on un_test (owner,data_object_id);
Index created.
Elapsed: 00:00:00.96
SQL> select owner ,data_object_id from un_test
  2  where data_object_id >5;
6891 rows selected.
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 287826149
-------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                          |  6913 | 55304 |    32   (4)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IDX_UN_TSET_OWNID_DATAID |  6913 | 55304 |    32   (4)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DATA_OBJECT_ID">5)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        602  consistent gets
        135  physical reads
          0  redo size
          ..........
          0  sorts (memory)
          0  sorts (disk)
       6891  rows processed

SQL> select owner ,data_object_id from un_test
  2   where data_object_id =5;
no rows selected
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1786681723
---------------------------------------------------------------------------------------------
| Id  | Operation        | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                          |     1 |     8 |    21   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IDX_UN_TSET_OWNID_DATAID |     1 |     8 |    21   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DATA_OBJECT_ID"=5)
       filter("DATA_OBJECT_ID"=5)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
           。。。。。。
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select object_id from un_test where object_id = 5;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 333614268
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |     5 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| I_TEST_UNI |     1 |     5 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------
   1 - access("OBJECT_ID"=5)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
           。。。。。
          0  sorts (disk)
          1  rows processed
SQL> select /*+ index_ffs(a)*/ owner,data_object_id from un_test a
  2  where wner = 'SYS';
23021 rows selected.
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
Plan hash value: 287826149
-------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                          |  2668 | 21344 |    32   (4)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IDX_UN_TSET_OWNID_DATAID |  2668 | 21344 |    32   (4)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"='SYS')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1674  consistent gets
          0  physical reads
          。。。。。。。
          0  sorts (memory)
          0  sorts (disk)
      23021  rows processed
----------------
SQL> SELECT /* INDEX(A)*/ OWNER,DATA_OBJECT_ID FROM UN_TEST A
  2  WHERE WNER ='SYS';

23021 rows selected.
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
Plan hash value: 3998526102
---------------------------------------------------------------------------------------------
| Id  | Operation        | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                          |  2668 | 21344 |     8   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_UN_TSET_OWNID_DATAID |  2668 | 21344 |     8   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1586  consistent gets
          0  physical reads
          。。。。。
          0  sorts (disk)
      23021  rows processed
----------------------------
SQL> select /*+ index (a idx_un_test_ownid_dataid)*/ count(*) from un_test a
  2  where data_object_id = 5;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1636324484
---------------------------------------------------------------------------------------------
| Id  | Operation        | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                          |     1 |     2 |    21   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |                          |     1 |     2 |            |          |
|*  2 |   INDEX SKIP SCAN| IDX_UN_TSET_OWNID_DATAID |     1 |     2 |    21   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DATA_OBJECT_ID"=5)
       filter("DATA_OBJECT_ID"=5)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
           。。。。。
          0  sorts (disk)
          1  rows processed

---位图索引。。
SQL> select /*+ index(a idx_type_bit)*/ * from un_test a
  2  where a.object_type ='TABLE';
2921 rows selected.
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 4191918535
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  1779 |   161K|   194   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID | UN_TEST      |  1779 |   161K|   194   (0)| 00:00:03 |
|   2 |   BITMAP CONVERSION TO ROWIDS|              |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | IDX_TYPE_BIT |       |       |            |          |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        347  consistent gets
          1  physical reads
          0  sorts (memory)
          0  sorts (disk)
       2921  rows processed

上一篇:阿里云SSL证书类型DV SSL、OV SSL和EV SSL区别及选择方法


下一篇:SQL两种简单分页查询方式