SQL Tuning(World Scenario)

SQL Tuning is very big and difficult subject. Each SQL Tuning scenario is unique.

To solve the problems easily, you need a lot of experience.

I decided to write every SQL Tuning Scenario which I have had.

So, let’s start:

Look at this SQL:

SELECT
       distinct col1,
                col2,
                col3,
                col4FROM HR.Table1

where col2 = 040533424

  and col1 !=

‘Mariam’

order by col4 desc

It’s SQL plan is the following:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 269539686
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 134 | 2625 (5)| 00:00:1
|   1 |  SORT UNIQUE       |              |     2 |   134 |  2624   (5)| 00:00:1
|*  2 |   TABLE ACCESS FULL| TABLE1       |     2 |   134 |  2623   (5)| 00:00:1
--------------------------------------------------------------------------------

This table has index named col2_indx on col2 column.But in our plan it is not used, why??

Now let’s drill down:

SQL> desc HR.TABLE1

Name   Type         Nullable Default Comments
------ ------------ -------- ------- -------- 
col1   VARCHAR2(15) Y

col2 VARCHAR2(30) Y

col3   VARCHAR2(50) Y
col4   VARCHAR2(30) Y

As you can see col2 type is varchar2… But in our SQl statement value of this column is equal to number type…

So Oracle is trying to convert number type to varchar2 type and at this time looses the ability to use that index.

If you follow my posts on that blog, you may remember the post about FUNCTION BASED INDEXES. There is the following scenario.

If you have index on some column and in select you are using this column like that to_char(col2) or with some other function… Index on that column is missed. You can have function based index on to_char(col2) combination…

Now, let’s rewrite our SQL:

SELECT
       distinct col1,
                col2,
                col3,
                col4FROM HR.Table1

where col2 = ‘040533424’

 and col1 !=

‘Mariam’

order by col4 desc

Plan for this statement:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4243002513
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 134 | 7 (2
|   1 |  SORT ORDER BY                |              |     2 |   134 |     7  (2
|   2 |   HASH UNIQUE                 |              |     2 |   134 |     6  (1
|*  3 |    TABLE ACCESS BY INDEX ROWID| TABLE1       |     2 |   134 |     5   (

|* 4 | INDEX RANGE SCAN | COL2_INDX | 2 | | 3 (

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

And this select is faster than the previous.

I hope it was helpful for you…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: