Sunday, March 22, 2009

Getting accurate cardinality estimates for the LIKE pattern matching - basic column statistics, histograms and dynamic sampling

If you need to get accurate cardinality/selectivity estimates from the cost based optimizer for the LIKE operator used with pattern operations (e.g. 'A%'), there are significant differences between basic column statistics and histograms.

The following test case run against 10.2.0.4 compares the cardinality estimates you get with basic column statistics and histograms.

It also demonstrates that pattern searches that use a pattern as leading character (e.g. '%A') don't benefit from histograms but can only be alleviated by using dynamic sampling which of course comes at the price of additional work performed at optimization time.


SQL>
SQL> create table like_test
2 as
3 select * from all_objects
4 where rownum <= 1000;

Table created.

SQL>
SQL> -- basic column statistics, no histograms
SQL> exec dbms_stats.gather_table_stats(null, 'LIKE_TEST', method_opt=>'FOR ALL COLUMNS SIZE 1')

PL/SQL procedure successfully completed.

SQL>
SQL> select
2 num_distinct
3 from
4 user_tab_cols
5 where
6 table_name = 'LIKE_TEST'
7 and column_name = 'OBJECT_NAME';

NUM_DISTINCT
------------
992

SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- different but inaccurate estimates based on LOW_VALUE/HIGH_VALUE
SQL> select * from like_test where object_name like 'A%';

38 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 2686 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 34 | 2686 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE 'A%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
3028 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
38 rows processed

SQL>
SQL> select * from like_test where object_name like 'B%';

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 3634 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 46 | 3634 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE 'B%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1376 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
SQL> select * from like_test where object_name like 'N%';

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 3634 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 46 | 3634 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE 'N%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1456 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
SQL> select * from like_test where object_name like 'X%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE 'X%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select * from like_test where object_name like 'AB%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE 'AB%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select * from like_test where object_name like 'V$%';

94 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE 'V$%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4896 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
94 rows processed

SQL>
SQL> -- 5% guess for unprefixed wildcard searches like this
SQL> select * from like_test where object_name like '%V$%';

96 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 3950 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%V$%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4999 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed

SQL>
SQL> select * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 3950 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%AA%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> -- use dynamic sampling to get accurate estimates in this case
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%V$%';

96 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 7584 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 96 | 7584 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%V$%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
36 consistent gets
0 physical reads
0 redo size
4999 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


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

SQL>
SQL> set autotrace off
SQL>
SQL> -- regather the statistics
SQL> -- could generate a histogram on object_name now
SQL> -- based on above column workload
SQL> exec dbms_stats.gather_table_stats(null, 'LIKE_TEST', method_opt=>'FOR ALL COLUMNS SIZE AUTO')

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- more accurate estimates based on histogram
SQL> select * from like_test where object_name like 'A%';

38 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 38 | 3002 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 38 | 3002 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE 'A%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
3028 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
38 rows processed

SQL>
SQL> select * from like_test where object_name like 'B%';

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 632 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 8 | 632 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE 'B%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1376 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
SQL> select * from like_test where object_name like 'N%';

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 316 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 4 | 316 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE 'N%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1456 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
SQL> select * from like_test where object_name like 'X%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE 'X%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select * from like_test where object_name like 'AB%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE 'AB%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select * from like_test where object_name like 'V$%';

94 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 91 | 7189 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 91 | 7189 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE 'V$%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4896 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
94 rows processed

SQL>
SQL> -- 5% guess for unprefixed wildcard searches like this
SQL> -- histogram is if no use in this case
SQL> select * from like_test where object_name like '%V$%';

96 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 3950 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%V$%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4999 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed

SQL>
SQL> select * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 3950 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%AA%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> -- use dynamic sampling to get accurate estimates in this case
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%V$%';

96 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 7584 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 96 | 7584 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%V$%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4999 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> set autotrace off
SQL>


Basic column statistics can only use the recorded NUM_DISTINCT, LOW and HIGH VALUE for the selectivity estimate, therefore values that fall within the recorded range all get similar cardinality estimates, it does a bad job however when using multi-character search patterns (e.g. 'AB%').

Using histograms shows quite accurate estimates, even in case of the multi-character seach patterns.

In case of wildcards as leading characters of the search pattern (e.g. '%AA%') the optimizer falls back to a default 5% guess. In this case dynamic sampling can be used to get accurate estimates. If you're using the optimizer_dynamic_sampling parameter or the dynamic_sampling hint without specifying the table it needs to be set to level 3 at least to apply dynamic sampling to predicates that are based on guesses.

A slightly different variant is the following where a function-based index is used for an expression. The test case demonstrates another important point to keep in mind when adding function-based indexes: Creating the index adds a hidden/virtual column to the table that initially doesn't have any column statistics, although the index itself is analyzed by default from 10g on.

Therefore you should always make sure that the hidden/virtual columns added to a table do have statistics populated, otherwise you might be in for a surprise.


SQL> -- create function based index, but it's missing the column statistics
SQL> create index like_test_idx1 on like_test (lpad(object_name, 5, '0'));

Index created.

SQL>
SQL> select
2 num_distinct
3 from
4 user_tab_cols
5 where
6 table_name = 'LIKE_TEST'
7 and hidden_column = 'YES';

NUM_DISTINCT
------------


SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- no difference between estimates
SQL> -- due to missing column statistics
SQL> -- of hidden column
SQL> -- 5% default guess is being used
SQL> select * from like_test where lpad(object_name, 5, '0') like 'A%';

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 50 | 3950 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'A%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'A%')


Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
19 consistent gets
1 physical reads
0 redo size
3137 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'B%';


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 50 | 3950 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'B%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'B%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1361 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'N%';

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 50 | 3950 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'N%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'N%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
1 physical reads
0 redo size
1498 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'X%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 50 | 3950 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'X%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'X%')


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

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'AB%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 50 | 3950 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'AB%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'AB%')


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

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'V$%';

93 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 50 | 3950 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'V$%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'V$%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
6041 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
93 rows processed

SQL>
SQL> -- 5% guess for unprefixed wildcard searches like this
SQL> select * from like_test where lpad(object_name, 5, '0') like '%V$%';

95 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 3950 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%V$%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4932 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
95 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 3950 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%AA%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> -- use dynamic sampling to get accurate estimates in this case
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%V$%';

96 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 7584 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 96 | 7584 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%V$%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
36 consistent gets
0 physical reads
0 redo size
4999 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


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

SQL>
SQL> set autotrace off
SQL>
SQL> -- basic column statistics
SQL> exec dbms_stats.gather_table_stats(null, 'LIKE_TEST', method_opt=>'FOR ALL HIDDEN COLUMNS SIZE 1')

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- different but inaccurate estimates based on LOW_VALUE/HIGH_VALUE
SQL> select * from like_test where lpad(object_name, 5, '0') like 'A%';

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28 | 2380 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 28 | 2380 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE 'A%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
3137 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'B%';


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28 | 2380 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 28 | 2380 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE 'B%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1361 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'N%';

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28 | 2380 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 28 | 2380 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE 'N%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1498 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'X%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 170 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 2 | 170 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 2 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'X%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'X%')


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

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'AB%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 170 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 2 | 170 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 2 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'AB%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'AB%')


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

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'V$%';

93 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 170 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 2 | 170 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 2 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'V$%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'V$%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
6041 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
93 rows processed

SQL>
SQL> -- 5% guess for unprefixed wildcard searches like this
SQL> select * from like_test where lpad(object_name, 5, '0') like '%V$%';

95 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 4250 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 4250 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%V$%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4932 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
95 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 4250 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 4250 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%AA%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> -- use dynamic sampling to get accurate estimates in this case
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%V$%';

96 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 8160 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 96 | 8160 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%V$%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4999 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 85 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> -- generate a histogram
SQL> exec dbms_stats.gather_table_stats(null, 'LIKE_TEST', method_opt=>'FOR ALL HIDDEN COLUMNS SIZE 254')

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- more accurate estimates based on histogram
SQL> select * from like_test where lpad(object_name, 5, '0') like 'A%';

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 3060 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 36 | 3060 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE 'A%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
3137 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'B%';


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 340 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 4 | 340 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 4 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'B%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'B%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1361 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'N%';

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 340 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 4 | 340 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 4 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'N%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'N%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1498 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'X%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 170 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 2 | 170 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 2 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'X%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'X%')


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

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'AB%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 170 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 2 | 170 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 2 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'AB%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'AB%')


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

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'V$%';

93 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 87 | 7395 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 87 | 7395 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE 'V$%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
6041 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
93 rows processed

SQL>
SQL> -- 5% guess for unprefixed wildcard searches like this
SQL> -- histogram is if no use in this case
SQL> select * from like_test where lpad(object_name, 5, '0') like '%V$%';

95 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 4250 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 4250 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%V$%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4932 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
95 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 4250 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 4250 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%AA%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> -- use dynamic sampling to get accurate estimates in this case
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%V$%';

96 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 8160 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 96 | 8160 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%V$%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4999 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 85 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed



The final test case shows that even dynamic sampling not always gets it right. Although it shows the correct estimate for the table it doesn't correct the estimate used for the index, and the overall cost estimate is therefore incorrect, too, since it is based on the uncorrected index selectivity.

The AUTOTRACE output clearly shows that using the index in this case requires actually only the index related I/O.


SQL>
SQL> select /*+ index(like_test, like_test_idx1) */
2 * from like_test where lpad(object_name, 5, '0') like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1731345218

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 4250 | 23 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 50 | 4250 | 23 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | LIKE_TEST_IDX1 | 50 | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%AA%')


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

SQL>
SQL> select /*+ dynamic_sampling(3) index(like_test, like_test_idx1) */
2 * from like_test where lpad(object_name, 5, '0') like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1731345218

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | 23 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 1 | 85 | 23 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | LIKE_TEST_IDX1 | 50 | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> -- once again without recursive overhead
SQL> select /*+ dynamic_sampling(3) index(like_test, like_test_idx1) */
2 * from like_test where lpad(object_name, 5, '0') like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1731345218

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | 23 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 1 | 85 | 23 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | LIKE_TEST_IDX1 | 50 | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


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

SQL>
SQL> set autotrace off
SQL>


Running this test case against 9.2.0.8 shows similar results.

11.1.0.7 showed an interesting oddity: When using default "NLS_SORT = binary" NLS sort setting, the LIKE '%pattern%' predicate was not considered as "guess" and therefore the dynamic sampling wasn't performed. When switching to non-default "NLS_SORT" settings, like 'german' or 'french' dynamic sampling took place.

Note that other expressions, e.g. SUBSTR functions, obviously are not affected and still are considered as guess in both cases.


SQL>
SQL> set autotrace traceonly
SQL>
SQL> alter session set nls_sort = binary;

Session altered.

SQL>
SQL> alter session set tracefile_identifier = 'binary_sort';

Session altered.

SQL>
SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 4900 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 4900 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%AA%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1123 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like 'AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 98 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE 'AA%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1123 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where substr(object_name, 4, 2) = 'AA';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 98 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter(SUBSTR("OBJECT_NAME",4,2)='AA')

Note
-----
- dynamic sampling used for this statement


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

SQL>
SQL> alter session set nls_sort = german;

Session altered.

SQL>
SQL> alter session set tracefile_identifier = 'non_binary_sort';

Session altered.

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 98 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1123 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like 'AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 98 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE 'AA%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1123 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where substr(object_name, 4, 2) = 'AA';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 98 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter(SUBSTR("OBJECT_NAME",4,2)='AA')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1123 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> spool off


I'm not sure if this is intended behaviour, and it could lead to significant changes in the execution plans when upgrading to 11g.

The corresponding 10053 trace files snippets look like this:

Binary sort order:


***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for LIKE_TEST[LIKE_TEST]

*** 2009-03-22 14:30:23.422
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning FALSE.
Column (#2):
NewDensity:0.001015, OldDensity:0.001034 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:985
Table: LIKE_TEST Alias: LIKE_TEST
Card: Original: 1000.000000 Rounded: 50 Computed: 50.00 Non Adjusted: 50.00
Access Path: TableScan
Cost: 6.07 Resp: 6.07 Degree: 0
Cost_io: 6.00 Cost_cpu: 382700
Resp_io: 6.00 Resp_cpu: 382700
Best:: AccessPath: TableScan
Cost: 6.07 Degree: 1 Resp: 6.07 Card: 50.00 Bytes: 0

***************************************


Non-binary sort order:


***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for LIKE_TEST[LIKE_TEST]

*** 2009-03-22 14:30:23.762
** Performing dynamic sampling initial checks. **
Column (#2):
NewDensity:0.001015, OldDensity:0.001034 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:985
** Dynamic sampling initial checks returning TRUE (level = 3).

*** 2009-03-22 14:30:23.762
** Generated dynamic sampling query:
query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("LIKE_TEST") FULL("LIKE_TEST") NO_PARALLEL_INDEX("LIKE_TEST") */ 1 AS C1, 1 AS C2 FROM "LIKE_TEST" "LIKE_TEST" WHERE "LIKE_TEST"."OBJECT_NAME" LIKE '%AA%') SAMPLESUB

*** 2009-03-22 14:30:23.762
** Executed dynamic sampling query:
level : 3
sample pct. : 100.000000
actual sample size : 1000
filtered sample card. : 0
orig. card. : 1000
block cnt. table stat. : 14
block cnt. for sampling: 14
max. sample block cnt. : 32
sample block cnt. : 14
min. sel. est. : 0.05000000
** Using single table dynamic sel. est. : 0.00000000
Table: LIKE_TEST Alias: LIKE_TEST
Card: Original: 1000.000000 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
Access Path: TableScan
Cost: 6.07 Resp: 6.07 Degree: 0
Cost_io: 6.00 Cost_cpu: 369960
Resp_io: 6.00 Resp_cpu: 369960
Best:: AccessPath: TableScan
Cost: 6.07 Degree: 1 Resp: 6.07 Card: 0.00 Bytes: 0

***************************************


So if you're relying on dynamic sampling and upgrade to 11g there might surprises waiting for you.

Sunday, March 8, 2009

Optimizer partition oddities, part 1: Severe bug in 10.2.0.4 patch set

Forward to part 2

It seems to be a bit like in the movie the "Groundhog Day": Although I've highlighted already twice an issue when using range-list subpartition pruning, it seems to be reoccurring and there are still a lot of oddities to tell about.

A recent case of a client showed that the subpartition pruning fix that has been incorporated into the 10.2.0.4 patch set is unfortunately quite buggy.

The test case used in my previous posts therefore represents only half of the truth.

Although when using explicit subpartition pruning by means of the "subpartition" clause 10.2.0.4 correctly applies the subpartition statistics (in contrast to 10.2.0.3 which uses the partition level statistics), it fails miserably when using "normal" partition pruning by specifying the partition keys.

Using the same test setup as in my previous post running this simple query in the different versions reveals some interesting results:


select data1 from partition_test where x_pkey = 1 and x_slice = '001';


This simply prunes to a single subpartition containing 10 rows which can be seen from the subpartition level statistics. You can refer to the previous posts for the setup and statistics details.

Here are the EXPLAIN PLAN results from the different versions which were taken from the 10053 trace files (explaining the slightly odd formatting).

11.1.0.7:


------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 2 | | | |
| 1 | PARTITION RANGE SINGLE | | 9 | 90 | 2 | 00:00:01 | 2 | 2 |
| 2 | PARTITION LIST SINGLE | | 9 | 90 | 2 | 00:00:01 | 2 | 2 |
| 3 | TABLE ACCESS FULL | PARTITION_TEST| 9 | 90 | 2 | 00:00:01 | 4 | 4 |
------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
3 - filter("X_PKEY"=1)


10.2.0.4:


------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 2 | | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 38 | 2 | 00:00:01 | 2 | 2 |
| 2 | PARTITION LIST SINGLE | | 1 | 38 | 2 | 00:00:01 | 2 | 2 |
| 3 | TABLE ACCESS FULL | PARTITION_TEST| 1 | 38 | 2 | 00:00:01 | 4 | 4 |
------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
3 - filter("X_PKEY"=1)


10.2.0.3:


------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 2 | | | |
| 1 | PARTITION RANGE SINGLE | | 10 | 100 | 2 | 00:00:01 | 2 | 2 |
| 2 | PARTITION LIST SINGLE | | 10 | 100 | 2 | 00:00:01 | 2 | 2 |
| 3 | TABLE ACCESS FULL | PARTITION_TEST| 10 | 100 | 2 | 00:00:01 | 4 | 4 |
------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
3 - filter("X_PKEY"=1)


So according to the EXPLAIN PLAN output 11.1.0.7 and 10.2.0.3 get it right, but 10.2.0.4 reports an incorrect cardinality.

Checking the 10053 optimizer trace file reveals some interesting details:

11.1.0.7:


BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: PARTITION_TEST Alias: PARTITION_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 10 #Blks: 1 AvgRowLen: 10.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [3]
#Rows: 10 #Blks: 1 AvgRowLen: 10.00
#Rows: 10 #Blks: 1 AvgRowLen: 10.00
Access path analysis for PARTITION_TEST
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for PARTITION_TEST[PARTITION_TEST]
Column (#2):
NewDensity:0.004950, OldDensity:0.000495 BktCnt:1010, PopBktCnt:1010, PopValCnt:2, NDV:2

Table: PARTITION_TEST Alias: PARTITION_TEST
Card: Original: 10.000000 Rounded: 9 Computed: 9.03 Non Adjusted: 9.03
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 8055
Resp_io: 2.00 Resp_cpu: 8055
Best:: AccessPath: TableScan
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 9.03 Bytes: 0

***************************************


11.1.0.7 correctly identifies the subpartition (10 rows, 1 block). What seems to be odd that it reports for the column #2 (X_SLICE) apparently partition level statistics (1010 BktCnt, Number of Distinct Values: 2), but it doesn't seem to influence the cardinality estimate significantly. When removing any histograms on subpartition level the cardinality estimate is correctly shown as 10.

10.2.0.4:


BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: PARTITION_TEST Alias: PARTITION_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 0 #Blks: 1 AvgRowLen: 0.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [2] (NOT ANALYZED)
#Rows: 0 #Blks: 58593632 AvgRowLen: 1025.00
#Rows: 0 #Blks: 1 AvgRowLen: 0.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#1): X_PKEY(NUMBER) Part#: 2
AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+000 Min: 0 Max: 0
Column (#1): X_PKEY(NUMBER)
AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+000 Min: 0 Max: 0
Column (#2): X_SLICE(VARCHAR2) Part#: 2
AvgLen: 20.00 NDV: 0 Nulls: 0 Density: 0.0000e+000 Min: 0 Max: 0
Column (#2): X_SLICE(VARCHAR2)
AvgLen: 20.00 NDV: 0 Nulls: 0 Density: 0.0000e+000 Min: 0 Max: 0
Table: PARTITION_TEST Alias: PARTITION_TEST
Card: Original: 0 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 7121
Resp_io: 2.00 Resp_cpu: 7121
Best:: AccessPath: TableScan
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 0.00 Bytes: 0
***************************************


This looks like a mess, and it is indeed. You'll get always an estimated cardinality of 0 (sanity checked to 1) in the 10.2.0.4 patch set if you prune to a single subpartition. This will screw up almost any plan and hence potentially show abysmal performance.

The issue is tracked with bug Bug 7210921 - STATISTICS WITH SUBPARTITIONS ARE NOT CORRECTLY INTERPRETED IN EXPLAIN PLANS and Bug 7264383 - WRONG STATISTICS 0 ROWS FOR QUERY PRUNED TO SUBPARTITION ON 10.2.0.4. More information can be found in Metalink Note 728056.1. A patch for bug 7210921 can be requested and is already available for a couple of platforms.

10.2.0.3:


BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: PARTITION_TEST Alias: PARTITION_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 1010 #Blks: 4 AvgRowLen: 10.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [1]
#Rows: 1010 #Blks: 4 AvgRowLen: 10.00
#Rows: 1010 #Blks: 1 AvgRowLen: 10.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#1): X_PKEY(NUMBER) Part#: 1
AvgLen: 3.00 NDV: 1 Nulls: 0 Density: 4.9505e-004 Min: 1 Max: 1
Histogram: Freq #Bkts: 1 UncompBkts: 1010 EndPtVals: 1
Column (#1): X_PKEY(NUMBER)
AvgLen: 3.00 NDV: 1 Nulls: 0 Density: 4.9505e-004 Min: 1 Max: 1
Histogram: Freq #Bkts: 1 UncompBkts: 1010 EndPtVals: 1
Column (#2): X_SLICE(VARCHAR2) Part#: 1
AvgLen: 4.00 NDV: 2 Nulls: 0 Density: 4.9505e-004
Histogram: Freq #Bkts: 2 UncompBkts: 1010 EndPtVals: 2
Column (#2): X_SLICE(VARCHAR2)
AvgLen: 4.00 NDV: 2 Nulls: 0 Density: 4.9505e-004
Histogram: Freq #Bkts: 2 UncompBkts: 1010 EndPtVals: 2
Table: PARTITION_TEST Alias: PARTITION_TEST
Card: Original: 1010 Rounded: 10 Computed: 10.00 Non Adjusted: 10.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 2.01 Resp: 2.01 Degree: 0
Cost_io: 2.00 Cost_cpu: 63081
Resp_io: 2.00 Resp_cpu: 63081
Best:: AccessPath: TableScan
Cost: 2.01 Degree: 1 Resp: 2.01 Card: 10.00 Bytes: 0
***************************************


The 10.2.0.3 output reveals that it uses the partition level statistics and only gets it right in this particular case due to an frequency histogram on X_SLICE on the partition level. If you remove this histogram the cardinality estimate will be wrong (505).

So if you're using range-list subpartitioning with 10.2.0.4 and use to prune to a single subpartition you should either use 10.2.0.3 optimizer features using the OPTIMIZER_FEATURES_ENABLE parameter/hint to avoid the mess shown above - though 10.2.0.3 can only use partition level statistics - or get the patch mentioned installed.

In the next part of this series I'm going to show some further oddities when using list partitioning. These apply also to 11gR1.