What construction works faster than IN or EXISTS?



  • There are two designs in the refrigeration OBD. IN and EXISTS

    In the case of a request against which a comparison does not contain a record of NULLit doesn't really matter what to use.

    What is more productive?



  • If there's one line in it, then the time should be the same. I see. exists The optimizer may himself know that the internal demand is to be carried out with the optimization of FIRST_ROW, that is, for the rapid receipt of the first entry and that it will stop after receipt of the record. In case in The optimizer may go another way to get all the lines more quickly. And this path may not be the way to get one line.

    In addition, in theory, in The optimizer may start performing with request and then try to select the entries from the basic table of condition. In case exists The optimizer may decide to follow the main table and, for each of its records, check the request. But in practice, the optimizer will not forget what indices are available, watch the statistics on tables and indices, and decide on the basis of this information.

    But what is the way to be really quicker to predict in general? Many factors must be taken into account. If you're interested in the speed of the request, you'll have to watch the implementation plan for every request and if the plan doesn't suit the different options.

    And a single recipe is that some design can't be faster or slower. It should also be pointed out that different releases of the DSB (even not large versions) can optimise a request differently.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2