Frequency histograms - edge cases

Oracle introduced with the 10.2.0.4 patch set a significant change how non-existing values are treated when there is a frequency histogram on a column / expression. See Jonathan Lewis' blog post which is probably the most concise description of the issue. In a nutshell the change is about the following (quoted from Jonathan's post): "If the value you supply does not appear in the histogram, but is inside the low/high range of the histogram then the cardinality will be half the cardinality of the least frequently occurring value that is in the histogram".

I'm still a bit puzzled why Oracle introduced such a significant change to the optimizer with a patch set, but one of the most obvious reasons might be that the change allows to generate frequency histograms using a rather low sample size, because there is no longer a similar threat as before when the frequency histogram misses one of the existing values (which would then return an estimate of 1 if they didn't appear in the histogram).

In fact when using the default DBMS_STATS.AUTO_SAMPLE_SIZE Oracle exactly does this: It uses by default a quite low sample size to perform the additional runs required for each histogram - probably an attempt to minimize the additional work that needs to be done for histogram generation.

In it is however quite interesting to see how exactly this behaviour together with the new treatment of non-existing values can turn into a threat as a recent thread on OTN demonstrated.

Consider the following scenario: You have a column with a highly skewed data distribution; there is a single, very popular value, and a few other, very unpopular values.

Now you have a query type that filters on this column and frequently searches for non-existing values. In order to speed up the query an index has been created on the column, and in order to make the optimizer aware of the fact that the data distribution is highly skewed a histogram is generated, so that the optimizer should favor the index only for those unpopular respectively non-existing values.

The following test case (run on 11.1.0.7) emulates this scenario:

create table t1 (
id number(*, 0)
, t_status number(*, 0)
, vc varchar2(100)
);

-- 1 million rows
-- One very popular value
-- Two very unpopular values
-- in column T_STATUS
insert /*+ append */ into t1 (id, t_status, vc)
with generator as (
select /*+ materialize */
level as id
from
dual
connect by
level <= 10000
)
select /*+ use_nl(v1, v2) */
rownum as id
, case
when rownum <= 10
then 1
when rownum <= 20
then 2
else 0
end as t_status
, rpad('x', 100) as vc
from
generator v1
, generator v2
where
rownum <= 1000000;

commit;

create index t1_idx on t1 (t_status);

exec dbms_stats.gather_table_stats(null, 'T1', method_opt => 'for all columns size 1 for columns t_status size 254')

The first interesting point are the generated column statistics:

SQL>
SQL> -- Note that the basic column statistics
SQL> -- are generated with a much higher sample size
SQL> -- The histogram however was created with a sample size
SQL> -- of 5,000 rows only
SQL> -- Therefore we get three distinct values but only a single bucket
SQL> -- But not always => Potential instability issue
SQL> select
2 column_name
3 , num_distinct
4 , sample_size
5 from
6 user_tab_col_statistics
7 where
8 table_name = 'T1';

COLUMN_NAME NUM_DISTINCT SAMPLE_SIZE
--------------- ------------ -----------
ID 1000000 1000000
T_STATUS 3 5499
VC 1 1000000

SQL>
SQL> -- This results in a single bucket
SQL> -- But not always => Potential instability issue
SQL> select
2 column_name
3 , endpoint_number
4 , endpoint_value
5 from
6 user_tab_histograms
7 where
8 table_name = 'T1'
9 and column_name = 'T_STATUS';

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------- --------------
T_STATUS 5499 0

Notice the inconsistency: The basic column statistics (number of distinct values, low value, high value) obviously have been generated with a much higher sample size (in fact a compute in this case) than the histogram on T_STATUS. The histogram therefore misses the unpopular values and consists only of a single value - the very popular one.

Now watch closely what happens to the cardinality estimates of the non-existing values:

SQL>
SQL> -- The popular value
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status = 0
8 ;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999K| 102M| 4319 (2)| 00:00:52 |
|* 1 | TABLE ACCESS FULL| T1 | 999K| 102M| 4319 (2)| 00:00:52 |
--------------------------------------------------------------------------

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

1 - filter("T_STATUS"=0)

13 rows selected.

SQL>
SQL> -- A non-existing value
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status = 1000
8 ;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500K| 51M| 4316 (2)| 00:00:52 |
|* 1 | TABLE ACCESS FULL| T1 | 500K| 51M| 4316 (2)| 00:00:52 |
--------------------------------------------------------------------------

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

1 - filter("T_STATUS"=1000)

13 rows selected.

SQL>
SQL> -- Two non-existing values
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status in (1000, 2000)
8 ;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 102M| 4325 (3)| 00:00:52 |
|* 1 | TABLE ACCESS FULL| T1 | 1000K| 102M| 4325 (3)| 00:00:52 |
--------------------------------------------------------------------------

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

1 - filter("T_STATUS"=1000 OR "T_STATUS"=2000)

13 rows selected.

SQL>

Ouch, ouch: Whereas the estimate for the popular value is correct, the estimates for the unpopular values are totally way-off - in fact using an IN clause with two non-existing values gets an estimate of all rows contained in the table.

The explanation: Since the least popular value is the single bucket covering virtually all rows, half of it is still 50% of the total cardinality - so two non-existing values in an IN clause end up with a selectivity of 1.

Furthermore the usual decay to values outside the low/high column values doesn't apply here either - no matter what non-existing values get used, the overestimation stays the same.

Update April 2012: It's probably worth to point out that this behaviour of ignoring the low/high values can only be observed when the basic column statistics Number of Distinct Values (NDV) is not consistent with the corresponding histogram. If both are consistent (for example, both show one distinct value) then the out-of-range detection will work as expected.
This also means that since in 10.2 the histogram and the basic column statistics tend to be more consistent than in 11g due to the new AUTO_SAMPLE_SIZE algorithm the problem by default doesn't show up in 10.2.

These overestimates have obviously a significant impact - here the suitable index doesn't get used - more complex plans might turn even into a complete disaster.

The default behaviour that histograms are generated with a much lower sample size than the basic column statistics also introduces a kind of instability - try to run the example several times. Sometimes one of the unpopular values might be caught by the default histogram generation, sometimes not. The effect is dramatic: If one of the unpopular values gets caught, the estimates will be reasonable again, since then the least popular value do have a very low cardinality - if not, you get exactly the opposite result just demonstrated.

If the old behaviour gets re-activated, the results are as expected with the same set of statistics:

SQL>
SQL> -- Switch off new behaviour
SQL> alter session set "_fix_control" = '5483301:off';

Session altered.

SQL>
SQL> -- The popular value
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status = 0
8 ;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999K| 102M| 4319 (2)| 00:00:52 |
|* 1 | TABLE ACCESS FULL| T1 | 999K| 102M| 4319 (2)| 00:00:52 |
--------------------------------------------------------------------------

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

1 - filter("T_STATUS"=0)

13 rows selected.

SQL>
SQL> -- A non-existing value
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status = 1000
8 ;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 546753835

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

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

2 - access("T_STATUS"=1000)

14 rows selected.

SQL>
SQL> -- Two non-existing values
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status in (1000, 2000)
8 ;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3743026710

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 5 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 107 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_IDX | 1 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

3 - access("T_STATUS"=1000 OR "T_STATUS"=2000)

15 rows selected.

Switching back to the former treatment that non-existing values lead to an estimate of 1 will fix this issue, however since this has a potential impact on every execution plan thorough regression testing would be required with this used as a global setting.

Increasing the sample size is another option, if it ensures that unpopular values get caught by the histogram generation, so that the least popular value of the histogram is one with a low cardinality. Note however that this will increase the amount of work necessary to gather the statistics for the histograms.

SQL>
SQL> -- Gather statistics with 100% sample size
SQL> exec dbms_stats.gather_table_stats(null, 'T1', estimate_percent => null, method_opt => 'for all columns size 1 for columns t_status size 254')

PL/SQL procedure successfully completed.

SQL>
SQL> -- Now the statistics are more representative
SQL> select
2 column_name
3 , num_distinct
4 , sample_size
5 from
6 user_tab_col_statistics
7 where
8 table_name = 'T1';

COLUMN_NAME NUM_DISTINCT SAMPLE_SIZE
--------------- ------------ -----------
ID 1000000 1000000
T_STATUS 3 1000000
VC 1 1000000

SQL>
SQL> -- The histogram now covers also the unpopular values
SQL> select
2 column_name
3 , endpoint_number
4 , endpoint_value
5 from
6 user_tab_histograms
7 where
8 table_name = 'T1'
9 and column_name = 'T_STATUS';

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------- --------------
T_STATUS 999980 0
T_STATUS 999990 1
T_STATUS 1000000 2

SQL>
SQL> -- Switch back on new behaviour
SQL> alter session set "_fix_control" = '5483301:on';

Session altered.

SQL>
SQL> -- A non-existing value
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status = 1000
8 ;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 546753835

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

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

2 - access("T_STATUS"=1000)

14 rows selected.

SQL>
SQL> -- Two non-existing values
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status in (1000, 2000)
8 ;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3743026710

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 5 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 107 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_IDX | 1 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

3 - access("T_STATUS"=1000 OR "T_STATUS"=2000)

15 rows selected.

As suggested by Jonathan Lewis in the OTN thread, another elegant solution to the problem of searching for non-existing values would be to add a virtual column that filtered out the popular values. This approach has several advantages if applicable: The index maintained is very small, minimizes the maintenance effort (and might also address index-efficiency related issues in case of frequent updates to the column) and solves the histogram issue since the histogram will only cover the unpopular values:

SQL>
SQL> -- Add a virtual column (the same could be achieved using a function-based index instead for pre-11g versions)
SQL> alter table t1 add (t_status_unpop as (case when t_status != 0 then t_status end));

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'T1', method_opt => 'for columns t_status_unpop size 254')

PL/SQL procedure successfully completed.

SQL>
SQL> -- Since the virtual column only covers
SQL> -- the unpopular values, the histogram
SQL> -- will be very precise even with a low sample size
SQL> select
2 column_name
3 , num_distinct
4 , sample_size
5 from
6 user_tab_col_statistics
7 where
8 table_name = 'T1';

COLUMN_NAME NUM_DISTINCT SAMPLE_SIZE
--------------- ------------ -----------
ID 1000000 1000000
T_STATUS 3 1000000
VC 1 1000000
T_STATUS_UNPOP 2 20

SQL>
SQL> -- The histogram only covers the unpopular values
SQL> select
2 column_name
3 , endpoint_number
4 , endpoint_value
5 from
6 user_tab_histograms
7 where
8 table_name = 'T1'
9 and column_name = 'T_STATUS_UNPOP';

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------- --------------
T_STATUS_UNPOP 10 1
T_STATUS_UNPOP 20 2

SQL>
SQL> -- Two non-existing values
SQL> -- So we don't run into the same problem here
SQL> -- The estimate is reasonable for non-existing values
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status_unpop in (1000, 2000)
8 ;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 108 | 4470 (6)| 00:00:54 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 108 | 4470 (6)| 00:00:54 |
--------------------------------------------------------------------------

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

1 - filter("T_STATUS_UNPOP"=1000 OR "T_STATUS_UNPOP"=2000)

13 rows selected.

I haven't created an index on the virtual column, but the estimate is correct and a suitable index would get used if it existed.

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.