How to create index on type object?

Question

Currently i'm working with type object in oracle 11g. In this i have DB objects as follows:

1. table

   CREATE TABLE students
     (rollno NUMBER(15) primary key, s_Name VARCHAR2(20), Marks type_1
     );
   

2. Type object specification is

   CREATE OR REPLACE type type_1
   AS
     object
     (
       sub_1 NUMBER,
       sub_2 NUMBER,
       sub_3 NUMBER,
       member FUNCTION total
       RETURN NUMBER,
       member FUNCTION e_result
       RETURN VARCHAR2);
   

3. It's body

   CREATE OR REPLACE type body type_1
   AS
     member FUNCTION total
     RETURN NUMBER
   IS
   BEGIN
     RETURN (sub_1+sub_2+sub_3);
   END;
   member FUNCTION e_result
     RETURN VARCHAR2
   IS
     DECLARE
       temp NUMBER;
     BEGIN
       temp  :=sub_1+sub_2+sub_3;
       IF(temp>50) THEN
         RETURN ('pass');
       ELSE
         RETURN ('fail');
       END IF;
     END;
   END;
   

After create all these thing even I've successfully populated the student table using

BEGIN
FOR i IN 1..800 LOOP
        FOR j IN 1..400 LOOP
INSERT INTO students
VALUES     (sequence1.NEXTVAL,
            dbms_random.String('A', 5),
            Type_1(Round(dbms_random.Value(10, 100)), Round(
            dbms_random.Value(10, 100)), Round(dbms_random.Value(10, 100))));
END LOOP;
END LOOP;

dbms_output.Put_line('completed');

COMMIT;
END;

/ 

I need to create bitmap index on e_result column and normal index on tot column.

I have tried

create index id1 on students(marks.total)

and

create bitmap index bid1 on students(marks.e-result)

But I can't. What am I supposed to do?

Answer 1

Firstly you have to have your functions declared as DETERMINISTIC for them to be used in an SQL index (i.e. you have to assert to Oracle that given the same input, they give the same output).

eg:

SQL> CREATE OR REPLACE type type_1
  2  AS
  3    object
  4    (
  5      sub_1 NUMBER,
  6      sub_2 NUMBER,
  7      sub_3 NUMBER,
  8      member FUNCTION total
  9      RETURN NUMBER deterministic,
 10      member FUNCTION e_result
 11      RETURN VARCHAR2 deterministic
 12  );
 13  /

Type created.

SQL> CREATE OR REPLACE type body type_1
  2  AS
  3    member FUNCTION total
  4    RETURN NUMBER deterministic
  5  IS
  6  BEGIN
  7    RETURN (sub_1+sub_2+sub_3);
  8  END;
  9  member FUNCTION e_result
 10    RETURN VARCHAR2 deterministic
 11  IS
 12      temp NUMBER;
 13    BEGIN
 14      temp  :=sub_1+sub_2+sub_3;
 15      IF(temp>50) THEN
 16        RETURN ('pass');
 17      ELSE
 18        RETURN ('fail');
 19      END IF;
 20    END;
 21  END;
 22  /

secondly you have to use () when calling the function otherwise it will assume it a column named total:

SQL> create index id1 on students (marks.total());

Index created.

SQL> create bitmap index bid1 on students(marks.e_result());

Index created.

then you should see the indexes used:

SQL> exec dbms_stats.gather_table_stats(user, 'STUDENTS', method_opt=>'for all indexed columns size skewonly')

PL/SQL procedure successfully completed.

SQL> explain plan for select * from students s where s.marks.e_result() = 'fail';

Explained.

SQL> @explain ""

Plan hash value: 1595221732

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   635 | 17780 |   109   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID | STUDENTS |   635 | 17780 |   109   (0)| 00:00:02 |
|   2 |   BITMAP CONVERSION TO ROWIDS|          |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | BID1     |       |       |            |          |
-----------------------------------------------------------------------------------------