Collations & Caching - 5

DATA_RETENTION_TIME_IN_DAYS is set to 1 at the account level, 30 on DB_A, and 15 on DB_A.SchemaA.

You create a table in DB_A.SchemaA without specifying a retention time. What will the table use?

• 1 day

• 15 days

• 30 days

Answer explanation

If no time travel value is set on an object,

it will inherit the value used by the

closest enclosing object.

Using the default collation method, in

what order will the following values be

listed in the results if you do an ORDER

BY on the column?

Hot Dogs

Hot dogs

hamburgers

pizza

Mac 'n Cheese

anchovies

Answer explanation

With UTF-8, upper-case letters will

always be sorted before lower-case.

You issue this query:

SELECT * FROM my_table;

Followed by this query:

SELECT * from my_table;

Which cache will help increase the

speed of this query?

Answer explanation

The data cache. The query result cache

would not be used in this instance,

because the queries are not identical

(FROM versus from).

You run this query on a cold

warehouse:

SELECT

col1, col2, col3

FROM

my_table

WHERE

col1 > 128.02

What cache, if any, will be used?

Answer explanation

The metadata cache will be used by the

query optimizer, to prune out micro-

partitions that are not relevant to the

query.

However, if the query was run previously,

none of the micro-partitions relevant to

the query were changed, the persisted

result is still available, and the user’s role

has the right privileges to the objects in

the query, the query results cache will be

used instead.

In the query profile shown below, which

cache is being referred to?

Answer explanation

"Percentage scanned from cache"

refers to the data cache.

Rate each of the following statements as

TRUE or FALSE:

A. Virtual warehouse costs are always

decreased by setting a very short

auto-suspend time.

B. The metadata cache is used in every

query that has a WHERE clause.

C. Data cache use is all-or-nothing.

D. The timer on the query result cache

resets every time someone uses the

result.

Answer explanation

A. FALSE – shutting down the virtual

warehouse clears the data cache, so

shutting a warehouse down too

quickly can reduce use of that cache

B. TRUE – even if the query cannot be

pruned, the metadata is examined

C. FALSE – you can use as much of the

data cache as is relevant to your

query

D. TRUE – it resets to 24 hours

What are the three types of cache?