Pages

Sunday 28 October 2012

Diff B/W Char, varchar2 & Varchar in Oracle DB


Char, Varchar and Varchar2 are used to store strings values. But they have major differences let see what they are

Purpose
Char
Varchar2
Varchar

Char is a data type which is used to store the fixed length character strings.
 String values will be space/blank padded before stored on disk.
Non Efficient use of space
Varchar2 is used for storing the variable length character strings.

Efficient use of Space.
Varchar is just a synonym for varchar2 from (Oracle 9i Onward  its behave like varchar2, but oracle recommend not to use it as in future releases it will be used for another  functionality don’t know when and in which release.
Efficient use of Space.
Max length SQL
Bytes / Character

2000
4000
In Oracle 12C Release Varchar2 in SQL and PLSQL have same max length 32767
4000

Min Length Bytes / Character

1
1
1
Default length
Bytes / Character

1
NA
NA
Max length PLSQL
Bytes / Character

32767
32767
32767
.

As per me char or Varchar data types are of no use because there are no as such benefits of Char & Varchar as compare to Varchar2, Char is used by Oracle just for Backward Compatibility and Varchar will be suppose to use for different functionality in Future releases of Oracle.

Generally we use char when you know the data length is fixed... like Flags value like Y, N or gender information like ‘M’,’F’ etc..
But char(1)= varchar2(1) for fixed length values are identical.

Example value of ‘x’ in both fields is same So why to use char  even for fix length values

Drawbacks of Char:

1. More space required on disk as it is fixed length.

2. Not suitable for comparisons with Varchar or varchar2 because char values consist padded blanks and for match we need to use trim or rpad function again this will impact performance.

3. Char is slow in searching as compare to varchar2

See Examples.

Example 1 Space & Padded Blanks test

-- Create table var_char_diff values with name 1 varchar2(5) and name2 char(5) columns
SQL> create table var_char_diff (name1 varchar2(5), name2 char(5));

Table created.
-- insert value ‘AR’ in both name1 and name2

SQL> insert into var_char_diff values ('AR','AR');

1 row created.
-- Check length of name1 and name2
SQL> select length(name1), length(name2) from var_char_diff ;

LENGTH(NAME1) LENGTH(NAME2)  
------------- -------------    
2                 5  
-- Check what is padded for char with dump function

SQL>  select dump(name1),dump(name2), length(name1), length(name2) from var_char_diff ;
DUMP(NAME1)     DUMP(NAME2)    LENGTH(NAME1)    LENGTH(NAME2)
------------- -------------   --------------------------------------------      
Typ=1 Len=2: 65,82     yp=96 Len=5: 65,82,32,32,32    2      5    

-- Chr(32) Is value for the space

SQL> select chr(32) from dual;
C
-
Not convinced use rpad method …

SQL> select replace(name1,’ ‘,’*'),replace(name2,’ ‘,’*') from var_char_diff
REPLACE(CO REPLACE(CO
———- ———-
AR*** AR

Problems occur when you comapre char value with varchar
Work around is to use trim or Rpad when you compare the char and varchar2

SQL> select count(*) from var_char_diff where name1=name2 ;
  COUNT(*) 
----------     
         0

--use trim to cut the leading and trailing spaces    
SQL> select count(*) from var_char_diff where name1=trim(name2) ;

  COUNT(*) 
----------     
         1
  SQL> select * from var_char_diff values where name2= ‘AR’;

Name1 Name2
———- ———-
AR       AR

Here oracle user implicit conversion trims on both sides if data type is char

SQL> variable v1 varchar2(10)

SQL> exec :v1 := ‘AR’

PL/SQL procedure successfully completed.

SQL> select * from var_char_diff values where Name2 = :v1;

no rows selected

Strange what’s going wrong there We are comparing Varchar2 with char

SQL> select * from var_char_diff values where name2 = rpad(:v1,10);

NAME1 NAME2
———- ———-
AR    AR

So you could clearly see the side effect: To get a hit in comparing a character value with a varchar2 variable, you need to use rpad, which appends blank spaces at the end of the variable v1.

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

2. How char is take more space.
create two tables char_t & Varchar_t with one column name in both
One with char(1000) and another with varchar2(1000)
insert 10000 values in both tables same values ‘suppose text ‘arun’ & see the
result.

SQL> create table char_t (name char(2000));

Table created.

SQL> create table varchar_t (name varchar2(2000));

Table created.
SQL> select bytes,blocks,extents from user_segments where segment_name='VARCHAR_T';

     BYTES     BLOCKS    EXTENTS 
---------- ---------- ----------
     65536          8          1    
  
SQL> select bytes,blocks,extents from user_segments where segment_name='CHAR_T';

BYTES     BLOCKS    EXTENTS 
---------- ---------- ----------
     65536          8          1    

--     Block to insert 10000 values     

SQL> begin
      for i in 1..10000 loop
    insert into varchar_t values ('arun');
    end loop;
    end;
    /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

--- Block to insert 10000 values

SQL> begin
    for i in 1..10000 loop
    insert into char_t values ('arun');
    end loop;
    end;
    /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select bytes,blocks,extents from user_segments where segment_name='VARCHAR_T' ;

       BYTES     BLOCKS    EXTENTS 
---------- ---------- ----------      
           196608         24          3          

SQL> select bytes,blocks,extents from user_segments where segment_name='CHAR_T' ;

BYTES     BLOCKS    EXTENTS 
---------- ---------- ----------
  28311552       3456         42   

--See the difference of 3432 blocks & 26Mb more space just for the use of char data type.

   &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

3. Char takes more time in searching as compare to varchar2
SQL> select count(*) from varchar_t;

  COUNT(*)  
----------
10000

Execution Plan
----------------------------------------------------------  
Plan hash value: 1791642866  
------------------------------------------------------------------------  
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |     
------------------------------------------------------------------------  
|   0 | SELECT STATEMENT   |           |     1 |     6   (0)| 00:00:01 |  
|   1 |  SORT AGGREGATE    |           |     1 |            |          |                      
|   2 |   TABLE ACCESS FULL| VARCHAR_T | 10000 |     6   (0)| 00:00:01 |  
------------------------------------------------------------------------  
SQL> select count(*) from char_t ;

  COUNT(*)   
----------
     10000

Execution Plan
----------------------------------------------------------  
Plan hash value: 4232242706   
------------------------------------------------------------------------  
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |     
------------------------------------------------------------------------  
|   0 | SELECT STATEMENT   |        |     1 |   748   (1)| 00:00:09 |    
|   1 |  SORT AGGREGATE    |        |     1 |            |          |        
|   2 |   TABLE ACCESS FULL| CHAR_T | 10182 |   748   (1)| 00:00:09 |   
---------------------------------------------------------------------       
SQL> spool off       

08 sec more time in case of char

I always welcome your queries and feedback :)

 More Detail on Our Website

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      

Saturday 20 October 2012


Diff B/w Delete & Truncate in Oracle DB




More Details on Our Website


On bigger picture they serve the same purpose but there are many Differences listed with examples  
Point
                                  Delete
                    Truncate
1.  Data Recovery

Delete: Come under the DML Category, we need to commit or Rollback explicitly to make the changes permanent, so we can recover the data by Rollback command fully with in a session or up to a point if Save Points are used
See listing 1 for more details
Fall In DDL Category (DDL Command issue the Auto commit implicitly) so no chances of Recovery even not using the Flashback table method.
But Truncate operations are also logged , they didn’t generate redo SQL but they are logged , view for truncated data info V$LOGMNR_CONTENTS  .More details on link
2. Data Removal
Delete Can remove all or selected data using the Where Clause predicates. Or we can say delete any subset of rows
We can Truncate complete table or a partition or sub partition of a table.
See listing 1 for more details
3. Speed
Delete is Slower because oracle maintain the redo logs for Read Consistency (so that every session connected can see a consistent data at a given point of time ) Delete is very time consuming activity especially when table have numerous indexes and Triggers associated with table
Faster as no data logs are maintained no associated trigger firing.
4. DML Triggers Firing

DML (Delete) triggers associated with table will fire.
DML Trigger will not fire in case of truncate method. See Listing 2 for more details.

5. Flashback Technology

Data can be recovered even after commit operation using Flashback Table options Flashback_transaction_query table will give what to recover and up to which point.
Data cannot be recovered in truncate method by Flashback table option.
See Listing 3 for more details

6. Referential Integrity Constraint Behavior

if we don’t have related data in child table then we can delete the data from the parent table or we have variants like On Delete Cascade & on Delete set Null.
For more details see listing 4
We can’t truncate a table with enable Referential Integrity Constraint, even there is no data in the child table, we have to disable or drop the constraint if we want to truncate the table.
Exception: Truncate is possible if the FK is self-referential means primary key and foreign key are on the same table.
7. Space De allocation or Space Utilization

No extent reset with delete when deleting rows from a table, extents are not de allocated,
So if there were 50 extents in the table before the deletion, there will still be 50 after the deletion. See Listing 5 & 6 for more details
Truncate: When a table is truncated it will free the space allocated except in case of reuse storage clause. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations .All extents are de allocated leaving only the extents specified when the table was originally created  .Example So if the table was originally created with min extents 3, there will be 3 extents remaining when the tables is truncated. When you truncate a table, NEXT is automatically reset to the last extent deleted.
8. High Water Mark

Delete will not reset the high water mark
See Listing 7 for more details.
Truncate will reset the High Water mark which is very important for performance point of view as in case of full table scan and full index scan oracle will read all the block under high water mark this makes a lot of difference in terms of performance.
9. Cluster

No as such restriction with delete.
See Listing 8 for More Details
You cannot individually truncate a table that is part of a cluster. You must truncate the cluster, Delete all rows from the table, or drop and re-create the table.
10. Information Capturing

Delete : we can capture the row information what we have deleted using Delete Method, f you are deleting multiple records then use composite data types (collections & records)
Truncate Don’t have this feature of capturing the deleted records.
See Listing 9
11. Function Based Index Impact
DELETE You cannot delete rows from a table if a function-based index on the table has become invalid. You must first validate the function-based index.

Truncate: No as such restriction
12. UNUSABLE Indexes
Delete no as such feature.
Truncate if table is not empty then truncate make all unusable indexes to useable.
13. Complex views

You cannot delete data from a Complex view except through INSTEAD OF triggers.
But we can delete data from simple Views and MV.
We cannot truncate a view simple or complex but you can truncate MV with special Features like Preserve MV Logs and Purge MV Logs.
14. Privileges
Delete You need to provide delete table privilege on object.
Truncate you must have drop table privilege there is no truncate table privilege exists.
15. Domain Index
No as such restriction
You cannot truncate the object having domain index in invalid or In progress state


Listing 1 Delete, Truncate, Rollback & Partitions Test
-- Create partitioned table
SQL>create table df (id number, name varchar2 (100))
Partition by range (id) (Partition a1 values less than (10000),
 Partition a2 values less than (maxvalue))
Table created.
-- Insert 100000 records for testing
SQL> begin
    for i in 1..100000 loop
insert into df values (i,chr(i));
 end loop;
     commit;
     end;
   /
PL/SQL procedure successfully completed.
SQL> select count (*) from df
COUNT(*) 
    100000  
-- Check records in each partition                                                                                      
SQL> select count(*) from df partition (a1)
COUNT(*) 
      9999                                                                                          
SQL>  select count(*) from df partition (a2);
COUNT(*) 
      90001
-- Delete all the data                                                                                          
SQL> delete from df
100000 rows deleted.
SQL> select count(*) from df ;
  COUNT(*)                                                                                         
----------                                                                                          
         0   
-- Rollback as deleted data can be rollback.                                                                                     
SQL> rollback;
Rollback complete.
-- delete from individual partition
SQL> delete from df PARTITION (a1);
19999 rows deleted.
SQL> select count(*) from df ;
  COUNT(*)                                                                                         
----------                                                                                         
     80001                                                                                         
SQL> rollback;
Rollback complete.
SQL> select count(*) from df;
  COUNT(*)                                                                                          
----------                                                                                         
    100000   
-- Truncate partition to remove the selected data                                                                                       
SQL> alter table df truncate partition a1 ;
Table altered.
--Check record from truncated partition
SQL> select count(*) from df partition (a1) ;
  COUNT(*)                                                                                         
----------                                                                                         
         0  
--Check record from non truncated partition
              SQL> select count(*) from df partition (a2);
  COUNT(*)                                                                                          
----------                                                                                         
        90001     
          **** END of Listing 1 **** 

Listing 2. DML triggers fire in case of delete not in Truncate
DML Delete triggers fire for Delete not for Truncate.
SQL> CREATE TABLE MY_NAME (FIRST_NAME VARCHAR2 (10),LAST_NAME VARCHAR2 (10));
Table created.
-- Table for error logging
SQL> CREATE TABLE ERR (MSG VARCHAR2(109));
Table created.
--Trigger on Delete event for each row
SQL>  CREATE OR REPLACE TRIGGER DIFFD
   BEFORE DELETE ON MY_NAME
For each row
    BEGIN
    INSERT INTO ERR VALUES ('LOG FIRSTTHEN DELETE');
   END;
    /
Trigger created.
-- Insert data for testing in loop
SQL> BEGIN
    FOR I IN 1..10 LOOP
    INSERT INTO MY_NAME VALUES ('ARUN'||I,'BHARAT'||I);
    END LOOP;
    COMMIT;
    END;
    /
PL/SQL procedure successfully completed.
--Delete from my_name table ..to test firing of trigger
SQL> DELETE FROM MY_NAME ;
10 rows deleted.
-- data comes into the err table as result of trigger firing on MY_Name table
SQL> SELECT count(*) FROM ERR
Count(*)
----------------
10
SQL> ROLLBACK
Rollback complete.
SQL> SELECT count(*) FROM MY_NAME
Count(*)
-------
10
SQL> SELECT count(*) FROM ERR;
Count(*)
-------
0
-- Now truncate the table , no trigger fired here
SQL> TRUNCATE TABLE MY_NAME ;
Table truncated.
SQL> SELECT count(*) FROM MY_NAME
Count(*)
-------
0
SQL> SELECT count( *) FROM ERR
  Count(*)
-------
0
              **** END of Listing 2 ****
**************************************************************
Listing 3. Referential Integrity Constraints behavior with delete & Truncate
With example of Self referential Integrity constraint
-- Master table
  1* CREATE TABLE MASTER (ID NUMBER PRIMARY KEY ,NAME VARCHAR2(10));
Table created.
-- Detail table
   CREATE TABLE DETAIL (ID NUMBER , VID NUMBER);
Table created.
-- Foreign Key Constraint added
SQL> ALTER TABLE DETAIL ADD CONSTRAINT FK_MM FOREIGN KEY (ID) REFERENCES MASTER(ID);
Table altered.
-- Truncate master table, it can’t because referential integrity constraint is enable for truncate you have to disable the constraint or drop the constraint.
SQL> TRUNCATE TABLE MASTER ;
TRUNCATE TABLE MASTER * ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
--insert data in master table
SQL> INSERT INTO MASTER VALUES (1,'ARUN');
1 row created.
SQL> INSERT INTO DETAIL VALUES (1,2);
1 row created.
SQL> INSERT INTO DETAIL VALUES (1,5);
1 row created.
SQL> DELETE FROM MASTER ;
DELETE FROM MASTER ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_MM) violated - child record found
SQL> DELETE FROM DETAIL;
2 rows deleted.
--now dependency over we can delete now
SQL> DELETE FROM MASTER;
1 row deleted.
--Exception with Truncate we can truncate if referential Integrity constraint is self referential (foreign key and primary key on same table)
-- table with Self Integrity Constraint
create table my_emp_test (empid number,name varchar2(10),manager_id number );
Table created
--Primary Key Constraint
alter table my_emp_test add constraint my_pk primary key(empid);
Table altered
--Foreign Key Constraint
alter table my_emp_test add constraint  my_fk foreign key(manager_id) references my_emp_test(empid);
Table altered
--insert data in table
insert into my_emp_test  values(100,'arun',null);
1 row created
insert into my_emp_test  values(200,'arun',100);
1 row created
insert into my_emp_test  values(300,'arun',200);
1 row created
insert into my_emp_test  values(400,'arun',100);
1 row created
--we can truncate table with enable self referential integrity constraint
Truncate table my_emp_test;
Table truncated.
            **** END of Listing 3 ****
************************************************************
Listing 4. Flashback Method
We can recover deleted data after commit by Flashback method but not truncated data
SQL> create table flash (id number , name varchar2(10));
Table created.
SQL> insert into flash values (1,'ar');
1 row created.
SQL> insert into flash values (2,'bh');
1 row created.
SQL> select * from flash
ID   NAME
--- -----
1    ar
2    bh
SQL> commit;
Commit complete.
SQL> SQL> delete from flash ;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from flash ;
no rows selected
  1 SQL> select current_scn from v$database
CURRENT_SCN
-----------
5701428

SQL> flashback table flash to scn 5701428
flashback table flash to scn 5701428                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SQL> alter table flash enable row movement;
Table altered.
SQL> flashback table flash to scn 5701428
  Flashback complete.
SQL> select * from flash
no rows selected
SQL>  flashback table flash to scn 5701342
Flashback complete.
SQL>  select * from flash
ID  NAME
---- ------
1    ar
2    bh
   SQL> truncate table flash;
Table truncated.
We cannot flashback the truncated data.

 **** END of Listing 4 ****

Listing 5. Space De allocation Test
SQL> create table arun_t (id number);

Table created.

SQL>  select extent_id, bytes, blocks
   from user_extents
   where segment_name = 'ARUN_T';

EXTENT_ID     BYTES    BLOCKS


        0     65536         8

-- Insert data for testing
SQL> begin
   for i in 1..100000
     loop
    insert into arun_t values (i);
   end loop;
commit;
 end;
/
PL/SQL procedure successfully completed.
n  Now check for the space

n  SQL> select extent_id, bytes, blocks
  2  from user_extents
  3  where segment_name = 'ARUN_T';

EXTENT_ID     BYTES    BLOCKS

        0     65536         8

        1     65536         8

        2     65536         8

        3     65536         8

        4     65536         8

        5     65536         8

        6     65536         8

        7     65536         8

        8     65536         8

        9     65536         8

       10     65536         8

       11     65536         8

       12     65536         8

       13     65536         8

       14     65536         8

       15     65536         8

       16   1048576       128

17 rows selected.

Now i just delete alls record from table test and commit,
delete will not shrink the table and indexes to the original size when table was
created without records nor it will reset the high water mark.
SQL> delete from test;

100000 rows deleted.

SQL> commit;

Commit complete.
SQL> select extent_id, bytes, blocks
  2  from user_extents
  3  where segment_name = 'TEST';
EXTENT_ID     BYTES    BLOCKS


        0     65536         8

        1     65536         8

        2     65536         8

        3     65536         8

        4     65536         8

        5     65536         8

        6     65536         8

        7     65536         8

        8     65536         8

        9     65536         8

       10     65536         8

       11     65536         8

       12     65536         8

       13     65536         8

       14     65536         8

       15     65536         8

       16   1048576       128

17 rows selected.

trucate will shrink the table and indexes to the original size when table was created without records and it will also reset the high water mark.
SQL> truncate table test;

Table truncated.

SQL> select extent_id, bytes, blocks

    from user_extents

    where segment_name = 'ARUN_T';


EXTENT_ID     BYTES    BLOCKS


        0     65536         8
              **** END of Listing 5 ****
************************************************************
Listing 6 Drop Storage and Reuse Storage with truncate and effects
SQL> begin
  2  for i in 1..1000000 loop
  3  insert into test_hwm values (I);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

BLOCKS
-------
 1664

SQL> select count(*) from test_hwm ;

COUNT(*)
--------
1000000

SQL> truncate table test_hwm reuse storage
  2  /

Table truncated.

SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

BLOCKS
-------
 1664

SQL> select count(*) from test_hwm ;
COUNT(*)
--------
0

SQL> truncate table test_hwm drop storage ;

Table truncated.

SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

BLOCKS
-------
 8

SQL> select count(*) from test_hwm;

  COUNT(*)
--------
0
              **** END of Listing 6 ****
      Listing 7 High Water Mark Test
Brief about High water Mark for more details search www.google.com
High Water Mark (HWM)
It is an Indicator which tells how much data a block ever have or
Oracle uses the high water mark to identify the highest amount of space used by a particular segment. It acts as the boundary between used and unused space. As the amount of data grows due to row inserts and updates, the segment's high water mark grows accordingly. 
The database doesn't know what is on a block unless and until, It goes to the block.

So, when you delete the information, the block is still "a block", it is just a block that once had active rows - but no longer does.
And when you full scan the table - we have to read all blocks that at any time contained data - because - they could contain data now. We won't know what is there until we read it.
Set autotrace on
SQL> create table test_hwm (id number);

Table created.

SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

BLOCKS
-------
 8


SQL> begin
  2  for i in 1..1000000 loop
  3  insert into test_hwm values(i);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

BLOCKS
-------
 8

SQL> select count(*) from test_hwm ;

       COUNT(*)
----------------
       1000000


Execution Plan
----------------------------------------------------------

Plan hash value: 2326001506
----------------------------------------------------------

| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   369   (7)| 00:00:05 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_HWM |   902K|   369   (7)| 00:00:05 |
-----------------------------------------------------------------------  

-- See time to count the records is 05 seconds.

SQL> delete from test_hwm where id<10000;
  9999 rows deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 2971358912
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
 |   0 | DELETE STATEMENT   |          |    17 |   221 |   376   (9)| 00:00:05 |

 |   1 |  DELETE            | TEST_HWM |       |       |            |          |

 |*  2 |   TABLE ACCESS FULL| TEST_HWM |    17 |   221 |   376   (9)| 00:00:05 | 
-------------------------------------------------------------------------------

-- Note Blocks consumed or Still same.

SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

       BLOCKS
       ---------
       1664

SQL> rollback;
 
Rollback complete.


-- delete all the records
SQL> delete from test_hwm ;

1000000 rows deleted.


-- Time taken to count the o records are same 0.5 seconds

SQL> select count(*) from test_hwm ;

       COUNT(*)
    -------------
         0          

Execution Plan
----------------------------------------------------------

Plan hash value: 2326001506
----------------------------------------------------------

| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   369   (7)| 00:00:05 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_HWM |   902K|   369   (7)| 00:00:05 |
-----------------------------------------------------------------------  
 
  -- Note blocks are still same

 SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

       BLOCKS
       ---------
       1664


SQL> rollback;

Rollback complete.

SQL> select count(*) from test_hwm ;

COUNT(*)
    -------------
         1000000    

Plan hash value: 2326001506
----------------------------------------------------------

| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   369   (7)| 00:00:05 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_HWM |   902K|   369   (7)| 00:00:05 |
-----------------------------------------------------------------------  

-- truncate table which reset the HWM and time to find the o record reduce to 0.1 sec.
SQL> truncate table test_hwm
  2  /

Table truncated.

SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

       BLOCKS
       ---------
         8

SQL> select count(*) from test_hwm

COUNT(*)
    -------------
         0

Execution Plan
----------------------------------------------------------
 Plan hash value: 2326001506

----------------------------------------------------------   
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_HWM |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------



SQL> spool off

                  **** END of Listing 7 ****
*************************************************************
List 8 .Cluster With Truncate & delete 
--create cluster
SQL> create cluster cluster_test (id number(2));
Cluster created.
--=create cluster tables
SQL> create table cluster_table1 (roll number, name1 varchar2(100),id number(2)) cluster cluster_test(id);
Table created.
SQL> create table cluster_table2 (roll1 number, name2 varchar2(100),id number(2)) cluster cluster_test(id);
Table created.
SQL> insert into cluster_table1 values(1,'arun',1);
insert into cluster_table1 values(1,'arun',1)
            *
ERROR at line 1:
ORA-02032: clustered tables cannot be used before the cluster index is built
--cluster index
SQL> create index c_i on cluster cluster_test;
Index created.
SQL> insert into cluster_table1 values(1,'arun',1);
1 row created.
--Delete from cluster table will work
SQL> delete from cluster_table1;
2 rows deleted.
SQL> rollback;
Rollback complete.
--we  can not truncate a table which is part of cluster
SQL> truncate table cluster_table1;
  truncate table cluster_table1
               *
ERROR at line 1:
ORA-03292: Table to be truncated is part of a cluster

SQL> truncate cluster cluster_test;
  Cluster truncated.
SQL> select count(*) from cluster_table1;
COUNT(*)
--------
0
   SQL> truncate table cluster_table1;
truncate table cluster_table1                *
ERROR at line 1:
ORA-03292: Table to be truncated is part of a cluster

         **** END of Listing 8 ****
*************************************************************
           
Listing 9 RETURNING Clause: Example 
       Variable bnd1 number
       Begin 
         DELETE FROM employees
         WHERE employee_id=&empid          
         RETURNING salary INTO: bnd1
         End;
         Print bnd1
         BND1
        2000


        **** END of Listing 9 ****
*************************************************************

I always welcome your queries and feedback :) 

More details on Our Website