в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:58:37Z

1 версия

Новая страница

==Execution plan for the specified statement_id==



<source lang="sql">

SQL>
SQL> UNDEFINE v_statement_id;
SQL>
SQL> SELECT id ||
2 DECODE(id, 0, "", LPAD(" ", 2*(level - 1))) || " " ||
3 operation || " " ||
4 options || " " ||
5 object_name || " " ||
6 object_type || " " ||
7 DECODE(cost, NULL, "", "Cost = " || position)
8 AS execution_plan
9 FROM plan_table
10 CONNECT BY PRIOR id = parent_id
11 AND statement_id = "&&v_statement_id"
12 START WITH id = 0
13 AND statement_id = "&v_statement_id";
Enter value for v_statement_id:
old 11: AND statement_id = "&&v_statement_id"
new 11: AND statement_id = ""
old 13: AND statement_id = "&v_statement_id"
new 13: AND statement_id = ""
no rows selected
SQL>

</source>

==explain plan for query==



<source lang="sql">

SQL>
SQL> create table emp(
2 emp_id integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,shortZipCode varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,company_name varchar2(50));
Table created.
SQL>
SQL>
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (1,"Jones","Joe","J","1 Ave","New York","NY","11202","1111","212", "221-4333","Big Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (2,"Smith","Sue","J","1 Street","New York","NY","11444","1111","212", "436-6773","Little Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (3,"X","Peggy","J","1 Drive","New York","NY","45502","2222","212", "234-4444","Medium Company");
1 row created.

SQL> explain plan for
2 select *
3 from emp
4 where lastname = "Young";
Explained.
SQL> select operation
2 from plan_table;
SELECT STATEMENT
TABLE ACCESS
SELECT STATEMENT
TABLE ACCESS
SELECT STATEMENT
TABLE ACCESS
6 rows selected.
SQL> explain plan for
2 select *
3 from emp
4 where firstname="Joe";
Explained.
SQL> select operation
2 from plan_table;
SELECT STATEMENT
TABLE ACCESS
SELECT STATEMENT
TABLE ACCESS
SELECT STATEMENT
TABLE ACCESS
SELECT STATEMENT
TABLE ACCESS
8 rows selected.
SQL>
SQL>
SQL> set autotrace off
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>

</source>

==explain plan for select statement==



<source lang="sql">

SQL> create table t as select mod(object_id,10) id, a.* from all_objects a;
SQL>
SQL> analyze table t compute statistics
2 for table
3 for columns id;
SQL>
SQL>
SQL>
SQL> alter session set optimizer_max_permutations=80000;
SQL>
SQL> explain plan for
2 select count(*)
3 from t t1, t t2, t t3, t t4, t t5, t t6
4 where t1.id = t2.id
5 and t1.id = t3.id
6 and t1.id = t4.id
7 and t1.id = t5.id
8 and t1.id = t6.id;
SQL>
SQL> set autotrace off
SQL>
SQL>
SQL> drop table t;

</source>

==Explain sql statement==



<source lang="sql">

SQL>
SQL> create table emp (
2 EmpNo NUMBER(10) primary key,
3 Name VARCHAR2(40),
4 DeptNo NUMBER(2),
5 Salary NUMBER(7,2),
6 Birth_Date DATE,
7 Soc_Sec_Num VARCHAR2(9),
8 State_Code CHAR(2)
9 );
SQL>
SQL> explain plan
2 set Statement_Id = "TEST"
3 for
4 select * from emp;
SQL>
SQL> select LPAD(" ",2*Level)||Operation||" "||Options||" "||Object_Name Q_Plan
2 from PLAN_TABLE
3 where Statement_Id = "TEST"
4 connect by prior ID = Parent_ID and Statement_ID = "TEST"
5 start with ID=0;

Q_PLAN
------------------------------------------------------------------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS FULL EMP
TABLE ACCESS FULL EMP
SELECT STATEMENT
TABLE ACCESS FULL EMP
TABLE ACCESS FULL EMP

SQL>
SQL> set autotrace off explain
SQL>
SQL>
SQL> drop table emp;

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/SQL Plus/explain plan - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия