You can translate the question and the replies:

Forcing primary key to remove duplicates from a derived view

Hiwe havearun 600 columns in our derived view and select distinct Option in the output tab would generate a vql with select distinct on all the 600 columns. This makes the query very expensive on the data source ( fully delegated). Is there a way to force pk in the vql generated
primary keys Duplicates
user
27-01-2018 23:12:54 -0500
code

1 Answer

Hi, The DISTINCT clause will always consider all the columns in a select statement - or in this case your derived view - when determining uniqueness. Using a primary key would require a different approach, as you'd need to specify which values to use in situations where one primary key yields multiple records. If the DISTINCT clause is being fully delegated, however, your data source should be able to leverage its index instead of doing a full table scan. I'd suggest starting by confirming that you've gathered statistics on the base data source, and, if necessary, [Gather Statistics](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/optimizing_queries/cost-based_optimization/gathering_the_statistics_of_views) on the Derived View, then compare the performance from there. Hope this helps!
Denodo Team
30-01-2018 13:23:25 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here