Skip to main content

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<5;

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"<5)

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
Labels:

Comments

Post a Comment

Popular posts from this blog

Oracle and VB 6.0 Connectivity (Part 1)

Here is a basic VB application called voter information system demonstrated which explains the concepts quite clearly. The first step required to establish connectivity is preparing the back end and the front end of the project. These steps are divided into two parts first the back end that is the oracle and front end that is the Visual Basic 6.0. Oracle Firstly we need to set up the database. Create the tables, triggers and procedures and database in the oracle. Create tables: Example: create table voter(id number(5) primary key, name varchar2(25),address varchar2(50),age number(3),gender varchar2(6),dob varchar2(30)); create table votertemp(id number(5) primary key, name varchar2(25),address varchar2(50),age number(3),gender varchar2(6),dob varchar2(30)); (we will be using the next table in the triggers that we will be implementing) Visual Basic 6.0 Develop the VB application as in this example given below. Design the necessary forms for the application. The next part is ...
13 comments
Read more

Validations in Visual Basic 6.0

This topic will demonstrate how to perform client side validations in Visual Basic 6.0. It will guide you with a step by step procedure. 1) Firstly in the below image suppose you want to accept only integer numbers as id in the textbox next to ID label in the form from the user you can perform client side validations in the following way: These are the ascii codes that i am using for acepting only integer values from user. Back space - 8 0 to 9 Integers - 48 to 57 Delete - 127 This is how the function will look. Private Sub Text2_KeyPress(KeyAscii As Integer) If Not ((KeyAscii >= 48 And KeyAscii <= 57) Or KeyAscii = 127 Or KeyAscii = 8) Then MsgBox "enter proper value" KeyAscii = 0 End If End Sub Double click on the text box which you want to validate. Next select the event as keypress from top right list box. In this case the name of text box is text1. This is the function for accepting input as integer value only from the user. ...
11 comments
Read more

Oracle and VB 6.0 Connectivity (Part 3)

Now we will see how to insert values into table a table and to view them. Oracle Initially we had created table in part1. Visual Basic 6.0 Now we wil insert data into the table through visual basic. The code for inserting will be as follows When you double click on add button it will open your code window it will have this code! Private Sub Command1_Click() Dim gen As String If (Option1.Value = True) Then gen = "male" ElseIf (Option2.Value = True) Then gen = "female" End If rsv.Open "insert into voter values(" & Text5.Text & ",'" & Text2.Text & "','" & Text3.Text & "','" & Combo1.Text & "','" & gen & "','" & DTPicker1.Value & "')", conv, adOpenDynamic, adLockOptimistic If (rsv.State = 1) Then rsv.Close End If MsgBox ("Data Entered") End Sub This will add the data entered into...
7 comments
Read more