All Forums Database
LUCAS 56 posts Joined 06/09
05 Nov 2015
NO USE OF PI IN VIEW WITH QUALIFY row_number()

Hi all,
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.
Pierre
 

You must sign in to leave a comment.