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/，如需轉載，請註明出處，否則將追究法律責任。