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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-611391/，如需轉載，請註明出處，否則將追究法律責任。