RE: index columns
Date: Sat, 18 Apr 2015 18:34:38 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D928288EA7_at_EXMBX01.thus.corp>
Mohamed,
Your question is exactly the point I was making about Tim's example of yes_or_no with a 99/1 split and a nearly unique account_id. Just because there is a massive skew in one column that doesn't mean you can see ANY skew in the combination when the other column has a large number of distinct values.
In particular - Oracle 11g has a limit of 255 columns in a frequency histogram, 12c has a limit of 2,000 (or maybe 2,047), so your example with one column having 2,712 distinct values CLEARLY can't get a frequency histogram when you combine it with another column.
If BOTH columns had individual histograms the histogram on your vpk_id column would have to be height-balanced (or top-N, or hybrid) and might be identifying one or more extremely popular columns - in which case a popular vpk_id value might frequently coincide with a popular layer_code, so that a height-balanced/top-N/hybrid histogram on the column group could identify the special combination. But since vpk_id doesn't have (need) a histogram then the combination can't need a histogram.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: Mohamed Houri [mohamed.houri_at_gmail.com] Sent: 18 April 2015 15:50
To: Jonathan Lewis
Cc: oracle-l_at_freelists.org
Subject: Re: index columns
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/
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
- 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
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Apr 18 2015 - 20:34:38 CEST