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

統計資訊
----------------------------------------------------------
          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> spool off

執行資訊統計後沒有使用索引

相關文章