SQL query Optimization or Query Evalution

Note: I have performed on oracle 10g wonder whether it will work on oracle 9i.

SQL> set autotrace on;
SQL> create table sailors (sid number(5) primary key, sname varchar2(10), rating number(5),
age number(5));

Table created.

SQL> create table reserves (sid number(5) , did number(5), day number(5), rname varchar2(5), CONSTRAINT col3_fk FOREIGN KEY(sid) REFERENCES sailors(sid) ON DELETE CASCADE);

Table created.

SQL> select * from sailors;

       SID SNAME          RATING        AGE
————— ————— ————— —————
         1 asss                1         22
         2 adfss               2         12
         3 affss               6         52
         4 afdss               6         32
         5 ddss                6         62
         6 iops                6         35

6 rows selected.

Execution Plan
—————————————————————————————
Plan hash value: 1417977701

——————————————————————————————————————-
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————————————-
|   0 | SELECT STATEMENT  |         |     6 |   276 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| SAILORS |     6 |   276 |     2   (0)| 00:00:01 |
——————————————————————————————————————-

Note
——-
   – dynamic sampling used for this statement

Statistics
—————————————————————————————
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        701  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL> select * from reserves;

       SID        DID        DAY RNAME
————— ————— ————— ——-
         1          1          4 asb
         1          1          4 asb
         2         12          7 sdb
         3         52          9 begb
         4         13          3 htyb
         5         17          6 bhjb
         6         27          8 qweb

7 rows selected.

Execution Plan
—————————————————————————————
Plan hash value: 3735829263

———————————————————————————————————————
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————————————
|   0 | SELECT STATEMENT  |          |     7 |   301 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| RESERVES |     7 |   301 |     2   (0)| 00:00:01 |
———————————————————————————————————————

Note
——-
   – dynamic sampling used for this statement

Statistics
—————————————————————————————
         88  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        710  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          7  rows processed

Queries

1)
SQL> SELECT S.sname
  2  FROM Reserves R, Sailors S
  3  WHERE R.sid=S.sid AND S.rating>5;

SNAME
—————
affss
afdss
ddss
iops

Execution Plan
—————————————————————————————
Plan hash value: 1082800282

———————————————————————————————————————-
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————————————-
|   0 | SELECT STATEMENT   |          |     5 |   230 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |          |     5 |   230 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| SAILORS  |     4 |   132 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| RESERVES |     7 |    91 |     2   (0)| 00:00:01 |
———————————————————————————————————————-

Predicate Information (identified by operation id):
—————————————————————————-

   1 – access(“R”.”SID”=”S”.”SID”)
   2 – filter(“S”.”RATING”>5)

Note
——-
   – dynamic sampling used for this statement

Statistics
—————————————————————————————
          9  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        473  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          4  rows processed

2)

SQL> SELECT S.sname
  2  FROM Reserves R, Sailors S
  3  WHERE R.sid=S.sid AND
  4  R.did=1 AND S.rating
SNAME
—————
asss
asss

Execution Plan
—————————————————————————————
Plan hash value: 1694003264

———————————————————————————————————————-
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————————————-
|   0 | SELECT STATEMENT   |          |     2 |   118 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |          |     2 |   118 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| RESERVES |     2 |    52 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| SAILORS  |     2 |    66 |     2   (0)| 00:00:01 |
———————————————————————————————————————-

Predicate Information (identified by operation id):
—————————————————————————-

   1 – access(“R”.”SID”=”S”.”SID”)
   2 – filter(“R”.”DID”=1)
   3 – filter(“S”.”RATING”
Note
——-
   – dynamic sampling used for this statement

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

3)

SQL> SELECT S.sname
  2  FROM Sailors S
  3  WHERE S.rating >
  4  (SELECT Avg(rating)
  5  FROM Sailors);

SNAME
—————
affss
afdss
ddss
iops

Execution Plan
—————————————————————————————
Plan hash value: 2915248218

———————————————————————————————————————-
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————————————-
|   0 | SELECT STATEMENT    |         |     1 |    20 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL  | SAILORS |     1 |    20 |     2   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE    |         |     1 |    13 |            |          |
|   3 |    TABLE ACCESS FULL| SAILORS |     6 |    78 |     2   (0)| 00:00:01 |
———————————————————————————————————————-

Predicate Information (identified by operation id):
—————————————————————————-

   1 – filter(“S”.”RATING”> (SELECT AVG(“RATING”) FROM “SAILORS”
              “SAILORS”))

Note
——-
   – dynamic sampling used for this statement

Statistics
—————————————————————————————
          8  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        473  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

4)
SQL> SELECT S.sname
  2  FROM Sailors S
  3  WHERE EXISTS
  4  (SELECT *
  5  FROM Reserves R
  6  WHERE R.sid=S.sid);

SNAME
—————
asss
adfss
affss
afdss
ddss
iops

6 rows selected.

Execution Plan
—————————————————————————————
Plan hash value: 15409597

———————————————————————————————————————-
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————————————-
|   0 | SELECT STATEMENT   |          |     6 |   198 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |          |     6 |   198 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| SAILORS  |     6 |   120 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| RESERVES |     7 |    91 |     2   (0)| 00:00:01 |
———————————————————————————————————————-

Predicate Information (identified by operation id):
—————————————————————————-

   1 – access(“R”.”SID”=”S”.”SID”)

Note
——-
   – dynamic sampling used for this statement

Statistics
—————————————————————————————
          8  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        494  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          6  rows processed

5)
SQL> SELECT S.sname
  2  FROM Sailors S
  3  WHERE EXISTS
  4  (SELECT *
  5  FROM Reserves R
  6  WHERE R.did=1
  7  AND R.sid=S.sid);

SNAME
—————
asss

Execution Plan
—————————————————————————————
Plan hash value: 2677790372

————————————————————————————————————————

——————

| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)

| Time     |

————————————————————————————————————————

——————

|   0 | SELECT STATEMENT             |             |     2 |    92 |     4  (25)

| 00:00:01 |

|   1 |  NESTED LOOPS                |             |     2 |    92 |     4  (25)

| 00:00:01 |

|   2 |   SORT UNIQUE                |             |     2 |    52 |     2   (0)

| 00:00:01 |

|*  3 |    TABLE ACCESS FULL         | RESERVES    |     2 |    52 |     2   (0)

| 00:00:01 |

|   4 |   TABLE ACCESS BY INDEX ROWID| SAILORS     |     1 |    20 |     1   (0)

| 00:00:01 |

|*  5 |    INDEX UNIQUE SCAN         | SYS_C004050 |     1 |       |     0   (0)

| 00:00:01 |

————————————————————————————————————————

——————

Predicate Information (identified by operation id):
—————————————————————————-

   3 – filter(“R”.”DID”=1)
   5 – access(“R”.”SID”=”S”.”SID”)

Note
——-
   – dynamic sampling used for this statement

Statistics
—————————————————————————————
          8  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        410  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

Advertisements