A view on a big table (1,8 billions) joined to N smaller tables (500 M or less) doesn't use PI at all when QUALIFY row_number() is added at the end of the SQL:
select cols from View where ID = 12345 (ID is the PI of the big table) is going on with a full scan on big table.
when removing QUALIFY ROW_NUMBER from the view and adding it to the SELECT from View direct access on ID is OK.
how to explain that ? qualify is partitionned on columns from the main table and joined tables, no where condition in the view.
rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.