about partiton column with date or varchar2？
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
最近有一个需求需要了解关于分区列字段的选择的问题，下面进行了简单的测试：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
SQL> show user USER is "ROGER" SQL> create table tab1 (id number primary key, time date); Table created. SQL> insert into tab1 select rownum, created from sys.dba_objects; 51138 rows created. SQL> commit; Commit complete. SQL> create table tab2 (id number primary key, time varchar2(12)); Table created. SQL> create table tab3 (id number primary key, time number); Table created. SQL> SQL> insert into tab2 2 select rownum, to_char(created, 'yyyy-mm-dd') from sys.dba_objects; 51138 rows created. SQL> insert into tab3 2 select rownum, 3 to_number(to_char(created, 'yyyymmdd')) from sys.dba_objects; 51138 rows created. SQL> commit; Commit complete. SQL> SQL> desc tab1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER TIME DATE SQL> desc tab2 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER TIME VARCHAR2(12) SQL> desc tab3 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER TIME NUMBER SQL> analyze table tab1 compute statistics; Table analyzed. SQL> analyze table tab2 compute statistics; Table analyzed. SQL> analyze table tab3 compute statistics; Table analyzed. SQL> select count(1) from tab1 where id=51111; COUNT(1) ---------- 1 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
SQL> set autot traceonly SQL> select * from tab1 2 where time between to_date('2012-07-06','yyyy-mm-dd') 3 and to_date('2012-07-07','yyyy-mm-dd'); 46 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2211052296 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 181 | 1991 | 68 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB1 | 181 | 1991 | 68 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TIME">=TO_DATE(' 2012-07-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME"<=TO_DATE(' 2012-07-07 00:00:00', 'syyyy-mm -dd hh24:mi:ss')) Statistics ---------------------------------------------------------- 312 recursive calls 0 db block gets 225 consistent gets 0 physical reads 0 redo size 1394 bytes sent via SQL*Net to client 433 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 46 rows processed SQL> select * from tab2 2 where time between '20120706' and '20120707'; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 2156729920 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1136 | 15904 | 68 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB2 | 1136 | 15904 | 68 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TIME">='20120706' AND "TIME"<='20120707') Statistics ---------------------------------------------------------- 312 recursive calls 0 db block gets 253 consistent gets 0 physical reads 0 redo size 325 bytes sent via SQL*Net to client 389 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 0 rows processed SQL> select * from tab3 2 where time between 20120706 and 20120707; 48 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2826512543 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1152 | 10368 | 34 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB3 | 1152 | 10368 | 34 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TIME">=20120706 AND "TIME"<=20120707) Statistics ---------------------------------------------------------- 312 recursive calls 0 db block gets 176 consistent gets 0 physical reads 0 redo size 1359 bytes sent via SQL*Net to client 433 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 48 rows processed |
我们可以看到，对于date，varchar2和number类型，cbo对于card的计算是有差异的，相比之下，date最小。
下面分别来看下3个sql 的10053 trace：
####### 10053 trace
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
------------- tab1 Table Stats:: Table: TAB1 Alias: TAB1 #Rows: 51138 #Blks: 244 AvgRowLen: 16.00 Index Stats:: Index: SYS_C006431 Col#: 1 LVLS: 1 #LB: 95 #DK: 51138 LB/K: 1.00 DB/K: 1.00 CLUF: 126.00 *************************************** SINGLE TABLE ACCESS PATH ----------------------------------------- BEGIN Single Table Cardinality Estimation ----------------------------------------- Column (#2): TIME(DATE) AvgLen: 7.00 NDV: 866 Nulls: 0 Density: 0.0011547 Min: 2455303 Max: 2456121 Table: TAB1 Alias: TAB1 Card: Original: 51138 Rounded: 181 Computed: 180.56 Non Adjusted: 180.56 ----------------------------------------- END Single Table Cardinality Estimation ----------------------------------------- Access Path: TableScan Cost: 68.45 Resp: 68.45 Degree: 0 Cost_io: 68.00 Cost_cpu: 13010734 Resp_io: 68.00 Resp_cpu: 13010734 Best:: AccessPath: TableScan Cost: 68.45 Degree: 1 Resp: 68.45 Card: 180.56 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 *********************** Join order[1]: TAB1[TAB1]#0 *********************** Best so far: Table#: 0 cost: 68.4521 card: 180.5575 bytes: 1991 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000 ********************************* Number of join permutations tried: 1 ********************************* Final - All Rows Plan: Best join order: 1 Cost: 68.4521 Degree: 1 Card: 181.0000 Bytes: 1991 Resc: 68.4521 Resc_io: 68.0000 Resc_cpu: 13010734 Resp: 68.4521 Resp_io: 68.0000 Resc_cpu: 13010734 kkoipt: Query block SEL$1 (#0) ******* UNPARSED QUERY IS ******* SELECT "TAB1"."ID" "ID","TAB1"."TIME" "TIME" FROM "ROGER"."TAB1" "TAB1" WHERE "TAB1"."TIME">=TO_DATE(' 2012-07-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TAB1"."TIME"<=TO_DATE(' 2012-07-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') kkoqbc-subheap (delete addr=0xb72ebf88, in-use=10140, alloc=10552) kkoqbc-end : call(in-use=12996, alloc=32736), compile(in-use=33796, alloc=38000) apadrv-end: call(in-use=12996, alloc=32736), compile(in-use=34332, alloc=38000) sql_id=gjmqcxyagbuq4. Current SQL statement for this session: select * from tab1 where time between to_date('2012-07-06','yyyy-mm-dd') and to_date('2012-07-07','yyyy-mm-dd') ============ Plan Table ============ -------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 68 | | | 1 | TABLE ACCESS FULL | TAB1 | 181 | 1991 | 68 | 00:00:01 | -------------------------------------+-----------------------------------+ 对于tab1： time列的选择性计算为：card=row_nums * selectivity =51138 * 1/(866)=59.0508083 SQL> select count(time) from tab1; COUNT(TIME) ----------- 51138 SQL> select count(distinct time) from tab1; COUNT(DISTINCTTIME) ------------------- 866 SQL> select 1/866 from dual; 1/866 ---------- .001154734 SQL> SQL> select 51138 * 1/(866) from dual; 51138*1/(866) ------------- 59.0508083 |
—— tab2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
QUERY BLOCK TEXT **************** select * from tab2 where time between '20120706' and '20120707' ********************* QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0 fro(0): flg=0 objn=56298 hint_alias="TAB2"@"SEL$1" ***************************** SYSTEM STATISTICS INFORMATION ***************************** Using NOWORKLOAD Stats CPUSPEED: 2398 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: TAB2 Alias: TAB2 #Rows: 51138 #Blks: 244 AvgRowLen: 19.00 Index Stats:: Index: SYS_C006428 Col#: 1 LVLS: 1 #LB: 95 #DK: 51138 LB/K: 1.00 DB/K: 1.00 CLUF: 147.00 *************************************** SINGLE TABLE ACCESS PATH ----------------------------------------- BEGIN Single Table Cardinality Estimation ----------------------------------------- Column (#2): TIME(VARCHAR2) AvgLen: 10.00 NDV: 45 Nulls: 0 Density: 0.022222 Using prorated density: 0.022091 of col #2 as selectivity of out-of-range value pred Table: TAB2 Alias: TAB2 Card: Original: 51138 Rounded: 1136 Computed: 1136.40 Non Adjusted: 1136.40 ----------------------------------------- END Single Table Cardinality Estimation ----------------------------------------- Access Path: TableScan Cost: 68.45 Resp: 68.45 Degree: 0 Cost_io: 68.00 Cost_cpu: 13044475 Resp_io: 68.00 Resp_cpu: 13044475 Best:: AccessPath: TableScan Cost: 68.45 Degree: 1 Resp: 68.45 Card: 1136.40 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 *********************** Join order[1]: TAB2[TAB2]#0 *********************** Best so far: Table#: 0 cost: 68.4532 card: 1136.4000 bytes: 15904 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000 ********************************* Number of join permutations tried: 1 ********************************* Final - All Rows Plan: Best join order: 1 Cost: 68.4532 Degree: 1 Card: 1136.0000 Bytes: 15904 Resc: 68.4532 Resc_io: 68.0000 Resc_cpu: 13044475 Resp: 68.4532 Resp_io: 68.0000 Resc_cpu: 13044475 kkoipt: Query block SEL$1 (#0) ******* UNPARSED QUERY IS ******* SELECT "TAB2"."ID" "ID","TAB2"."TIME" "TIME" FROM "ROGER"."TAB2" "TAB2" WHERE "TAB2"."TIME">='20120706' AND "TAB2"."TIME"<='20120707' kkoqbc-subheap (delete addr=0xb72ebe20, in-use=10140, alloc=10840) kkoqbc-end : call(in-use=12900, alloc=32736), compile(in-use=33380, alloc=33876) apadrv-end: call(in-use=12900, alloc=32736), compile(in-use=33916, alloc=38000) sql_id=f4uv6abzf040v. Current SQL statement for this session: select * from tab2 where time between '20120706' and '20120707' ============ Plan Table ============ -------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 68 | | | 1 | TABLE ACCESS FULL | TAB2 | 1136 | 16K | 68 | 00:00:01 | -------------------------------------+-----------------------------------+ 观察这条信息： Using prorated density: 0.022091 of col #2 as selectivity of out-of-range value pred 重点就是这个density的计算，根据cbo的描述是这样的： density=(20120707-20120706)/(20120712-20100415)+2/num_distinct =1/20297+2/45=.044493713 SQL> select max(time) as max,min(time) as min from tab2; MAX MIN ------------ ------------ 2012-07-12 2010-04-15 SQL> SQL> select count(time) from tab2; COUNT(TIME) ----------- 51138 SQL> select count(distinct time) from tab2; COUNT(DISTINCTTIME) ------------------- 45 |
这里似乎跟cbo 上面描述的出入，这是正常的，当取值超过范围时，
oracle会使用一个估算的selectivity。
—– tab3
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
*********************** Table Stats:: Table: TAB3 Alias: TAB3 #Rows: 51138 #Blks: 118 AvgRowLen: 14.00 Index Stats:: Index: SYS_C006430 Col#: 1 LVLS: 1 #LB: 95 #DK: 51138 LB/K: 1.00 DB/K: 1.00 CLUF: 112.00 *************************************** SINGLE TABLE ACCESS PATH ----------------------------------------- BEGIN Single Table Cardinality Estimation ----------------------------------------- Column (#2): TIME(NUMBER) AvgLen: 5.00 NDV: 45 Nulls: 0 Density: 0.022222 Min: 20100415 Max: 20120712 Table: TAB3 Alias: TAB3 Card: Original: 51138 Rounded: 1152 Computed: 1151.52 Non Adjusted: 1151.52 ----------------------------------------- END Single Table Cardinality Estimation ----------------------------------------- Access Path: TableScan Cost: 34.42 Resp: 34.42 Degree: 0 Cost_io: 34.00 Cost_cpu: 12148266 Resp_io: 34.00 Resp_cpu: 12148266 Best:: AccessPath: TableScan Cost: 34.42 Degree: 1 Resp: 34.42 Card: 1151.52 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 *********************** Join order[1]: TAB3[TAB3]#0 *********************** Best so far: Table#: 0 cost: 34.4221 card: 1151.5169 bytes: 10368 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000 ********************************* Number of join permutations tried: 1 ********************************* Final - All Rows Plan: Best join order: 1 Cost: 34.4221 Degree: 1 Card: 1152.0000 Bytes: 10368 Resc: 34.4221 Resc_io: 34.0000 Resc_cpu: 12148266 Resp: 34.4221 Resp_io: 34.0000 Resc_cpu: 12148266 kkoipt: Query block SEL$1 (#0) ******* UNPARSED QUERY IS ******* SELECT "TAB3"."ID" "ID","TAB3"."TIME" "TIME" FROM "ROGER"."TAB3" "TAB3" WHERE "TAB3"."TIME">=20120706 AND "TAB3"."TIME"<=20120707 kkoqbc-subheap (delete addr=0xb72ebe20, in-use=10140, alloc=10840) kkoqbc-end : call(in-use=12708, alloc=32736), compile(in-use=33380, alloc=33876) apadrv-end: call(in-use=12708, alloc=32736), compile(in-use=33916, alloc=38000) sql_id=byy6pnh4g9s49. Current SQL statement for this session: select * from tab3 where time between 20120706 and 20120707 ============ Plan Table ============ -------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 34 | | | 1 | TABLE ACCESS FULL | TAB3 | 1152 | 10K | 34 | 00:00:01 | -------------------------------------+-----------------------------------+ 对于tab3： time列的选择性计算为：card=row_nums * selectivity =51138*(1/45)=51338*0.022222 SQL> select 51138*0.022222 from dual; 51138*0.022222 -------------- 1136.38864 可以看到实际上最后的card是1151.52，跟上面的1136有点诧异。 |
######### 测试分区
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 |
SQL> create table tab4 (id number primary key, time date); Table created. SQL> insert into tab4 select rownum, created from sys.dba_objects; 51140 rows created. SQL> commit; Commit complete. SQL> create table tab5 (id number primary key, time varchar2(9)) partition by range (time) 2 (partition p1 values less than ('20111101'), 3 partition p2 values less than ('20111201'), 4 partition p3 values less than ('20120101'), 5 partition p4 values less than ('20120201'), 6 partition p5 values less than ('20120301'), 7 partition p6 values less than ('20120401'), 8 partition p7 values less than ('20120501'), 9 partition p8 values less than ('20120601'), 10 partition p9 values less than ('20120701'), 11 partition p10 values less than (maxvalue)) 12 / Table created. SQL> insert into tab5 select * from tab4; 51140 rows created. SQL> commit; Commit complete. SQL> SQL> create table tab6 (id, time) partition by range (time) 2 (partition p1 values less than (to_date('2011-11-1', 'yyyy-mm-dd')), 3 partition p2 values less than (to_date('2011-12-1', 'yyyy-mm-dd')), 4 partition p3 values less than (to_date('2012-1-1', 'yyyy-mm-dd')), 5 partition p4 values less than (to_date('2012-2-1', 'yyyy-mm-dd')), 6 partition p5 values less than (to_date('2012-3-1', 'yyyy-mm-dd')), 7 partition p6 values less than (to_date('2012-4-1', 'yyyy-mm-dd')), 8 partition p7 values less than (to_date('2012-5-1', 'yyyy-mm-dd')), 9 partition p8 values less than (to_date('2012-6-1', 'yyyy-mm-dd')), 10 partition p9 values less than (to_date('2012-7-1', 'yyyy-mm-dd')), 11 partition p10 values less than (maxvalue)) 12 as select id, time from tab4; Table created. SQL> exec dbms_stats.gather_table_stats('ROGER','TAB5',degree=>2); PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_table_stats('ROGER','TAB6',degree=>2); PL/SQL procedure successfully completed. SQL> SQL> desc tab5 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER TIME VARCHAR2(9) SQL> desc tab6 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER TIME DATE SQL> alter system flush shared_pool; System altered. SQL> set autot traceonly SQL> select * from tab6 2 where time between to_date('2012-07-06','yyyy-mm-dd') 3 and to_date('2012-07-07','yyyy-mm-dd'); 46 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2102902811 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 30 | 360 | 3 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 30 | 360 | 3 (0)| 00:00:01 | 10 | 10 | |* 2 | TABLE ACCESS FULL | TAB6 | 30 | 360 | 3 (0)| 00:00:01 | 10 | 10 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TIME"<=TO_DATE(' 2012-07-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME">=TO_DATE(' 2012-07-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) Statistics ---------------------------------------------------------- 1885 recursive calls 0 db block gets 345 consistent gets 9 physical reads 0 redo size 1394 bytes sent via SQL*Net to client 433 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 43 sorts (memory) 0 sorts (disk) 46 rows processed SQL> select * from tab5 2 where time between '20120706' and '20120707'; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 2465561023 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 98 | 3 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 7 | 98 | 3 (0)| 00:00:01 | 10 | 10 | |* 2 | TABLE ACCESS FULL | TAB5 | 7 | 98 | 3 (0)| 00:00:01 | 10 | 10 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TIME"<='20120707' AND "TIME">='20120706') Statistics ---------------------------------------------------------- 577 recursive calls 0 db block gets 107 consistent gets 0 physical reads 0 redo size 325 bytes sent via SQL*Net to client 389 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 0 rows processed SQL> select * 2 from tab5 3 where to_date(time,'dd-mon-yyyy') between 4 to_date('06-jul-2012','dd-mon-yyyy') and 5 to_date('07-jul-2012','dd-mon-yyyy'); no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 317698379 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 129 | 1806 | 70 (2)| 00:00:01 | | | | 1 | PARTITION RANGE ALL| | 129 | 1806 | 70 (2)| 00:00:01 | 1 | 10 | |* 2 | TABLE ACCESS FULL | TAB5 | 129 | 1806 | 70 (2)| 00:00:01 | 1 | 10 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TO_DATE("TIME",'dd-mon-yyyy')>=TO_DATE(' 2012-07-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TO_DATE("TIME",'dd-mon-yyyy')<=TO_DATE(' 2012-07-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 221 consistent gets 0 physical reads 0 redo size 325 bytes sent via SQL*Net to client 389 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed |
我们知道between and是等价于>= and <= 的，其计算selectivity的公式如下： selectivity=(high_limit – low_limit)/(high_value – low_value)+1/num_distinct+1/num_distinct 上面的3个sql查询，我们发现根据这个公式去计算存在一定的差异。 虽然从test来看，似乎有些失败，但是至少我们可以得出如下几个结论： 1. 当列取值超过范围时，oracle针对该列会使用估算的selectivity. 2. 对于分区表按照时间的分区进行的范围分区，对于分区键，不推荐使用除date或timestamp之外的其他的类型。 3. oracle 优化器在处理date，number和varchar2类型时是不同的。 4. 针对CBO一书，第6章节还需要进行大量的测试，毕竟oracle的cbo算法是在不断的改进。
Leave a Reply
You must be logged in to post a comment.