gistfile1.txt

***************with Auto_sample_size ******************

cw1pc5y2pjfyq

insert /*+ append */ into sys.ora_temp_1_ds_2571455 SELECT /*+  parallel(t,4) parallel_index(t,4) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  */"TD_ID","AID","DB_TNUM", rowid SYS_DS_ALIAS_0  from "USER1"."TAB1" sample (   .0094875083)  t  UNION ALL SELECT  "TD_ID", "AID", "DB_TNUM", SYS_DS_ALIAS_0 FROM sys.ora_temp_1_ds_2571455 WHERE 1 = 0 

b72ufk48b6u7c

select substrb(dump(val,16,0,64),1,240) ep,  freq, cdn, ndv, (sum(pop) over()) popcnt,  (sum(pop*freq) over()) popfreq,  substrb(dump(max(val) over(),16,0,64),1,240) maxval,  substrb(dump(min(val) over(),16,0,64),1,240) minval  from (select val, freq,  (sum(freq) over()) cdn, (count(*) over()) ndv,  (case when freq > ((sum(freq) over())/254)  then 1  else 0 end) pop from  (select /*+  parallel(t,4) parallel_index(t,4) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  */ max("DB_TNUM")  val, count("DB_TNUM") freq  from "USER1"."TAB1" t  where "DB_TNUM" is not null  group by "DB_TNUM")) order by val "USER1"."TAB1" sample (   .0094875083)  t  UNION ALL SELECT  "TD_ID", "AID", "DB_TNUM", SYS_DS_ALIAS_0 FROM sys.ora_temp_1_ds_2571455 WHERE 1 = 0 

1zbwf260h70my

select /*+ opt_param('_optimizer_use_auto_indexes' 'on')   parallel_index(t, "TAB1_IX1",4)  dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  no_expand index_ffs(t,"TAB1_IX1") */ count(*) as nrw,approx_count_distinct(sys_op_lbid(175373,'L',t.rowid)) as nlb,null as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "USER1"."TAB1" t where "TD_ID" is not null


***************with sample_size=>50 ******************

89dt78xh804cf

select substrb(dump(val,16,0,64),1,240) ep, cnt from (select /*+ no_expand_table(t) index_rs(t)   parallel(t,4) parallel_index(t,4) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  */max("OAR") val,count(*) cnt  from "USER1"."TAB1" sample ( 50.0000000000)  t  where "OAR" is not null  group by "OARSP") order by val

3sdnkagq7gt86

select substrb(dump(val,16,0,64),1,240) ep, cnt from (select /*+ no_expand_table(t) index_rs(t)   parallel(t,4) parallel_index(t,4) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  */max("TTYP") val,count(*) cnt  from "USER1"."TAB1" sample ( 50.0000000000)  t  where "TTYP" is not null  group by "TTYP") order by val

d3mqrf289f3nt

select min(minbkt),maxbkt,substrb(dump(min(val),16,0,64),1,240) minval,substrb(dump(max(val),16,0,64),1,240) maxval,sum(rep) sumrep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from (select val,min(bkt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(val) repsq from (select /*+ no_expand_table(t) index_rs(t)   parallel(t,4) parallel_index(t,4) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  */"AID" val, ntile(254) over (order by "AID") bkt  from "USER1"."TAB1" sample ( 50.0000000000)  t  where "AID" is not null) group by val) group by maxbkt order by maxbkt

7u3y5q5t9226c

select substrb(dump(val,16,0,64),1,240) ep, cnt from (select /*+ no_expand_table(t) index_rs(t)   parallel(t,4) parallel_index(t,4) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  */max("UBD") val,count(*) cnt  from "USER1"."TAB1" sample ( 50.0000000000)  t  where "UBD" is not null  group by "UBD") order by val

3kypnshptczh3

select substrb(dump(val,16,0,64),1,240) ep, cnt from (select /*+ no_expand_table(t) index_rs(t)   parallel(t,4) parallel_index(t,4) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  */"PART_DT" val,count(*) cnt  from "USER1"."TAB1" sample ( 50.0000000000)  t  where "PART_DT" is not null  group by "PART_DT") order by val

azu57mnsuj70v

select /*+  parallel(t,4) parallel_index(t,4) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  */count(*), count("DB_TNUM"), count(distinct "DB_TNUM"), sum(sys_op_opnsize("DB_TNUM")), substrb(dump(min("DB_TNUM"),16,0,64),1,240), substrb(dump(max("DB_TNUM"),16,0,64),1,240) from "USER1"."TAB1" sample ( 50.0000000000)  t 

g2r7a388fhquj

select /*+ opt_param('_optimizer_use_auto_indexes' 'on')   parallel_index(t, "TAB1_IX1",4)  dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  no_expand index_ffs(t,"TAB1_IX1") */ count(*) as nrw,count(distinct sys_op_lbid(175373,'L',t.rowid)) as nlb,count(distinct "TD_ID") as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "USER1"."TAB1" sample block ( 50.0000000000,110)  t where "TD_ID" is not null