OS & Oracle

At least, there will be two ways to handle left outer join, one is the traditional way to handle the case such as t1.x = t2.x (+), and the new way we saw in last article to handle the new ANSI syntax. So we will begin with such two execution plans.

cbo@ORCL> select * from t1 [...]

Oracle guys usually use Oracle native left outer join syntax to do left outer join, such as

cbo@ORCL> select * from t1,t2 where t1.x = t2.x(+);

C X Y [...]

For the following statement, 9i, 10g  and 11g output the different cardinality and cost.

create table t1
nologging
as
select
trunc(dbms_random.value(0,25)) n1,
rpad(’x',40) ind_pad,
trunc(dbms_random.value(0,20)) n2,
lpad(rownum,10,’0′) small_vc,
rpad(’x',200) padding
from
all_objects
where
rownum <= 10000
;
update t1 set n1 = [...]

Two important and related concepts inside CBO is selectivity and cardinality. The cardinality means that how many rows should be returned by CBO after evaluating the predicates. And this values is always equal to (number of input rows)* selectivity. So we can guess that selectivity means that how many percents of the input rows will [...]

SQL tuning is the most important part of performance tuning. Cost based optimizer, which is used to evaluate the cost for the SQL statement and determine which execution plan is suitable for that specified statement, plays a key role in SQL tuning.
So, what is Cost? From the Oracle Document, cost is defined as below:

Cost = [...]