jump to navigation

12c Indexing Extended Data Types Part II (15 Steps) November 14, 2013

Posted by Richard Foote in 12c, Extended Data Types, Function Based Indexes, Oracle Indexes.
trackback

Finally, at long long last, I have a spare 30 minutes in my life to complete this blog entry !!

As discussed previously, Oracle has extended the maximum length of varchar2, nvarchar and raw columns to 32K, but this comes with some challenges when it comes to indexing such columns due to restrictions on the maximum length of an index entry.

A function-based index on the hash of the column value as previously demonstrated can be used for equality based predicates but not for ranged based requirements.

If index accesses are required for ranged based predicates, then a simple sub-string function-based index can be considered. Using the same set-up and demo as in Part I, let’s create a function-based index that stores the first (say) 1000 characters of an extended data type column. This should provide more than enough detail of the column contents to be sufficiently selective in most practical scenarios.

SQL> create index bowie_substr_text_i on bowie(substr(text,1,1000));

Index created.

SQL> select index_name, num_rows, leaf_blocks from dba_indexes where index_name = 'BOWIE_SUBSTR_TEXT_I';

INDEX_NAME             NUM_ROWS LEAF_BLOCKS
-------------------- ---------- -----------
BOWIE_SUBSTR_TEXT_I      100000         306

Such a substr function-based index is viable not only with equality based predicates:

SQL> select * from bowie where text = '42BOWIE';

Execution Plan
----------------------------------------------------------

Plan hash value: 4016785672
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     1 |    16 |    80   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE               |     1 |    16 |    80   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_SUBSTR_TEXT_I |   400 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"='42BOWIE')
2 - access(SUBSTR("TEXT",1,1000)='42BOWIE')

Statistics
----------------------------------------------------------

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

But unlike the hash function-based index in my previous post, it can also be considered in a range (pun fully intended) of ranged-based predicates as well, for example:

SQL> select * from bowie where text between '4299BOWIE' and '42BOWIE';

Execution Plan
----------------------------------------------------------

Plan hash value: 4016785672
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     2 |    32 |    92   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE               |     2 |    32 |    92   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_SUBSTR_TEXT_I |   450 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"<='42BOWIE' AND "TEXT">='4299BOWIE')
2 - access(SUBSTR("TEXT",1,1000)>='4299BOWIE' AND SUBSTR("TEXT",1,1000)<='42BOWIE')

Statistics
----------------------------------------------------------

0  recursive calls
0  db block gets
6  consistent gets
0  physical reads
0  redo size
693  bytes sent via SQL*Net to client
544  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
3  rows processed
SQL> select * from bowie where text > 'C';

Execution Plan
----------------------------------------------------------

Plan hash value: 4016785672
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     | 63040 |  2277K|   181   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE               | 63040 |  2277K|   181   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_SUBSTR_TEXT_I |   900 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT">'C')
2 - access(SUBSTR("TEXT",1,1000)>='C')

Statistics
----------------------------------------------------------

0  recursive calls
0  db block gets
10  consistent gets
0  physical reads
0  redo size
12884  bytes sent via SQL*Net to client
544  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
2  rows processed

Comments»

1. 32K Columns | Oracle Scratchpad - November 14, 2013

[…] Part 2 […]


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,703 other followers

%d bloggers like this: