优化Snowflake查询避免全表扫描：多表视图精准查询需求

针对你描述的场景——每个name仅存在于单个category_N表，且id列与表编号一一对应，当前Union All视图查询时会扫描所有表的问题，提供以下几种优化方案：

方案1：基于ID映射的条件过滤视图 #

利用id与表编号的对应关系，在视图中为每个表添加id = N的过滤条件，让Snowflake的查询优化器能通过谓词下推跳过无关表：

create or replace view optimized_all_names_view as
select name, id, last_seen_time_id_utc from category_1 where id = 1
union all
select name, id, last_seen_time_id_utc from category_2 where id = 2
union all
-- ... 依次添加category_3到category_200的分支
union all
select name, id, last_seen_time_id_utc from category_200 where id = 200;

使用方式：查询时同时指定name和id，例如：

select * from optimized_all_names_view where name = 'Sam' and id = 4;

此时Snowflake会仅扫描category_4表，完全跳过其他199张表。

方案2：动态查询存储过程（适用于仅知道name的场景） #

如果查询时仅知道name而不知道id，可以通过构建名称-表名映射表+存储过程实现精准查询：

步骤1：创建映射表并初始化数据 #

-- 创建映射表，存储name与对应表名的关系
create or replace table name_to_category_map (
    name varchar not null primary key,
    category_table varchar not null
);

-- 一次性初始化所有表的映射关系
insert into name_to_category_map
select name, 'category_1' from category_1
union all
select name, 'category_2' from category_2
-- ... 依次添加category_3到category_200的分支
union all
select name, 'category_200' from category_200;

步骤2：创建动态查询存储过程 #

create or replace procedure get_name_details(p_name varchar)
returns table(name varchar, id int, last_seen_time_id_utc timestamp)
language sql
as
$$
declare
    v_target_table varchar;
begin
    -- 根据name获取对应的表名
    select category_table into v_target_table 
    from name_to_category_map 
    where name = p_name;

    -- 动态执行查询，仅扫描目标表
    if (v_target_table is not null) then
        return execute immediate 
            'select name, id, last_seen_time_id_utc from ' || v_target_table || ' where name = ?'
            using p_name;
    else
        -- 未找到匹配name时返回空结果
        return select null, null, null where 1=0;
    end if;
end;
$$;

使用方式：调用存储过程获取结果：

call get_name_details('Sam');

注意事项 #

• 映射表需要定期同步（可通过Snowflake Task定时执行插入/更新逻辑），确保与底层表数据一致。

方案3：使用搜索优化服务（无需修改查询逻辑） #

如果希望保留原视图的使用方式，可给每个category_N表的name列添加搜索优化服务，让Snowflake快速定位到包含目标name的表：

-- 为每个category表的name列添加搜索优化
alter table category_1 add search optimization on (name);
alter table category_2 add search optimization on (name);
-- ... 依次为category_3到category_200执行相同操作
alter table category_200 add search optimization on (name);

添加后，原视图的查询select * from all_names_view where name = 'Sam';会自动仅扫描category_4表，无需修改查询语句。

优缺点说明 #

• 优势：对用户完全透明，无需修改现有查询逻辑。
• 劣势：会增加存储成本（需维护搜索索引），且底层表写入数据时会有额外的索引维护开销。

内容的提问来源于stack exchange，提问作者Sara