【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

  2   where data_object_id =5;

no rows selected

elapsed: 00:00:00.00

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 |

   1 - access("data_object_id"=5)

       filter("data_object_id"=5)

         15  consistent gets

          0  physical reads

           。。。。。。

          0  rows processed

sql> select object_id from un_test where object_id = 5;

elapsed: 00:00:00.01

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 |

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

   1 - access("object_id"=5)

          2  consistent gets

           。。。。。

          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

|   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 |

   1 - filter("owner"='sys')

       1674  consistent gets

          。。。。。。。

      23021  rows processed

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

sql> select /* index(a)*/ owner,data_object_id from un_test a

  2  where wner ='sys';

plan hash value: 3998526102

|   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 |

   1 - access("owner"='sys')

       1586  consistent gets

          。。。。。

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

sql> select /*+ index (a idx_un_test_ownid_dataid)*/ count(*) from un_test a

  2  where data_object_id = 5;

plan hash value: 1636324484

|   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 |

   2 - access("data_object_id"=5)

---位图索引。。

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

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 |       |       |            |          |

   3 - access("a"."object_type"='table')

        347  consistent gets

          1  physical reads

       2921  rows processed