frequency of different types of join predicates

From: Bob Jenkins <bob_jenkins_at_burtleburtle.net>
Date: 23 Oct 2001 16:47:09 -0700
Message-ID: <a5d787df.0110231547.21794c4_at_posting.google.com>


What is the frequency of equijoins versus range scans versus likes? Which SQL predicates are most common?

I made a rough estimate by searching for them in catalog.sql and the other .sql files in that directory. I screened out any rows with comments (rem, /*, //), then got rid of the patterns :=, <<, >>, ->,
=>. I got rid of <=, >=, !=, <> before counting <, >, =. The counts
were:

  11184  82.4%  =
    763   5.6%  >
    632   4.7%  <
    365   2.7%  !=
    238   1.7%  is null
    131   0.9%  like
    119   0.9%  is not null
     73   0.5%  >=
     36   0.3%  <>
     27   0.2%  <=

   3457         :=
    958         (+)
    115         =>
     58         ->
     23         connect by
     14         <<
     14         >>
Received on Wed Oct 24 2001 - 01:47:09 CEST

Original text of this message