Next generation strategy and solutions

SQL Server Indexes – An Insider Look - Part 3

2011-06-02T04:02:00.000-07:00

Non-Clustered Index

What we saw till now is a very ideal situtation where we have a well defined key and can go ahead to create a clustered index. But what if the data is not so organized but we still need to have an index on the column. Enter the world of Non-Clustered Index. Where actually the data is not organized or ordered in any pre-existing condition, but still satisfy the purposes. The major difference is leaf level does not contain data. It includes pointers to the relevant data and the pointer is a simple RID column. RID columns include the information on extent, page and row offset. Using this RID column it will go and fetch the data from different pages.

Another issue with with non-clustered index is it behaves differently for heap and clustered table. Here again we will see two scenarios. One Non-clustered index created on a heap and then created on a clustered table.

Scenario 3: Non-Clustered Index on Heap Table

Let us create a table called PRODUCTS as follows:

CREATE TABLE PRODUCTS (PRODUCTNUMBER NUMERIC(6), PRODUCTNAME VARCHAR(30), PRODUCTDESCRIPTION VARCHAR(50), PRODUCTTYPE VARCHAR(1))

Now let us create a non-clustered index as follows:

CREATE NONCLUSTERED INDEX PRODIDX ON PRODUCTS(PRODUCTNUMBER, PRODUCTNAME)

The following script will insert the data into this table.

BEGIN

DECLARE @COUNTER AS INT

SET @COUNTER=1

WHILE (@COUNTER <= 50000)

BEGIN

INSERT INTO PRODUCTS VALUES

(CAST(@COUNTER AS VARCHAR),

'PRODUCTS ' + CAST(@COUNTER AS VARCHAR),

'DESCRIPTION ' + CAST(@COUNTER AS VARCHAR),

(case (@COUNTER % 2) when 1 then 'I' when 0 then 'R' else NULL end))

SET @COUNTER=@COUNTER+1

END

Now let us use a simple select clause and observe the execution plan.

SELECT * FROM PRODUCTS WHERE PRODUCTNUMBER=2089

If you see this execution plan carefully, the following can be observed:

a) Since we do not have clustered index in this table, it has used RID lookup to retrieve other two columns PRODUCTDESCRIPTION and PRODUCTTYPE

b) It has used Index Seek to retrieve indexed columns PRODUCTNUMBER and PRODUCTNAME

c) Internally what happens is the optimizer uses NESTED LOOPS to join the data from the results of the two operations viz Index Seek and RID Lookup.

Is this kind of execution plan is bad? Depends on the business needs and execution time. The above query took couple of seconds to execute. Let us now select only the indexed columns and see what is the execution plan for the same query.

SELECT PRODUCTNUMBER, PRODUCTNAME FROM PRODUCTS WHERE PRODUCTNUMBER=2089

Now the execution plan is changed, no RID lookup because we have selected only the indexed columns. What if we wanted to avoid RID lookup and at the same time most of the columns in the table to be selected? Use Include option along with the definition of the index creation as follows:

DROP INDEX PRODUCTS.PRODIDX

CREATE NONCLUSTERED INDEX PRODIDX ON PRODUCTS(PRODUCTNUMBER, PRODUCTNAME) INCLUDE PRODUCTDESCRIPTION, PRODUCTTYPE

The following select statement provides an execution plan

SELECT PRODUCTNUMBER, PRODUCTNAME FROM PRODUCTS WHERE PRODUCTNUMBER=2089

Please note that we did not get RID lookup, which is because of the columns included in the index creation. However, please be careful with INCLUDE column option. Please do benchmark before using it as it occupies more space in LEAF level and there on reducing the number of rows and increasing the number of IOs.

Scenario 4: Non-Clustered Index on Clustered Table

Now, in the same PRODUCTS table I have dropped the indexes and created two indexes as follows:

DROP INDEX PRODUCTS.PRODIDX

CREATE CLUSTERED INDEX PRODCLUSTIDX ON PRODUCTS(PRODUCTNUMBER)

CREATE NONCLUSTERED INDEX PRODIDX ON PRODUCTS(PRODUCTNAME)

Again we have the same number of records viz 50,000 records in the table. The following select statement produces the below execution plan:

Select * from products where productname=’PRODUCTS 9201’

If you read the execution plan, the following can be observed:

a) Since we have clustered index in this table, it has used Key Lookup to retrieve other two columns PRODUCTDESCRIPTION and PRODUCTTYPE

b) It has used Index Seek to retrieve indexed columns PRODUCTNUMBER and PRODUCTNAME

c) Internally what happens is the optimizer uses NESTED LOOPS to join the data from the results of the two operations viz Index Seek and Key Lookup.

Further please note that in case if the KEY LOOKUP is to be avoided, use INCLUDE option while creating the index.

Non-Clustered index can be used as part of heap table or as part of clustered table. There is no hard and fast rule that it has to be used only with heap or clustered table. Before using the same we need to benchmark and test it completely.

Further Reading

Please don’t forget to visit this site to read my next articles on

Ø Joins and Sets

Ø Hierarchical Queries

SQL Server Indexes – An Insider Look - Part 2

2011-06-02T03:57:00.000-07:00

Now let us start loading the data using the following script.

BEGIN

DECLARE @COUNTER AS INT

SET @COUNTER=300000

WHILE (@COUNTER <= 500000)

begin

INSERT INTO ACCOUNTS VALUES (@COUNTER, ('ACCOUNT ' + CAST(@COUNTER AS VARCHAR)), ('ACCOUNT DESC ' + CAST(@COUNTER AS VARCHAR)), (CASE (CAST(@COUNTER AS VARCHAR) % 4) WHEN 1 THEN 'A' WHEN 2 THEN 'L' WHEN 3 THEN 'E' WHEN 4 THEN 'I' ELSE NULL END))

set @COUNTER=@COUNTER+1

end

END

It took 1 minute to execute the above script and Page Splits per second increased from 18,562 to 19,604. It took just 1042 page splits per second to load 150,000 records. Fragment details are provided below:

Now let us start working on the second set of data and observe the page splits and fragmentation.

BEGIN

DECLARE @COUNTER AS INT

SET @COUNTER=300000

WHILE (@COUNTER <= 500000)

begin

set @COUNTER=@COUNTER+1

end

END

It took 1 minute and 39 seconds to execute the above script and Page Splits per second increased from 19,604 to 21,030. It took just 1426 page splits per second to load 200,000 records. Fragment details are provided below:

The most interesting part is the third set, which actually inserts the data in between the existing data. This will increase the page splits further and cause the cascading effects on the remaining data as well. This should not cause any problem if the clustering key is defined properly. Let us execute the following script to insert the missing data.

BEGIN

DECLARE @COUNTER AS INT

SET @COUNTER=150001

WHILE (@COUNTER <= 299999)

begin

set @COUNTER=@COUNTER+1

end

END

It took 1 minute to execute the above script and Page Splits per second increased from 21,030 to 22,099. It took just 1069 page splits per second to load 150,000 records. Fragment details are provided below:

Scenario 2: Not So Well Defined Clustered Key

Now let us change the scenario and use a calculated column and index the same. By calculated column, I mean a column that is derived from another column or a combination of values that can be put in one column. For this, I will create another table called PurchaseMaster as follows:

CREATE TABLE [dbo].[purchasemaster](

[purchasenumber] [varchar](20) NULL,

[purchasedescription] [varchar](30) NULL,

[vendorcode] [varchar](15) NULL,

[dateofpurchase] [datetime] NULL

)

A clustered index was also created on the column purchasenumber as follows:

CREATE CLUSTERED INDEX PURCHASEMASTERIDX ON PURCHASEMASTER(PURCHASENUMBER)

The column PURCHASENUMBER is derived on the basis of date of purchase. For example, the following is the sample data available in the PURCHASENUMBER column.

purchasenumber

1\2011-2012

10\2011-2012

100\2011-2012

1000\2011-2012

101\2011-2012

102\2011-2012

103\2011-2012

104\2011-2012

105\2011-2012

106\2011-2012

Please note that each purchase number is appended with the financial year. The purchase number 100 has the financial year 2011-2012 and the same for the other purchase numbers. As soon as the person who is raising the purchase order looks at this, the person will know the financial year details. This is indeed very useful and infact very handy as well. Also, when we issue the following statement, we did not see any anomaly.

SELECT * FROM PURCHASEMASTER WHERE PURCHASENUMBER='1000\2011-2012'

The execution plan for the above select statement is provided below:

Now, if we see a range filter based query as follows, the results were not proper.

SELECT * FROM PURCHASEMASTER WHERE PURCHASENUMBER>'1\2011-2012' AND PURCHASENUMBER <'101\2011-2012'

purchasenumber purchasedescription vendorcode dateofpurchase

10\2011-2012 Purchase 10 Vendor 10 2011-05-30 19:08:48.653

100\2011-2012 Purchase 100 Vendor 100 2011-05-30 19:08:48.687

1000\2011-2012 Purchase 1000 Vendor 1000 2011-05-30 19:08:49.217

Now there are no records in the table and entire data was truncated. We will now study the page splits and the fragmentation details for this clustered index. Initially the page splits is calculated using the following query and was observed to be 9325.

SELECT * FROM SYS.SYSPERFINFO WHERE COUNTER_NAME = 'PAGE SPLITS/SEC' AND OBJECT_NAME LIKE '%ACCESS METHODS%'

Also, the fragmentation details for this table and index is illustrated below:

Now we will insert two sets of data, purchase number from 1 to 150,000 and from 300,000 to 500,000 with a gap of 150,000 between the sets of data. After inserting every set of data, we will observe the corresponding page splits and fragment.

The following script will be used to generate these sets of data.

BEGIN

DECLARE @COUNTER AS INT

SET @COUNTER=1

WHILE (@COUNTER <= 150000)

BEGIN

INSERT INTO PURCHASEMASTER VALUES

(CAST(@COUNTER AS VARCHAR) + '\' + cast(YEAR(GETDATE()) as varchar) + '-' + cast(YEAR(GETDATE())+1 as varchar),

'PURCHASE ' + CAST(@COUNTER AS VARCHAR),

'VENDOR ' + CAST(@COUNTER AS VARCHAR),

GETDATE())

SET @COUNTER=@COUNTER+1

END

Inserting 150,000 records took just 1 minute and 3 seconds. The page splits/second and fragmentation after this are provided below:

From 9325, page splits/second has increased to 11523 which caused 2198 page splits per second during this insert operation.

Also the fragment details are provided below:

Please note that the fragmentaion is observed to be high in this case. Now, let us consider the next set, which is inserting next set of 200,000 records from 300,000 to 500,000.

BEGIN

DECLARE @COUNTER AS INT

SET @COUNTER=300000

WHILE (@COUNTER <= 500000)

BEGIN

INSERT INTO PURCHASEMASTER VALUES

(CAST(@COUNTER AS VARCHAR) + '\' + cast(YEAR(GETDATE()) as varchar) + '-' + cast(YEAR(GETDATE())+1 as varchar),

'PURCHASE ' + CAST(@COUNTER AS VARCHAR),

'VENDOR ' + CAST(@COUNTER AS VARCHAR),

GETDATE())

SET @COUNTER=@COUNTER+1

END

Inserting 200,000 records took just 1 minute and 18 seconds. The page splits/second and fragmentation after this are provided below:

From 11,523, page splits/second has increased to 14,947 which caused 3,424 page splits per second during this insert operation. Also the fragment details are provided below:

Wow, that is a great increase in fragmentation.

Now the final is what more important to observe both Page Splits and Fragmentation. We are now going to insert the missing records between 150,000 and 300,000 and observe the page splits. I will also explain diagramatically how this would happen. Now let us execute the same script by changing the values.

BEGIN

DECLARE @COUNTER AS INT

SET @COUNTER=150001

WHILE (@COUNTER <= 299999)

BEGIN

INSERT INTO PURCHASEMASTER VALUES

(CAST(@COUNTER AS VARCHAR) + '\' + cast(YEAR(GETDATE()) as varchar) + '-' + cast(YEAR(GETDATE())+1 as varchar),

'PURCHASE ' + CAST(@COUNTER AS VARCHAR),

'VENDOR ' + CAST(@COUNTER AS VARCHAR),

GETDATE())

SET @COUNTER=@COUNTER+1

END

The third batch took just 57 seconds to complete. Page Splits per second was observed to be 17,520 which caused 2,573 page splits per second during this insert operation. Also the fragment details are provided below:

Now having said this on page splits and fragmentation, how do we know if this is good or bad? Let us compare the scenario 1 and 2 in the following table and observe the results.

Description		Execution Time (mm:ss)	Page Splits	Fragmentation (AVG_FRAGMENTATION_ IN_PERCENT FOR INDEX_LEVEL=0)
Scenario 1	1-150,000	01:00	1,042	0.68
	300,000-500,000	01:39	1,426	0.57
	150,001-299,999	01:00	1,069	0.62
Scenario 2	1-150,000	01:03	2,198	35.21
	300,000-500,000	01:18	3,424	41.87
	150,001-299,999	00:57	2,573	43.36

This shows in Scenario 2, as the cluster key is not well defined, the inserts to fill the missing gap (150,001 to 299,999) is observed to be high. Also, the fragmentation and Page Splits are quite high since the cluster key is not well defined. Whenever we need to define a clustered index on a key, please make sure the following:

a) Key should be well defined, satisfying the business requirements and as well as the providing a greater selectivity.

b) Key should not be defined on derived table and calculated column

c) Other columns in the table should be completely dependent on the cluster key

SQL Server Indexes – An Insider Look - Part 1

2011-06-02T03:43:00.000-07:00

Introduction

There was a time where many have not realized the importance of indexes in database world. Indexes were predominantly used to decrease the execution time of a query without understanding the side effects it would cause on DML statements. However, in this contemporary world where lots and lots of information are available in the net, many have realized that indexes are to be used carefully and used only when a need arises. This article even though provides information on the basics, we will also delve into the internals on how these indexes react under the cover.

People who travel to the world of SQL Server from other databases will obviously have to confine themselves with only two types of indexes viz Clustered Indexes and Non-Clustered indexes. The design and architecture of SQL Server allows the user to play with these indexes alone (even though SQL Server 2008 has provided a new feature called FILTERED INDEXES, which we will see in next article). We will work on the different scenarios, observe the results and provide a logical conclusion towards the end. Please note that this is not a universal conclusion and results may vary depending on the data size, underlying OS, hardware configuration, number of concurrent users etc.

I will be using SQL Server 2008 on Windows XP. As and when needed, I will also provide the information on the data size and other variable factors.

Definitions

Before we dive deep into this article, it would be better if we understand the terminologies and their definitions as it would be easy to understand the contents in this article.

Fragmentation	A process by which the spaces occur due to frequent updates and deletes in the page. More this fragmentation more the performance issues or maintenance will be.
Index Level	This value provides the number of levels from the bottom up of an index. Please note that Leaf Node will always have 0.
Clustered Index Seek	Index seek uses a clustered index to look up the rows to be returned directly
Index Depth	Number of levels we need to pass to get into the index.
Record Count	Number of records available in the page. In general this value at index level 0 will be the number of records in the table. But in case of any forwarding count, then there will be some differences
Extent	An extent is the basic unit of storage used to allocate space for tables and indexes within a given data file. It is made up of eight contiguous 64KB data pages
Page	A page is the unit of allocation within a specific extent. There are eight pages in every extent. Please note that a page is the last level you reach before hitting at the actual data row.
Page Splits	Whenever a page becomes full, half of the data is shifted to the newly created page and pointers are kept in the old page pointing to the new location of the data. This is called Page Split. In case of a clustered index the inserted row which does not have enough space alone is shifted to the newly created page.
Row Offset	For every row that we insert into the database, SQL Server will place the “Row Offset” located at the end of the page, to actually indicate where in that page the row data starts. Indirectly it tells us how many spaces we need to leave to read the data from the top.
Index Seek	Index seek uses a non-clustered index to look up the rows to be returned directly
Nested Loops	Takes the inputs from two data sets and performs the scanning of the outer set for each inner set. Very efficient operation if the data sets are small.
RID Lookup	The query optimizer performs a RID LookUp, a type of bookmark lookup that occurs on a heap table and uses a row identifier to find the rows to return.
Key Lookup	A Key Lookup is a bookmark lookup on a clustered table and which essentially means that the optimizer cannot retrieve the rows in a single operation, and has to use a clustered key to return the corresponding rows from a clustered index (or from the table itself).
AVERAGE_FRAGMENTATION_ IN_PERCENT	The degree of fragmentation in the index tree which is based on pages or extents that are not in sync or out of order (i.e.) the pointer to the logical next page is not the same as the physical next page. Please note that we need to be looking for a low number here, though how low depends on the the scenario and requirements

Clustered Index

Clustered index, as the name suggests, the indexes are clustered in one place with the data. The major advantage (in some cases disadvantage) with clustered index is the arrangement of data in one particular order. Since the data is arranged in one order, we can have only one clustered index in a table. By default, as soon as a primary key for a table is created, internally SQL Server creates a clustered index. However, when we create a unique key, we can specify either clustered or non-clustered index. Creating a clustered index is a time consuming task and in case if this is not defined properly then it may lead to lot of performance issues. In this section, we will see where this can be a gain and where this can lead to performance issues.

Scenario 1: Well defined Cluster Key

Let us create a table called “Accounts” having information related to a company accounts as follows:

CREATE TABLE ACCOUNTS (ACCOUNTNUMBER NUMBER(6), ACCOUNTNAME VARCHAR(50), ACCOUNTDESCRIPTION VARCHAR(50), ACCOUNT_TYPE VARCHAR(1))

Let us now create a clustered index on this table on the column ACCOUNTNUMBER.

CREATE CLUSTERED INDEX ACCNTCLUSTIDX ON ACCOUNTS(ACCOUNTNUMBER);

In order to test various scenarios, let me load 50,000 records into this table using the following script:

BEGIN

DECLARE @COUNTER AS INT

SET @COUNTER=1

WHILE (@COUNTER <= 50000)

END

We have four different types through which we classify the account as Asset (A), Liability (L), Expense (E) and Income (I).

Now to insert these many records using SQL Server Management Studio (SSMS), it took around 23 seconds.

The following query is a simple select statement filtering the data on accountnumber.

SELECT * FROM ACCOUNTS WHERE ACCOUNTNUMBER < 1000

The above query got executed in no time and returned the 999 rows. The actual execution plan showed that the query took the CLUSTERED INDEX SEEK plan, which actually used the index and other parameters (or some authors term as attributes) are also well within the bounds. Please also note that since the key what we have chosen to index is well organized, whatever be the filter on the clustered key it would use the index.

Since this clustered key is well organized, even the page splits and fragmentation should be well within the limits. We can look at this in three different angles. First let us truncate the existing records in this table and load the table with three sets of data. First data will be from 1 to 150,000. Second will be from 300,000 and 500,000. And final set will be between 150,000 and 300,000. At the initial stage before starting this experiment the page splits per second is 18,562 as calculated through the following query:

SELECT * FROM SYS.SYSPERFINFO WHERE COUNTER_NAME = 'PAGE SPLITS/SEC' AND OBJECT_NAME LIKE '%ACCESS METHODS%'

Now the fragmentation for the index on this table is provided below:

Index Organized Tables (IOT) - Part 3

2011-04-14T05:23:00.000-07:00

Now, we will try to retrieve more records from the table and analyze the results.

SQL> SELECT * FROM IOT_TABLE WHERE IOTNUMBER > 2000;

98000 ROWS SELECTED.

EXECUTION PLAN

----------------------------------------------------------

PLAN HASH VALUE: 451903227

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 37 | 0 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| SYS_IOT_TOP_232322 | 1 | 37 | 0 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):

---------------------------------------------------

1 - ACCESS("IOTNUMBER">2000)

STATISTICS

----------------------------------------------------------

1 RECURSIVE CALLS

0 DB BLOCK GETS

6885 CONSISTENT GETS

0 PHYSICAL READS

0 REDO SIZE

3098440 BYTES SENT VIA SQL*NET TO CLIENT

72239 BYTES RECEIVED VIA SQL*NET FROM CLIENT

6535 SQL*NET ROUNDTRIPS TO/FROM CLIENT

0 SORTS (MEMORY)

0 SORTS (DISK)

98000 ROWS PROCESSED

SQL> SELECT * FROM NON_IOT_TABLE WHERE NON_IOTNUMBER > 2000;

98000 ROWS SELECTED.

EXECUTION PLAN

----------------------------------------------------------

PLAN HASH VALUE: 1269025442

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 37 | 0 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| NON_IOT_TABLE | 1 | 37 | 0 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | SYS_C00104050 | 1 | | 0 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):

---------------------------------------------------

2 - ACCESS("NON_IOTNUMBER">2000)

STATISTICS

----------------------------------------------------------

1 RECURSIVE CALLS

0 DB BLOCK GETS

13643 CONSISTENT GETS

0 PHYSICAL READS

0 REDO SIZE

3098452 BYTES SENT VIA SQL*NET TO CLIENT

72239 BYTES RECEIVED VIA SQL*NET FROM CLIENT

6535 SQL*NET ROUNDTRIPS TO/FROM CLIENT

0 SORTS (MEMORY)

0 SORTS (DISK)

98000 ROWS PROCESSED

As the number of records increased, the difference in accessing the data using IOT and regular tables are also increased. Wherever IOT is beneficial, regular tables are proved to be non-beneficial.

Effect of DMLs on IOT

I was actually very curious to check on this because everything seems to be positive for IOT and was wondering why IOT was not used in place of regular tables. In order to test the effect of DMLs on IOT, I took two cases viz. Bulk Insert and Serial Insert. By Serial Insert I mean inserting records one by one in a loop. I have used the same table, IOT_TABLE and issued the following insert statement.

SQL> INSERT INTO IOT_TABLE SELECT (IOTNUMBER+100000), IOTNAME, IOTDETAILS FROM IOT_TABLE;

100000 rows created.

Execution Plan

----------------------------------------------------------

Plan hash value: 3323075685

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 0 | INSERT STATEMENT | |100K| 2343K| 90 (2)| 00:00:02 |

| 1 | INDEX FAST FULL SCAN| SYS_IOT_TOP_232322 | 100K| 2343K| 90 (2)| 00:00:02 |

-------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1055 recursive calls

536919 db block gets

17450 consistent gets

445 physical reads

81962588 redo size

549 bytes sent via SQL*Net to client

612 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

9 sorts (memory)

0 sorts (disk)

100000 rows processed

What a pleasant surprise!! it takes 17,450 consistent gets and 445 physical reads to insert 100,000 records. But how much cost it would have for a regular table? Below is the next set of statement

SQL> INSERT INTO NON_IOT_TABLE SELECT (NON_IOTNUMBER+100000), NON_IOTNAME, NON_IOTDETAILS FROM NON_IOT_TABLE;

100000 rows created.

Execution Plan

----------------------------------------------------------

Plan hash value: 277682176

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 0 | INSERT STATEMENT | | 100K| 2343K| 111 (1)| 00:00:02 |

| 1 | TABLE ACCESS FULL| NON_IOT_TABLE | 100K| 2343K| 111 (1)| 00:00:02 |

-----------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

238 recursive calls

12235 db block gets

2963 consistent gets

42 physical reads

12218160 redo size

558 bytes sent via SQL*Net to client

632 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

100000 rows processed

That’s a huge difference. Almost 6x difference between the insert on regular table and IOT. Is this the same for update and delete? When I checked on update and delete, I found another set of interesting facts.

Description	No of Records	Consistent Gets	Physical Reads
Bulk Update IOT	90,978	27,634	110
Bulk Update Regular	90,978	2282	6
Bulk Delete IOT	90,978	977	7
Bulk Delete Regular	90,978	877	7

Instead of bulk insert, I tried single insert (record by record in a loop with a final commit outside the loop) in both IOT and regular tables and obtained the following results:

Description	No of Records	CPU	Elapsed Time	Physical Reads	IOs
Insert – Regular Tables	100,000	9.79	10.52	9	312,262
Insert - IOT	100,000	8.46	10.62	7	211,301

Benefits of IOT

The structure of an index-organized table provides the following benefits:

Ø Fast random access on the primary key because an index-only scan is sufficient.

Ø There is no separate table storage area, hence changes to the table data result only in updating the index structure.

Ø Fast range access on the primary key because the rows are clustered in primary key order.

Ø Lower storage requirements because duplication of primary keys is avoided.

Conclusion

From the above article, the following can be observed:

Ø IOT is not recommended to be used for bulk processing

Ø In case of OLTP transactions, IOT can be used

Ø Query retrieval is faster compared to the regular tables

Ø Using COMPRESS option, (even otherwise as well), storage can be saved phenomenally

Please do understand that these are not thumb rules to work on. Please do benchmark the usage of IOT before using the same.

Further Reading	Please don’t forget to visit this site to read my next articles on Ø Joins and Access Methods Ø Oracle Indexes
References	Ø Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions By Thomas Kyte

Index Organized Tables (IOT) - Part 2

2011-04-14T05:21:00.000-07:00

Now, comes an important decision on from which column or value of the column should be moved on to the overflow segment. This is optionally specified by INCLUDING clause. All the columns specified after the name in this clause will be moved to OVERFLOW segment. In this example, AGE_PHOBIA and AGE_IDEAS will be stored in a separate overflow segment.

Another interesting factor when we include OVERFLOW clause is as follows:

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','AGEING_DEFECTS') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','AGEING_DEFECTS')

--------------------------------------------------------------------------------

CREATE TABLE "INXQA"."AGEING_DEFECTS"

("NORMAL_HUMAN_AGE" VARCHAR2(10),

"AGE_ISSUES" VARCHAR2(30),

"AGE_PHOBIA" VARCHAR2(30),

"AGE_IDEAS" VARCHAR2(10),

PRIMARY KEY ("NORMAL_HUMAN_AGE") ENABLE

) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING

STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "PORT_DATA"

PCTTHRESHOLD 20 INCLUDING "AGE_ISSUES" OVERFLOW

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PORT_DATA"

Please note that the above options include PCTUSED with 40% as its value. This is because OVERFLOW segment is a separate segment, which has all the features and options of regular segments and hence we have PCTUSED.

NOCOMPRESS and COMPRESS options

When we uncompress the data, the primary key (or key columns) are repeated in the block along with the non-key columns. When we compress the index, based on the number of columns we specify, the indexes will be compressed (i.e.) the key columns will not be repeated. Please note that for the compress option to work, you need to have composite primary key. Otherwise, you will get the following error:

SQL> ALTER TABLE IOT_TABLE MOVE COMPRESS;

ALTER TABLE IOT_TABLE MOVE COMPRESS

ERROR AT LINE 1:

ORA-25193: CANNOT USE COMPRESS OPTION FOR A SINGLE COLUMN KEY

In order to show the COMPRESS option, let us create another table as follows:

CREATE TABLE IOT_TABLES

(IOT1 VARCHAR2(10),

IOT2 VARCHAR2(10),

IOT3 VARCHAR2(10),

IOT4 VARCHAR2(10),

IOT5 VARCHAR2(10),

PRIMARY KEY(IOT1,IOT2,IOT3))

ORGANIZATION INDEX;

Assume that we have a primary key on three columns with the following distribution of data in NOCOMPRESS option:

9,4,1,5,6

9,4,2,5,7

9,4,3,5,8

9,4,0,5,9

9,4,9,6,0

9,5,4,1,2

9,5,5,1,3

9,5,6,1,4

9,5,7,1,5

9,5,8,1,6

If we compress as follows:

ALTER TABLE IOT_TABLES MOVE COMPRESS 2;

9,4 and 9,5 are stored only once along with the data.

The main advantage of using COMPRESS technique is to save usage space.

Comparing the performance between Regular tables and IOT

Now, we will compare the results obtained from regular tables and IOT. Let us create two tables viz NON_IOT_TABLE and IOT_TABLE as follows:

CREATE TABLE NON_IOT_TABLE

(NON_IOTNUMBER NUMBER,

NON_IOTNAME VARCHAR2(20),

NON_IOTDETAILS VARCHAR2(20),

PRIMARY KEY(NON_IOTNUMBER));

Now, let us create a small procedure to insert equal number of rows into these tables and see the results.

CREATE OR REPLACE PROCEDURE IOTINSERT AS

INTERNALCOUNTER NUMBER;

BEGIN

INTERNALCOUNTER := 1;

WHILE INTERNALCOUNTER <= 100000

LOOP

INSERT INTO IOT_TABLE VALUES (INTERNALCOUNTER, 'AA' || INTERNALCOUNTER, 'AABBCC' || INTERNALCOUNTER);

INSERT INTO NON_IOT_TABLE VALUES (INTERNALCOUNTER, 'AA' || INTERNALCOUNTER, 'AABBCC' || INTERNALCOUNTER);

INTERNALCOUNTER := INTERNALCOUNTER + 1;

END LOOP;

COMMIT;

END;

SQL> SELECT * FROM IOT_TABLE WHERE IOTNUMBER < 2000;

1999 ROWS SELECTED.

EXECUTION PLAN

----------------------------------------------------------

PLAN HASH VALUE: 451903227

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 37 | 0 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| SYS_IOT_TOP_232322 | 1 | 37 | 0 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):

---------------------------------------------------

1 - ACCESS("IOTNUMBER"<2000)

STATISTICS

----------------------------------------------------------

1 RECURSIVE CALLS

0 DB BLOCK GETS

143 CONSISTENT GETS

0 PHYSICAL READS

124 REDO SIZE

55870 BYTES SENT VIA SQL*NET TO CLIENT

1839 BYTES RECEIVED VIA SQL*NET FROM CLIENT

135 SQL*NET ROUNDTRIPS TO/FROM CLIENT

0 SORTS (MEMORY)

0 SORTS (DISK)

1999 ROWS PROCESSED

SQL> SELECT * FROM NON_IOT_TABLE WHERE NON_IOTNUMBER < 2000;

1999 ROWS SELECTED.

EXECUTION PLAN

----------------------------------------------------------

PLAN HASH VALUE: 1269025442

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 37 | 0 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| NON_IOT_TABLE | 1 | 37 | 0 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | SYS_C00104050 | 1 | | 0 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):

---------------------------------------------------

2 - ACCESS("NON_IOTNUMBER"<2000)

STATISTICS

----------------------------------------------------------

1 RECURSIVE CALLS

0 DB BLOCK GETS

283 CONSISTENT GETS

0 PHYSICAL READS

124 REDO SIZE

55882 BYTES SENT VIA SQL*NET TO CLIENT

1839 BYTES RECEIVED VIA SQL*NET FROM CLIENT

135 SQL*NET ROUNDTRIPS TO/FROM CLIENT

0 SORTS (MEMORY)

0 SORTS (DISK)

1999 ROWS PROCESSED

If we see the execution plan for both the select statements, we observe that "Consistent Gets" are more in NON_IOT_TABLE. This is very much true because the index will be searched first and then the data will be retrieved from the respective table. However, in case if IOT_TABLE table, the index and data resides in the same place and hence the "Consistent Gets" remains less. The same reason is provided for the 3 steps execution plan v/s 2 step execution plan, provided above.

Index Organized Tables (IOT) - Part 1

2011-04-14T05:12:00.000-07:00

Index Organized Tables

Introduction

Index Organized Tables (IOT) are tables where data is stored in form of index structure. In IOT, the data is co-located and sorted by primary key. Each leaf block in the index structure contains both Key and Non-Key Columns. Overhead on storing the data along with index is absolutely nothing. This article demonstrates the concept behind IOT, usage of IOT and compares the performance with the regular tables.

Audience	Intermediate Oracle Developers, who wanted to have a clear understanding on IOT
Pre-Requisite Understanding	Audience of this article should possess knowledge on Explain Plan and Usage of Package
Some history/version info	Oracle 9i/10g

IOT Creation

When we create an IOT, we specify the clause ORGANIZATION INDEX along with Create table statement and it’s mandatory to provide a primary key (either single or composite). Syntax for this is given below:

CREATE TABLE IOT_TABLE

(IOTNUMBER NUMBER,

IOTNAME VARCHAR2(20),

IOTDETAILS VARCHAR2(20),

PRIMARY KEY(IOTNUMBER))

ORGANIZATION INDEX;

Now, let us check the options available with IOT. For this, we use DBMS_METADATA package provided by Oracle as follows:

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','IOT_TABLE') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','IOT_TABLE')

--------------------------------------------------------------------------------

CREATE TABLE "HARI"."IOT_TABLE"

( "IOTNUMBER" NUMBER, "IOTNAME" VARCHAR2(20),

"IOTDETAILS" VARCHAR2(2), PRIMARY KEY ("IOTNUMBER") ENABLE

) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "USERS"

PCTTHRESHOLD 50

Note: To view the complete resultset of this command you will need to set the LONG SIZE for your session to 15000 as follows:

SQL> set long 15000

Couple of interesting parameters to note in options available for IOT

a) There is no PCTUSED.

b) NOCOMPRESS is available in IOT, which by default will not compress the index created

PCTUSED is not available in case of HEAP table, as the data is scattered throughout and we specify PCTUSED to identify the used percentage in each block. However in the case of IOT, data has to be co-located and sorted and hence data will go to the block where it has to go. The behavior of the remaining parameters is the same as it is for the regular tables.

We also have the option of NOCOMPRESS, where in the indexes remain uncompressed (i.e.) in IOT, the index and data are stored together and hence when we create a primary key, the index will be repeated along with the data in every block. NOCOMPRESS is default option.

We can also use OVERFLOW and INCLUDING clause along with the create table statement for IOT. The detailed explanation on these parameters is provided in the following section of this article.

Overflow, PCTThreshold and Including

In order to explain the concepts behind these terms, let us create a table and using the table I will try to explain the concept.

CREATE TABLE AGEING_DEFECTS

(NORMAL_HUMAN_AGE VARCHAR2(10),

AGE_ISSUES VARCHAR2(30),

AGE_PHOBIA VARCHAR2(30),

AGE_IDEAS VARCHAR2(10),

PRIMARY KEY(NORMAL_HUMAN_AGE))

ORGANIZATION INDEX PCTTHRESHOLD 20 OVERFLOW INCLUDING AGE_ISSUES;

According to the oracle manual, these three concepts are defined as follows. These three terms are intertwined and one cannot be explained without reference to another.

Ø An OVERFLOW clause, which preserves dense clustering of the B-tree index by storing the row column values exceeding a specified threshold in a separate overflow data segment.

Ø A PCTTHRESHOLD value, which defines the percentage of space reserved in the index block for an index-organized table. Any portion of the row that exceeds the specified threshold is stored in the overflow segment. In other words, the row is broken at a column boundary into two pieces, a head piece and tail piece. The head piece fits in the specified threshold and is stored along with the key in the index leaf block. The tail piece is stored in the overflow area as one or more row pieces. Thus, the index entry contains the key value, the non-key column values that fit the specified threshold, and a pointer to the rest of the row.

Ø An INCLUDING clause, which can be used to specify non-key columns that are to be stored in the overflow data segment.

The overflow data segment can be in the same tablespace or it can be in different tablespace. To explain this, let me illustrate with an example.

Assume that the data block size is 2KB. Roughly, each row in the table will hold 80 characters (blindly adding the size of each column in the table). Let us assume that this will occupy approximately 100 bytes. Now, each block will hold 2048 bytes, which means each block can roughly hold 20 rows. Now, after inserting 7000 records into this table, we decide to increase the column length for AGE_PHOBIA and AGE_IDEAS (as provided below). Suppose we insert more records, the complete record may not get accommodated in the same block. Oracle allows the data to get OVERFLOWED to the OVERFLOW SEGMENT. How does it know when to overflow? This is done by PCTTHRESHOLD. If any of the nonkey columns of rows exceed 20% of the block size, they are placed in the overflow data segment in the same tablespace. The key columns should fit the specified threshold. Here Key column is NORMAL_HUMAN_AGE and Non Key Columns are AGE_ISSUES, AGE_PHOBIA and AGE_IDEAS.

SQL> ALTER TABLE AGEING_DEFECTS MODIFY AGE_PHOBIA VARCHAR2(40);

TABLE ALTERED.

SQL> ALTER TABLE AGEING_DEFECTS MODIFY AGE_IDEAS VARCHAR2(20);

TABLE ALTERED.

Difference Between In and Exists - Part 2

2010-12-07T03:26:00.000-08:00

SQL> select * from smalltable1 where data1 in (select data5 from largetable);

100 rows selected.

SQL> select * from smalltable1 st where exists (select 1 from largetable lt where st.data1=lt.data5);

100 rows selected.

SQL> select * from largetable lt where data5 in (select data1 from smalltable1 st);

100 rows selected.

SQL> select * from largetable lt where exists (select 1 from smalltable1 st where st.data1=lt.data5);

100 rows selected.

Inference

From the preceding example, it can be inferred that usage of IN and EXISTS provided us with the same results for both Small table to large table and large table to small table.

Scenario 3: Large table to Large table

Now, the last scenario is to compare large table with another large table and observe the results. Let us create another table largetable2 and compare the results.

SQL> select * from largetable where data5 in (select data5 from largetable2);

100000 rows selected.

SQL> select * from largetable lt where exists (select 1 from largetable2 lt2 where lt.data5=lt2.data5);

100000 rows selected.

To Conclude:

In all the three scenarios, except for the negligible difference in statistics, it can be observed that both IN and EXISTS behave equally.

Difference Between In and Exists

2010-12-07T01:13:00.000-08:00

Database Version: Oracle 10g
Operating System: Windows XP

I was actually under the impression that there is actually a good difference between IN and EXISTS. But to my surprise, I found something interesting and further digged into the details and found some good facts. I had a wrong conception, which got cleared after the experiment. I actually wanted to share the same with you.

Till Oracle 8i, IN and Exists were processed in different way by Oracle. However from Oracle 9i, predominantly in Oracle 10g they are much processed on the same way, thanks to CBO intelligent optimizer.

For the illustration purpose I have created four tables, two small and two big as follows. Now I will compare small to small, small to big, big to big using both IN and EXISTS and see what are the results.

Scenario 1: Small table to Small table

Table

create table smalltable1 (data1 number(3), data2 varchar2(10), data3 varchar2(10), data4 varchar2(10));

Procedure

create or replace procedure smalltableproc is

counter number(3);

begin

counter := 1;

while (counter <= 100)

loop

insert into smalltable1 values (counter,'aa' || counter,'bb' || counter, 'cc' || counter);

counter := counter + 1;

end loop;

commit;

end;

Index

create index smalltable1data1idx on smalltable1(data1);

Procedure Execution

exec smalltableproc;

Now, I will create the second small table from the first small table as follows:

create table smalltable2 as select * from smalltable1;

create index smalltabled2ata1idx on smalltable2(data1);

Before comparing the results, in general I used to update the statistics for both indexes and tables. Even though, creating the indexes will actually build the statistics, I always make it a point to do this.

SQL> exec DBMS_STATS.gather_table_stats('INXQA','SMALLTABLE1');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.gather_table_stats('INXQA','SMALLTABLE2');

PL/SQL procedure successfully completed.

Now let us see the results between the usage of IN and EXISTS

SQL> select * from smalltable1 where data1 in (select data1 from smalltable2);

100 rows selected.

SQL> select * from smalltable1 ss where exists (select 1 from smalltable2 st where ss.data1=st.data1);

100 rows selected.

Inference

It can be inferred from the above example that both IN and EXISTS provided us with the same results for the smaller tables.

Now, let us take the next comparison of Small table to Big Table and Vice versa.

Scenario 2: Small table to Large table and Large Table to Small Table

Table

create table largetable (data5 number(6), data6 varchar2(10), data7 varchar2(10), data8 varchar2(10));

Procedure

create or replace procedure largetableproc is

counter number(6);

begin

counter := 1;

while (counter <= 100000)

loop

insert into largetable values (counter,'aa' || counter,'bb' || counter, 'cc' || counter);

counter := counter + 1;

end loop;

commit;

end;

Index

create index largetabledata5idx on largetable(data5);

Procedure Execution

SQL> exec largetableproc

PL/SQL procedure successfully completed.

Statistics

SQL> exec DBMS_STATS.gather_table_stats('HARI','LARGETABLE');

PL/SQL procedure successfully completed.

Bitmap Indexes - Part 2

2010-11-28T04:23:00.000-08:00

Case 1: Using BITMAP for BULK Load.

To illustrate this, I have created a table called bitmapdemotable as provided below:

create table bitmapdemotable (runnumber number(6), runname varchar2(15), runtype number(2));

I have also created a procedure to populate this table. Please note that I am using a "While Loop" here to simulate row-by-row insert operation.

create or replace procedure bitmapproc(noofrecords in number) is

counter number;

begin

counter := 1;

while (counter <= noofrecords)

loop

insert into bitmapdemotable values (counter, 'a' || counter,mod(counter,2));

counter := counter + 1;

end loop;

commit;

end;

Next, I have created an index on this table for the column "runtype" which actually holds either 0 or 1.

create bitmap index bitmapidx on bitmapdemotable(runtype);

In order to study the behaviour, we need to use TKPROF. For this we need to change the configuration settings of TIMED_STATISTICS and SQL_TRACE

alter session set timed_statistics=true;

alter session set sql_trace=true;

Now, I am executing the procedure by passing the value as 50000.

exec bitmapproc(50000)

When we see the trace file (after extracting the contents of the trace file into a local text file using TKPROF), it looks like the following figure:

The initial is for the procedure as a whole (does not include the data for the insert statement as it is separately captured) and the second is for the insert statement. Please note that the insert statement took more CPU, elapsed time and logical I/Os (both query and current). Now, please also note that ELAPSED is greater than CPU, which may be due to some wait events.

Now, instead of row-by-row insert we have captured the statistics for the bulk load and the result is shown in the following figure:

As expected, the SELECT statement, led to FULL TABLE SCAN. But, when we look at the statistics, we can observe vast difference between the row-by-row and BULK load. The reason being, when we do a row-by-row insert, the B-tree is used to locate the leaf nodes that contain bitmap segments for a given value of the key. Start ROWID and the bitmap segments are used to locate the rows that contain the key value. When changes are made to the key column in the table, bitmaps must be modified. This results in the locking of the relevant bitmap segments. Because locks are acquired on the whole bitmap segment, a row that is covered by the bitmap cannot be updated by other transactions until the first transaction ends. Oracle server should repeat this for every row that gets either inserted or updated. Hence row by row manipulation on the rows consists of bitmap index is costly.

Bitmap Indexes - Part 4

2010-11-28T03:54:00.000-08:00

SQL> select * from bitmapdemotable where runtype=1;

125000 rows selected.

SQL> select * from btreedemotable where runtype=1;

125000 rows selected.

This states that as far as the query is concerned, there is no difference between the query results on the tables that actually uses btree index and bitmap index. We have considered the scenario where we have two distinct values for run type. Now, let us create the similar type of indexes in another column in the same table and check. Also, we will drop the existing indexes on the runtype column.

drop index btreeidx;

drop index bitmapidx;

To create the indexes on runname column:

create index runnamebtree on btreedemotable(runname);

create bitmap index runnamebitmap on bitmapdemotable(runname);

exec DBMS_STATS.gather_index_stats('HARI', 'RUNNAMEBTREE');

exec DBMS_STATS.gather_index_stats('HARI', 'RUNNAMEBITMAP');

SQL> select * from bitmapdemotable where runname='a1000';

SQL> select * from btreedemotable where runname='a1000';

I actually tried to insert into the tables (with index on runname) and found the following:

SQL> insert into btreedemotable select * from btreedemotable;

250000 rows created.

SQL> insert into bitmapdemotable select * from bitmapdemotable;

250000 rows created.

Please notice the huge difference between the tables having different indexes on the similar column.

To conclude:

a) There is no much difference between b*tree and bitmap as far as the query execution is concerned. Both are exhibiting similar statistics

b) The difference comes when we try to perform DML operations on the indexes.

Bitmap Indexes - Part 3

2010-11-28T03:52:00.000-08:00

Case 2: Comparison between B*Tree and Bitmap

Let us illustrate this point by identifying when a B*Tree can be used and when we should use Bitmap. Let us consider the same example of what we have done it in case 1 and see if there is any difference when we use b*tree instead of bitmap

Let us create a table for creating a b*tree index, as follows:

create table btreedemotable as select * from bitmapdemotable where 1=0;

create index btreeidx on btreedemotable(runtype);

Let us create the same procedure, but to populate btreedemotable.

create or replace procedure btreeproc(noofrecords in number) is

counter number;

begin

counter := 1;

while (counter <= noofrecords)

loop

insert into btreedemotable values (counter, 'a' || counter,mod(counter,2));

counter := counter + 1;

end loop;

commit;

end;

alter session set timed_statistics=true;

alter session set sql_trace=true;

Please note that the row-by-row insert for a b*tree index based table works like a champ as compared to bitmap index based table. Now, we will try to select it from the table and see if there are any differences:

SQL> select * from btreedemotable where runtype=1;

25000 rows selected.

SQL> select * from bitmapdemotable where runtype=1;

25000 rows selected.

Except for the minor changes in the statistics and cost, there seems to be no big difference between the two tables having different index types. Now, let us check for the query that returns 125,000 records.

Bitmap Indexes

2010-11-28T03:37:00.000-08:00

Database Version: Oracle 10g
Operating System: Windows XP

I was working on tuning a procedure in my current company, when I found something interesting with my colleague. He has created a table with a bitmap index for one of the columns. The insert into the table was found to be very slow and when I checked in the process, he is actually doing a single row operation (of around 50K every day) which is actually taking couple of minutes to insert. There was actually an argument with him and he claims that the slow in insert operation is not due to bitmap index and it is because of something else. Further he claimed that BITMAP index is similar in B*Tree index. Not only him, many could not understand when we should create BITMAP INDEX and what actually it is.

In this article, I will explain what is BITMAP Index, their operational difference (or comparison) and when do we need to create BITMAP indexes. As always said, please do provide your comments.

What is BITMAP index?

Oracle created BITMAP index to cater the need for data warehousing and adhoc query mechanism. In general, usage of bitmap index in OLTP is not a highly advisable solution. In a bitmap index, a bitmap for each key value replaces a list of rowids. Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value. Oracle has created a proprietary mapping function that converts the bit position to an actual rowid, so that the bitmap index provides the same functionality as a regular index. Bitmap indexes store the bitmaps in a compressed way.

Bitmap indexes are most appropriate on low distinct cardinality data (i.e., data with relatively few discrete values when compared to the cardinality of the entire set. It is difficult to define what low distinct cardinality is?. This is data where the number of distinct items in the set of rows divided by the number of rows is a small number (near zero). For example, in a table of 10000 rows, 1 or 2 distinct valued column would be appropriate for a bitmap index. However, in a table of 1000000 rows, 100 or 200 distinct valued column would be appropriate for a bitmap index. I have seen people telling me that columns with more than 5 or 10 distinct columns cannot participate in a bitmap index, which is actually wrong.

In general, when the data is packed for bitmap index, it is not packed like b*tree index. In case of b*tree index, the leaf node will have a list of rowids. In case of bitmap index, the leaf node contains bitmap, with the value set if the key value is available and not set if not. Each leaf node contains the entry header, key, start rowid, end rowid and bitmap segment. A bitmap segment consisting of a string of bits. The bit is set when the corresponding row contains the key value and is unset when the row does not contain the key value. Entry header, which contains the information on the number of columns and lock information. The Oracle server uses a patented compression technique to store bitmap segments and also bitmap Index uses restricted rowid format

Difference between Bitmap and B*Tree Indexes

Bitmap indexes actually store pointers to many rows with a single index key entry, as compared to a B*Tree. In B*Tree index, we will be able to pair the index keys and the corresponding rows in a table. In a bitmap index, there will be a very small number of index entries, each of which points to many rows. In a conventional B*Tree, one index entry points to a single row.

When do we need BITMAP index?

We need BITMAP index for data warehousing application, Adhoc Query Processing, and when the data load is quiet large(and we have bulk processing). We should never use a BITMAP index for OLTP processing. We shall consider couple of scenarios where in we check on the usage of BITMAP index and study the response. On the first case, I will show what happens if we use BITMAP index for row-by-row insert and on the second case, we shall see the operational difference between b*tree index and Bitmap Index.

Part 2 Part 3 Part 4

Facts on Indexes

2010-10-08T04:59:00.000-07:00

Author:Hariharan T Sairam. My Best Friend :)

In a sequence of my learning and experiments, I found some interesting facts on Indexes, which I thought I can provide as an article.

Some of the most celebrated myths are:

a) Difference in creation of Indexes

b) Full table scan is evil

c) Rebuilding Index leads to faster retrieval of records

Myth 1: Difference in creation of Indexes

In this section, we shall consider two cases viz Indexes on individual columns and composite index.

Case 1: Indexes on individual columns

Let us take an example to illustrate the creation of indexes.

We first create a table called GENERALWORKS, with the following columns:

create table generalworks

(

GENNO NUMBER(7),

GENNAME VARCHAR2(40),

GENCATG VARCHAR2(2),

GENDESC VARCHAR2(3),

GENDETS VARCHAR2(6)

)

Also, for this table, let us create three indexes, one on each column as follows:

create index GENERALWORKSGENCATGIDX on generalworks(gencatg);

create index GENERALWORKSGENNMEIDX on generalworks(genname);

create index GENERALWORKSGENIDX on generalworks(genno);

Let me also insert 50,000 records into this table.

SQL> select count(*) from generalworks;

COUNT(*)

----------

50000

To be on the safer side, let me also gather the statistics for the table and indexes as follows:

SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'GENERALWORKS');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.gather_index_stats('HARI', 'GENERALWORKSGENCATGIDX');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.gather_index_stats('HARI', 'GENERALWORKSGENNMEIDX');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.gather_index_stats('HARI', 'GENERALWORKSGENIDX');

PL/SQL procedure successfully completed.

Scenario 1:

We will now retrieve the records based on the columns GENNAME and GENCATG

SQL> select * from generalworks where genname='aaaabbbb' and gencatg='ab';

3847 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4242548220

-----------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 298 | 8046 | 99 (2)| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID | GENERALWORKS | 298 | 8046 | 99 (2)| 00:00:02 |

| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |

| 3 | BITMAP AND | | | | | |

| 4 | BITMAP CONVERSION FROM ROWIDS | | | | | |

|* 5 | INDEX RANGE SCAN | GENERALWORKSGENCATGIDX | 3860 | | 12 (0)| 00:00:01 |

| 6 | BITMAP CONVERSION FROM ROWIDS | | | | | |

|* 7 | INDEX RANGE SCAN | GENERALWORKSGENNMEIDX | 3860 | | 16 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - access("GENCATG"='ab')

7 - access("GENNAME"='aaaabbbb')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

571 consistent gets

0 physical reads

0 redo size

160080 bytes sent via SQL*Net to client

3197 bytes received via SQL*Net from client

258 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3847 rows processed

From the above execution plan, it is quiet evident that the indexes are getting used and the cost is also less to retrieve 3,847 records. But, wait a minute, something different is seen in the plan. BITMAP? We have created only B*Tree indexes and we are seeing BITMAP in the plan? Reason being, in order to retrieve the data back faster, Oracle decided to implement the mechanism of creating a BITMAP INDEX on the fly. This is done only for certain types of query which will get benefited by this. Otherwise, No.

Also note that the execution plan shows 298 rows for the Step 1 and 0, but actual number processed was 3847. The reason being, execution plan is always an estimate and need not always be similar to the actual plan. Also, there are queries where the actual plan will never coincide with the estimated plan.

Scenario 2:

SQL> select * from generalworks where genname='aaaabbbb';

3847 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1336713796

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3860 | 101K|245 (1)| 00:00:03 |

| 1 | TABLE ACCESS BY INDEX ROWID| GENERALWORKS | 3860 | 101K|245 (1)| 00:00:03 |

|* 2 | INDEX RANGE SCAN | GENERALWORKSGENNMEIDX | 3860 | | 16 (0) | 00:00:01 |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

-----------------------------------------------------------

2 - access("GENNAME"='aaaabbbb')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

785 consistent gets

0 physical reads

0 redo size

160080 bytes sent via SQL*Net to client

3197 bytes received via SQL*Net from client

258 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3847 rows processed

Scenario 3:

SQL> select * from generalworks where genno > 10000 and genname='aaaabbbb';

3077 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1336713796

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3090 | 83430 |245 (1)| 00:00:03 |

|* 1 | TABLE ACCESS BY INDEX ROWID | GENERALWORKS | 3090 | 83430 |245 (1)| 00:00:03 |

|* 2 | INDEX RANGE SCAN | GENERALWORKSGENNMEIDX | 3860 | | 16 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("GENNO">10000)

2 - access("GENNAME"='aaaabbbb')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

685 consistent gets

0 physical reads

0 redo size

90912 bytes sent via SQL*Net to client

2636 bytes received via SQL*Net from client

207 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3077 rows processed

Scenario 4:

SQL> select * from generalworks where genno > 10000 and genname='aaaabbbb' and gencatg='ab

3077 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4242548220

-----------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 239 | 6453 | 99 (2)| 00:00:02 |

|* 1 | TABLE ACCESS BY INDEX ROWID | GENERALWORKS | 239 | 6453 | 99 (2)| 00:00:02 |

| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |

| 3 | BITMAP AND | | | | | |

| 4 | BITMAP CONVERSION FROM ROWIDS | | | | | |

|* 5 | INDEX RANGE SCAN | GENERALWORKSGENCATGIDX | 3860 | 12 (0)| 00:00:01 |

| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |

|* 7 | INDEX RANGE SCAN | GENERALWORKSGENNMEIDX | 3860 || 16 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("GENNO">10000)

5 - access("GENCATG"='ab')

7 - access("GENNAME"='aaaabbbb')

Statistics

----------------------------------------------------------

161 recursive calls

0 db block gets

555 consistent gets

0 physical reads

0 redo size

90912 bytes sent via SQL*Net to client

2636 bytes received via SQL*Net from client

207 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

3077 rows processed

Here again, the BITMAP conversion is used with the number of rows not tallying with the actual processed.

Scenario 5:

SQL> select * from generalworks where gencatg='ab';

3847 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 418290240

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3906 | 102K| 239 (1)| 00:00:03 |

| 1 | TABLE ACCESS BY INDEX ROWID| GENERALWORKS | 3906 | 102K| 239 (1)| 00:00:03 |

|* 2 | INDEX RANGE SCAN | GENERALWORKSGENCATGIDX | 3906 | | 8 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("GENCATG"='ab')

Statistics

----------------------------------------------------------

203 recursive calls

0 db block gets

773 consistent gets

12 physical reads

0 redo size

160080 bytes sent via SQL*Net to client

3197 bytes received via SQL*Net from client

258 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

3847 rows processed

Scenario 6:

SQL> select * from generalworks where genno > 40000;

10000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2559079067

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 9999 | 263K| 77 (2)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | GENERALWORKS | 9999 | 263K| 77 (2)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | GENERALWORKSGENIDX | 9999 | | 23 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("GENNO">40000)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

1406 consistent gets

24 physical reads

0 redo size

417145 bytes sent via SQL*Net to client

7707 bytes received via SQL*Net from client

668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10000 rows processed

Note:

If we replace the condition “genno > 40000” with “genno > 10000” we will incur FULL TABLE SCAN as the number of records to be selected is 40,000.

Case 2: Composite Indexes

Instead of creating individual indexes for the 3 columns, let us drop the indexes and create a composite index (as below) and study its execution plan.

create index genworkscompindx on generalworks(genno,genname,gencatg);

In this, first position is GENNO, 2nd position is GENNAME and 3rd is GENCATG. Reason for creating this composite index is to analyze and understand the difference in creating indexes in these two different forms. Now, we shall analyze all the scenarios that we have tested and study the implications

Scenario 1

SQL> select * from generalworks where genname='aaaabbbb' and gencatg='ab';

3847 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 857713684

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 298 | 8046 | 280 (2)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| GENERALWORKS | 298 | 8046 | 280 (2)| 00:00:04 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("GENNAME"='aaaabbbb' AND "GENCATG"='ab')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

1514 consistent gets

0 physical reads

0 redo size

112736 bytes sent via SQL*Net to client

3197 bytes received via SQL*Net from client

258 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3847 rows processed

Please note that the parameter “consistent gets” is high and this query is undergoing full table scan. Also, the select statement took two seconds more than the first statement. We now need to identify the reason for the table scan (even though table scan is not evil). On the surface level, we might say that the columns GENNAME and GENCATG are in positions 2 and 3 and hence the index is not been used properly. If we execute the following statement

SELECT INDEX_NAME, COLUMN_NAME, NUM_ROWS, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR FROM USER_INDEXES UI, USER_IND_COLUMNS UIC WHERE UI.INDEX_NAME=UIC.INDEX_NAME AND UI.TABLE_NAME=’GENERALWORKS’;

We observe that the CLUSTERING_FACTOR to be 264 and LEAF_BLOCKS to be 194, which actually shows that the indexes will be used as the CLUSTERING_FACTOR is almost close to LEAF_BLOCKS. However, the index will be used only as a whole (i.e.) GENNO, GENNAME and GENCATG (any combination of this). If we carefully study the rest of the scenarios, most will lead to table scan. Indexes will be used when we use all the columns that are indexed in the where clause.

Scenario 2:

SQL> select * from generalworks where genname='aaaabbbb';

3847 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 857713684

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3860 | 101K| 279 (2)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| GENERALWORKS | 3860 | 101K| 279 (2)| 00:00:04 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("GENNAME"='aaaabbbb')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

1514 consistent gets

0 physical reads

0 redo size

112736 bytes sent via SQL*Net to client

3197 bytes received via SQL*Net from client

258 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3847 rows processed

Please note that the value for consistent gets is high (almost doubled). Also we can observe that the query is undergoing table scan.

Scenario 3:

SQL> select * from generalworks where genno > 10000 and genname='aaaabbbb';

3077 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1452202321

----------------------------------------------------------

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3125 | 84375 | 176 (2)| 00:00:03 |

| 1 | TABLE ACCESS BY INDEX ROWID| GENERALWORKS | 3125 | 84375 | 176 (2)| 00:00:03 |

|* 2 | INDEX RANGE SCAN | GENWORKSCOMPINDX | 3125 | | 159 (2)| 00:00:02 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("GENNO">10000 AND "GENNAME"='aaaabbbb' AND "GENNO" IS NOT NULL)

filter("GENNAME"='aaaabbbb')

Statistics

----------------------------------------------------------

386 recursive calls

0 db block gets

806 consistent gets

376 physical reads

0 redo size

128826 bytes sent via SQL*Net to client

2636 bytes received via SQL*Net from client

207 SQL*Net roundtrips to/from client

8 sorts (memory)

0 sorts (disk)

3077 rows processed

There is something quite interesting happening in this query. Cost has come down from 245 to 176 for access by ROWID. However, the cost of the index range scan has increased drastically from 16 to 159. Here, the cost specified is for the whole index GENWORKSCOMPINDX, which comprises 3 columns and not a single column. Hence the cost is based on the whole index and not for an individual column. Now, if we drop this index and create an index for individual columns, the cost will be less as it will be based on a single column.

Scenario 4:

SQL> select * from generalworks where genno > 10000 and genname='aaaabbbb' and gencatg='ab';

3077 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1452202321

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 244 | 6588 | 161 (2)| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID| GENERALWORKS | 244 | 6588 | 161 (2)| 00:00:02 |

|* 2 | INDEX RANGE SCAN | GENWORKSCOMPINDX | 244 | | 159 (2)| 00:00:02 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("GENNO">10000 AND "GENNAME"='aaaabbbb' AND "GENCATG"='ab' AND "GEN

NO" IS NOT NULL) filter("GENNAME"='aaaabbbb' AND "GENCATG"='ab')

Statistics

----------------------------------------------------------

141 recursive calls

0 db block gets

769 consistent gets

0 physical reads

0 redo size

128826 bytes sent via SQL*Net to client

2636 bytes received via SQL*Net from client

207 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

3077 rows processed

Here again, we could see an increase in the cost for the range scan.

Scenario 5:

SQL> select * from generalworks where gencatg='ab';

3847 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 857713684

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3906 | 102K| 280 (2)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| GENERALWORKS | 3906 | 102K| 280 (2)| 00:00:04 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("GENCATG"='ab')

Statistics

----------------------------------------------------------

386 recursive calls

0 db block gets

1577 consistent gets

1257 physical reads

0 redo size

112736 bytes sent via SQL*Net to client

3197 bytes received via SQL*Net from client

258 SQL*Net roundtrips to/from client

8 sorts (memory)

0 sorts (disk)

3847 rows processed

Here again, there is a huge increase in “Consistent Gets” and “Physical Reads” and the query is undergoing a table scan. This is because we have used a column in the where clause which is in the third position of the index and does not have statistics of its own. Hence, when this query is executed, the statistics by optimizer for this query will be of no use and is not used to arrive at the proper execution plan.

Scenario 6:

SQL> select * from generalworks where genno > 40000;

10000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1452202321

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 9999 | 263K| 94 (2)| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID| GENERALWORKS | 9999 | 263K| 94 (2)| 00:00:02 |

|* 2 | INDEX RANGE SCAN | GENWORKSCOMPINDX | 9999 | | 40 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("GENNO">40000 AND "GENNO" IS NOT NULL)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

1421 consistent gets

41 physical reads

0 redo size

417145 bytes sent via SQL*Net to client

7707 bytes received via SQL*Net from client

668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10000 rows processed

Since the first column in the index is used in the where clause, the optimizer is able to arrive at the proper execution plan using the column. Hence index path is chosen to execute this query.

Scenario 7:

SQL> select * from generalworks where gencatg='ab' and genname='aaaabbbb' and genno > 10000;

3077 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1452202321

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 244 | 6588 | 161 (2)| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID| GENERALWORKS | 244 | 6588 | 161 (2)| 00:00:02 |

|* 2 | INDEX RANGE SCAN | GENWORKSCOMPINDX | 244 | | 159 (2)| 00:00:02 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("GENNO">10000 AND "GENNAME"='aaaabbbb' AND "GENCATG"='ab' AND "GENNO" IS NOT NULL)

filter("GENCATG"='ab' AND "GENNAME"='aaaabbbb')

Statistics

----------------------------------------------------------

183 recursive calls

0 db block gets

773 consistent gets

0 physical reads

0 redo size

128826 bytes sent via SQL*Net to client

2636 bytes received via SQL*Net from client

207 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

3077 rows processed

The above scenario is provided to illustrate an important point. If an index is create on three columns viz Column a, Column b and Column c, then these three columns can be used in any order (i.e.)

Where Column a = ‘’ or Column b=’’ and Column c=’’

Where Column b = ‘’ or Column c=’’ and Column a=’’

Where Column c = ‘’ or Column a=’’ and Column b=’’

Where Column a = ‘’ or Column c=’’ and Column b=’’

Where Column b = ‘’ or Column a=’’ and Column c=’’

Where Column c = ‘’ or Column b=’’ and Column a=’’

All the above combination will yield the index usage and should not create any major difference in the level of execution.

Conclusion:

If the columns are not always used together, please do create indexes on separate columns. If the columns are always used together, please do create composite indexes. Please also note that creation of index depends on the use case.

Myth 2: Full Table Scan is evil

Many think that full table scan is evil and they try to create indexes or use hints to convert this full table scan into other types of scan where indexes can be used. However, it is not so. Full table scan is never an evil way of executing the query. The reason for the optimizer to decide on the full table scan is probably due to low execution cost. From Oracle documentation, the following on Cost Based Optimizer (CBO) is known:

The CBO determines which execution plan is most efficient by considering available access paths and by factoring in information based on statistics for the schema objects (tables or indexes) accessed by the SQL statement. The CBO performs the following steps:

1. The optimizer generates a set of potential plans for the SQL statement based on available access paths and hints.

2. The optimizer estimates the cost of each plan based on statistics in the data dictionary for the data distribution and storage characteristics of the tables, indexes, and partitions accessed by the statement.

3. The optimizer compares the costs of the plans and chooses the one with the lowest cost.

Let us take the example using the tables created in Myth 1 and see what happens if we induce the index. The following statement is without using an index hint and hence it goes for the full table scan.

SQL> select * from generalworks where genno > 10000;

40000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 857713684

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 40000 | 1054K| 280 (2)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| GENERALWORKS | 40000 | 1054K| 280 (2)| 00:00:04 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("GENNO">10000)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3913 consistent gets

0 physical reads

0 redo size

1506899 bytes sent via SQL*Net to client

29707 bytes received via SQL*Net from client

2668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

40000 rows processed

Now, we shall use the index hint so that the optimizer will use this index to execute the query.

SQL> select /*+ index(generalworks genworkscompindx) */ * from generalworks where genno > 10000;

40000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1452202321

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 40000 | 1054K| 372 (1)| 00:00:05 |

| 1 | TABLE ACCESS BY INDEX ROWID| GENERALWORKS | 40000 | 1054K| 372 (1)| 00:00:05 |

|* 2 | INDEX RANGE SCAN | GENWORKSCOMPINDX | 40000 | | 159 (2)| 00:00:02 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("GENNO">10000 AND "GENNO" IS NOT NULL)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

5685 consistent gets

117 physical reads

0 redo size

1666842 bytes sent via SQL*Net to client

29707 bytes received via SQL*Net from client

2668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

40000 rows processed

Now, we have successfully made this query to execute by using the index. However, the following is to be noted:

a) Cost is increased

b) Consistent gets is increased

c) Physical Reads increased

Reason for this is, the optimizer when it compared the cost of executing the queries for various options, it found that using index for this is a costly affair. Hence it chose the full table scan. But when we induced the index usage, the execution plan is observed to be costly.

Having said this, the following are also to be noted:

a) If the cost is less after inducing the index, it means that proper statistics is not fed into the optimizer. Please make sure that all necessary statistics are updated in the tables.

b) Table scan in some occasions may prove to be an evil, if we do not perform the normal ground work like creating appropriate indexes, statistics collection and proper joins.

Myth 3: Rebuilding Index leads to faster retrieval of records.

Rebuilding an index would to some extent decrease the execution time of a select query, but increases the execution time of the DML operation. Please note that I said some extent and not to a greater extent. When we rebuild an index, it tightly compacts the index in such a way that most of the indexes almost lie near to each other there on the query operations completes a bit faster. However, this will also affect the involved DML operations. Let us illustrate this with an example.

Let me create a table, insert some data into it and study the corresponding statistics. After that we will rebuild the involved indexes and study the corresponding statistics and compare the same.

SQL> create table rebuildcheck (firstcol number);

Table created.

SQL> create index rebuildindex on rebuildcheck(firstcol);

Index created.

I created a small procedure to insert the data into this table as follows:

create or replace procedure rebuildproc as

begin

for i in 1 .. 400000

loop

insert into rebuildcheck values (trunc(dbms_random.value(1,500000)));

end loop;

commit;

end;

In order to study the behaviour of this insert operation, I turned on the SQL trace as follows:

SQL> ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

System altered.

SQL> alter session set sql_trace=true;

Session altered.

SQL> exec rebuildproc

PL/SQL procedure successfully completed.

SQL> alter session set sql_trace=false;

Session altered.

Now in my command prompt, I converted the generated trace file into a text file using TKPROF utility as follows:

tkprof D:\oracle\product\10.2.0\admin\orcl\udump\

orcl_ora_3820.trc d:\oracleexperiments\rebuildtrace.txt explain=hari/hari

The result shows the execution details of this procedure as follows:

BEGIN rebuildproc; END;

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.01 0.00 0 0 0 0

Execute 1 48.17 51.25 0 0 0 1

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 48.18 51.25 0 0 0 1

CPU is 48 seconds with the elapsed time of 51 seconds

Now, let us also select from this table and see the execution plan

SQL> select * from rebuildcheck;

400000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3132651637

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 378K| 4803K| 149 (8)| 00:00:02 |

| 1 | TABLE ACCESS FULL| REBUILDCHECK | 378K| 4803K| 149 (8)| 00:00:02 |

----------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

4 recursive calls

0 db block gets

27359 consistent gets

0 physical reads

0 redo size

7454987 bytes sent via SQL*Net to client

293707 bytes received via SQL*Net from client

26668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

400000 rows processed

Please note that even if we induce index the cost remains more or less the same as shown below:

SQL> select /*+ index(rebuildcheck rebuildindex) */ * from rebuildcheck;

400000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3132651637

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 345K| 4384K| 148 (7)| 00:00:02 |

| 1 | TABLE ACCESS FULL| REBUILDCHECK | 345K| 4384K| 148 (7)| 00:00:02 |

----------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

205 recursive calls

1 db block gets

27392 consistent gets

0 physical reads

176 redo size

5854855 bytes sent via SQL*Net to client

293707 bytes received via SQL*Net from client

26668 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

400000 rows processed

Now, let us rebuild the index and see what happens:

SQL> alter index rebuildindex rebuild;

Index altered.

SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'REBUILDCHECK');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_index_stats('HARI', 'REBUILDINDEX');

PL/SQL procedure successfully completed.

SQL> select * from rebuildcheck;

400000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3132651637

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 378K| 4803K| 149 (8)| 00:00:02 |

| 1 | TABLE ACCESS FULL| REBUILDCHECK | 378K| 4803K| 149 (8)| 00:00:02 |

----------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

50 recursive calls

0 db block gets

27365 consistent gets

0 physical reads

0 redo size

7454987 bytes sent via SQL*Net to client

293707 bytes received via SQL*Net from client

26668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

400000 rows processed

SQL> select /*+ index(rebuildcheck rebuildindex) */ * from rebuildcheck;

400000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3132651637

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 400K| 1562K| 149 (8)| 00:00:02 |

| 1 | TABLE ACCESS FULL| REBUILDCHECK | 400K| 1562K| 149 (8)| 00:00:02 |

----------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

27293 consistent gets

0 physical reads

0 redo size

5854855 bytes sent via SQL*Net to client

293707 bytes received via SQL*Net from client

26668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

400000 rows processed

BEGIN rebuildproc; END;

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 2 0.03 0.00 0 0 0 0

Execute 2 101.79 108.58 0 0 0 2

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 101.82 108.59 0 0 0 2

Misses in library cache during parse: 2

Optimizer mode: ALL_ROWS

Parsing user id: 62 (HARI)

Please note that CPU is increased 102 seconds with the elapsed time of 109 seconds. Almost 54 seconds more than expected with 58 seconds more on elapsed time. Also, note that not much improvement is noticed on the query execution.

The argument on using the rebuild index option still continues in this database world. One side of experts advise not to use rebuild option at all while the other end of experts advise to use the same. If we use rebuild, the issue is on using the DML statements.

Database view is not an evil database object

2010-10-08T04:38:00.000-07:00

Author:Hariharan T Sairam

Many folks in IT industry think that using database view is an evil, degrade performance on usage and should never be used. This article provides substantial evidence that view never impacts performance.

By definition, a database view is a virtual query. It never exists as an object. If a view is slow in performance, it is not due to view and it is due to the underlying query. Many fail to understand this basic concept. In order to understand this, I have created the following tables with index on EMPNO and each table has 100,000 records.

1. EMPLOYEE

2. EMPSALARY

3. EMPADDRESS

To begin with, I have created a view called "EMPVIEW" joining the tables EMPLOYEE and EMPSALARY and its definition is given below:

CREATE VIEW EMPVIEW AS SELECT EMP.EMPNO, EMP.EMPNAME, EMPS.EMPMONTH, EMPS.EMPBASIC, EMPS.EMPHRA, EMPS.EMPCONV FROM EMPLOYEE EMP JOIN EMPSALARY EMPS ON EMP.EMPNO=EMPS.EMPNO;

SQL> SELECT * FROM EMPVIEW;

100000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 820308138

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 83 | 5 (20)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 83 | 5 (20)| 00:00:01 |

| 2 | TABLE ACCESS FULL| EMPLOYEE | 1 | 25 | 2 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| EMPSALARY | 1 | 58 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access ("EMP"."EMPNO"="EMPS"."EMPNO")

Statistics

----------------------------------------------------------

612 recursive calls

0 db block gets

7436 consistent gets

0 physical reads

0 redo size

3518422 bytes sent via SQL*Net to client

73707 bytes received via SQL*Net from client

6668 SQL*Net roundtrips to/from client

16 sorts (memory)

0 sorts (disk)

100000 rows processed

Now, if we execute the underlying query of the view (which is given below), obviously we will come to know that the explain plan is same except for a very small difference in the statistics.

SELECT EMP.EMPNO, EMP.EMPNAME, EMPS.EMPMONTH, EMPS.EMPBASIC, EMPS.EMPHRA, EMPS.EMPCONV FROM EMPLOYEE EMP JOIN EMPSALARY EMPS ON EMP.EMPNO=EMPS.EMPNO;

100000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 820308138

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 83 | 5 (20)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 83 | 5 (20)| 00:00:01 |

| 2 | TABLE ACCESS FULL| EMPLOYEE | 1 | 25 | 2 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| EMPSALARY | 1 | 58 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("EMP"."EMPNO"="EMPS"."EMPNO")

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

7343 consistent gets

0 physical reads

0 redo size

3518422 bytes sent via SQL*Net to client

73707 bytes received via SQL*Net from client

6668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

100000 rows processed

Now, I am joining all the three tables and the explain plan for the same is given below:

SELECT EMP.EMPNO, EMP.EMPNAME, EMPA.EMPADDR1, EMPA.EMPADDR2, EMPA.EMPADDR3, EMPS.EMPMONTH, EMPS.EMPBASIC, EMPS.EMPHRA, EMPS.EMPCONV FROM EMPLOYEE EMP JOIN EMPADDRESS EMPA ON EMP.EMPNO=EMPA.EMPNO JOIN EMPSALARY EMPS ON EMP.EMPNO=EMPS.EMPNO

100000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3201299630

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 100K| 4492K| | 838 (4)|00:00:11 |

|* 1 | HASH JOIN | | 100K| 4492K| 3328K| 838 (4)|00:00:11 |

|* 2 | HASH JOIN | | 100K| 2148K| 1960K| 398 (4)|00:00:05 |

| 3 | TABLE ACCESS FULL| EMPLOYEE | 100K| 781K| | 72 (5)|00:00:01 |

| 4 | TABLE ACCESS FULL| EMPADDRESS | 100K| 1367K| | 100 (4)|00:00:02 |

| 5 | TABLE ACCESS FULL | EMPSALARY | 100K| 2343K| | 100 (4)|00:00:02 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("EMP"."EMPNO"="EMPS"."EMPNO")

2 - access("EMP"."EMPNO"="EMPA"."EMPNO")

Statistics

----------------------------------------------------------

942 recursive calls

0 db block gets

7922 consistent gets

1130 physical reads

0 redo size

3618604 bytes sent via SQL*Net to client

73707 bytes received via SQL*Net from client

6668 SQL*Net roundtrips to/from client

18 sorts (memory)

0 sorts (disk)

100000 rows processed

Now let us join the view with the table “EMPADDRESS” and see the results:

SELECT EMPV.EMPNO, EMPV.EMPNAME, EMPA.EMPADDR1, EMPA.EMPADDR2, EMPA.EMPADDR3, EMPV.EMPMONTH, EMPV.EMPBASIC, EMPV.EMPHRA, EMPV.EMPCONV FROM EMPVIEW EMPV JOIN EMPADDRESS EMPA ON EMPV.EMPNO=EMPA.EMPNO

100000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3201299630

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 100K| 4492K| | 838 (4)|00:00:11 |

|* 1 | HASH JOIN | | 100K| 4492K| 3328K| 838 (4)|00:00:11 |

|* 2 | HASH JOIN | | 100K| 2148K| 1960K| 398 (4)|00:00:05 |

| 3 | TABLE ACCESS FULL| EMPLOYEE | 100K| 781K| | 72 (5)|00:00:01 |

| 4 | TABLE ACCESS FULL| EMPADDRESS | 100K| 1367K| | 100 (4)|00:00:02 |

| 5 | TABLE ACCESS FULL | EMPSALARY | 100K| 2343K| | 100 (4)|00:00:02 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("EMP"."EMPNO"="EMPS"."EMPNO")

2 - access("EMP"."EMPNO"="EMPA"."EMPNO")

Statistics

----------------------------------------------------------

588 recursive calls

0 db block gets

7878 consistent gets

3 physical reads

0 redo size

3618604 bytes sent via SQL*Net to client

73707 bytes received via SQL*Net from client

6668 SQL*Net roundtrips to/from client

20 sorts (memory)

0 sorts (disk)

100000 rows processed

This proves a point that the execution plan of a query with view is same as using the actual query. However, let us see what happens if we create a view on another view. For this let us create another table called "EMPWORKS" having 100,000 records

Let us create a view called “EMPWORKVIEW” by joining the already created EMPVIEW with EMPWORKS table.

CREATE VIEW EMPWORKVIEW AS SELECT EMPV.EMPNO, EMPV.EMPNAME, EMPW.EMPWRKAREA, EMPW.EMPWRKLOC, EMPW.EMPROLE, EMPV.EMPMONTH, EMPV.EMPBASIC, EMPV.EMPHRA, EMPV.EMPCONV FROM EMPVIEW EMPV JOIN EMPWORKS EMPW ON EMPV.EMPNO=EMPW.EMPNO

SELECT * FROM EMPWORKVIEW

100000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2018455878

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 84747 | 5462K| | 918 (3)| 00:00:12 |

|* 1 | HASH JOIN | | 84747 | 5462K| 3808K| 918 (3)| 00:00:12 |

| 2 | TABLE ACCESS FULL | EMPWORKS | 84747 | 2813K| | 72 (5)| 00:00:01 |

|* 3 | HASH JOIN | | 100K| 3125K| 1960K| 446 (4)| 00:00:06 |

| 4 | TABLE ACCESS FULL| EMPLOYEE | 100K| 781K| | 72 (5)| 00:00:01 |

| 5 | TABLE ACCESS FULL| EMPSALARY | 100K| 2343K| | 100 (4)| 00:00:02 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access ("EMP"."EMPNO"="EMPW"."EMPNO")

3 - access ("EMP"."EMPNO"="EMPS"."EMPNO")

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

252 recursive calls

1 db block gets

7751 consistent gets

0 physical reads

176 redo size

4502964 bytes sent via SQL*Net to client

73707 bytes received via SQL*Net from client

6668 SQL*Net roundtrips to/from client

7 sorts (memory)

0 sorts (disk)

100000 rows processed

Now, if we join the three tables EMPWORKS, EMPSALARY and EMPWORKS we can see that the execution plan remains the same, but with meager difference in the statistics.

SELECT EMP.EMPNO, EMP.EMPNAME, EMPS.EMPMONTH, EMPS.EMPBASIC, EMPS.EMPHRA, EMPS.EMPCONV, EMPW.EMPWRKAREA, EMPW.EMPWRKLOC, EMPW.EMPROLE FROM EMPLOYEE EMP JOIN EMPSALARY EMPS ON EMP.EMPNO=EMPS.EMPNO JOIN EMPWORKS EMPW ON EMP.EMPNO=EMPW.EMPNO;