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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-611391/