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 Repliesthis 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 am trying to get a line of best fit for a set of data in oracle
TERM Count
TERM_080178
TERM_080258
TERM_08030
TERM_080482
TERM_08050
TERM_08060
TERM_080732
TERM_08080
TERM_090192
[code]...
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 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?
I've below table structure with given table data. I need below given output i.e. a linear output. It should be dynamic i.e. there are only three columns for Manoj and four columns for Arun. If I insert one more row for Arun the new added row should also be reflected in the same row and every column should give same contact type.
CREATE TABLE TEST123
(
PERSON_NAME VARCHAR2(100),
CONTACT_TYPE VARCHAR2(20),
CONTACT_NO VARCHAR2(15)
) ;
[code]...
how go generate this output on single query.
Provided a sample table to determine the sales agents performance below.
Id AgenName Zone1 Zone2 Zone3 Zone4
1 ABC 90 85 65 55
2 SDD 45 67 78 90
3 ERF 85 30 52 45
If in any zone, the sales is less than 40, it should be failed.This can be determined using the below query
select Id, AgenName, Case when Zone1 < 40 or Zone2 < 40 or Zone3 < 40 or Zone4 < 40 Then 'Failed'
Else 'Eligible'
End as Flg_Comm
from sales_table;
But the problem is when the zone grows (zone5, zone6) every time the query requires to be added with or condition.
Is there a way to dynamically determine the result even when no. of columns grow (or added)?