Speeding up a query with INNER JOIN
<p>I have a query that takes a long time to execute. I've waited for about 10 mins and it's still not finished executing.</p>

<p>The query looks something like this:</p>

<pre><code>SELECT 
  one.ID, 
  two.NAME, 
  two.STATUS, 
  four.KEY, 
  four.VALUE, 
  count(one.ID) as num
FROM TABLE_ONE one, TABLE_TWO two, TABLE_THREE three, TABLE_FOUR four
WHERE one.STATE='RED'
  AND (two.STATUS='ON' OR two.STATUS='OFF')
  AND (
    four.KEY='FINAL'
    OR four.KEY='LIMIT'
    OR (
      four.KEY='MODE'
      AND (
        four.VALUE='T'
        OR four.VALUE='R')))
GROUP BY one.ID, two.NAME, two.STATUS, four.KEY, four.VALUE
ORDER BY group_name ASC;
</code></pre>

<p>I have another query which is equivalent but executes very fast (about 1 second to execute).</p>

<p>Here is that query:</p>

<pre><code>SELECT 
  one.ID, 
  two.NAME, 
  two.STATUS, 
  four.KEY, 
  four.VALUE, 
  count(one.ID) as num
FROM TABLE_ONE one
INNER JOIN TABLE_TWO two
ON one.ID=two.ID
INNER JOIN TABLE_THREE three
ON two.ID=three.GROUP_ID
INNER JOIN TABLE_FOUR four
ON three.ID=four.ID
 WHERE one.STATE='RED'
  AND (two.STATUS='ON' OR two.STATUS='OFF')
  AND (
    four.KEY='FINAL'
    OR four.KEY='LIMIT'
    OR (
      four.KEY='MODE'
      AND (
        four.VALUE='T'
        OR four.VALUE='R')))
GROUP BY one.ID, two.NAME, two.STATUS, four.KEY, four.VALUE
ORDER BY group_name ASC;
</code></pre>

<p>I'm kind of confused why the query with <code>INNER JOIN</code> executes really fast (about 1 second) and the one without takes a long time (waited about 10mins and still not finised executing).</p>

<p>Is there anything I can do to the query without the <code>INNER JOIN</code> to speed up the execution time?</p>

<p>I am using ORACLE.</p>