I need to find out if there are at least n number of records in a table meeting the WHERE criteria. I am trying to decide what is more efficient, using "HAVING COUNT(*) > P_THRSHLD;" , FETCH FIRST, ROWNUM, LIMIT etc. Do they all do a table scan?
Below is one version I have. Is there a more efficient way of doing this? This simple query could be run 10K to 60K times a day.
create or replace PROCEDURE QRY1
(
P_USERID IN TYPES.CHAR8,
P_THRSHLD IN INTEGER,
P_CURSOR IN OUT SYS_REFCURSOR
)
AS
-- Miscellaneous work variables
REC_COUNT NUMBER DEFAULT 0;
-- Cursor definitions
CURSOR CRS1(C_USERID TYPES.CHAR8) IS
SELECT 1 INTO REC_COUNT
FROM TBL1_TST TBL1,
TBL2_TST TBL2
WHERE C_USERID = TBL1.USERID
AND TBL1.STATUS = TBL2.STATUS
AND TBL1.TYPE1 = TBL2.TYPE1
AND TBL1.TYPE2 = TBL2.TYPE2
AND TBL2.FLG1 = 'N'
AND TBL2.FLG2 = 'N'
HAVING COUNT(*) > P_THRSHLD;
BEGIN
OPEN CRS1( P_USERID );
FETCH CRS1 INTO REC_COUNT;
OPEN P_CURSOR FOR
SELECT REC_COUNT "RECORD_COUNT"
FROM DUAL;
END QRY1;