inlist oracle,oracle实验记录 （inlist card）

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod

PL/SQL Release 10.1.0.2.0 - Production

CORE    10.1.0.2.0      Production

TNS for 32-bit Windows: Version 10.1.0.2.0 - Production

NLSRTL Version 10.1.0.2.0 - Production

SQL> create table t3 (a int);

Table created.

SQL> declare

2  begin

3  for i in 1..10000 loop

4  insert into t3 values(i);

5  commit;

6  end loop;

7  end;

8  /

PL/SQL procedure successfully completed.

SQL> select * from t3 where a=1;

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=1 Bytes=13)

1    0   TABLE ACCESS (FULL) OF 'T3' (TABLE) (Cost=2 Card=1 Bytes=1

3)

SQL> select column_name,num_distinct ,density from user_tab_col_statistics where

table_name='T3';

no rows selected

SQL> select count(*) from t3;

COUNT(*)

----------

10000

_optimizer_cost_model          CHOOSE

optimizer_dynamic_sampling     1

SQL> exec dbms_stats.gather_table_stats('sys','t3');

PL/SQL procedure successfully completed.

SQL> select column_name,num_distinct ,density from user_tab_col_statistics where

table_name='T3';

COLUMN_NAME                    NUM_DISTINCT    DENSITY

------------------------------ ------------ ----------

A                                     10000      .0001

SQL> select * from t3 where a in (1,2);

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=8 Card=2 Bytes=6)

1    0   TABLE ACCESS (FULL) OF 'T3' (TABLE) (Cost=8 Card=2 Bytes=6

)

SQL> select * from t3 where a in (1,2,3);

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=8 Card=3 Bytes=9)

1    0   TABLE ACCESS (FULL) OF 'T3' (TABLE) (Cost=8 Card=3 Bytes=9

)

SQL> select 10000*(0.0001+0.0001+0.0001-3/(10000*10000) + 3/(10000*10000*1000))f

rom dual;

10000*(0.0001+0.0001+0.0001-3/(10000*10000)+3/(10000*10000*1000))

-----------------------------------------------------------------

2.9997003

8i