See why hundreds of industry leaders trust Secoda to unlock their data's full potential.
See why hundreds of industry leaders trust Secoda to unlock their data's full potential.
Get the guideSnowflake's Search Optimization Service (SOS) is a feature designed to enhance the performance of highly selective queries on the Snowflake data platform. By creating a secondary data structure known as the Search Access Path, SOS tracks which values appear in micro-partitions, significantly optimizing the scanning process for quicker query responses.
create or replace table test_table (id int, c1 int, c2 string, c3 date) as select * from values
(1, 3, '4', '1985-05-11'),
(2, 4, '3', '1996-12-20'),
(3, 2, '1', '1974-02-03'),
(4, 1, '2', '2004-03-09'),
(5, null, null, null);
alter table test_table add search optimization;
This code snippet demonstrates how to create a table and apply search optimization in Snowflake. The alter table statement adds the search optimization feature to the table, enabling faster query performance.
Snowflake's SOS provides several key benefits:
Assess whether a table is suitable for SOS based on its query patterns and data structure.
select * from information_schema.tables where table_name = 'test_table';
This query retrieves information about the table, helping to determine if it is a good candidate for search optimization.
Use Snowflake tools to estimate the build and maintenance costs of the Search Access Path.
select system$estimate_search_optimization_costs('test_table');
This function provides an estimate of the costs associated with implementing search optimization on the specified table.
Start building the Search Access Paths for the selected tables and columns.
alter table test_table add search optimization;
This command initiates the creation of the Search Access Path, enhancing query performance for the table.
Write queries that leverage the search paths for better performance.
select * from test_table where id = 2;
select * from test_table where c2 = '1';
These queries utilize the Search Access Path to quickly retrieve the desired data, significantly improving performance.
search_optimization_progress column in SHOW TABLES.Built for data teams, designed for everyone so you can get more from your data stack.
© 2024 Secoda, Inc. All rights reserved.
By using this website, you accept our Terms of Use and Privacy Policy.
