-
Fetching Cursor problem
Hi, this is my first post in this forum, I hope you can help me, I'll try to explain you the situation as best as possible. Thank you in advance.
Situation:
I have a schema with a table called NEWS. This table has 3 Oracle Text indexes and 2 indexes.
This schema has a stored procedure that retrieves a cursor like this:
OPEN l_cursor FOR
SELECT *
FROM (SELECT /*+ FIRST_ROWS(50) */
(score (1) + score (2) + score (3)
) AS RANK, ID, TO_CHAR (dateinsert, 'dd/mm/yyyy')
FROM news
WHERE ( contains (resume, v_palabra, 1) > 0
OR contains (insert, v_palabra, 2) > 0
OR contains (theme, v_palabra, 3) > 0
)
AND ouid = v_ouid
ORDER BY dateinsert DESC)
WHERE ROWNUM < 4001;
I also have a connection user that calls that store procedure and fetches that cursor, printing all the records.
DECLARE
CUR SYS_REFCURSOR;
X NUMBER := 0;
TYPE TYP_C_TEMP IS RECORD (SCORE NUMBER, ID NUMBER, DATEINSERT VARCHAR2(10));
C_TEMP TYP_C_TEMP;
BEGIN
DBMS_OUTPUT.put_line ('BEGINS QUERYING' || ' ----> TIME: '|| SYSTIMESTAMP);
CUR:= FILNEWS.GET_ALL('CRASH','C10','R');
DBMS_OUTPUT.put_line ('ENDS QUERYING' || ' ----> TIME: '|| SYSTIMESTAMP);
DBMS_OUTPUT.put_line ('STARTS FETCHING CURSOR' || ' ----> TIME: '|| SYSTIMESTAMP);
LOOP
FETCH CUR INTO C_TEMP;
X := X + 1;
EXIT WHEN CUR%NOTFOUND OR X = 10;
DBMS_OUTPUT.put_line ('RECORD NUMBER: ' || X || ' ----> TIME: '|| SYSTIMESTAMP);
END LOOP;
DBMS_OUTPUT.put_line ('FINISHES FETCHING CURSOR' || ' ----> TIME: '|| SYSTIMESTAMP);
END;
/
The problem is that when I run this script, it takes more than 7secs. from the moment that it starts fetching the cursor until it retrieves the first record.
This is an example:
BEGINS QUERYING ----> TIME: 30/08/2007 12:02:27,612996000 PM -03:00
ENDS QUERYING ----> TIME: 30/08/2007 12:02:30,565031000 PM -03:00
STARTS FETCHING CURSOR ----> TIME: 30/08/2007 12:02:30,565102000 PM -03:00
RECORD NUMBER: 1 ----> TIME: 30/08/2007 12:02:37,123874000 PM -03:00
RECORD NUMBER: 2 ----> TIME: 30/08/2007 12:02:37,123985000 PM -03:00
RECORD NUMBER: 3 ----> TIME: 30/08/2007 12:02:37,124037000 PM -03:00
RECORD NUMBER: 4 ----> TIME: 30/08/2007 12:02:37,124086000 PM -03:00
RECORD NUMBER: 5 ----> TIME: 30/08/2007 12:02:37,124135000 PM -03:00
RECORD NUMBER: 6 ----> TIME: 30/08/2007 12:02:37,124183000 PM -03:00
RECORD NUMBER: 7 ----> TIME: 30/08/2007 12:02:37,124236000 PM -03:00
RECORD NUMBER: 8 ----> TIME: 30/08/2007 12:02:37,124283000 PM -03:00
RECORD NUMBER: 9 ----> TIME: 30/08/2007 12:02:37,124330000 PM -03:00
FINISHES FETCHING CURSOR ----> TIME: 30/08/2007 12:02:37,124377000 PM -03:00
PL/SQL procedure successfully completed.
Elapsed: 00:00:10:87
Thank you.