Hi,
I will explain Real Life Index Usage and Index Performance Test in Oracle Database in this article.
If you don’t know What is the Index and how to create it, read following article.
Let’s start make a Real life example about Index create and its performance effect.
When I query my customer table, there are 100 million records as follows.
SQL> select count(*) from MSDEVECI.CUSTOMER; COUNT(*) ---------- 100000000 SQL>
The script and sample data of this sample table are as follows.
MSDEVECI.MUSTERI table in the Screenshots Means MSDEVECI.CUSTOMER. So MUSTERI and CUSTOMER tables are the same.
CREATE TABLE MSDEVECI.MUSTERI ( MUSTERI_ID NUMBER, ADI VARCHAR2(50 BYTE), TEL_NO NUMBER, ADRES VARCHAR2(100 BYTE), SEHIR VARCHAR2(50 BYTE), ABONE_TARIHI DATE, YORUM VARCHAR2(150 BYTE) ) TABLESPACE USERS RESULT_CACHE (MODE DEFAULT) PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING;
When we examine the above script of the sample table, there is no Index in this table.
When I query the customer that musteri_id is 93 as follows. When the query is completed, the response time of this query is 5 minutes and 56 seconds, although just 1 record is queried as shown below. This duration is too much and unacceptable value for Production databases. Both system resources are consumed unnecessarily, and with different waiting times, users become inoperable.
SQL> set timing on
SQL> select * from MSDEVECI.CUSTOMER where musteri_id=93;
MUSTERI_ID ADI TEL_NO ADRES SEHIR ABONE_TARIH YORUM
---------- -------------------------------------------------- ---------- ------------------------------------------------------------------------------------------
93 93.MUSTERI 5429999999 Sincan Ankara 01-JAN-13 ABONELER TEST AMACLI YUKLENMISTIR
Elapsed: 00:05:56.16
SQL>
Execution plan of the above query is as follows. Query performs Table Access full
SQL> set autotrace on SQL> select * from MSDEVECI.CUSTOMER where musteri_id=93; MUSTERI_ID ADI TEL_NO ---------- -------------------------------------------------- ---------- ADRES -------------------------------------------------------------------------------- SEHIR ABONE_TAR -------------------------------------------------- --------- YORUM -------------------------------------------------------------------------------- 93 93.MUSTERI 5429999999 Sincan Ankara 01-JAN-13 ABONELER TEST AMACLI YUKLENMISTIR Execution Plan ---------------------------------------------------------- Plan hash value: 2659560815 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 86 | 364K (1)| 01:12:52 | |* 1 | TABLE ACCESS FULL| MUSTERI | 1 | 86 | 364K (1)| 01:12:52 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("MUSTERI_ID"=93) Statistics ---------------------------------------------------------- 0 recursive calls 2 db block gets 1341427 consistent gets 1339007 physical reads 0 redo size 1010 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL>
Now let’s create an index on our customer table and after the index is create, query the same select and see differences
When creating the index, I create the index online and parallel using the following script depending on the number of cpu the system has.
SQL> create index MID_IX on MSDEVECI.CUSTOMER(musteri_id) parallel 4 online; Index created.
After creating this Index, check whether the Index is created as follows.
SQL> select * from dba_indexes where table_name='MUSTERI'; -- MUSTERI and CUSTOMER same table
After the index is created, the above query which musteri_id is 93 query run as follows , you can see that the query runs in a very much less than 1 second.
SQL> select * from MSDEVECI.CUSTOMER where musteri_id=93;
MUSTERI_ID ADI TEL_NO ADRES SEHIR ABONE_TARIH YORUM
---------- -------------------------------------------------- ---------- ------------------------------------------------------------------------------------------
93 93.MUSTERI 5429999999 Sincan Ankara 01-JAN-13 ABONELER TEST AMACLI YUKLENMISTIR
Elapsed: 00:00:00.01
SQL>
When we look at the execution plan of this query, it looks like the following. Query performs Index range scan instead of Full table scan.
SQL> set autotrace traceonly SQL> select * from MSDEVECI.CUSTOMER where musteri_id=93 Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 3780290458 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 86 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| MUSTERI | 1 | 86 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | MID_IX | 1 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("MUSTERI_ID"=93) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 1014 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL>
You can see the difference between the indexed table and non indexed table. when the index is used, system resources are not wasted too much and our queries return in almost 1-2 seconds, whereas the same query lasted more than 5 minutes before using the index.
There is full table access in the first execution plan and Index Range Scan in the second execution plan. Mostly we prefer that queries use Index range scan instead of full table access.