Results 1 to 8 of 8

Thread: SQL question

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: SQL question

    What exactly the purpose of select count(1)? I am debugging a code that has that select statement. I know that select count returns the total of the values that are stored in the column, and I've used count before (select count(col1)), but I have never used count(1).

  2. #2
    Join Date
    Mar 2004
    Location
    California
    Posts
    58
    As far as I know Count(1) does the same thing as count(*), but some people believe count(1) is better from the performance point of view especially for tables containing large data volumes and having many columns.

  3. #3
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106

    Post

    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  4. #4
    Join Date
    Jul 2004
    Posts
    268

    Post SQL question

    I have a stored procedure that has few variables in the declare section and the select statement in the begin section.

    Here is the store procedure:

    create or replace procedure proc_1(var1 in varchar2, var2 in number, var3 in number)

    v_1 varchar2(100)

    begin
    select count(1)
    into v_1
    from temp_table
    where col1 = var3;

    begin
    select col1
    from table1, table2
    so on

    I don't understand what is the purpose of the first select. Please explain. Thanks.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    count(1) and count(*) are just the same ... there's no difference. Read more about it here.

    Do you use "v_1" later in a procedure body? If not, it doesn't make any sense to me. Can't be even used to raise a NO_DATA_FOUND exception (as it returns 0, if nothing else).

    And where's the point declaring v_1 as a VARCHAR(100) and selecting a NUMBER (result of a count function) into it?

  6. #6
    Join Date
    Mar 2004
    Location
    California
    Posts
    58
    Quote Originally Posted by inka
    I have a stored procedure that has few variables in the declare section and the select statement in the begin section.

    Here is the store procedure:

    create or replace procedure proc_1(var1 in varchar2, var2 in number, var3 in number)

    v_1 varchar2(100)

    begin
    select count(1)
    into v_1
    from temp_table
    where col1 = var3;

    begin
    select col1
    from table1, table2
    so on

    I don't understand what is the purpose of the first select. Please explain. Thanks.

    As it looks to me, there must be some decision being made later based on the number of rows returned by the condition col1=var3 from temp_table.

  7. #7
    Join Date
    Jul 2004
    Posts
    268

    Red face SQL Question

    There is actually a line of code that I forgot to include that is between first and second begin.


    begin
    select count(1)
    into v_1
    from temp_table
    where col1 = var3;

    v_1 := v_1 + 1;

    begin
    select col1
    from table1, table2
    so on

    Do you think they use select count(1) as the counter?

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Search for any usage of the variable v1, if it is not used then the select is not needed, but since it is being put into a string, I suspect that someone is putting out a report of some kind at the end. Maybe a comparison of the count of col1 = var2 before and after the procedure (just a guess)
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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