SQL调优

sql 代码

SQL>create table Employee(
  2        ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3       First_Name         VARCHAR2(10 BYTE),
  4        Last_Name          VARCHAR2(10 BYTE),
  5        Start_Date         DATE,
  6       End_Date           DATE,
  7       Salary             Number(8,2),
  8        City               VARCHAR2(10 BYTE),
  9        Description        VARCHAR2(15 BYTE)
 10    )
 11    /

表已创建。

SQL>insert into Employee(ID,  First_Name, Last_Name, Start_Date,
      End_Date,                       Salary,  City,       Description)
  2                   values ('01','Jason',    'Martin',  to_date('19960725','YY
YYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
  3     /

已创建 1 行。

SQL>insert into Employee(ID,  First_Name, Last_Name, Start_Date,
      End_Date,                       Salary,  City,       Description)
  2                   values('02','Alison',   'Mathews', to_date('19760321','YYY
YMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
  3      /

已创建 1 行。

SQL>select * from Employee
  2   /

ID   FIRST_NAME LAST_NAME  START_DATE     END_DATE           SALARY CITY
---- ---------- ---------- -------------- -------------- ---------- ----------
DESCRIPTION
---------------
01   Jason      Martin     25-7月 -96     25-7月 -06        1234.56 Toronto
Programmer

02   Alison     Mathews    21-3月 -76     21-2月 -86        6661.78 Vancouver
Tester


SQL>set autotrace on;
SQL>select count(*) from employee;

  COUNT(*)
----------
         2


执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=2
          )





统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        390  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>set autotrace off;
SQL>set autotrace on;
SQL>set timing on;
SQL>select count(*) from employee;

  COUNT(*)
----------
         2

已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=2
          )





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

SQL>set autotrace off;
SQL>set autotrace traceonly explain
SQL>select * from employee;
已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=130
          )

   1    0   TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=2 B
          ytes=130)




SQL>analyze table employee compute statistics;

表已分析。

已用时间:  00: 00: 00.18
SQL> select * from employee;
已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=98)
   1    0   TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=2 B
          ytes=98)




SQL>set autotrace off

分享到：

dos下启动监听及服务 | SQL PLUS命令的使用大全

• 2007-10-07 09:06
• 浏览 1395
• 评论(0)
• 论坛回复 / 浏览 (0 / 3212)
• 分类:数据库
• 查看更多