Oracle B-Tree Index On A BLOB

An interesting question was posted on the Oracle General Database forums recently inquiring about creating a B-Tree index on a BLOB to speed queries looking for NULLs in that BLOB. The conventional wisdom states that, because it’s a BLOB, NULLs shouldn’t be present. And because it’s a BLOB, containing binary data, creating such an index shouldn’t be possible, but through the magic of Oracle (and a possible bug) that ‘wisdom’ was proven wrong. Let’s look at that situation and see how someone can make an ‘end run’ around Oracle and do the seemingly impossible.

For those who don’t use them or who may not be familiar with the term, a BLOB is a Binary Large OBject, a datatype that stores, well, binary data. As such there should be no NULL values in binary data (it’s all 0s and 1s and the character(?) associated with NULL has a binary representation of 0 [looking at an ASCII table]) the benefit of having such an index is questionable, at best. It IS possible to set a BLOB to NULL, however, as the following example proves:


SQL> update blobbo set yorm = null;

1 row updated.

SQL>

Even with that it can make someone wonder what actual benefit a B-Tree index can provide on binary data. Looking at the complete example that was run may provide some additional insight. It begins, simply enough, by creating a table to ‘experiment on’ and then trying to create a B-Tree index on all columns, including the BLOB:


SQL> 
SQL> 
SQL> set echo on linesize 150 pagesize 60
SQL> 
SQL> create table blobbo(
  2  yorm    blob,
  3  qwert   number default 4);

Table created.

SQL> 

SQL> create index blobbo_blob_ix on blobbo(yorm, qwert);
create index blobbo_blob_ix on blobbo(yorm, qwert)
                                      *
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB 


SQL> 

The initial attempt to create the index failed, even with the non-BLOB column specified. One can create a B-Tree index by specifying a constant value along with the column name, which is the workaround for non-BLOB columns to get NULLs in the index [since the entire key is not NULL] so let’s try that avenue of attack:


SQL> 
SQL> create index blobbo_blob_ix on blobbo(yorm, 'X');

Index created.

SQL> 

And we have a B-Tree index on a BLOB. As shown above, the BLOB can be set to NULL by assignment:


SQL> 
SQL> insert into blobbo(yorm) values(empty_blob());

1 row created.

SQL> 
SQL> update blobbo set yorm = null;

1 row updated.

SQL> 

Since a NULL and the LOB locator established by using the empty_blob() function look the same to the naked eye it’s difficult to tell if the query output shows a NULL exists:


SQL> 
SQL> select * from blobbo;

YORM                                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------
     QWERT                                                                                                                                            
----------                                                                                                                                            
                                                                                                                                                      
         4                                                                                                                                            
                                                                                                                                                      

SQL> 

The execution plan shows what is expected, a full table scan for the unqualified query:


SQL> 
SQL> select * from table(dbms_xplan.display_Cursor());

PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4gcbmd14ap73j, child number 0                                                                                                                 
-------------------------------------                                                                                                                 
select * from blobbo                                                                                                                                  
                                                                                                                                                      
Plan hash value: 925165977                                                                                                                            
                                                                                                                                                      
----------------------------------------------------------------------------                                                                          
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                          
----------------------------------------------------------------------------                                                                          
|   0 | SELECT STATEMENT  |        |       |       |     3 (100)|          |                                                                          
|   1 |  TABLE ACCESS FULL| BLOBBO |     1 |  2015 |     3   (0)| 00:00:01 |                                                                          
----------------------------------------------------------------------------                                                                          
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic statistics used: dynamic sampling (level=2)                                                                                              
                                                                                                                                                      

18 rows selected.

SQL> 

Writing a qualified query, to find records where our BLOB is NULL, shows a different, but expected, plan:


SQL> 
SQL> select * from blobbo where yorm is null;

YORM                                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------
     QWERT                                                                                                                                            
----------                                                                                                                                            
                                                                                                                                                      
         4                                                                                                                                            
                                                                                                                                                      

SQL> 
SQL> select * from table(dbms_xplan.display_Cursor());

PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cz0x5cbkwqkbu, child number 0                                                                                                                 
-------------------------------------                                                                                                                 
select * from blobbo where yorm is null                                                                                                               
                                                                                                                                                      
Plan hash value: 1649995771                                                                                                                           
                                                                                                                                                      
------------------------------------------------------------------------------------------------------                                                
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |                                                
------------------------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT                    |                |       |       |     1 (100)|          |                                                
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BLOBBO         |     1 |  2015 |     1   (0)| 00:00:01 |                                                
|*  2 |   INDEX RANGE SCAN                  | BLOBBO_BLOB_IX |     1 |       |     1   (0)| 00:00:01 |                                                
------------------------------------------------------------------------------------------------------                                                
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   2 - access("YORM" IS NULL)                                                                                                                         
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic statistics used: dynamic sampling (level=2)                                                                                              
                                                                                                                                                      

24 rows selected.

SQL> 

Setting the BLOB to an actual, non-NULL value and querying the table shows the expected non-index plan, because we have no function-based index on the BLOB (and actually, would have no reason to attempt to create one):


SQL> 
SQL> update blobbo set yorm = hextoraw(rpad('EAEAEAFF',32764, 'CDEF')) where qwert = 4;

1 row updated.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select * from blobbo where yorm is not null;

YORM                                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------
     QWERT                                                                                                                                            
----------                                                                                                                                            
EAEAEAFFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCD
EFCDEFCDEF                                                                                                                                            
         4                                                                                                                                            
                                                                                                                                                      

SQL> 
SQL> select * from table(dbms_xplan.display_Cursor());

PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3d24twzgz2zry, child number 0                                                                                                                 
-------------------------------------                                                                                                                 
select * from blobbo where yorm is not null                                                                                                           
                                                                                                                                                      
Plan hash value: 925165977                                                                                                                            
                                                                                                                                                      
----------------------------------------------------------------------------                                                                          
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                          
----------------------------------------------------------------------------                                                                          
|   0 | SELECT STATEMENT  |        |       |       |     3 (100)|          |                                                                          
|*  1 |  TABLE ACCESS FULL| BLOBBO |     1 |  2015 |     3   (0)| 00:00:01 |                                                                          
----------------------------------------------------------------------------                                                                          
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   1 - filter("YORM" IS NOT NULL)                                                                                                                     
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic statistics used: dynamic sampling (level=2)                                                                                              
                                                                                                                                                      

23 rows selected.

SQL> 
SQL> select * from blobbo where dbms_lob.instr(yorm,hextoraw('EAEAEAFF')) > 0;

YORM                                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------
     QWERT                                                                                                                                            
----------                                                                                                                                            
EAEAEAFFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCD
EFCDEFCDEF                                                                                                                                            
         4                                                                                                                                            
                                                                                                                                                      

SQL> 
SQL> select * from table(dbms_xplan.display_Cursor());

PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bj30wyv2sf5jg, child number 0                                                                                                                 
-------------------------------------                                                                                                                 
select * from blobbo where dbms_lob.instr(yorm,hextoraw('EAEAEAFF')) > 0                                                                              
                                                                                                                                                      
Plan hash value: 925165977                                                                                                                            
                                                                                                                                                      
----------------------------------------------------------------------------                                                                          
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                          
----------------------------------------------------------------------------                                                                          
|   0 | SELECT STATEMENT  |        |       |       |     3 (100)|          |                                                                          
|*  1 |  TABLE ACCESS FULL| BLOBBO |     1 |  2015 |     3   (0)| 00:00:01 |                                                                          
----------------------------------------------------------------------------                                                                          
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   1 - filter("DBMS_LOB"."INSTR"("YORM",HEXTORAW('EAEAEAFF'))>0)                                                                                      
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic statistics used: dynamic sampling (level=2)                                                                                              
                                                                                                                                                      

23 rows selected.

SQL> 

As mentioned earlier the ability to create such an index on a BLOB is likely a bug that should be addressed, but likely won’t be because it’s not common to want a B-Tree index on a BLOB, much less common to create one. One hint that this might be a bug is the fact that an index key can only be as long as 80% of the column length, and since a BLOB easily exceeds the maximum length of a VARCHAR2 field, problems can arise from such an index. With the index in place on our BLOB, trying to get the length of it throws an error:


SQL> select dbms_lob.getlength(yorm) from blobbo;
select dbms_lob.getlength(yorm) from blobbo
       *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1_1607784644$" too small 
ORA-06512: at "SYS.DBMS_LOB", line 837 


SQL> 

Dropping the ‘errant’ index solves the problem:


SQL> 
SQL> drop index blobbo_blob_ix;

Index dropped.

SQL> 
SQL> select dbms_lob.getlength(yorm) from blobbo;

DBMS_LOB.GETLENGTH(YORM)                                                                                                                              
------------------------                                                                                                                              
                   16382                                                                                                                              

SQL> 

Even though a B-Tree index on a BLOB column can be created it seems to be of limited, if not questionable, use since NULLs (as ‘seen’ in character data) can’t exist in the binary data stream. It also appears to create problems when performing standard operations on a BLOB, such as finding the length.

Sometimes what can be done, and what should be done, are two completely different things, so think carefully when asking for non-standard behavior from Oracle. Getting what you asked for can also give you much that you DIDN’T ask for or even expect.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles