Oracle PL/SQL/SQL Plus/autotrace
Содержание
- 1 Autotrace a large table
- 2 autotrace a nested query
- 3 autotrace ansi full outer join
- 4 Autotrace a query on a huge table
- 5 Autotrace a query with group clause
- 6 autotrace command
- 7 autotrace count(*)
- 8 autotrace ctxsys.context index
- 9 AUTOTRACE exists (subquery)
- 10 Autotrace lower text function
- 11 autotrace merge command
- 12 Autotrace on and off
- 13 Autotrace running total
- 14 AUTOTRACE table joining
- 15 AUTOTRACE table joining and aggregate function
- 16 autotrace table with/without an index
- 17 Execution Plan
- 18 set autotrace on explain for every single statement
- 19 set autotrace traceonly
- 20 set autotrace traceonly explain, and condition
- 21 set autotrace traceonly explain for bitmap index
- 22 set autotrace traceonly statistics
- 23 set autotrace traceonly statistics for "select * from tableName"
Autotrace a large table
SQL>
SQL> create table my_all_objects
2 nologging
3 as
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 /
Table created.
SQL>
SQL>
SQL> set autotrace traceonly
SQL>
SQL> select owner, count(*)
2 from my_all_objects
3 group by owner;
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2509106709
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33589 | 557K| 136 (6)| 00:00:02 |
| 1 | HASH GROUP BY | | 33589 | 557K| 136 (6)| 00:00:02 |
| 2 | TABLE ACCESS FULL| MY_ALL_OBJECTS | 33589 | 557K| 130 (2)| 00:00:02 |
-------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
537 consistent gets
466 physical reads
0 redo size
694 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL>
SQL> drop table my_all_objects;
Table dropped.
SQL>
SQL>
SQL>
autotrace a nested query
SQL>
SQL> create table customer(
2 cust_no 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 ,zip_4 varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,company_name varchar2(50)
14 );
Table created.
SQL>
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1, "Allen", "Joe","J","10 Ave","London","CA","11111","1111","111", "111-1111","Big Company");
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Ward","Sue","W","20 Ave","New York","NY","44444","4444","444", "436-4444","B Company");
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Jason","Pure","J","50 St","Longli","CA","55555","5555","555", "234-4444","C Company");
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Bird","Jill", null,"30 St","Pais","NY","22222","2222","222", "634-7733","D Company");
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"Hill","Carl","H","19 Drive","A Town","CA","66666","6566","666", "243-4243","E Company");
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Yari","P","38 Ave","Small City","NY","77777","7777","777", "454-5443","F Inc");
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Joe","Paula","J","78 St. Apt 3A","Queen City","NY","32322","2323","888", "664-4333","E Inc");
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Chili","Steve","C","38 Ave Apt 62","Mili","CA","88888","8888","787", "456-4566","G Inc");
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","M","930 Ave933","Kansas City","MO","12345","1234","412", "456-4563","H Inc");
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hack","Kisi","H","Kings Rd","Bellmore","NY","54321","3898","516", "767-5677","I Inc");
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bill","Jose","B","12 Giant Rd.","Newton","NJ","23454","1234","958", "123-7367","J Associates");
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(12,"Taker","Lawrence","T","1 Sask Rd.","Camp","NJ","19191","3298","928", "123-7384","K Company");
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(13,"Richer","Doris","R","213 Easy Street","WarPease","RI","34343","2112","501", "123-7384","L Inc");
1 row created.
SQL> insert into customer( cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(14,"Pete","Doris","P","9 Ave","New York","NY","45454","4222","112", "123-1234","M Company");
1 row created.
SQL>
SQL> create table ord(
2 order_no integer primary key
3 ,cust_no integer
4 ,order_date date not null
5 ,total_order_price number(7,2)
6 ,deliver_date date
7 ,deliver_time varchar2(7)
8 ,payment_method varchar2(2)
9 ,emp_no number(3,0)
10 ,deliver_name varchar2(35)
11 ,gift_message varchar2(100)
12 );
Table created.
SQL>
SQL>
SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO" , 2, "Cora", "Happy Birthday from John");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI", 3, "Larry", "Happy New Year from Lawrence");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created.
SQL>
SQL>
SQL>
SQL> set autotrace traceonly
SQL> SELECT cust_no, lastname
2 FROM customer c
3 WHERE cust_no NOT IN (SELECT cust_no
4 FROM ord );
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2957014985
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| CUSTOMER | 14 | 350 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| ORD | 12 | 156 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "ORD" "ORD" WHERE
LNNVL("CUST_NO"<>:B1)))
3 - filter(LNNVL("CUST_NO"<>:B1))
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
58 recursive calls
0 db block gets
66 consistent gets
0 physical reads
0 redo size
586 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed
SQL>
SQL> set autotrace off
SQL> drop table customer;
Table dropped.
SQL> drop table ord;
Table dropped.
SQL>
SQL> --
autotrace ansi full outer join
SQL>
SQL>
SQL> create table myTable as
2 select "myTable" as C1
3 ,OBJECT_NAME
4 ,SUBOBJECT_NAME
5 ,OBJECT_ID
6 ,DATA_OBJECT_ID
7 ,OBJECT_TYPE
8 ,CREATED
9 ,LAST_DDL_TIME
10 ,TIMESTAMP
11 ,STATUS
12 ,TEMPORARY
13 ,GENERATED
14 ,SECONDARY
15 from dba_objects;
Table created.
SQL>
SQL> create table myTable2 as
2 select "myTable2" as C1
3 ,OBJECT_NAME || "myTable2" as object_name
4 ,SUBOBJECT_NAME
5 ,OBJECT_ID
6 ,DATA_OBJECT_ID
7 ,OBJECT_TYPE
8 ,CREATED
9 ,LAST_DDL_TIME
10 ,TIMESTAMP
11 ,STATUS
12 ,TEMPORARY
13 ,GENERATED
14 ,SECONDARY
15 from dba_objects
16 where rownum <= 10000;
Table created.
SQL>
SQL> create index myTable_object_id on myTable (object_id);
Index created.
SQL>
SQL> create index myTable2_object_id on myTable2 (object_id);
Index created.
SQL>
SQL> analyze table myTable compute statistics;
Table analyzed.
SQL>
SQL> analyze table myTable2 compute statistics;
Table analyzed.
SQL>
SQL> set autotrace TRACEONLY
SQL> set timing on
SQL> select *
2 from myTable a, myTable2 b
3 where a.object_id = b.object_id(+)
4 union
5 select *
6 from myTable a, myTable2 b
7 where a.object_id(+) = b.object_id;
13158 rows selected.
Elapsed: 00:00:00.62
Execution Plan
----------------------------------------------------------
Plan hash value: 4186416997
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23158 | 4296K| | 47039 |
| 1 | SORT UNIQUE | | 23158 | 4296K| 10M| 47039 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS OUTER | | 13158 | 2441K| | 26343 |
| 4 | TABLE ACCESS FULL | MYTABLE | 13158 | 1169K| | 27 |
| 5 | TABLE ACCESS BY INDEX ROWID| MYTABLE2 | 1 | 99 | | 2 |
|* 6 | INDEX RANGE SCAN | MYTABLE2_OBJECT_ID | 1 | | | 1 |
| 7 | NESTED LOOPS OUTER | | 10000 | 1855K| | 20022 |
| 8 | TABLE ACCESS FULL | MYTABLE2 | 10000 | 966K| | 22 |
| 9 | TABLE ACCESS BY INDEX ROWID| MYTABLE | 1 | 91 | | 2 |
|* 10 | INDEX RANGE SCAN | MYTABLE_OBJECT_ID | 1 | | | 1 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))
10 - access("A"."OBJECT_ID"(+)="B"."OBJECT_ID")
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
43520 consistent gets
0 physical reads
0 redo size
1301014 bytes sent via SQL*Net to client
10027 bytes received via SQL*Net from client
879 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13158 rows processed
SQL>
SQL>
SQL> select *
2 from myTable a full outer join myTable2 b
3 using (object_id);
13158 rows selected.
Elapsed: 00:00:00.52
Execution Plan
----------------------------------------------------------
Plan hash value: 3236823177
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13658 | 4401K| 26365 |
| 1 | VIEW | | 13658 | 4401K| 26365 |
| 2 | UNION-ALL | | | | |
| 3 | NESTED LOOPS OUTER | | 13158 | 2441K| 26343 |
| 4 | TABLE ACCESS FULL | MYTABLE | 13158 | 1169K| 27 |
| 5 | TABLE ACCESS BY INDEX ROWID| MYTABLE2 | 1 | 99 | 2 |
|* 6 | INDEX RANGE SCAN | MYTABLE2_OBJECT_ID | 1 | | 1 |
|* 7 | FILTER | | | | |
| 8 | TABLE ACCESS FULL | MYTABLE2 | 500 | 49500 | 22 |
|* 9 | INDEX RANGE SCAN | MYTABLE_OBJECT_ID | 1 | 13 | 1 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))
7 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "MYTABLE" "A" WHERE
"A"."OBJECT_ID"=:B1))
9 - access("A"."OBJECT_ID"=:B1)
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
45912 consistent gets
0 physical reads
0 redo size
956084 bytes sent via SQL*Net to client
10027 bytes received via SQL*Net from client
879 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13158 rows processed
SQL>
SQL> set timing off
SQL> set autotrace off
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL> drop table myTable2;
Table dropped.
Autotrace a query on a huge table
SQL>
SQL> create table my_all_objects
2 nologging
3 as
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 /
Table created.
SQL>
SQL> set autotrace traceonly
SQL> select owner, count(*) from my_all_objects group by owner;
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2509106709
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33589 | 557K| 136 (6)| 00:00:02 |
| 1 | HASH GROUP BY | | 33589 | 557K| 136 (6)| 00:00:02 |
| 2 | TABLE ACCESS FULL| MY_ALL_OBJECTS | 33589 | 557K| 130 (2)| 00:00:02 |
-------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
537 consistent gets
466 physical reads
0 redo size
694 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
SQL> set autotrace off
SQL>
SQL> drop table my_all_objects;
Table dropped.
SQL>
Autotrace a query with group clause
SQL>
SQL>
SQL> create table my_all_objects
2 nologging
3 as
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 /
Table created.
SQL>
SQL> insert into my_all_objects
2 ( owner, object_name, object_type, object_id )
3 values
4 ( "New Owner", "New Name", "New Type", 1111111 );
1 row created.
SQL>
SQL>
SQL> set autotrace traceonly
SQL> select owner, count(*)
2 from my_all_objects
3 where owner = "New Owner"
4 group by owner;
Execution Plan
----------------------------------------------------------
Plan hash value: 3282931909
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 85 | 131 (2)| 00:00:02 |
| 1 | SORT GROUP BY NOSORT| | 5 | 85 | 131 (2)| 00:00:02 |
|* 2 | TABLE ACCESS FULL | MY_ALL_OBJECTS | 5 | 85 | 131 (2)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"="New Owner")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
50 recursive calls
0 db block gets
548 consistent gets
466 physical reads
0 redo size
476 bytes sent via SQL*Net to client
380 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>
SQL> drop table my_all_objects;
Table dropped.
SQL>
SQL>
autotrace command
SQL>
SQL>
SQL> -- create demo table
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 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> set autotrace on;
SQL>
SQL> select count(*) from employee;
COUNT(*)
----------
8
Execution Plan
----------------------------------------------------------
Plan hash value: 301197670
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMPLOYEE | 8 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
autotrace count(*)
SQL>
SQL> create table t as select * from all_objects;
Table created.
SQL>
SQL> set autotrace on
SQL> select count(*) from t;
COUNT(*)
----------
12586
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
163 consistent gets
159 physical reads
0 redo size
413 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL>
SQL> set autotrace off
SQL>
SQL> delete from t where owner <> "SCOTT";
12586 rows deleted.
SQL>
SQL> set autotrace on
SQL> select count(*) from t;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
163 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL>
SQL> set autotrace off
SQL> alter table t move;
Table altered.
SQL> set autotrace on
SQL> select count(*) from t;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> drop table t;
Table dropped.
autotrace ctxsys.context index
SQL>
SQL>
SQL> create table t ( x clob );
Table created.
SQL>
SQL> set autotrace traceonly explain
SQL> select * from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T |
----------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL>
SQL> create index t_idx on t(x) indextype is ctxsys.context;
Index created.
SQL> select * from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 8200 | 1 |
| 1 | TABLE ACCESS FULL| T | 82 | 8200 | 1 |
----------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
SQL>
SQL> set autotrace off
SQL>
SQL> drop table t;
Table dropped.
AUTOTRACE exists (subquery)
SQL>CREATE TABLE project (
2 pro_id NUMBER(4),
3 pro_name VARCHAR2(40),
4 budget NUMBER(9,2),
5 CONSTRAINT project_pk PRIMARY KEY (pro_id)
6 );
Table created.
SQL>
SQL>
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, "A",12345);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, "ERP",23456);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, "SQL",34567);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, "CRM",45678);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, "VPN",56789);
1 row created.
SQL>
SQL>
SQL>CREATE TABLE server_usage (
2 pro_id NUMBER(4),
3 emp_id NUMBER,
4 time_log_date DATE,
5 hours_logged NUMBER(8,2),
6 dollars_charged NUMBER(8,2),
7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date)
8 );
Table created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);
1 row created.
SQL>
SQL>CREATE TABLE emp (
2 emp_id NUMBER,
3 ename VARCHAR2(40),
4 hire_date DATE DEFAULT sysdate,
5 end_date DATE,
6 rate NUMBER(5,2),
7 CONSTRAINT emp_pk PRIMARY KEY (emp_id)
8 );
Table created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL>
SQL>SET ECHO ON
SQL>SET AUTOTRACE ON
SQL>
SQL>SELECT emp_id, ename
2 FROM emp
3 WHERE EXISTS (SELECT *
4 FROM server_usage
5 WHERE server_usage.pro_id = 1001
6 AND server_usage.emp_id = emp.emp_id);
User System Privileges Page 1
---------- --------------------
########## Mary
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2816981487
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | TABLE ACCESS FULL| EMP |
|* 3 | INDEX RANGE SCAN | SERVER_USAGE_PK |
----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "SERVER_USAGE" "SERVER_USAGE"
WHERE "SERVER_USAGE"."EMP_ID"=:B1 AND "SERVER_USAGE"."PRO_ID"=1
001))
3 - access("SERVER_USAGE"."PRO_ID"=1001 AND
"SERVER_USAGE"."EMP_ID"=:B1)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
470 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL>set autotrace off
SQL>
SQL>drop table emp;
Table dropped.
SQL>drop table project;
Table dropped.
SQL>drop table server_usage;
Table dropped.
Autotrace lower text function
SQL>
SQL> create table my_all_objects
2 nologging
3 as
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 /
Table created.
SQL>
SQL>
SQL> set autotrace traceonly
SQL> select lower(owner) from my_all_objects group by owner;
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2509106709
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33589 | 557K| 136 (6)| 00:00:02 |
| 1 | HASH GROUP BY | | 33589 | 557K| 136 (6)| 00:00:02 |
| 2 | TABLE ACCESS FULL| MY_ALL_OBJECTS | 33589 | 557K| 130 (2)| 00:00:02 |
-------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
537 consistent gets
466 physical reads
0 redo size
593 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
SQL> set autotrace off
SQL>
SQL> drop table my_all_objects;
Table dropped.
SQL>
autotrace merge command
SQL> create table myTable as select * from dba_objects;
Table created.
SQL> delete from myTable where rownum <= 100;
100 rows deleted.
SQL> create table myTable2 as select * from dba_objects;
Table created.
SQL> set autotrace on
SQL> merge into myTable b
2 using myTable2 o
3 on (b.owner = o.owner and b.object_name = o.object_name
4 and
5 b.subobject_name = o.subobject_name
6 and
7 b.object_id = o.object_id)
8 when matched then update set b.created = o.created
9 when not matched then insert
10 values ( o.OWNER ,o.OBJECT_NAME ,o.SUBOBJECT_NAME ,o.OBJECT_ID ,o.DATA_OBJECT_ID
11 ,o.OBJECT_TYPE ,o.CREATED ,o.LAST_DDL_TIME,o.TIMESTAMP ,o.STATUS,o.TEMPORARY,o.GENERATED
12 ,o.SECONDARY )
13 /
13219 rows merged.
Execution Plan
----------------------------------------------------------
Plan hash value: 449939568
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 13723 | 3832K| | 99 |
| 1 | MERGE | MYTABLE | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN OUTER | | 13723 | 2680K| 1504K| 99 |
| 4 | TABLE ACCESS FULL| MYTABLE2 | 13723 | 1340K| | 26 |
| 5 | TABLE ACCESS FULL| MYTABLE | 13723 | 1340K| | 26 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."OBJECT_ID"(+)="O"."OBJECT_ID" AND
"B"."SUBOBJECT_NAME"(+)="O"."SUBOBJECT_NAME" AND
"B"."OBJECT_NAME"(+)="O"."OBJECT_NAME" AND "B"."OWNER"(+)="O"."OWN
ER")
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
253 recursive calls
14050 db block gets
388 consistent gets
329 physical reads
4644528 redo size
929 bytes sent via SQL*Net to client
1374 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13219 rows processed
SQL>
SQL> set autotrace off
SQL> rollback;
Rollback complete.
SQL> set autotrace on
SQL>
SQL> merge into myTable b
2 using (select * from myTable2) o
3 on (b.owner = o.owner and b.object_name = o.object_name
4 and
5 b.subobject_name = o.subobject_name
6 and
7 b.object_id = o.object_id)
8 when matched then update set b.created = o.created
9 when not matched then insert
10 values ( o.OWNER ,o.OBJECT_NAME ,o.SUBOBJECT_NAME ,o.OBJECT_ID ,o.DATA_OBJECT_ID
11 ,o.OBJECT_TYPE ,o.CREATED ,o.LAST_DDL_TIME,o.TIMESTAMP ,o.STATUS,o.TEMPORARY,o.GENERATED
12 ,o.SECONDARY )
13 /
13219 rows merged.
Execution Plan
----------------------------------------------------------
Plan hash value: 449939568
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 13723 | 3832K| | 99 |
| 1 | MERGE | MYTABLE | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN OUTER | | 13723 | 2680K| 1504K| 99 |
| 4 | TABLE ACCESS FULL| MYTABLE2 | 13723 | 1340K| | 26 |
| 5 | TABLE ACCESS FULL| MYTABLE | 13723 | 1340K| | 26 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."OBJECT_ID"(+)="MYTABLE2"."OBJECT_ID" AND
"B"."SUBOBJECT_NAME"(+)="MYTABLE2"."SUBOBJECT_NAME" AND
"B"."OBJECT_NAME"(+)="MYTABLE2"."OBJECT_NAME" AND
"B"."OWNER"(+)="MYTABLE2"."OWNER")
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
4 recursive calls
13763 db block gets
678 consistent gets
0 physical reads
4614184 redo size
929 bytes sent via SQL*Net to client
1390 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13219 rows processed
SQL>
SQL> set autotrace off
SQL> rollback;
Rollback complete.
SQL>
SQL> drop table myTable;
Table dropped.
SQL> drop table myTable2;
Table dropped.
SQL>
SQL>
SQL>
Autotrace on and off
SQL>
SQL> create table my_all_objects
2 nologging
3 as
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 /
Table created.
SQL>
SQL> insert into my_all_objects
2 ( owner, object_name, object_type, object_id )
3 values
4 ( "New Owner", "New Name", "New Type", 1111111 );
1 row created.
SQL>
SQL>
SQL> set autotrace traceonly
SQL> select count(*)
2 from my_all_objects
3 where owner = "New Owner";
Execution Plan
----------------------------------------------------------
Plan hash value: 2942800307
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 131 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| MY_ALL_OBJECTS | 5 | 85 | 131 (2)| 00:00:02 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"="New Owner")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
50 recursive calls
0 db block gets
548 consistent gets
466 physical reads
0 redo size
411 bytes sent via SQL*Net to client
380 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>
SQL> drop table my_all_objects;
Table dropped.
SQL>
SQL>
Autotrace running total
SQL>
SQL> set echo on
SQL>
SQL> create table t
2 as
3 select object_name ename,
4 mod(object_id,50) deptno,
5 object_id sal
6 from all_objects
7 where rownum <= 1000
8 /
Table created.
SQL>
SQL> create index t_idx on t(deptno,ename);
Index created.
SQL>
SQL> select ename, deptno, sal,
2 (select sum(sal)
3 from t e2
4 where e2.deptno < emp.deptno
5 or (e2.deptno = emp.deptno and e2.ename <= emp.ename ))
6 running_total,
7 (select sum(sal)
8 from t e3
9 where e3.deptno = emp.deptno
10 and e3.ename <= emp.ename)
11 department_total,
12 (select count(ename)
13 from t e3
14 where e3.deptno = emp.deptno
15 and e3.ename <= emp.ename) seq
16 from t emp
17 order by deptno, ename
18 /
1000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 92139442
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 43000 | 4 (25)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 43 | | |
|* 2 | TABLE ACCESS FULL | T | 50 | 2150 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 43 | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 43 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T_IDX | 1 | | 2 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 30 | | |
|* 7 | INDEX RANGE SCAN | T_IDX | 1 | 30 | 2 (0)| 00:00:01 |
| 8 | SORT ORDER BY | | 1000 | 43000 | 4 (25)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T | 1000 | 43000 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E2"."DEPTNO"<:B1 OR "E2"."DEPTNO"=:B2 AND "E2"."ENAME"<=:B3)
5 - access("E3"."DEPTNO"=:B1 AND "E3"."ENAME"<=:B2)
7 - access("E3"."DEPTNO"=:B1 AND "E3"."ENAME"<=:B2)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
39 recursive calls
0 db block gets
15678 consistent gets
4 physical reads
0 redo size
44533 bytes sent via SQL*Net to client
1106 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL>
SQL> set autotrace off
AUTOTRACE table joining
SQL>
SQL>CREATE TABLE project (
2 pro_id NUMBER(4),
3 pro_name VARCHAR2(40),
4 budget NUMBER(9,2),
5 CONSTRAINT project_pk PRIMARY KEY (pro_id)
6 );
Table created.
SQL>
SQL>
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, "A",12345);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, "ERP",23456);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, "SQL",34567);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, "CRM",45678);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, "VPN",56789);
1 row created.
SQL>
SQL>
SQL>CREATE TABLE server_usage (
2 pro_id NUMBER(4),
3 emp_id NUMBER,
4 time_log_date DATE,
5 hours_logged NUMBER(8,2),
6 dollars_charged NUMBER(8,2),
7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date)
8 );
Table created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);
1 row created.
SQL>
SQL>CREATE TABLE emp (
2 emp_id NUMBER,
3 ename VARCHAR2(40),
4 hire_date DATE DEFAULT sysdate,
5 end_date DATE,
6 rate NUMBER(5,2),
7 CONSTRAINT emp_pk PRIMARY KEY (emp_id)
8 );
Table created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL>
SQL>SET ECHO ON
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL>
SQL>SELECT ename, SUM(hours_logged)
2 FROM emp, server_usage
3 WHERE emp.emp_id = server_usage.emp_id
4 GROUP BY ename;
Execution Plan
----------------------------------------------------------
Plan hash value: 2561811960
------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL | SERVER_USAGE |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP |
|* 5 | INDEX UNIQUE SCAN | EMP_PK |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("EMP"."EMP_ID"="SERVER_USAGE"."EMP_ID")
Note
-----
- rule based optimizer used (consider using cbo)
SQL>
SQL>
SQL>set autotrace off
SQL>
SQL>drop table emp;
Table dropped.
SQL>drop table project;
Table dropped.
SQL>drop table server_usage;
Table dropped.
AUTOTRACE table joining and aggregate function
SQL>CREATE TABLE project (
2 pro_id NUMBER(4),
3 pro_name VARCHAR2(40),
4 budget NUMBER(9,2),
5 CONSTRAINT project_pk PRIMARY KEY (pro_id)
6 );
Table created.
SQL>
SQL>
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, "A",12345);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, "ERP",23456);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, "SQL",34567);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, "CRM",45678);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, "VPN",56789);
1 row created.
SQL>
SQL>
SQL>CREATE TABLE server_usage (
2 pro_id NUMBER(4),
3 emp_id NUMBER,
4 time_log_date DATE,
5 hours_logged NUMBER(8,2),
6 dollars_charged NUMBER(8,2),
7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date)
8 );
Table created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);
1 row created.
SQL>
SQL>CREATE TABLE emp (
2 emp_id NUMBER,
3 ename VARCHAR2(40),
4 hire_date DATE DEFAULT sysdate,
5 end_date DATE,
6 rate NUMBER(5,2),
7 CONSTRAINT emp_pk PRIMARY KEY (emp_id)
8 );
Table created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL>
SQL>SET ECHO ON
SQL>SET AUTOTRACE ON EXPLAIN
SQL>
SQL>SELECT ename, SUM(hours_logged)
2 FROM emp, server_usage
3 WHERE emp.emp_id = server_usage.emp_id
4 GROUP BY ename;
User System Privileges Page 1
SUM(HOURS_LOGGED)
-------------------- -----------------
Less 1129
Mary 1123
Mike 1127
Peter 1126
Tom 1124
5 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2561811960
------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL | SERVER_USAGE |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP |
|* 5 | INDEX UNIQUE SCAN | EMP_PK |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("EMP"."EMP_ID"="SERVER_USAGE"."EMP_ID")
Note
-----
- rule based optimizer used (consider using cbo)
SQL>
SQL>set autotrace off
SQL>
SQL>drop table emp;
Table dropped.
SQL>drop table project;
Table dropped.
SQL>drop table server_usage;
Table dropped.
autotrace table with/without an index
SQL> create table website
2 ( hostname varchar2(10),
3 upTime date,
4 load number,
5 other_stats char(65),
6 constraint website_pk primary key(hostname,upTime)
7 )
8 /
Table created.
SQL>
SQL>
SQL> create table indexedwebsite
2 ( hostname varchar2(10),
3 upTime date,
4 load number,
5 other_stats char(65),
6 constraint indexedwebsite_pk primary key(hostname,upTime)
7 )
8 organization index
9 /
Table created.
SQL>
SQL>
SQL> declare
2 l_load number;
3 begin
4 for l_HOURS in 1 .. 100
5 loop
6 for l_HOSTS in 1 .. 100
7 loop
8 l_load := dbms_random.random;
9 insert into website(hostname,upTime,load,other_stats)values("hostnm" || l_hosts, sysdate-(100-l_hours)/24,l_load, "x" );
10 insert into indexedwebsite(hostname,upTime,load,other_stats)values("hostnm" || l_hosts, sysdate-(100-l_hours)/24,l_load, "x" );
11 end loop;
12 commit;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> analyze table website compute statistics;
Table analyzed.
SQL>
SQL>
SQL> analyze table indexedwebsite compute statistics;
Table analyzed.
SQL>
SQL>
SQL> set autotrace on
SQL> select avg(load)
2 from website
3 where hostname = "hostnm50"
4 and upTime >= sysdate-100/24
5 /
AVG(LOAD)
----------
45908976.3
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 757115644
--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 21 |
| 1 | SORT AGGREGATE | | 1 | 29 | |
|* 2 | TABLE ACCESS FULL| WEBSITE | 100 | 2900 | 21 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("HOSTNAME"="hostnm50" AND
"UPTIME">=SYSDATE@!-4.16666666666666666666666666666666666667)
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
136 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select avg(load)
2 from indexedwebsite
3 where hostname = "hostnm50"
4 and upTime >= sysdate-100/24
5 /
AVG(LOAD)
----------
45908976.3
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2378983545
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 4 |
| 1 | SORT AGGREGATE | | 1 | 29 | |
|* 2 | INDEX RANGE SCAN| INDEXEDWEBSITE_PK | 100 | 2900 | 4 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("HOSTNAME"="hostnm50" AND
"UPTIME">=SYSDATE@!-4.16666666666666666666666666666666666667)
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> drop table website;
Table dropped.
SQL> drop table indexedwebsite;
Table dropped.
Execution Plan
SQL>
SQL> create table indextest as select * from dba_objects
2 where owner in ("OUTLN","PUBLIC","SCOTT","SYS","SYSTEM");
Table created.
SQL>
SQL> analyze table indextest compute statistics;
Table analyzed.
SQL>
SQL> set autotrace trace explain
SQL> select owner, object_name from indextest
2 where object_name = "DBA_INDEXES";
Execution Plan
----------------------------------------------------------
Plan hash value: 2792531790
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 36 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| INDEXTEST | 1 | 21 | 36 (3)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"="DBA_INDEXES")
SQL>
SQL> create index indxtest_objname_idx
2 on indextest (object_name);
Index created.
SQL>
SQL> select owner, object_name from indextest
2 where object_name = "DBA_INDEXES";
Execution Plan
----------------------------------------------------------
Plan hash value: 1012932391
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| INDEXTEST | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDXTEST_OBJNAME_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"="DBA_INDEXES")
SQL>
SQL>
SQL> drop table indextest;
Table dropped.
SQL>
SQL>
set autotrace on explain for every single statement
SQL> set autotrace on explain
SQL>
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "Coder", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | INSERT STATEMENT | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | INSERT STATEMENT | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | INSERT STATEMENT | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | INSERT STATEMENT | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | INSERT STATEMENT | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | INSERT STATEMENT | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | INSERT STATEMENT | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | INSERT STATEMENT | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | INSERT STATEMENT | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | INSERT STATEMENT | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | INSERT STATEMENT | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | INSERT STATEMENT | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | INSERT STATEMENT | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | INSERT STATEMENT | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
SQL>
SQL> set autotrace on
SQL>
SQL> select ename from emp where empno < 7500;
ENAME
--------
Tom
Jack
Wil
Jane
Mary
Black
Chris
Smart
Peter
Take
Ana
Jane
Fake
Mike
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1482559701
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
|* 2 | INDEX RANGE SCAN | E_PK |
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"<7500)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
572 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> drop table emp;
Table dropped.
set autotrace traceonly
create table t as
select *
from all_objects
where rownum < 10001;
create index t_idx on t(object_id);
analyze table t compute statistics;
set autotrace traceonly
select * from t where object_id = 10;
drop table t;
--
set autotrace traceonly explain, and condition
SQL>
SQL>
SQL> create table t
2 as
3 select mod(ROWNUM,3) a, ROWNUM b, ROWNUM c
4 from all_tables;
Table created.
SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where b = 1 and c = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C"=1 AND "B"=1)
Note
-----
- rule based optimizer used (consider using cbo)
SQL>
SQL>
SQL> set autotrace off
SQL>
SQL> drop table t;
Table dropped.
set autotrace traceonly explain for bitmap index
SQL>
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL>
SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2),
3 DNAME VARCHAR2(14),
4 LOC VARCHAR2(13)
5 );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL>
SQL> alter table dept add constraint dept_pk primary key(deptno);
Table altered.
SQL>
SQL> create bitmap index emp_bm_idx on emp( d.dname)from emp e, dept d where e.deptno = d.deptno
2 /
SQL> begin
2 dbms_stats.set_table_stats( user, "EMP", numrows => 1000000, numblks => 300000 );
3 dbms_stats.set_table_stats( user, "DEPT",numrows => 100000, numblks => 30000 );
4 end;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select count(*) from emp, dept where emp.deptno = dept.deptno and dept.dname = "SALES"
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1546158010
------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 50088 |
| 1 | SORT AGGREGATE | | 1 | 35 | |
|* 2 | HASH JOIN | | 10M| 333M| 50088 |
|* 3 | TABLE ACCESS FULL| DEPT | 1000 | 22000 | 4554 |
| 4 | TABLE ACCESS FULL| EMP | 1000K| 12M| 45533 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
3 - filter("DEPT"."DNAME"="SALES")
Note
-----
- cpu costing is off (consider enabling it)
SQL>
SQL> set autotrace off
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table dept;
Table dropped.
SQL>
SQL>
set autotrace traceonly statistics
SQL>
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "Coder", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> set autotrace traceonly statistics
SQL>
SQL> select * from emp;
14 rows selected.
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1433 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL>
SQL> drop table emp;
Table dropped.
set autotrace traceonly statistics for "select * from tableName"
SQL>
SQL> create table t as select * from all_objects where 1=0;
Table created.
SQL>
SQL> select * from t;
no rows selected
SQL>
SQL> set autotrace traceonly statistics
SQL>
SQL> select * from t;
no rows selected
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> insert into t select * from all_objects;
12599 rows created.
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> select * from t;
no rows selected
SQL>
SQL> set autotrace traceonly statistics
SQL>
SQL> select * from t;
no rows selected
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
166 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL>