Mega Code Archive

ALTER SESSION SET QUERY_REWRITE_ENABLED

SQL> SQL> set echo on SQL> SQL> CREATE TABLE lecturer ( 2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 ); Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10001, 'Scott', 'Lawson','Computer Science', 11); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits) 2 VALUES (10002, 'Mar', 'Wells','History', 4); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10003, 'Jone', 'Bliss','Computer Science', 8); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10004, 'Man', 'Kyte','Economics', 8); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10005, 'Pat', 'Poll','History', 4); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10006, 'Tim', 'Viper','History', 4); 1 row created. SQL> SQL> SQL> ALTER SESSION SET SQL_TRACE = TRUE; Session altered. SQL> SQL> CREATE OR REPLACE FUNCTION lecturerStatus( 2 p_NumCredits IN NUMBER) 3 RETURN VARCHAR2 AS 4 BEGIN 5 IF p_NumCredits = 0 THEN 6 RETURN 'Inactive'; 7 ELSIF p_NumCredits <= 12 THEN 8 RETURN 'Part Time'; 9 ELSE 10 RETURN 'Full Time'; 11 END IF; 12 END lecturerStatus; 13 / Function created. SQL> SQL> SELECT id 2 FROM lecturer 3 WHERE SUBSTR(lecturerStatus(current_credits), 1, 20) = 4 'Part Time'; ID ---------- 10001 10002 10003 10004 10005 10006 6 rows selected. SQL> SQL> CREATE OR REPLACE FUNCTION lecturerStatus(p_NumCredits IN NUMBER) 2 RETURN VARCHAR2 3 DETERMINISTIC AS 4 BEGIN 5 IF p_NumCredits = 0 THEN 6 RETURN 'Inactive'; 7 ELSIF p_NumCredits <= 12 THEN 8 RETURN 'Part Time'; 9 ELSE 10 RETURN 'Full Time'; 11 END IF; 12 END lecturerStatus; 13 / Function created. SQL> SQL> CREATE INDEX lecturer_index ON lecturer 2 (SUBSTR(lecturerStatus(current_credits), 1, 20)) 3 COMPUTE STATISTICS; Index created. SQL> SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE; Session altered. SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; Session altered. SQL> SQL> SELECT /*+ index(lecturer,lecturer_index) */ id 2 FROM lecturer 3 WHERE SUBSTR(lecturerStatus(current_credits), 1, 20) = 4 'Part Time'; ID ---------- 10001 10002 10003 10004 10005 10006 6 rows selected. SQL> SQL> DROP INDEX lecturer_index; Index dropped. SQL> SQL> drop table lecturer; Table dropped.