执行信息统计后没有使用索引

最新推荐文章于 2022-07-08 10:29:04 发布

cojm55771

最新推荐文章于 2022-07-08 10:29:04 发布

阅读量125

点赞数

SQL> conn system/yang as sysdba
已连接。
SQL> create table t1 as select * from dba_objects
2 where wner='SYS'
3 and object_type not like '%BODY'
4 and object_type not like '%JAVA%';
表已创建。
SQL> set timing on
SQL> create table t2 as select * from dba_segments where wner='SYS';
表已创建。
已用时间: 00: 00: 00.34
SQL> create table t3 as select * from dba_indexes where wner='SYS';
表已创建。
已用时间: 00: 00: 00.51
SQL> select count(*) from t2;
COUNT(*)
----------
      2087
已用时间: 00: 00: 00.06
SQL> select count(*) from t3;
COUNT(*)
----------
      1060
已用时间: 00: 00: 00.03
SQL> alter table t1 add constraint pk_t1 primary key (object_name);
alter table t1 add constraint pk_t1 primary key (object_name)
                              *
第 1 行出现错误:
ORA-02437: 无法验证 (SYS.PK_T1) - 违反主键
SQL> create index i_t1 on t1 t1(object_id);
索引已创建。
已用时间: 00: 00: 00.11
SQL> set autot on
SQL> select count (*) from t1;
COUNT(*)
----------
      7909
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    29   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 8165 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
         28 recursive calls
          0 db block gets
        178 consistent gets
          0 physical reads
          0 redo size
        420 bytes sent via SQL*Net to client
        416 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> analyze table t1 compute statistics;
表已分析。
已用时间: 00: 00: 00.20
SQL> select count (*) from t1;

COUNT(*)
----------
      7909
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    29   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 7909 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
        105 consistent gets
          0 physical reads
          0 redo size
        420 bytes sent via SQL*Net to client
        416 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> ALTER SESSION SET optimizer_mode=first_rows;
会话已更改。
已用时间: 00: 00: 00.00
SQL> select count (*) from t1;
COUNT(*)
----------
      7909

已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    29   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 7909 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------

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

SQL> ALTER SESSION SET optimizer_mode=choose;
会话已更改。
已用时间: 00: 00: 00.00
SQL> select count (*) from t1;
COUNT(*)
----------
      7909
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    29   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 7909 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------