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
