ARA-R01 practice question 2 of 10

Two queries are run on the customer_address table: create or replace TABLE CUSTOMER_ADDRESS ( CA_ADDRESS_SK NUMBER(38,0), CA_ADDRESS_ID VARCHAR(16)…

Domain Implementing and Deploying a Solution

Two queries are run on the customer_address table: create or replace TABLE CUSTOMER_ADDRESS ( CA_ADDRESS_SK NUMBER(38,0), CA_ADDRESS_ID VARCHAR(16), CA_STREET_NUMBER VARCHAR(IO) CA_STREET_NAME VARCHAR(60), CA_STREET_TYPE VARCHAR(15), CA_SUITE_NUMBER VARCHAR(10), CA_CITY VARCHAR(60), CA_COUNTY VARCHAR(30), CA_STATE VARCHAR(2), CA_ZIP VARCHAR(10), CA_COUNTRY VARCHAR(20), CA_GMT_OFFSET NUMBER(5,2), CA_LOCATION_TYPE VARCHAR(20) ); ALTER TABLE DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS ADD SEARCH OPTIMIZATION ON SUBSTRING(CA_ADDRESS_ID); Which queries will benefit from the use of the search optimization service? (Select TWO).

Answer options

A: select * from DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS Where substring(CA_ADDRESS_ID,1,8)= substring('AAAAAAAAPHPPLBAAASKDJHASLKDJHASKJD',1,8); Correct answer

B: select * from DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS Where CA_ADDRESS_ID= substring('AAAAAAAAPHPPLBAAASKDJHASLKDJHASKJD',1,16);

C: select * from DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS Where CA_ADDRESS_ID LIKE ’%BAAASKD%'; Correct answer

D: select * from DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS Where CA_ADDRESS_ID LIKE '%PHPP%';

E: select * from DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS Where CA_ADDRESS_ID NOT LIKE '%AAAAAAAAPHPPL%';

Correct Answer:

A. select * from DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS Where substring(CA_ADDRESS_ID,1,8)= substring('AAAAAAAAPHPPLBAAASKDJHASLKDJHASKJD',1,8);; C. select * from DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS Where CA_ADDRESS_ID LIKE ’%BAAASKD%';

Explanation

The command ADD SEARCH OPTIMIZATION ON SUBSTRING(CAADDRESSID) enables the search optimization service for queries that filter on substrings of the CAADDRESSID column. Query A uses the predicate substring(CAADDRESSID,1,8) = '...', which is an explicitly supported pattern for this type of optimization. The substring being searched for is 8 characters long. Query C uses the predicate CAADDRESSID LIKE '%BAAASKD%', which is also an explicitly supported pattern. The substring is 7 characters long. Snowflake documentation states that the service works best for substrings that are at least 5 characters long. Both queries A and C meet this criteria and will benefit from the configured search optimization.

Why Incorrect

B. This is an equality predicate. It requires ADD SEARCH OPTIMIZATION ON EQUALITY(...), not ON SUBSTRING(...), to be optimized.
D. The substring 'PHPP' is only 4 characters long. The search optimization service may not provide a significant performance improvement for substrings shorter than 5 characters.
E. NOT LIKE predicates are not supported by the search optimization service, as it is designed to prune data based on inclusion, not exclusion.

References

1. Snowflake Documentation: Search Optimization Service > Search Optimization for Substring and Regular Expression Searches. This section lists the supported predicates for substring optimization, including LIKE '%%' and SUBSTRING(, ...) = ''. It also contains the performance tip: "For substring searches, the search optimization service works best for selective queries that look for substrings that are at least 5 characters long."

2. Snowflake Documentation: ALTER TABLE. This document details the syntax for adding search optimization: ALTER TABLE ... ADD SEARCH OPTIMIZATION ON (), where SUBSTRING is one of the valid search methods. This confirms that the configuration in the question is valid for enabling substring searches.

Keep practicing

Take the free ARA-R01 practice test

Ten exam-style questions with answers and explanations, plus the exam facts and study guides.

Start the free practice test