"As I described, non-filtered full scans on MV are more efficient than
filtered full scans on a table"

--> But if your MV has the same primary key as your view, how can it be
possible ?

Can you elaborate on what you mean by "non filtered full scan on MV" ?
Please give us some sample SELECT queries

On Thu, Sep 7, 2017 at 5:11 PM, Alex Kotelnikov <
alex.kotelni...@diginetica.com> wrote:

> In this example all tables and materialized views share all columns. What
> is the question?
>
> On 7 September 2017 at 17:26, sha p <shatestt...@gmail.com> wrote:
>
>> There is one more column "data" here in MView?
>>
>> On 7 Sep 2017 7:49 p.m., "DuyHai Doan" <doanduy...@gmail.com> wrote:
>>
>>> The answer of your question is in the error message. For once it's very
>>> clear. The primary key of your materialized view is EXACTLY the same as for
>>> your base table.
>>>
>>> So the question is what's the point creating this materialized view ...
>>>
>>>
>>>
>>> On Thu, Sep 7, 2017 at 4:01 PM, Alex Kotelnikov <
>>> alex.kotelni...@diginetica.com> wrote:
>>>
>>>> Hey. I have a problem creating a materialized view.
>>>>
>>>> My case is quite similar to
>>>> https://issues.apache.org/jira/browse/CASSANDRA-13564
>>>> but discussion in comments there faded, let me describe by case.
>>>>
>>>> I have a table like
>>>> CREATE TABLE users (
>>>>   site_id int,
>>>>   user_id text,
>>>>   n int,
>>>>   data set<frozen<text>>,
>>>>   PRIMARY KEY ((site_id, user_id), n));
>>>>
>>>> user data is updated and read by PK and sometimes I have to fetch all
>>>> user for some specific site_id. It appeared that full scan by
>>>> token(site_id,user_id) filtered by WHERE site_id = <some id> works much
>>>> slower than unfiltered full scan on
>>>> CREATE MATERIALIZED VIEW users_1 AS
>>>> SELECT site_id, user_id, n, data
>>>> FROM users
>>>> WHERE site_id = 1 AND user_id IS NOT NULL AND n IS NOT NULL
>>>> PRIMARY KEY ((site_id, user_id), n);
>>>>
>>>> yes, you have to do so for each site_id, but it makes such bulk fetches
>>>> much faster. (When I do so, I am always puzzled, why I have to put NOT NULL
>>>> for a part of a primary key).
>>>> And just in case, I tried secondary indices on site_id. For such use
>>>> they improve nothing.
>>>>
>>>>
>>>> But things are changing and we realized that we want to get rid of
>>>> clustering key, n.
>>>>
>>>> DROP MATERIALIZED VIEW users_1;
>>>> DROP TABLE users;
>>>>
>>>> CREATE TABLE users (
>>>> site_id int,
>>>> user_id text,
>>>> data set<text>,
>>>> PRIMARY KEY ((site_id, user_id)));
>>>>
>>>> CREATE MATERIALIZED VIEW users_1 AS
>>>> SELECT site_id, user_id, data
>>>> FROM users
>>>> WHERE site_id = 1 AND user_id IS NOT NULL
>>>> PRIMARY KEY ((site_id, user_id));
>>>>
>>>> And here I get the error I listed in the subject.
>>>> InvalidRequest: Error from server: code=2200 [Invalid query]
>>>> message="No columns are defined for Materialized View other than primary
>>>> key"
>>>>
>>>> But why? I still expect scans to be faster with MV. It appears to be
>>>> possible to create a dummy column and using as a clustering key. That's
>>>> ugly.
>>>> --
>>>>
>>>> Best Regards,
>>>>
>>>>
>>>> *Alexander Kotelnikov*
>>>>
>>>> *Team Lead*
>>>>
>>>> DIGINETICA
>>>> Retail Technology Company
>>>>
>>>> m: +7.921.915.06.28 <+7%20921%20915-06-28>
>>>>
>>>> *www.diginetica.com <http://www.diginetica.com/>*
>>>>
>>>
>>>
>
>
> --
>
> Best Regards,
>
>
> *Alexander Kotelnikov*
>
> *Team Lead*
>
> DIGINETICA
> Retail Technology Company
>
> m: +7.921.915.06.28 <+7%20921%20915-06-28>
>
> *www.diginetica.com <http://www.diginetica.com/>*
>

Reply via email to