CREATE TABLE AS (with subquery using hints)

[former_member182500]

Hi,

OK, we all know the following is fine

create column table <SCHEMA>.<TABLENAME>  as (

SELECT *

FROM "_SYS_BIC".<VIEW>

);

Creates a table with the resultset from the subquery.

Can anyone confirm if it is a limitation of a subquery within CREATE TABLE AS that hints cannot be used?  Example below.

create column table <SCHEMA>.<TABLENAME>  as (

SELECT *

FROM "_SYS_BIC".<VIEW>

with hint (ignore_view_cache)

);

Many thanks.

[former_member182500 · Accepted solution]

Any idea appreciated, thanks.

[lbreddemann · Accepted solution]

Besides the dubious use case of taking snapshots of calculation views...

I don't know of any restriction that would disable the hints when used in this context.

However, the syntax for CTAS <subquery> doesn't allow for hints.

So, what you can do is to wrap the whole query including the hints into a SQL view and run the CTAS based on the view.

[former_member182500 · Accepted solution]

"Besides the dubious use case of taking snapshots of calculation views..." - you refer to hint ignore_view_cache?  Very specific use case as a card in the back pocket after all model optimization and hardware architecture options have been exhausted.

Thanks for the suggestion, tested and works, very much appreciated, cheers!