Cardinality is the predicted number of rows.
Game is like that;
Step 1: Give me the conditions..
Step 2: Hımm.
Step 3: CBO Guess the # of Cardinality.
Yes the Result of the Guess is Cardinality.
And Taking this into consideration Oracle Makes Plan.
But You May spoil the game.
While he is in step 2 you can say: "Answer is 14." "hıhıhıhı"
Then it works as you want.
select * from audience where month_no = 12;
here cardinality is 100
but i know the result; it is 106 for month_no = 12
then...
select /*+ cardinality(x 106) */ * from audience x where month_no = 12;
Quick Result: I am Smarter than CBO and I can give cardinality as a hint ;)
1 comment:
Two undocumented hints, being undocumented, there is no definitive statement of what they do or how they work.
The 10053 dump is once place where their effects can be directly observed in the line(s) of the SINGLE TABLE ACCESS PATH concerning table cardinality.
The selectivity replaces the filter factor percentages derived from the statistics with its own percentage.
Cardinality replaces the end result with its own absolute value, so not taking into account the actual size of the table.
The following are the adjusted queries and the respective table cardinality rows:
select /*+SELECTIVITY (a 0.5)*/ * from test_a a
where col_b like 'Tue%' and col_c like 'J%'
TABLE: TEST_A ORIG CDN: 200 ROUNDED CDN: 100 CMPTD CDN: 100
select /*+CARDINALITY (a 5)*/ * from test_a a
where col_b like 'Tue%' and col_c like 'J%'
TABLE: TEST_A ORIG CDN: 200 ROUNDED CDN: 5 CMPTD CDN: 5
Post a Comment