oracle 谓词越界测试

drop table test_obj;
create table test_obj(obj_id int,obj_date date);
create index  test_obj_idx1 on test_obj(obj_id);
create index   test_obj_date  on test_obj(obj_date);

DECLARE
i INT;
j INT;
BEGIN
  for i in 1 .. 10 LOOP
      for j in 1 .. 10000 loop
           insert into test_obj values(j,sysdate+i);
		   end loop;
		   commit;
  end loop;
end;
/



select  obj_date,count(1) from  test_obj group by obj_date order by obj_date;
OBJ_DATE              COUNT(1)
------------------- ----------
2024-01-06 11:04:35      10000
2024-01-07 11:04:35       6007
2024-01-07 11:04:36       3993
2024-01-08 11:04:36      10000
2024-01-09 11:04:36       5820
2024-01-09 11:04:37       4180
2024-01-10 11:04:37      10000
2024-01-11 11:04:37       6126
2024-01-11 11:04:38       3874
2024-01-12 11:04:38      10000
2024-01-13 11:04:38       7766
2024-01-13 11:04:39       2234
2024-01-14 11:04:39      10000
2024-01-15 11:04:39       7085
2024-01-15 11:04:40       2915


 
	
 
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                tabname          => 'TEST_OBJ',
                                estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
								 granularity      => 'GLOBAL',
                                method_opt       => 'for all columns size auto',
                                no_invalidate    => FALSE,
                                degree           => 1,
                                cascade          => TRUE);
END;
/

col low_value for a20
col high_value for a20
select  low_value ,high_value,num_distinct,num_nulls from  DBA_TAB_COL_STATISTICS where table_name='TEST_OBJ' and owner='SCOTT' and COLUMN_NAME='OBJ_DATE';


LOW_VALUE            HIGH_VALUE           NUM_DISTINCT  NUM_NULLS
-------------------- -------------------- ------------ ----------
787C01060C0524       787C010F0C0529                 15          0



col   DUMP(MIN(OBJ_DATE),16) for a50
select min(obj_date),dump(min(obj_date),16) from test_obj;

MIN(OBJ_DATE)       DUMP(MIN(OBJ_DATE),16)
------------------- --------------------------------------------------
2024-01-06 11:04:35 Typ=12 Len=7: 78,7c,1,6,c,5,24


col dump(max(obj_date),16) for a50
select max(obj_date),dump(max(obj_date),16) from test_obj;

MAX(OBJ_DATE)       DUMP(MAX(OBJ_DATE),16)
------------------- --------------------------------------------------
2024-01-15 11:04:40 Typ=12 Len=7: 78,7c,1,f,c,5,29



 


--查看索引统计信息

select INDEX_NAME,BLEVEL,LEAF_BLOCKS,NUM_ROWS,DISTINCT_KEYS,CLUSTERING_FACTOR,PARTITIONED,status  from dba_indexes where table_name='TEST_OBJ' and owner='SCOTT';

INDEX_NAME               BLEVEL LEAF_BLOCKS   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR PAR STATUS
-------------------- ---------- ----------- ---------- ------------- ----------------- --- --------
TEST_OBJ_IDX1                 1         300     100000         10000            100000 NO  VALID
TEST_OBJ_DATE                 1         350     100000            15               249 NO  VALID



 
 

---选择率公式1（无直方图）
----当val > high时，val值越大，越偏离，则选择率越小，当val=high+(high-low) 时,选择率为0 
select max(obj_date)+(max(obj_date)-min(obj_date))  from test_obj;
MAX(OBJ_DATE)+(MAX(
-------------------
2024-01-24 11:04:45
 
 
 
----当val < low时，val值越小，越偏离，则选择率越小， 当val=low-(high-low) 时,选择率为0 
 select min(obj_date)-(max(obj_date)-min(obj_date))  from test_obj;
MIN(OBJ_DATE)-(MAX(
-------------------
2023-12-28 11:04:30


alter session set events '10053 trace name context forever,level 1';
explain plan for
select   obj_id,obj_date  from test_obj
 where obj_id=999
   and obj_date = to_date  ('2024-01-13 17:41:29','yyyy-mm-dd hh24:mi:ss');
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
select * from v$diag_info;

  
--调整obj_date的值，选择率的变化
--density一次比一次小
Using prorated density: 0.074074 of col #2 as selectvity of out-of-range/non-existent value pred

--density一次比一次小
Using prorated density: 0.064815 of col #2 as selectvity of out-of-range/non-existent value pred

--接近0
Using prorated density: 0.000005 of col #2 as selectvity of out-of-range/non-existent value pred