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.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.