2 Replies Latest reply: Jun 21, 2013 2:07 AM by _Karthick_ RSS

    Need depth knowledge on these questions

    1011786

      Hi,

       

      1. Under what circumstances are indexes not used by Oracle while processing queries?

       

      2. Why is comparison of 'null' values directly not possible in SQL?

       

      Please share your views..

        • 1. Re: Need depth knowledge on these questions
          Etbin

          1. it depends (as usually) something to read about

          http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm#BABHJAJF

          2. exception

          http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions049.htm#SQLRF00631

          In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.

          otherwise null is used for undefined and you cannot compare two undefined things.


          Regards


          Etbin

          • 2. Re: Need depth knowledge on these questions
            _Karthick_

            > 1011786 wrote:

            > Hi,

            > 1. Under what circumstances are indexes not used by Oracle while processing queries?

             

            CBO has to consider lot of factors before determining a access path.

             

            When a index will not be used? To put it simple i will give 2 case (There are lot more)

             

            1. When the index column value is transformed in a Query, By using a function or expression

             

            e.g.

             

            select *

              from emp

            where to_char(empid, '0999999999') = '0000000001'

             

            In this case a normal index on empid will not be considered. Because the column value of empid is transformed to a different value.

             

            2. When the I/O needed to get a row is more in Index scan than Full table scan. In this case also index will not be used. Consider you writing a query like this

             

            select *

              from emp

            where empid between 1 and 9000

             

            And you have only 10000 employees. This means you are picking 90% of the data from emp table. So if Oracle uses the Index on empid to reach to the rows of emp table then it will have to perform more I/O than just doing a full table scan.


            If you are interested in going deep into the internals of index I would suggest Richard Foote's Blog

             

            http://richardfoote.wordpress.com/