Views Flashcards
What are secured views? Can it be applied on both materialized and non materialized views?
Secured views provides data privacy especially while data sharing to other users. It blocks the view DDL from the user, block underlying tables/functions used.
It can be applied to both view types materialized and non materialized views
How you will create secure vews?
CREATE OR REPLACE SECURE VIEW AS
select * from ……
Can you change or update the view definition anytime?
you can only create or replace. Cannot alter whether it is normal view or materialixed veew.
Changing table definition makes the view definition change automatically?
No…view should be manually recreated.
Does secure view impact query performance nnegatively?
Yes.
Query optimizer generally bypasses some optimizations used for normal views. So DE must be careful about creating security views.
How do you make normal view secure view? and can you do vice versa?
Yes. ALTER VIEW set SECURE or unset SECURE.
How do you identify if the view is secure or not?
IS_SECURE flag in information schema or account usage schema.views will provide this information
Are there any pitfalls in still exposing some data privacy while using secured views?
Yes. Ex: If a column is sequence number using auto increment, it will provide some picture to user. Best to use UUID_STRING for random identifiers there or not expose that sequence number columns.
Does SF gives size/byte information on amount of data scanned or total data in secured views?
No - SF does not give storage information on secured views. User might be able to guess based on run time though.
is CURRENT_ROLE or current_user function allowed in SF secured views that is shared to other accounts?
No because the person it was shared does not control role or user in the view.
What are materialized M views?
M view is a precomputed data set used for later use.
It is faster than normal queries.
So if users run some queries frequently on larger tables, M views can be a better option.
Which scenarios where M view very useful even though there are limitations?
- when data is in external tables. M view on external tables will improver performance.
- when query is so frequent/common and on larger tables.
- when query result is very small compared to bigger table
- when query takes very longer to run
- When query result change less frequently than usage of the view. kammi change but adhiga use.
Basically it offers a well performing reduced size table.
Are M views maintained by SF?
Yes M views are automatically maintained by SF whenever base table changes are done.
Data accessed thru M view is always current unless some delete or DDL changes happened in view. DML operations are all shown current in M view
But this takes some cost.
Is M view is faster than cached query results?
No…M view is not faster than caches q result but remember cached result stays active only for 24 hours and goes away when there is any table change.
Does regular view provide performance benefts?
Cant say like that. It provides security benefits.
M views are for performance and security benefits.
What is the role of query optimizer in M views?
Query optimizer can choose between M view and base table depending on what might be best and efficient.
ex: if base table is clustered by a field which is used in query, optimizer might go to table rather than M view.
How do you create/alter/drop/describe/show materialized views?
CREATE MATERIALIZED VIEW
ALTER MATERIALIZED VIEW
DROP MATERIALIZED VIEW
DESCRIBE MATERIALIZED VIEW
SHOW MATERIALIZED VIEWS
Can you do DML (insert/update/delete) on M views?
No. DML operations not allowed…even truncate.
