Query Using Like In Join Condition

Today I have received the below query, by examining the execution plan, the estimator calculates a cost of 1180m, times are 999: 59: 59, bytes about 11 Terabytes,  in short, that the probability of ending is null;

 

SELECT 
  /*+ FULL(T) PARALLEL (T 4)  FULL(P) PARALLEL (P 4) USE_HASH(T P) */                    
  T.COL1,            
  T.COL2,     
  T.COL3,      
  T.COL4,     
  T.COL5,     
  T.COL6,     
  T.COL7,     
  P.COL1        
FROM         
  TAB1 P,                  
  TAB2 T              
WHERE                             
  T.COL8 = 'FILE_1.TXT'            
  AND  T.COL9 LIKE NVL(P.COL2, '')||'%'||NVL(P.COL3,'')||'%'||COL4            
 
                                                                                                  
----------------------------------------------------------------------------------------------
| Id  | Operation                | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                   |       |       |  1180M(100)|          |
|   1 |  PX COORDINATOR          |                   |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001          |    80G|    11T|  1180M  (3)|999:59:59 |
|   3 |    NESTED LOOPS          |                   |    80G|    11T|  1180M  (3)|999:59:59 |
|   4 |     BUFFER SORT          |                   |       |       |            |          |
|   5 |      PX RECEIVE          |                   |       |       |            |          |
|   6 |       PX SEND BROADCAST  | :TQ10000          |       |       |            |          |
|   7 |        PX BLOCK ITERATOR |                   |   200K|    23M|  1510   (1)| 00:00:19 |
|*  8 |         TABLE ACCESS FULL| TAB2              |   200K|    23M|  1510   (1)| 00:00:19 |
|   9 |     PX BLOCK ITERATOR    |                   |   400K|    12M| 21251   (3)| 00:04:16 |
|* 10 |      TABLE ACCESS FULL   | TAB1              |   400K|    12M| 21251   (3)| 00:04:16 |
----------------------------------------------------------------------------------------------

 

The LIKE acts as a thetajoin, like the join is very very heavy, the amount of crossing rows is a really high number and the process of attachment, even using blooms filters can be very complex. The hint hash does not work due to the large number of values​​, you need to map large amounts of data in memory. the rest of the hints works fine, the full and parallelThe solution is to avoid the like and if you can not simplify to the maximum the join, convert the WHERE more restrict or add  any condition or new conditions more selective . If it still continues to be so complex, it would have to use a procedural language, in this case obviously PL ​​/ SQL and do a batch that can take  from hours to days, weeks, months

 

HTH – Antonio NAVARRO

 

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