CODEWITHSUNDEEP
snowflake-cloud-data-platformdata-engineering
seiya
seiya

Reputation: 1534

Search Optimization Service Not Used With AND conjunction

I have a table in Snowflake with the following schema

{
id VARCHAR(100),
first_name VARCHAR(16777216),
last_name VARCHAR(16777216)
...
}

Then I enabled the search optimization on columns 'first_name' and 'last_name', as shown below:

ALTER TABLE person_sso ADD SEARCH OPTIMIZATION ON EQUALITY(id, first_name, last_name);

Then I found if I do spot lookup with only one column for example:

select * from person where first_name = 'george'

or

select * from person where last_name= 'washington'

The search optimization will take effect, and most partitions will be pruned/skipped.

But if I search by both 'first_name' and 'last_name' columns, as shown below:

select * from person_sso where first_name = 'george' and last_name = 'washington';

Then the query will scan all partitions.

The question is: how to make sure the search optimization works for such queries?

Upvotes: 0

Views: 31

Answers (0)

Related Questions