2025 Performance and Resource Optimization Flashcards
What type of queries does Search Optimization help improve performance on
- Point lookup queries that return a few distinct rows.
- Substring and regular expression searches
- Queries on fields in VARIANT, OBJECT, and ARRAY columns that use certain types of predicates
- Queries that use selected geospatical functions
Search optimization works best for a query in which at least one of the columns has a minimum of WHAT distinct values
100 k
What is the minimum table size to get a benefit from Search Optimization
10 gb
How does Search Optimization and Cluserting keys fit in
Search optimization is best suited for cases where users query tables on columns beside the cluster key
What permissions are required to implement search optimization on a table
Ownership
ADD SEARCH OPTIMIZATION (on schema level)
To improve the performance on range searches, which method should you look at, Clustering or Search Optimization
Clustering, Search only speeds up equality searches
Is search optimization enabled on all columns or a select set of keys
All columns
What costs are associated with Search Optimization
Compute and Storage
What cost are associated with Clustering
Compute and Storage
Which type of searches can benefit from a materialized view, equality or range
both
What costs are associated with Materialized Views
Storage and Compute
What edition is required for Search Optimization
Enterprise and above
How can you tell how much it might cost to enable Search Optimization on a table
SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS(‘<t1>' , <search_>)</search_></t1>
What objects do not support Search Optimization
External tables
Materialized Views
Column concatenation
Analytical expressions
Casts on table columns
How long are results cached
24 hours or until the underlying table is updated
When a result is retrieved from the cache, what happens to the retention period
It is reset for a max of 31 days
How can you bypass the results cache in a session
alter session set USE_CACHED_RESULT = false
For local disk cache, warehouse cache, ssd cache, and data cache, what determines the size of the cache?
The warehouse size
What happens to a warehouse’s cache when the warehouse is suspended?
It loses the data cache. It rebuilt when the data is resumed
Search optimization service works best with a column that has a minimum of how many distinct values
100 k
What costs are associated with Materialized View
Storage and Compute
What costs are associated with Query Acceleration Service
Compute
