oracle实验记录 （选择率）

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 t1 (a varchar2(10), b char(10));

Table created.

SQL> insert into t1 values ('aa','aa');

1 row created.

SQL> insert into t1 values ('ab','ab');

1 row created.

SQL> insert into t1 values ('ac','ab');

1 row created.

SQL> insert into t1 values ('bb','bb');

1 row created.

SQL> insert into t1 values ('cc','cc');

1 row created.

SQL> commit;

Commit complete.

begin
 dbms_stats.gather_table_stats(
  ownname   => user,
  tabname   => 't1',
  cascade   => true,
  estimate_percent => null,
  method_opt  =>'for all columns size 10'
 );
end;
/
SQL> column endpoint_value format 999,999,999,999,999,999,999,999,999,999,999,
9 heading "End Value"
  1   select
  2      column_name,
  3  endpoint_value
  4   from
  5      user_tab_histograms
  6   where
  7*     table_name = 'T1'
SQL> /

COLUMN_NA                                        End Value
--------- ------------------------------------------------
A          505,620,189,009,433,000,000,000,000,000,000,000~~~~~~~~~~aa
A          505,640,471,419,036,000,000,000,000,000,000,000~~~~~~~~~~ab
A          505,660,753,828,640,000,000,000,000,000,000,000~~~~~~~~~~ac
A          510,832,768,277,571,000,000,000,000,000,000,000
A          516,045,347,545,709,000,000,000,000,000,000,000
B          505,622,734,252,991,000,000,000,000,000,000,000
B          505,643,016,662,594,000,000,000,000,000,000,000
B          510,835,313,521,129,000,000,000,000,000,000,000
B          516,047,892,789,268,000,000,000,000,000,000,000

9 rows selected.

 

 set autotrace traceonly explain
SQL> select * from t1 where  a between 'aa' and 'ac';~~~~~~~~~~~~~~~~~~~~~~~~~

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=2 Bytes=28)
   1    0   TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=2 Card=2 Bytes=2
          8)

PL/SQL procedure successfully completed.

SQL> select
  2     column_name, num_distinct, density
  3  from       user_tab_columns
  4  where      table_name = 'T1'
  5  ;

COLUMN_NAME                    NUM_DISTINCT    DENSITY
------------------------------ ------------ ----------
A                                         5         .1
B                                         4         .1

SQL> select 5*((505660753828640-505620189009433)/(516047892789268-50562018900943~~~~~~~~~~~~~~~~~~~~~~~~~~~card
3) +2/5   )  from dual;

5*((505660753828640-505620189009433)/(516047892789268-505620189009433)+2/5)
---------------------------------------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~字符借助histogram
                                                                  2.0194505

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~另外一个 实验不收集histogram

SQL> create table t4 (a varchar2(10), b char(10));

Table created.

SQL> insert into t4 values ('aa','aa');

1 row created.

SQL> insert into t4 values ('ab','ab');

1 row created.

SQL> insert into t4 values ('ac','ac');

1 row created.

SQL> insert into t4 values ('bb','bb');

1 row created.

SQL> insert into t4 values ('cc','cc');

1 row created.

SQL> commit;

Commit complete.

  1  select
  2       column_name,
  3    endpoint_value
  4     from
  5        user_tab_histograms
  6     where
  7*      table_name = 'T4'
  8  /

no rows selected~~~~~~~~~~~~~~~~~~~~~~~~~~~~没有histograms

SQL> select * from t4 where  a between 'aa' and 'ac';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=1 Bytes=19)
   1    0   TABLE ACCESS (FULL) OF 'T4' (TABLE) (Cost=2 Card=1 Bytes=1~~~~~~~~~~~~~~~~~用的是8I 那中方法 base cost  optimzer书中CBo_CHAR_VALUE那个函数算出来的,结果是错的
          9)

optimizer_dynamic_sampling     1~~~~~~~~~~~改改 动态采样
SQL> startup force
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                   788068 bytes
Variable Size             145488284 bytes
Database Buffers           41943040 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.

SQL> select * from t4 where  a between 'aa' and 'ac';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=3 Bytes=57)
   1    0   TABLE ACCESS (FULL) OF 'T4' (TABLE) (Cost=2 Card=3 Bytes=5         /~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~动采 太正确了                   
          7)

 

SQL> select * from t4 where  a between 'aa' and 'ac';

A          B
---------- ----------
aa         aa
ab         ab
ac         ac

 

  1   select
  2        column_name,
  3     endpoint_value
  4      from
  5         user_tab_histograms
  6*     where table_name = 'T4'
  7 

no rows selected~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~动采后,不会自动有HISTOGRAMS

  1  begin
  2     dbms_stats.gather_table_stats(
  3             ownname                 => user,
  4             tabname                 => 't4',
  5             cascade                 => true,
  6             estimate_percent        => null,
  7             method_opt              =>'for all columns size 10'
  8     );
  9* end;
 10  /

PL/SQL procedure successfully completed.

SQL>  select
  2        column_name,
  3     endpoint_value
  4      from
  5         user_tab_histograms
  6      where table_name = 'T4'
  7  /

COLUMN_NA                                        End Value
--------- ------------------------------------------------
A          505,620,189,009,433,000,000,000,000,000,000,000
A          505,640,471,419,036,000,000,000,000,000,000,000
A          505,660,753,828,640,000,000,000,000,000,000,000
A          510,832,768,277,571,000,000,000,000,000,000,000
A          516,045,347,545,709,000,000,000,000,000,000,000
B          505,622,734,252,991,000,000,000,000,000,000,000
B          505,643,016,662,594,000,000,000,000,000,000,000
B          505,663,299,072,198,000,000,000,000,000,000,000
B          510,835,313,521,129,000,000,000,000,000,000,000
B          516,047,892,789,268,000,000,000,000,000,000,000

10 rows selected.
SQL> select * from t4 where  a between 'aa' and 'ac';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=2 Bytes=28)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~MD 收集了HISTOGRAM后 card又变成接近了
   1    0   TABLE ACCESS (FULL) OF 'T4' (TABLE) (Cost=2 Card=2 Bytes=2
          8)

SQL> select 5*((505660753828640-505620189009433)/(516047892789268-50562018900943
3)+1/5+1/5) as card from dual;

      CARD
----------
 2.0194505

SQL>

SQL> set autotrace off
SQL> explain plan for select * from t4 where  a between 'aa' and 'ac';

Explained.

SQL> select * from table(dbms_xplan.display);;
select * from table(dbms_xplan.display);
                                       *
ERROR at line 1:
ORA-00911: invalid character

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 176316199

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    28 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T4   |     2 |    28 |     2   (0)| 00:00:01 |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~有必要看看dynamic simple 与histograms
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("A"<='ac' AND "A">='aa')

13 rows selected.

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-607617/，如需转载，请注明出处，否则将追究法律责任。

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