📄 stat.txt
字号:
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=2 rows expected= 1
TABLE ACCESS BY INDEX ROWID SCOTT.WZY ANALYZED cost=2 rows expected= 1
INDEX UNIQUE SCAN SCOTT.SYS_C001280 ANALYZED cost=1 rows expected= 1
SQL> delete from plan_table;
已删除3行。
SQL> commit;
提交完成。
SQL> explain plan
2 set statement_id='test'
3 for
4 select * from wzy where name like 'wzy1_';
已解释。
SQL>
SQL>
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= 12161
TABLE ACCESS FULL SCOTT.WZY ANALYZED cost=40 rows expected= 12161
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||' bytes='||bytes)
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= 12161 bytes=158093
TABLE ACCESS FULL SCOTT.WZY ANALYZED cost=40 rows expected= 12161 bytes=1580
93
SQL> delete from plan_table;
已删除2行。
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||' bytes='||bytes)
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 bytes=1300000
TABLE ACCESS FULL SCOTT.WZY ANALYZED cost=40 rows expected= 100000 bytes=130
0000
SQL> desc wzy
名称 空? 类型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
NAME VARCHAR2(20)
SQL> select avg(length(id)+length(name)) from wzy;
AVG(LENGTH(ID)+LENGTH(NAME))
----------------------------
12.7779
SQL> delete from plan_table;
已删除2行。
SQL> commit;
提交完成。
SQL> explain plan
2 set statement_id='test'
3 for
4 select * from wzy where id=213;
已解释。
SQL>
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||' bytes='||bytes)
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=2 rows expected= 1 bytes=13
TABLE ACCESS BY INDEX ROWID SCOTT.WZY ANALYZED cost=2 rows expected= 1 bytes
=13
INDEX UNIQUE SCAN SCOTT.SYS_C001280 ANALYZED cost=1 rows expected= 1 bytes
=
SQL> desc plan_table
名称 空? 类型
----------------------------------------- -------- ----------------------------
STATEMENT_ID VARCHAR2(30)
TIMESTAMP DATE
REMARKS VARCHAR2(80)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(30)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
DISTRIBUTION VARCHAR2(30)
SQL> desc wzy
名称 空? 类型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
NAME VARCHAR2(20)
SQL> delete from plan_table;
已删除3行。
SQL> commit;
提交完成。
SQL> explain plan
2 set statement_id='test'
3 for
4 select * from wzy where id>212 and id<214;
已解释。
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||' bytes='||bytes)
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=3 rows expected= 3 bytes=39
TABLE ACCESS BY INDEX ROWID SCOTT.WZY ANALYZED cost=3 rows expected= 3 bytes
=39
INDEX RANGE SCAN SCOTT.SYS_C001280 ANALYZED cost=2 rows expected= 3 bytes=
SQL> delete from plan_table;
已删除3行。
SQL> commit;
提交完成。
SQL> explain plan
2 set statement_id='test'
3 for
4 select * from wzy where name='wzy1'
5
SQL>
SQL>
SQL> delete from plan_table;
已删除0行。
SQL> commit;
提交完成。
SQL> explain plan
2 set statement_id='test'
3 for
4 select * from wzy where name='wzy1';
已解释。
SQL>
SQL>
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||' bytes='||bytes)
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= 1 bytes=13
TABLE ACCESS FULL SCOTT.WZY ANALYZED cost=40 rows expected= 1 bytes=13
SQL> delete from plan_table;
已删除2行。
SQL> commit;
提交完成。
SQL> explain plan
2 set statement_id='test'
3 for
4 select * from wzy where id=23;
已解释。
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||' bytes='||bytes)
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=2 rows expected= 1 bytes=13
TABLE ACCESS BY INDEX ROWID SCOTT.WZY ANALYZED cost=2 rows expected= 1 bytes
=13
INDEX UNIQUE SCAN SCOTT.SYS_C001280 ANALYZED cost=1 rows expected= 1 bytes
=
SQL> select cost from plan_table;
COST
----------
2
1
2
SQL> select operation from plan_table;
OPERATION
------------------------------
TABLE ACCESS
INDEX
SELECT STATEMENT
SQL> delete from plan_table;
已删除3行。
SQL> commit;
提交完成。
SQL> explain plan
2 set statement_id='test'
3 for
4 select * from wzy where name='wzy324';
已解释。
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||' bytes='||bytes)
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= 1 bytes=13
TABLE ACCESS FULL SCOTT.WZY ANALYZED cost=40 rows expected= 1 bytes=13
SQL> select * from wzy where name=wzy23';
ERROR：
ORA-01756: 括号内的字符串没有正确结束
SQL> select * from wzy where name='wzy23';
ID NAME
---------- --------------------
23 wzy23
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -