Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2005
    Posts
    29

    Unanswered: checking if a particular record is present in a table (was "SQL Query.")

    I wanted to find out a query which would return a particular value depending whether a particular condition is successful.

    For e.g.

    SELECT count(*) FROM dual
    WHERE EXISTS
    (
    SELECT 1 FROM my_month_table
    WHERE month_end_date = '30-apr-2005'
    )


    So if the condition matches, it would return a value > 0, else it would return 0.

    Can this be written in a better way?

    Many Thanks.

  2. #2
    Join Date
    Jan 2005
    Posts
    362
    An idea is to write a pl/sql block to do that you want if it is complicated, a stored procedure for instance.
    I do not know if there is another idea.
    dimis

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    why not just SELECT count(*) FROM my_month_table
    WHERE month_end_date = '30-apr-2005' ?

  4. #4
    Join Date
    Feb 2005
    Posts
    29
    I was hoping to find the most efficient way of checking if a particular record is present in a table. In this case, check if a particular month_end_date is present in my_month_table.

    I am trying to use a pl/sql block.

    DECLARE
    v_present number;
    BEGIN
    SELECT count(*)
    INTO v_present
    FROM dual
    WHERE EXISTS
    (
    SELECT 1 FROM my_month_table
    WHERE month_end_date = '30-apr-2005'
    )

    IF v_present = 1 THEN
    ....
    ELSE
    ....
    END IF;


    Depending on whether any record exists (i.e. v_present = 1) , a set of actions would be carried out.

    I hope I have made my question a bit more clear.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Like JMartinez said, simply rewrite it as

    Code:
    DECLARE
    v_present number;
    BEGIN
    SELECT count(*) 
     FROM my_month_table 
        WHERE month_end_date = '30-apr-2005' ;
    
    IF v_present > 0 THEN
    ....
    ELSE
    ....
    END IF;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Feb 2005
    Posts
    29
    Thanks.

    I thought using the exists clause, might be the faster than just a simple count.

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Or just avoid the condition all at once..

    Missing an into there.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by sashish4529
    Thanks.

    I thought using the exists clause, might be the faster than just a simple count.
    Yes, everyone seems to be missing your point that you don't want to count how many rows there are, you just want to see if one exists. Counting thousands of rows just to confirm that there is at least one is inefficient.

    All you need to do is this:

    SELECT 1 FROM my_month_table
    WHERE month_end_date = '30-apr-2005'
    AND ROWNUM = 1;

    Oracle will then stop counting as soon as the first matching row is found. You should see a query plan something like this:
    Code:
    SELECT STATEMENT Optimizer=CHOOSE (Cost=172 Card=1 Bytes=7)
      SORT (AGGREGATE)
        COUNT (STOPKEY)
          TABLE ACCESS (FULL) OF 'MY_MONTH_TABLE' (Cost=172 Card=140 Bytes=980)
    Here is an example anyone can try to demonstrate the significant time savings possible:
    Code:
    SQL> select count(*) from all_objects
      2  where object_name like '%A%'
      3  /
    
      COUNT(*)
    ----------
         19202
    
     real: 6047
    SQL> /
    
      COUNT(*)
    ----------
         19202
    
     real: 6063
    SQL> /
    
      COUNT(*)
    ----------
         19202
    
     real: 6047
    SQL> select count(*) from all_objects
      2  where object_name like '%A%'
      3  and rownum = 1
      4  /
    
      COUNT(*)
    ----------
             1
    
     real: 46
    SQL> /
    
      COUNT(*)
    ----------
             1
    
     real: 47
    SQL> /
    
      COUNT(*)
    ----------
             1
    
     real: 47
    i.e. 6 seconds versus 0.05 seconds!

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Oh the shame of it all. Tony is right, I meant to include the rownum = 1 and I forgot to. Thanks for the nice catch.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    andrewst, good catch.

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    nice example with timed output.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Feb 2005
    Posts
    29
    Yes. This was a good one. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •