如表所示：
reference 是低基数字段：共有125871行数据
sql如下：
select
count(distinct id_no_des)
from
variable_engine.customer_wide
where
reference = ‘fs1233333tyj’
and register_time >= date_add(now(), interval 24 hour);
查询耗时10分钟
profile如下：
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
|0 |SCALAR GROUP BY | |1 |13670814 |
|1 |└─TABLE FULL SCAN|customer_wide|7309847 |13538330 |
Outputs & filters:
0 - output([T_FUN_COUNT(distinct customer_wide.id_no_des(0x7f793b225510))(0x7f793b224e30)]), filter(nil), rowset=256
group(nil), agg_func([T_FUN_COUNT(distinct customer_wide.id_no_des(0x7f793b225510))(0x7f793b224e30)])
1 - output([customer_wide.id_no_des(0x7f793b225510)]), filter([customer_wide.reference(0x7f793b220a60) = ‘fs1233333tyj’(0x7f793b220310)], [INTERNAL_FUNCTION(customer_wide.register_time(0x7f793b222f50
), 112, 17)(0x7f793b226460) >= date_add(current_timestamp()(0x7f793b221a30), cast(24, VARCHAR(1048576))(0x7f793b2235f0), 3)(0x7f793b2212f0)(0x7f793b2227a0)]), rowset=256
access([customer_wide.reference(0x7f793b220a60)], [customer_wide.register_time(0x7f793b222f50)], [customer_wide.id_no_des(0x7f793b225510)]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false,false],
range_key([customer_wide.user_id(0x7f793b225f70)]), range(MIN ; MAX)always true
Used Hint:
/*+
*/
Qb name trace:
stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1
Outline Data:
/*+
BEGIN_OUTLINE_DATA
FULL(@“SEL$1” “variable_engine”.“customer_wide”@“SEL$1”)
OPTIMIZER_FEATURES_ENABLE(‘4.0.0.0’)
END_OUTLINE_DATA
*/
Optimization Info:
customer_wide:
table_rows:89945318
physical_range_rows:119927122
logical_range_rows:119927122
index_back_rows:0
output_rows:374
table_dop:1
dop_method:Table DOP
avaiable_index_name:[idx_channel_id, idx_id_no_des, idx_user_id, idx_register_time, idx_reference, customer_wide]
pruned_index_name:[idx_channel_id, idx_id_no_des, idx_user_id, idx_register_time]
stats version:1702917098355572
dynamic sampling level:0
Plan Type:
LOCAL
Note:
Degree of Parallelisim is 1 because of table property
有什么办法优化，能秒级查询吗
