fetch only first row from oracle - is it faster?

Question

My main goal with this question is optimization and faster run time.

After doing lot of processing in the Stored Proc I finally return a count like below:

          OPEN cv_1 FOR
             SELECT COUNT(*) num_of_members
               FROM HOUSEHOLD_MEMBER a,
                    HOUSEHOLD b
                WHERE RTRIM(LTRIM(a.mbr_last_name)) LIKE v_MBR_LAST_NAME || '%'
                        AND a.number = '01'
                        AND a.code = v_CODE
                        AND a.ssn_head = v_SSN_HEAD
                        AND TO_CHAR( a.mbr_dob, 'MM/DD/YYYY') = v_DOB;

But in my code that is calling the SP does not need the actual count. It just cares that count is greater than 1.

Question:

1. How can I change this to return just 1 or 0. 1 when count is > 0 and 0 when count > 1.
2. Will it be faster to do this rather than returning the whole count?

Answer 1

You could use HAVING to check that the COUNT is greater than 1.

CURSOR c_example IS
SELECT COUNT(*)
...
HAVING COUNT(*) > 1;
l_dummy PLS_INTEGER;


OPEN c_example;
FETCH c_example INTO l_dummy;
IF c_example%NOTFOUND THEN
  CLOSE c_example;
  RETURN FALSE
ELSE
  CLOSE c_example;
  RETURN TRUE
END IF;

Answer 2

What does "first" mean? Without a WHERE clause, you have no idea what order a relational database is storing rows in.

Do you have data suggesting that you have a performance issue, or are you optimizing prematurely?

I don't believe your idea will result in any performance improvement at all. I'd look elsewhere.

Start here:

AND TO_CHAR( a.mbr_dob, 'MM/DD/YYYY') = v_DOB;

I think if you EXPLAIN this, you'll find that TO_CHAR will force Oracle to use a TABLE SCAN. Any index on date of birth is useless in this case.

This would suggest to me that you've modeled a date of birth column as a string/VARCHAR. Why on earth would you do that? Use a DATE column and pass in a proper date variable. Your performance will improve, and you'll have the actual household count as well. It seems useful to me.

Answer 3

I would not have thought that introducing a filter on the count(*) would help with performance, but as has already been said, you can do this with a HAVING clause.

Your biggest bottleneck will probably be with your joins.

WHERE RTRIM(LTRIM(a.mbr_last_name)) LIKE v_MBR_LAST_NAME || '%'

This line is not going to help. Whenever you start performing functions of fields, it has trouble using the correct indexes, so if you can avoid trimming the name, that may help.

AND TO_CHAR( a.mbr_dob, 'MM/DD/YYYY') = v_DOB;

This looks like the biggest problem. If v_DOB could be converted to a date before the query, that would help. Alternatively, I think that switching the statement to use a TO_DATE instead may help.

The best way to optimize this is to get the explain plan:

EXPLAIN PLAN FOR
  ...

Answer 4

If you don't need the actual data, and you only want to know if there is at least 1, you can use ROWNUM to optimize it:

OPEN cv_1 FOR
    SELECT 1 has_at_least_one_member
    FROM HOUSEHOLD_MEMBER a,
         HOUSEHOLD b
    WHERE RTRIM(LTRIM(a.mbr_last_name)) LIKE v_MBR_LAST_NAME || '%'
    AND a.number = '01'
    AND a.code = v_CODE
    AND a.ssn_head = v_SSN_HEAD
    AND TO_CHAR( a.mbr_dob, 'MM/DD/YYYY') = v_DOB
    AND ROWNUM = 1;

Oracle will optimize this to stop processing as soon as it finds a match.

On an unrelated note, I suggest (as per duffymo's note) you remove the TO_CHAR around a.mbr_dob and add TO_DATE around v_DOB. This way, if there happens to be an index on a.mbr_dob, Oracle has an opportunity to use it.

Answer 5

My experience has been that it's often faster to fetch a single row from a cursor than to retrieve the COUNT(). This is useful if you need to know that matching data exists but don't care about how many rows. In this case the code could be rewritten as

DECLARE
  bRow_found  BOOLEAN := FALSE;
BEGIN
  FOR aRow IN (SELECT a.mbr_last_name
                 FROM HOUSEHOLD_MEMBER a, 
                      HOUSEHOLD b 
                 WHERE RTRIM(LTRIM(a.mbr_last_name)) LIKE v_MBR_LAST_NAME || '%' 
                       AND a.number = '01' 
                       AND a.code = v_CODE 
                       AND a.ssn_head = v_SSN_HEAD 
                       AND TO_CHAR( a.mbr_dob, 'MM/DD/YYYY') = v_DOB)
  LOOP
    bRow_found := TRUE;
    EXIT;
  END LOOP;
END;

Share and enjoy.