CREATE TABLE TEST ( ID NUMBER, TXT VARCHAR2(1000 BYTE), DT TIMESTAMP(6) ) PARTITION BY HASH (ID) PARTITIONS 16 ; CREATE SEQUENCE SYSTEM.S_TEST START WITH 100000000; CREATE OR REPLACE PROCEDURE test_fill IS tmpVar NUMBER; BEGIN tmpVar := 0; FOR i IN 1..10000 LOOP FOR J IN 1..100 LOOP INSERT INTO TEST VALUES ( s_test.NEXTVAL , 'BLA'||TO_CHAR(s_test.CURRVAL) ,SYSDATE) ; END LOOP; COMMIT; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN -- Consider logging the error and then re-raise RAISE; END test_fill; / execute test_fill; execute test_fill; execute test_fill; execute test_fill; execute test_fill; execute test_fill; execute test_fill; execute test_fill; execute test_fill; execute test_fill; ...... CREATE TABLE PROBE AS SELECT * FROM (SELECT * FROM TEST SAMPLE (30)) WHERE ROWNUM <= 2000000 CREATE OR REPLACE PACKAGE T_Sel AS FUNCTION t_ind RETURN NUMBER ; FUNCTION t_ind_only RETURN NUMBER ; END T_Sel ; / CREATE OR REPLACE PACKAGE BODY T_Sel AS FUNCTION t_ind RETURN NUMBER IS CURSOR c_p IS SELECT ID FROM PROBE; l_id NUMBER; l_txt VARCHAR2(100); r NUMBER; d DATE; BEGIN r:=0; FOR i IN (SELECT ID FROM PROBE ) LOOP SELECT d.ID , d.txt INTO l_ID , l_txt FROM TEST d WHERE d.ID = i.ID AND d.DT < d; r:=MOD(r+LENGTH(l_txt),56774); END LOOP; RETURN r; END; FUNCTION t_ind_only RETURN NUMBER IS CURSOR c_p IS SELECT ID FROM PROBE; l_id NUMBER; l_txt VARCHAR2(100); r NUMBER; BEGIN r:=0; FOR i IN (SELECT ID FROM PROBE ) LOOP SELECT d.ID INTO l_ID FROM TEST d WHERE d.ID = i.ID; r:=MOD(r+l_id,56774); END LOOP; RETURN r; END; END T_Sel; / CREATE UNIQUE INDEX TEST_ID ON TEST (ID) INITRANS 2 MAXTRANS 255 GLOBAL PARTITION BY HASH (ID) PARTITIONS Test: select t_sel.ind from dual -> Index and table scan select t_sel.ind_only from dual -> Index only scan