Teradata has the concept of confidence of cardinality estimates - URL>....
In short, the optimizer tries to figure out the amount of “guesswork” that is included into a cardinality estimate.Is there anything similar in Oracle? I am looking for anything - supported or not!
I have a quiet large table (around 140 GB) and i want to create an index on it. I was wondering, before i fire my my create index statement, i know in advance how long will it take to complete? I know after the create index is executed, we can monitor and gets some estimates from longops view. but is there any way we estimate before hand?
Also my database is in archive log mode and what would be the fastest way to create an online index?
this is all about cardinality, i tried caluclating cardinality but this is not matiching with the actual one.
according to the formula given in COST BASED ORACLE FUNDAMENTALS (J Lewis) for below kind of queries
QUERY :
select * from subs where ac_no between 990 and 999 ;
cardinality = num_rows * ( (our_high - our_low) / (table_high - table_low) + 2 / num_distinct) ).
below are the results form database
SQL> select max(ac_no) from subs;
MAX(AC_NO)
------------
111
SQL> select min(ac_no) from subs;
MIN(AC_NO)
------------
[code]...
so applying the formula, we get the below results
((999-990)/(111-1) + 2/(93))*952 = (0.081+0.02)*952=95.2
but when i checked the actaul plan in the database below is the result and to my surprise cardinality is 11
SQL> set autot trace exp
SQL> select * from subs where ac_no between 990 and 999;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=11 Bytes=1947
)
1 0 TABLE ACCESS (FULL) OF 'SUBS' (Cost=5 Card=11 Bytes=1947)
I've got date column with increasing date (populated as sysdate) , in point of time I created index (compute statistics) on that column without histogram (only min/max value).So if I dont regather stats on that column, my cardinality estimate will go lower day after day till value of 1 , right (because out of linear decay)?
View 13 Replies View RelatedI have a table with, for example, three columns: A, B,C.
I execute on this table only one select: CODESELECT * FROM TABLE WHERE A = :1 AND B=:2
Column A has a lot of distinct values (numbers), but B can have only two values: 'Y' or 'N' (cardinality about 50%/50%). It is worth to create index on two columns: A, B? Does query using index on A column will be much slower than using index on A, B?