Simple Performance Test in our Test ENV.

Simple Performance Test in our Test ENV.

Test in our env:

SQL> conn mir/(Password);
Connected

SQL> select table_name from user_tables;

TABLE_NAME
------------------
TEST
SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENO                                                NUMBER(10)
 NAME                                               VARCHAR2(20)
 SAL                                                NUMBER(20)

SQL> select * from test;
no rows selected

SQL> insert into test values(1,'a',1000);
1 row created.

SQL>  insert into test values(2,'b',2000);
1 row created.

SQL>  insert into test values(3,'c',3000);
1 row created.

SQL>  insert into test values(4,'d',4000);
1 row created.

SQL> explain plan for select * from test where sal=1000;
Explained

SQL>  select * from table(dbms_xplan.display());    ----------------------- As we could see the index is not created

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     8 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     8 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter("SAL"=1000)
13 rows selected.
As per the above query the index is not created on it

=========================================
Create an Index & Verify:

=========================================

SQL> create index indx1 on test(sal);

Index created.

SQL> explain plan for select * from test where sal=1000;
Explained

SQL> select * from table(dbms_xplan.display());        -------------- Created index & verified

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2984087803
--------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     8 |     2   (0)| 00:00
:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST  |     1 |     8 |     2   (0)| 00:00
:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00
:01 |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 – access("SAL"=1000)
14 rows selected.

Hence the Simple Performance test is tested & verify in our ENV