CODEWITHSUNDEEP
sqloracle10g
aBs0lut3z33r0
aBs0lut3z33r0

Reputation: 95

Which SQL Query is more efficient?

Which of the two queries is more efficient?

  1. SELECT *FROM Emp WHERE DeptNo<>20

  2. SELECT *FROM Emp WHERE NOT Deptno=20

My trainer told me the 1st one is more efficient and I understand the reason but how can I check this using Oracle 10g? He used some commands to show the CPU cycles consumed but I can't find them after a Google Search.

Outputs using AUTOTRACE are the same. 

SQL> SELECT *FROM Emp
    WHERE DeptNo<>20

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=9 Bytes=333
          )

   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=9 Bytes=
          333)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1125  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL> SELECT *FROM Emp
WHERE NOT Deptno=20

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=9 Bytes=333
          )

   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=9 Bytes=
          333)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1125  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

Upvotes: 1

Views: 369

Answers (3)

Andrew Lazarus
Andrew Lazarus

Reputation: 19350

The AUTOTRACE is telling you that in this case, the queries have been executed the exact same way, and therefore neither is better than the other. Both involved a full table scan. Databases can be pretty smart about optimizing. It is possible that Oracle spent 1 nanosecond less deriving the query plan for the <> version.

The general point that your trainer might have been trying to make involves when an index can be used, and when not. So, a SELECT with IN is likely to run faster than a SELECT with a NOT IN, because the DB has a better chance of using an index search for the former.

Upvotes: 1

Tom Gullen
Tom Gullen

Reputation: 61775

From the statistics they both look equal. A couple of tips though:

  • Don't use select *, only select the fields you need
  • Make sure you have indexes set up properly

Upvotes: 4

boisvert
boisvert

Reputation: 3739

In oracle both of these will be processed in the same way. Any difference will occur in parsing (=subdividing) the query, and even that will be make know difference if the queries run several times, because the parsing results are cached.

Focus efficiency efforts on more inefficient processes, e.g. joins and subqueries.

Upvotes: 1

Related Questions