jump to navigation

Indexes And Small Tables Part VI (Loaded) May 19, 2009

Posted by Richard Foote in Constraints, Oracle Indexes, Small Indexes, Unique Indexes.
trackback

Thanks to comments by PdV, I need yet another Part before I can look at completing this series ;)

OK, we’ve reached the stage in Part V of accessing this small, one block table with a Unique Index. This has reduced the number of consistent gets to 2, with both consistent get operations being the “pinless”, one latch consistent get examinations.

We basically need one consistent get to read the index and the other consistent get to read the row from the table block.

Not bad.

However, if we could somehow just store all the columns of interest within the index structure, we could potentially do even better because then we wouldn’t need to visit the table segment at all. A single consistent get from the index and bingo, we can retrieve all the required data directly from the index.

Overloading an index in this way is actually quite a common tuning trick. Simply add additional columns within the select list so that all the required columns can be found within the index, thereby elliminating the need to access the table at all and so potentially improve performance.

However, when it comes to overloading a unique index designed to specifically police a PK or Unique constraint we have a slight problem. Oracle will not allow a unique constraint to be policed by a unique index that does not have the same column list. It’s not a problem for a non-unique index (providing the leading columns match the constraint columns), but it’s an issue for a unique index.

Therefore, in our little example, we can’t simply create a single concatenated unique index on both the ID and NAME columns and use it to police a unique constraint on just the ID column. We must either use a unique index on the ID column or use a non-unique index on both the ID and NAME columns. If we want to create a unique index on both the ID and NAME columns, we would need to create an additional index on the ID column to police the PK on the ID column or change our business rules to make both the ID and NAME the PK (which is not likely something we would want to do). Note also by doing creating 2 unique indexes, we would effectively be storing the ID column in three separate places, within the table, within the ID index and also within the ID and NAME index. Again, not something we’re likely going to want to do.

To illustrate the point, drop any existing indexes on the SMALL table. If we attempt to create a unique index on both the ID and NAME columns while making the ID column only the PK, Oracle is going to complain:

SQL> alter table small add primary key(id) using index (create unique index small_uk_i on small(id, name));
alter table small add primary key(id) using index (create unique index small_uk_i on small(id, name))
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.try and cr

If we create a unique index first on both the ID and NAME columns:

SQL> create unique index small_uk_i on small(id, name);

Index created.

And then hope Oracle will simply use this index to police a PK constraint on just the ID column, we’ll be sadly disappointed as Oracle will actually created another unique index on the ID column behind the scenes:

SQL> alter table small add primary key(id);

Table altered.

SQL> select c.index_name, c.column_name from user_indexes i, user_ind_columns cwhere i.index_name = c.index_name and i.table_name = c.table_name and i.table_name = ‘SMALL';

INDEX_NAME   COLUMN_NAME
------------ ------------
SMALL_UK_I   ID
SMALL_UK_I   NAME
SYS_C009759  ID

 

The CBO will of course potentially look at using our SMALL_UK_I concatenated unique index to perform the select statement of our demo, but the efficiency results are not quite what we’re after:

SQL> select id, name from small where id = 42;

        ID NAME
---------- -----
        42 BOWIE
-----------------------------------
|Id| Operation        | Name      |
-----------------------------------
| 0| SELECT STATEMENT |           |
|*1|  INDEX RANGE SCAN| SMALL_UK_I|
-----------------------------------
Statistics
-------------------------------------------
  0  recursive calls
  0  db block gets
  2  consistent gets
  0  physical reads
  0  redo size
465  bytes sent via SQL*Net to client
396  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed

 

We first note Oracle is indeed using the concatenated, unique SMALL_UK_I index as it can retrieve all the necessary data from the query directly from the index.

However, also note the CBO is performing an index range scan, not a unique scan as only a portion of the index (the ID) is specified in the WHERE clause. Oracle doesn’t pick up the fact the select operation is actually unique as not all columns within the SMALL_UK_I unique index used by the CBO are specified in the WHERE clause. This despite the fact the ID is actually the defined PK of the table.

Therefore, Oracle is still performing 2 consistent get operations as there may be more rows to retrieve after performing the first fetch within the SQL*PLUS session. Also, if we examined the types of consistent reads being performed, we would note that neither of them are consistent get – examinations.

So we’re really not that far ahead of just using the unique index on the ID column as we did in Part V of this series. We still require 2 consistent gets (although neither of them are now examinations) and we’re having to store the ID in three separate locations for our trouble, rather than two.

Wouldn’t it be nice if we could have a PK index on just the ID column, but somehow add the NAME column (or any other columns of interest) to the index structure so that we only need to visit the index structure, thereby storing the ID in only the one index. Then we could potentially access the data with just one consistent get and with it being a unique index, it would be a consistent get examination requiring only the one latch access.

Hell, wouldn’t it be nice if we didn’t even bother with the table segment at all as all queries of interest would never actually need to access and use the table segment anyways, thereby storing the PK in possibly just the one location.

Such a solution has of course been possible for a long time …

;)

About these ads

Comments»

1. Paweł Barut - May 19, 2009

The solution is…
IOT=Index Organized Table

/Pawel

2. Centinul - May 19, 2009

I am guessing the next step would be an IOT. The first tip off to that was:

“Hell, wouldn’t it be nice if we didn’t even bother with the table segment at all…”

My test below may have confirmed this.

SQL> CREATE TABLE small (id NUMBER, name VARCHAR2(5), CONSTRAINT small_pk PRIMARY KEY (id)) ORGANIZATION INDEX;

Table created.

SQL> INSERT INTO small SELECT rownum, ‘BOWIE’ FROM DUAL CONNECT BY LEVEL exec dbms_stats.gather_table_stats(ownname=> null,tabname => ‘SMALL’, estimate_percent => null, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM small WHERE id=42;

ID NAME
———- —–
42 BOWIE

Execution Plan
———————————————————-
Plan hash value: 3791778982

——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 1 | 9 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| SMALL_PK | 1 | 9 | 0 (0)| 00:00:01 |
——————————————————————————

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

1 – access(“ID”=42)

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

By the way, I enjoy your posts very much. My apologies if the format of my example doesn’t work well, I don’t know how to post it in fixed width font.

Centinul - May 19, 2009

My apologies the following line:

SQL> INSERT INTO small SELECT rownum, ‘BOWIE’ FROM DUAL CONNECT BY LEVEL exec dbms_stats.gather_table_stats(ownname=> null,tabname => ‘SMALL’, estimate_percent => null, method_opt=>’FOR ALL COLUMNS SIZE 1′);

Should be replaced with:

SQL> INSERT INTO small SELECT rownum, ‘BOWIE’ FROM DUAL CONNECT BY LEVEL exec dbms_stats.gather_table_stats(ownname=> null,tabname => ‘SMALL’, estimate_percent => null, method_opt=>’FOR ALL COLUMNS SIZE 1′);

3. Richard Foote - May 19, 2009

Hi Guys

Indeed !! The butler did it ;)

4. PdV - May 21, 2009

Butler or not, and butler knows I like IOTs, but the Hash-Clusters can also be quite efficient. And not even an index in sight, hence when data volume increases, blevel has no impact (what am I missing) ?
I almost feel oblidged to experiment and blog more about clusters now.

5. Richard Foote - May 25, 2009

Hi Piet

Indeed, IOT and Hash Clusters can both help with this scenario, although for small level 0 indexes, latch overheads are just a little less with IOTs.

All to be discussed in future posts.

6. Indexes And Small tables Part VII (Cluster One) « Richard Foote’s Oracle Blog - May 27, 2009

[...] Another posssible option to improve things in our little demo is to create a Hash Cluster (as commented by Piet): [...]

7. Flado - August 27, 2009

“Oracle will not allow a unique constraint to be policed by a unique index that does not have the same column list. ”

Do you have an idea or a guess as to why this is so?

Richard Foote - August 29, 2009

Hi Flado

Good question, one perhaps that the Oracle kernal developers can be answer.

I suspect it has something to do with the fact unique indexes previously had to be used to police constraints and also had to be the same as the constraint column(s).

They’ve since added the ability and flexabilty to now use non-unique indexes, perhaps without amending the previous unique index code path too much for backward compatable reasons.


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

Follow

Get every new post delivered to your Inbox.

Join 1,852 other followers

%d bloggers like this: