This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jul 26, 2013 3:05 AM by Nikolay Savvinov Go to original post RSS
  • 15. Re: Index on column with only 2 distinct values
    Karthick_Arp Guru
    Currently Being Moderated

    padders wrote:

     

    What do you use it for? Perhaps you should consider whether you really need to.


    "Counting records is not smart, inefficient and should always be avoided", Tom Kyte, November 2009

     

    You are looking it out of context. Tom is referring to code like this.

     

    begin

      ...

      select count(*) into l_count

        from emp

       where empno = 1000

         and deptno = 10;

     

      if l_count > 0 then

        update emp

           set sal = sal + 1000

         where empno = 1000

           and deptno = 10;

      else

        raise_application_error(-20001, 'Emp not found');

      end if;

     

      ...

    end;

     

    He is referring to the count that is being used to check before processing the data. This no way means COUNT should not be used. There are tons of reporting requirements where you need to use count. For instance you do a summary report, you will be counting there. There are many cases.

     

    Tom's statement only means don't count data and check its existence and then process. Just process it. The above code should be done like this.

     

    begin

    ...

     

      update emp

         set sal = sal + 1000

       where empno = 1000

         and deptno = 10;

     

      if sql%notfound then

        raise_application_error(-20001, 'Emp not found');

      end if;

     

      ...

    end;

       

  • 16. Re: Index on column with only 2 distinct values
    padders Pro
    Currently Being Moderated

    > You are looking it out of context

     

    Possibly - it is an interesting question though - when do we really need to count :-D

     

    I didn't say I never use COUNT and you are correct it is a common business requirement in a reporting context.

     

    But would you index a non-selective column and accept the overhead on DML purely for the purposes of occasionally counting it when the same index is not useful for accessing the table?

  • 17. Re: Index on column with only 2 distinct values
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    DheerajSingh wrote:

     

    Hi All,

     

    Could i create an Index on column with only 2 distinct values('Y' and 'N').

     

    Thanks.

     

    Regards,

    Dheeraj

     

    yes you can. Like others already said, such an index could be helpful if the values are unevenly distributed. Moreover, in such case it could be a good idea to create an index with just ONE value, replacing the other one (the popular one) with NULL -- such an index would be much more compact.

     

    If you cannot redefine the column, you can use NVL function and functioned-based indexes (on 11g and higher, you can wrap it up into a virtual column).

     

    Best regards,

    Nikolay

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points