Virtual Columns in Oracle 11g

create table marks
 (
    physics      number,
    chemistry       number,
    total     number,
    aggre  varchar2(6)
    generated always as
    (
       case
          when total <= 50 then 'LOW'
          when total > 60 and total <= 80 then 'MEDIUM'
          when total > 81 and total <= 99 then 'HIGH'
          else 'ULTRA'
       end
     ) virtual
 );



 insert into marks (physics, chemistry, total) values (50,60,40);


 insert into marks (physics, chemistry, total) values (60,70,60);

 insert into marks (physics, chemistry, total) values (80,95,99);

commit;




12:45:32 SQL> select * from marks;

   PHYSICS  CHEMISTRY      TOTAL AGGRE
---------- ---------- ---------- ------
        50         60         40 LOW
        60         70         60 ULTRA
        80         95         99 HIGH


create index IN_TOTAL on marks (aggre);


select index_type from user_indexes where index_name = 'IN_TOTAL';


12:47:52 SQL> select index_type from user_indexes where index_name = 'IN_TOTAL';

INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL


12:48:25 SQL> select column_expression from user_ind_expressions where index_name = 'IN_TOTAL';

COLUMN_EXPRESSION
--------------------------------------------------------------------------------
CASE  WHEN "TOTAL"<=50 THEN 'LOW' WHEN ("TOTAL">60 AND "TOTAL"<=80) THEN 'MEDIUM' WHEN ("TOTAL">81 AND "TOTAL"<=99) THEN 'HIGH' ELSE 'ULTRA' END