Query Optimization - Single table

Oracle Query Optimization - Single table If we are using a query on single table with where clause on a column, it can be simply optimized by creating an Index.

If the query is fetching near 10% data, index would be used by query.

SQL> conn sys as sysdba

Enter password:

Connected.

SQL> grant plustrace to hr;
Grant succeeded.


Elapsed: 00:00:00.00

SQL> conn hr

Enter password:

Connected.

SQL> select count(*) from majestic_million;

COUNT(*)
----------
1000000

SQL> set autot trace

SQL> SELECT * FROM MAJESTIC_MILLION WHERE GLOBALRANK =994679;

Elapsed: 00:00:00.08

Execution Plan
----------------------------------------------------------
Plan hash value: 2029254678


-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  |   Operation                          | Name                             | Rows  |   Bytes |   Cost (%CPU)   |   Time    |
------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                                            |   134 |   46498 |     3303   (1)        |  00:00:40 |
|*  1 |  TABLE ACCESS FULL  | MAJESTIC_MILLION             |   134 |   46498 |     3303   (1)        |  00:00:40 |
------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter("GLOBALRANK"=994679)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      11484  consistent gets
          0  physical reads
          0  redo size
       1173  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> CREATE INDEX GLOBALRANK_INDX ON MAJESTIC_MILLION(GLOBALRANK);

Index created.

Elapsed: 00:00:01.02  
 
SQL> set autot trace

SQL> SELECT * FROM MAJESTIC_MILLION  WHERE GLOBALRANK =994679;

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 64544402

------------------------------------------------------------------------------------------------
| Id  | Operation                                          | Name                       | Rows  | Bytes | Cost (%CPU)| Time|
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                                 |  1      |    347 |     4   (0)        | 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | MAJESTIC_MILLION  |  1      |    347 |     4   (0)        | 00:00:01 |
|*  2 |   INDEX RANGE SCAN                     | GLOBALRANK_INDX  |  1      |          |     3   (0)        | 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access("GLOBALRANK"=994679)

Note
-----
   - dynamic sampling used for this statement (level=2)


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

Cheers

Rajani