Oracle LIKE predicate and cardinality estimations

Submitted by fpachot on Sun, 11/11/2018 - 21:01
Blog article:
Submitted by Rainer Stenzel (not verified) 1 year ago

Somehow the prefix length is taken into consideration too:

select * from CUSTOMER_BEHAVIOR
union all
select * from CUSTOMER_BEHAVIOR where NAME like 'a_'
union all
select * from CUSTOMER_BEHAVIOR where NAME like 'ab_'
union all
select * from CUSTOMER_BEHAVIOR where NAME like 'abc_'
union all
select * from CUSTOMER_BEHAVIOR where NAME like 'abcd_'

Plan hash value: 3540635705

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14M| 2227M| 464K (1)| 00:00:19 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS FULL| CUSTOMER_BEHAVIOR | 14M| 2195M| 92901 (1)| 00:00:04 |
|* 3 | TABLE ACCESS FULL| CUSTOMER_BEHAVIOR | 203K| 31M| 92822 (1)| 00:00:04 |
|* 4 | TABLE ACCESS FULL| CUSTOMER_BEHAVIOR | 794 | 125K| 92820 (1)| 00:00:04 |
|* 5 | TABLE ACCESS FULL| CUSTOMER_BEHAVIOR | 4 | 648 | 92820 (1)| 00:00:04 |
|* 6 | TABLE ACCESS FULL| CUSTOMER_BEHAVIOR | 1 | 162 | 92820 (1)| 00:00:04 |
----------------------------------------------------------------------------------------------

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

3 - filter("NAME" LIKE 'a_')
4 - filter("NAME" LIKE 'ab_')
5 - filter("NAME" LIKE 'abc_')
6 - filter("NAME" LIKE 'abcd_')

table name masking inspiration from
https://dilbert.com/strip/2000-11-13

Disclaimer

The views expressed in this blog are those of the authors and cannot be regarded as representing CERN’s official position.

CERN Social Media Guidelines

 

Blogroll