?? stat.txt
字號:
begin
dbms_stats.gather_table_stats(ownname=> 'SCOTT', tabname=> 'EMP', partname=> NULL);
end;
begin
dbms_stats.gather_index_stats(ownname=> 'SCOTT', indname=> 'PK_EMP', partname=> NULL);
end;
declare
counter number;
begin
for counter in 1..100000 loop
insert into wzy values(counter,'wzy'||counter);
end loop;
commit;
end;
select operation,cost,bytes from plan_table
SQL> select operation,cost,bytes from plan_table;
OPERATION COST BYTES
------------------------------ ---------- ----------
TABLE ACCESS 40 1200000
SELECT STATEMENT 40 1200000
OPERATION COST BYTES
------------------------------ ---------- ----------
TABLE ACCESS 40 1199988
SELECT STATEMENT 40 1199988
analyze table wzy compute statistics;
delete from plan_table;
commit;
explain plan
set statement_id='test'
for
select * from person where id=234;
select
LPAD(' ',2*LEVEL)||
OPERATION||' '||OPTIONS||' '||
decode (object_owner,null,' ',
object_owner||'.'||object_name)||' '||
decode(optimizer,null,' ',optimizer)||' '||
decode(cost,null,' ',
'cost='||cost||
' rows expected= '||cardinality||' bytes='||bytes)
Q_plan
from plan_table
connect by prior id=parent_id and statement_id='test'
start with id=0 and statement_id='test';
Q_PLAN
----------------------------------------------------------------
INSERT STATEMENT CHOOSE cost=1 rows expected= 2 bytes=14
SQL*Plus: Release 8.1.7.0.0 - Production on 星期三 11月 28 19:48:40 2001
(c) Copyright 2000 Oracle Corporation. All rights reserved.
連接到:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL> select count(*) from wzy;
COUNT(*)
----------
100000
SQL> explain plan for
2 select * from wzy;
已解釋。
SQL> delete from plan_table;
已刪除5行。
SQL> commit;
提交完成。
SQL> explain plan for
2 select * from wzy;
已解釋。
SQL> select operation,cost,bytes from plan_table;
OPERATION COST BYTES
------------------------------ ---------- ----------
TABLE ACCESS
SELECT STATEMENT
SQL> analyze table wzy;
analyze table wzy
*
ERROR 位于第 1 行:
ORA-01490: 無效的 ANALYZE 命令
SQL> analyze table wzy compute statistics;
表已分析。
SQL> delete from plan_table;
已刪除2行。
SQL> commit;
提交完成。
SQL> explain plan for
2 select * from wzy;
已解釋。
SQL> select operation,cost,bytes from plan_table;
OPERATION COST BYTES
------------------------------ ---------- ----------
TABLE ACCESS 40 1200000
SELECT STATEMENT 40 1200000
SQL> delete from plan_table;
已刪除2行。
SQL> commit;
提交完成。
SQL> explain plan for
2 select * from wzy where id>1;
已解釋。
SQL> select opertion,cost,bytes from plan_table;
select opertion,cost,bytes from plan_table
*
ERROR 位于第 1 行:
ORA-00904: 無效列名
SQL> edit
已寫入文件 afiedt.buf
1* select operation,cost,bytes from plan_table
SQL> /
OPERATION COST BYTES
------------------------------ ---------- ----------
TABLE ACCESS 40 1199988
SELECT STATEMENT 40 1199988
SQL> delete from plan_table;
已刪除2行。
SQL> explain plan for
2 select * from wzy where id='321';
已解釋。
SQL> select operation,cost,bytes from plan_table;
OPERATION COST BYTES
------------------------------ ---------- ----------
SELECT STATEMENT 2 12
TABLE ACCESS 2 12
INDEX 1
SQL> delete from plan_table;
已刪除3行。
SQL> commit;
提交完成。
SQL> explain plan for
2 select * from wzy;
已解釋。
SQL> select operation,cost,bytes from plan_table;
OPERATION COST BYTES
------------------------------ ---------- ----------
TABLE ACCESS 40 1300000
SELECT STATEMENT 40 1300000
SQL> delete from plan_table;
已刪除2行。
SQL> commit;
提交完成。
SQL> explain plan for
2 select * from where id=234;
select * from where id=234
*
ERROR 位于第 2 行:
ORA-00903: 無效表名
SQL> edit
已寫入文件 afiedt.buf
1 explain plan for
2* select * from wzy where id=234
SQL> /
已解釋。
SQL> select operation,cost,bytes from plan_table;
OPERATION COST BYTES
------------------------------ ---------- ----------
TABLE ACCESS 2 13
SELECT STATEMENT 2 13
INDEX 1
SQL> delete from plan_table;
已刪除3行。
SQL> commit;
提交完成。
SQL> explain plan
2 set statement_id='test'
3 for
4 select * from wzy;
已解釋。
SQL> select
2 LPAD(' ',2*LEVEL)||
3 OPERATION||' '||OPTIONS||' '||
4 decode (object_owner,null,' ',
5 object_owner,||'.'||object_name)||' '||
6 decode(optimizer,null,' ',optimizer)
7 decode(cost,null,' ',
8 'cost='||cost||
9 'rows expected ='||cardinality)
10 Q_plan
11 from plan_table
12 connect by prior id=parent_id and statement_id='test'
13 start with id=0 and statement_id='test';
object_owner,||'.'||object_name)||' '||
*
ERROR 位于第 5 行:
ORA-00936: 缺少表達式
SQL> select
2 LPAD(' ',2*LEVEL)||
3 OPERATION||' '||OPTIONS||' '||
4 decode (object_owner,null,' ',
5 object_owner||'.'||object_name)||' '||
6 decode(optimizer,null,' ',optimizer)
7 decode(cost,null,' ',
8 'cost='||cost||
9 'rows expected ='||cardinality)
10 Q_plan
11 from plan_table
12 connect by prior id=parent_id and statement_id='test'
13 start with id=0 and statement_id='test';
decode(cost,null,' ',
*
ERROR 位于第 7 行:
ORA-00923: 未找到預期 FROM 關鍵字
SQL> select
2 LPAD(' ',2*LEVEL)||
3 OPERATION||' '||OPTIONS||' '||
4 decode (object_owner,null,' ',
5 object_owner||'.'||object_name)||' '||
6 decode(optimizer,null,' ',optimizer)||
7 decode(cost,null,' ',
8 'cost='||cost||
9 'rows expected ='||cardinality)
10 Q_plan
11 from plan_table
12 connect by prior id=parent_id and statement_id='test'
13 start with id=0 and statement_id='test';
Q_PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT CHOOSEcost=40rows expected =100000
TABLE ACCESS FULL SCOTT.WZY ANALYZEDcost=40rows expected =100000
SQL> select
2 LPAD(' ',2*LEVEL)||
3 OPERATION||' '||OPTIONS||' '||
4 decode (object_owner,null,' ',
5 object_owner||'.'||object_name)||' '||
6 decode(optimizer,null,' ',optimizer)||' '||
7 decode(cost,null,' ',
8 'cost='||cost||
9 'rows expected ='||cardinality)
10 Q_plan
11 from plan_table
12 connect by prior id=parent_id and statement_id='test'
13 start with id=0 and statement_id='test';
Q_PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT CHOOSE cost=40rows expected =100000
TABLE ACCESS FULL SCOTT.WZY ANALYZED cost=40rows expected =100000
SQL> select
2 LPAD(' ',2*LEVEL)||
3 OPERATION||' '||OPTIONS||' '||
4 decode (object_owner,null,' ',
5 object_owner||'.'||object_name)||' '||
6 decode(optimizer,null,' ',optimizer)||' '||
7 decode(cost,null,' ',
8 'cost='||cost||
9 ' rows expected ='||cardinality)
10 Q_plan
11 from plan_table
12 connect by prior id=parent_id and statement_id='test'
13 start with id=0 and statement_id='test';
Q_PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT CHOOSE cost=40 rows expected =100000
TABLE ACCESS FULL SCOTT.WZY ANALYZED cost=40 rows expected =100000
SQL> select
2 LPAD(' ',2*LEVEL)||
3 OPERATION||' '||OPTIONS||' '||
4 decode (object_owner,null,' ',
5 object_owner||'.'||object_name)||' '||
6 decode(optimizer,null,' ',optimizer)||' '||
7 decode(cost,null,' ',
8 'cost='||cost||
9 ' rows expected= '||cardinality)
10 Q_plan
11 from plan_table
12 connect by prior id=parent_id and statement_id='test'
13 start with id=0 and statement_id='test';
Q_PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT CHOOSE cost=40 rows expected= 100000
TABLE ACCESS FULL SCOTT.WZY ANALYZED cost=40 rows expected= 100000
SQL> delete from plan_table;
已刪除2行。
SQL> commit;
提交完成。
SQL> delete from plan_table;
已刪除0行。
SQL> commit;
提交完成。
SQL> explain plan
2 set statement_id='test'
3 for
4 select * from wzy where id=23;
已解釋。
SQL>
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -