Hi listers,
Jonathan is absolutely correct as always - we can easily get it using the
following format:
column_stats("OWNER"."TABLE", "COLUMN", scale, length=NN distinct=NN
nulls=NN min=NN max=NN)
Simple example:
// Full example:
https://gist.github.com/xtender/fc3882af2ba3801935bdff2f3c17e567
create table test(a,b) as
select 1, 1 from dual
union all
select 10, 10 from dual connect by level<=10
union all
select 100, rownum from dual connect by level<=100
union all
select 1000, rownum from dual connect by level<=1000
union all
select 1e6, 0 from dual;
call dbms_stats.gather_table_stats('','test',estimate_percent=>100,
method_opt=>'for all columns size 255');
SQL> explain plan for select * from test where a=10;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 70 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 10 | 70 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=10)
SQL> explain plan for select * from test where a=:10;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 222 | 1554 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 222 | 1554 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=TO_NUMBER(:10))
SQL> explain plan for select/*+ column_stats("XTENDER"."TEST", "A", scale,
length=3 distinct=5 nulls=0 min=1 max=1000000) */ * from test where a=10;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 222 | 1554 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 222 | 1554 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=10)
On Wed, Jan 20, 2021 at 12:39 AM Mladen Gogala <gogala.mladen@xxxxxxxxx>
wrote:
Jonathan, I don't think that's possible. Optimizer first collects the
object information which can be manipulated by column_stats, index_stats
and opt_estimate hints. Only after that does the optimizer check the
histograms to determine the actual cardinalities of the row sources.
OPT_PARAM hint, mentioned by Tanel, is probably the only way of bypassing
histograms except dropping them using DBMS_STATS.
Regards
On 1/19/21 2:47 PM, Jonathan Lewis wrote:
I'm just wondering whether some form of the column_stats() hint could
override the use of the histogram.
Regards
Jonathan Lewis
On Tue, 19 Jan 2021 at 06:25, Tanel Poder <tanel@xxxxxxxxxxxxxx> wrote:
Hi Moustafa,Mladen Gogala
If you really need to do this at SQL level, you could use *opt_param*
hint with *_optimizer_use_histograms = false*.
But it would make this query ignore all histograms, not just one on a
specific column:
--
Database Consultanthttp://mgogala.byethost5.com
