ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (inlist card)

oracle实验记录 (inlist card)

原创 Linux操作系统 作者:fufuh2o 时间:2009-08-05 14:16:19 0 删除 编辑


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

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426913