sql - 如何在类型对象上创建索引？

Question

目前我正在使用 oracle 11g 中的类型对象。在此我有如下 DB 对象:

1. 表格

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

2. 类型对象规范是

   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. 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;
   

创建所有这些东西后，即使我已经使用

成功填充了学生表

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;

/ 

我需要在 e_result 列上创建位图索引，在 tot 列上创建普通索引。

我试过了

create index id1 on students(marks.total)

和

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

但是我不能。我该怎么办？

Answer 1

首先，您必须将函数声明为 DETERMINISTIC 才能在 SQL 索引中使用它们(即，您必须向 Oracle 断言给定相同的输入，它们会给出相同的输出)。

例如:

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  /

其次，调用该函数时必须使用 ()，否则它会假设它是一个名为 total 的列:

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

Index created.

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

Index created.

然后你应该看到使用的索引:

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     |       |       |            |          |
-----------------------------------------------------------------------------------------