Oracle, stores data in segments. Segments has free and used space. The mark(a logical mark) between free and used space can be called as high water mark(HWM). When new data filled, the mark moves. On deleting records, oracle does not move back HWM. That is, you have unused data but, HWM sees as if it were filled data. When you shrink table such as alter table move command, this HWM refreshes itself. When you delete from a table, a DML operation, oracle undos the changing. That is you can move back, with some additional works. But when you truncate a table, a DDL operation, oracle moves back the HWM. So this is faster than normal delete clause. With delete, you will fire existing triggers whereas truncate does not.
Truncate is especially is used instead of dropping and recreating tables. with truncate, no objects will become invalid and grants are remains the same.
Below demonstration shows analyses:
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL> set serveroutput on
SQL> set timing on
SQL>
SQL> --grant execute on dbms_space to hr;
Creating a function to calculate HWM:
SQL> create or replace function get_hwm(extend_name in varchar2
2 ,extend_owner in varchar2
3 ,extend_type in varchar2) return number is
4 total_block number;
5 unused_block number;
6 total_byte number;
7 unused_byte number;
8 last_extent_file_id number;
9 last_extent_block_id number;
10 last_block number;
11
12 hwm number;
13 begin
14 dbms_space.unused_space(extend_owner,
15 extend_name,
16 extend_type,
17 total_block,
18 total_byte,
19 unused_block,
20 unused_byte,
21 last_extent_file_id,
22 last_extent_block_id,
23 last_block);
24 hwm := total_block - (unused_block + 1);
25 dbms_output.put_line('hwm for ' || extend_owner || '.' || extend_name || ' ' ||
26 extend_type || ' is ' || hwm);
27 return hwm;
28 end;
29 /
Function created
Executed in 0,17 seconds
SQL> drop table t;
Table dropped
Executed in 0,02 seconds
SQL> create table t as select rownum as i from all_objects;
Table created
Executed in 3,935 seconds
SQL> create or replace trigger trg_del_t
2 before insert or update or delete
3 on t
4 begin
5 if inserting then
6 dbms_output.put_line('Inserting t');
7 elsif updating then
8 dbms_output.put_line('Updating t');
9 elsif deleting then
10 dbms_output.put_line('Deleting t');
11 end if;
12
13 end;
14 /
Trigger created
Executed in 0,02 seconds
SQL> select get_hwm('T','HR','TABLE') HWM from dual;
HWM
----------
19
hwm for HR.T TABLE is 19
Executed in 0,02 seconds
On delete, trigger fired
SQL> delete from t;
Deleting t
10209 rows deleted
Executed in 0,291 seconds
On delete, HWM remains the same
SQL> select get_hwm('T','HR','TABLE') HWM from dual;
HWM
----------
19
hwm for HR.T TABLE is 19
Executed in 0,01 seconds
SQL> alter table t move;
Table altered
Executed in 0,04 seconds
With ater table move HWM moves back:
SQL> select get_hwm('T','HR','TABLE') HWM from dual;
HWM
----------
2
hwm for HR.T TABLE is 2
Executed in 0,02 seconds
SQL> drop table t;
Table dropped
Executed in 0,15 seconds
SQL> create table t as select rownum as i from all_objects;
Table created
Executed in 2,714 seconds
SQL> create or replace trigger trg_del_t
2 before insert or update or delete
3 on t
4 begin
5 if inserting then
6 dbms_output.put_line('Inserting t');
7 elsif updating then
8 dbms_output.put_line('Updating t');
9 elsif deleting then
10 dbms_output.put_line('Deleting t');
11 end if;
12
13 end;
14 /
Trigger created
Executed in 0,02 seconds
SQL> select get_hwm('T','HR','TABLE') HWM from dual;
HWM
----------
19
hwm for HR.T TABLE is 19
Executed in 0,01 seconds
On truncate HWM DOES NOT remain the same
Truncate it is faster than normal delete
SQL> truncate table t;
Table truncated
Executed in 0,02 seconds
SQL> select get_hwm('T','HR','TABLE') HWM from dual;
HWM
----------
2
hwm for HR.T TABLE is 2
Executed in 0,01 seconds
SQL>
No comments:
Post a Comment