Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted May 11, 2017

Oracle B-Tree Index On A BLOB

By David Fitzjarrell

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM