When all other things being equal, pushing the less selective columns
toward the leading edge of the index in order to have a better index
compressibility might be an option.
Below is the conclusion of an article I wrote for all things Oracle about
index design
*http://allthingsoracle.com/index-design-discard-and-sort/
<http://allthingsoracle.com/index-design-discard-and-sort/>*
Conclusion
*Engineering an index should be dictated first by the query predicate part
(where clause, group by and order by). Look carefully to the column you
will use as the leading edge of the index. They should be the ones on which
an equality predicate is applied. You should also have a **“kill two birds
with one stone”** design strategy as far as with one index you can cover
multiple queries, **avoid redundant indexes*
<http://hourim.wordpress.com/2014/03/24/redundant-indexes/>* and cover the
foreign key lock threat. Do not forget the benefit an indexed virtual
column could have on helping the CBO make good guesses (estimations) and
producing attractive small indexes.*
*If a switch in the column order is still able to guaranty the precision
and the use of the index then start your index with the column having the
lowest number of distinct values**. As such you can efficiently compress
your index and give a CBO an extra possible path represented by the **index
skip scan**.*
As per regards to the column group extension created on columns having or
not histogram collected on them, I have an article to be published on that
topic which I have kept for several months waiting to validate one CBO
estimation I am still unable to figure out.
Christian article shows this
“*In other words, it seems that the query optimizer bypasses if the
extension has no histogram AND **histograms exist on the columns** on which
the extension is based.”*
My article shows that if only *one* column of the group of columns forming
the extension has histogram the CBO will by pass it
*1) no histogram at all*
SQL> SELECT column_name, num_distinct, density, histogram
FROM user_tab_col_statistics
WHERE table_name = 'T_EXT_STAT'
AND column_name in
('VPK_ID','LAYER_CODE','SYS_STUMVIRBZA6_$QWEX6DE2NGQA1');
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM
------------------------------ ------------ ---------- ---------------
VPK_ID 2712 .000368732 NONE
LAYER_CODE 4 .25 NONE
SYS_STUMVIRBZA6_$QWEX6DE2NGQA1 4731 .000211372 NONE
SQL> select
count(1)
from
t_ext_stat
where vpk_id = 63148
and layer_code = 'R';
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1
|00:00:00.01 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1
|00:00:00.01 |
|* 2 | INDEX RANGE SCAN| T_EXT_UK_I | 1 | 172 | 338
|00:00:00.01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("VPK_ID"=63148 AND "LAYER_CODE"='R')
Access path analysis for T_EXT_STAT
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T_EXT_STAT[VPK]
SPD: Directive valid: dirid = 17542990197075222359, state = 5, flags = 1,
loc = 1 {EC(98564)[2, 3]}
SPD: Return code in qosdDSDirSetup: EXISTS, estType = TABLE
Column (#2): VPK_ID(NUMBER)
AvgLen: 5 NDV: 2712 Nulls: 0 Density: 0.000000 Min: 0.000000 Max:
62849.000000
Column (#3): LAYER_CODE(VARCHAR2)
AvgLen: 2 NDV: 4 Nulls: 0 Density: 0.000000
Column (#9): SYS_STUMVIRBZA6_$QWEX6DE2NGQA1(NUMBER)
AvgLen: 12 NDV: 4731 Nulls: 0 Density: 0.000000
ColGroup (#2, Index) T_EXT_UK_I
Col#: 2 3 4 CorStregth: -1.00
ColGroup (#1, VC) SYS_STUMVIRBZA6_$QWEX6DE2NGQA1
Col#: 2 3 CorStregth: 2.29
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.0002
Table: T_EXT_STAT Alias: VPK
Card: Original: 813541.000000 Rounded: 172 Computed: 171.96 Non
Adjusted: 171.96
***** Logdef predicate Adjustment ******
The corresponding 10053 trace file clearly shows in this case that the
extension has been used:
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.0002
E-Rows = num_rows(t_ext_stat) * selectivity (SYS_STUMVIRBZA6_$QWEX6DE2NGQA1
);
E-rows = num_rows(t_ext_stat) * 1/(NDV(SYS_STUMVIRBZA6_$QWEX6DE2NGQA1));
E-rows = 813541.000000 * 1/*4731* = 171.959628 rounded to 172
*2) with histogram*
However, collecting histogram changed the CBO estimation
begin
dbms_stats.gather_table_stats
(user
,'t_ext_stat'
,method_opt => 'for all columns size auto'
,cascade => true
,no_invalidate => false
);
end;
/
SQL> SELECT column_name, num_distinct, density, histogram
FROM user_tab_col_statistics
WHERE table_name = 'T_EXT_STAT'
AND column_name in
('VPK_ID','LAYER_CODE','SYS_STUMVIRBZA6_$QWEX6DE2NGQA1');
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM
------------------------------ ------------ ---------- ---------------
SYS_STUMVIRBZA6_$QWEX6DE2NGQA1 4967 .000201329 NONE
LAYER_CODE 4 6.2471E-07 FREQUENCY
VPK_ID 2862 .000349406 NONE
The *layer_code* column, as expected, has been identified as a skewed
column and henceforth a frequency histogram has been gathered on it to
indicate this skewness. There is two remarks which seems to be worth
pointing them out
-
Since one of the column group extension has a histogram why the
extension itself has not been identified as a skewed column as well
-
What happens in this particular case where there is no histogram on the
extension and a histogram on one of the column forming the extension
select
count(1)
from
t_ext_stat
where vpk_id = 63148
and layer_code = 'R';
COUNT(1)
----------
338
SQL_ID d26ra17afbfyh, child number 0
-------------------------------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1
|00:00:00.01 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1
|00:00:00.01 |
|* 2 | INDEX RANGE SCAN| T_EXT_UK_I | 1 | 142 | 338
|00:00:00.01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("VPK_ID"=63148 AND "LAYER_CODE"='R')
A slight degradation on the estimation occurred. Has the CBO used the
extension to compute the 142 estimated cardinality? I don't think so
otherwise it would have given the following estimation:
E-rows = num_rows(t_ext_stat) * 1/(NDV(SYS_STUMVIRBZA6_$QWEX6DE2NGQA1))
E-rows = 803809 * 1/(4967) = 161.829877
In addition, the following line in the 10053 trace file
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
confirmsthat the extended column statistics has not been used. Otherwise we
would have observed, as shown in the first 10053 trace file above, the
following line
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.0002
This in fact confirms that when *one* of the columns on which an extension
has been created has a histogram while the virtual column representing the
extension has no histogram then the extension will not be used by the CBO.
But why Oracle has not collected a frequency histogram on the extension
when it knows that one of the columns participating in the extension posses
a histogram? Does this means that a data skewness is not guaranteed for a
combination of two columns where one column is skewed and the other isn't?
I hope I will publish this article asap
Best regards
Mohamed Houri
