1 2 Previous Next 17 Replies Latest reply: Jul 26, 2013 5:05 AM by Nikolay Savvinov Go to original post RSS
      • 15. Re: Index on column with only 2 distinct values
        Karthick_Arp

        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

          > 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

            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