You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by 胡永亮/Bob <hu...@neusoft.com> on 2016/10/24 07:47:34 UTC
Why doesn't the index be used in my test?
Hi everyone,
I have a model Kc21, akc273 is its one String column .
I create the index in this column, as the following:
@QuerySqlField(index = true)
private String akc273;
Then I load data into cache from oracle, total 47535542 rows.
I execute the sql query to get the execute plan:
SqlFieldsQuery sql = new SqlFieldsQuery(
"explain select BKC231 from Kc21 where akc273 = '王妍'");
logger.info("execute plan:"+cache.query(sql).getAll());
The result is:
execute plan:[[SELECT
BKC231 AS __C0
FROM "Kc21Cache".KC21
/* "Kc21Cache".KC21.__SCAN_ */
WHERE AKC273 = STRINGDECODE('\u738b\u598d')], [SELECT
__C0 AS BKC231
FROM PUBLIC.__T0
/* "Kc21Cache"."merge_scan" */]]
I think this tell me that the index is not used in this sql. Why?
And the query time also very long as the time before creating this index.
Thank your reply. ^V^
Bob
---------------------------------------------------------------------------------------------------
Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s)
is intended only for the use of the intended recipient and may be confidential and/or privileged of
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of this communication is
not the intended recipient, unauthorized use, forwarding, printing, storing, disclosure or copying
is strictly prohibited, and may be unlawful.If you have received this communication in error,please
immediately notify the sender by return e-mail, and delete the original message and all copies from
your system. Thank you.
---------------------------------------------------------------------------------------------------
Re: Re: Why doesn't the index be used in my test?
Posted by Vladislav Pyatkov <vl...@gmail.com>.
Hi Bob,
Make sure ignite-indexing attends in classpath.
And try to remove ccfg.setQueryEntities(qryEntities) from cache
configuration, when you using cfg.setIndexedTypes(Kc21Key.class,
Kc21.class).
On Wed, Oct 26, 2016 at 6:19 AM, 胡永亮/Bob <hu...@neusoft.com> wrote:
> Hi Vladislav
>
> First, thank you for you help, I am sorry I can't read document
> carefully.
>
> After I added CacheConfiguration.setIndexedTypes, no use.
> The result of explaining sql is also:
> sql = new SqlFieldsQuery(
> "explain select BKC231 from Kc21 where akc273 = '王妍'");
>
> CacheConfiguration<K, V> ccfg = new CacheConfiguration<>(cacheName);
> ccfg.setCacheStoreFactory(storeFactory);
> ccfg.setReadThrough(true);
> ccfg.setWriteThrough(true);
>
> Collection<JdbcType> jdbcTypes = new ArrayList<>();
>
> jdbcTypes.add(jdbcTypeKa02(cacheName));
> jdbcTypes.add(jdbcTypeKa06(cacheName));
> jdbcTypes.add(jdbcTypeKc21(cacheName));
> jdbcTypes.add(jdbcTypeKc22(cacheName));
> jdbcTypes.add(jdbcTypeKc24(cacheName));
> jdbcTypes.add(jdbcTypeKc60(cacheName));
>
> storeFactory.setTypes(jdbcTypes.toArray(new
> JdbcType[jdbcTypes.size()]));
>
> // Configure query entities.
> Collection<QueryEntity> qryEntities = new ArrayList<>();
>
> qryEntities.add(queryEntityKa02());
> qryEntities.add(queryEntityKa06());
> qryEntities.add(queryEntityKc21());
> qryEntities.add(queryEntityKc22());
> qryEntities.add(queryEntityKc24());
> qryEntities.add(queryEntityKc60());
>
> ccfg.setQueryEntities(qryEntities);
>
> cfg.setBackups(0);
> cfg.setMemoryMode(CacheMemoryMode.OFFHEAP_TIERED);
> cfg.setAtomicityMode(CacheAtomicityMode.ATOMIC);
> cfg.setCacheMode(CacheMode.PARTITIONED);
> cfg.setIndexedTypes(Kc21Key.class, Kc21.class);
>
> cache = ignite.getOrCreateCache(cfg)
>
> I don't where are the problems? Look forward to your help.
> Thank you.
>
> ------------------------------
> Bob
>
>
> *From:* Vladislav Pyatkov <vl...@gmail.com>
> *Date:* 2016-10-24 17:05
> *To:* user@ignite.apache.org
> *Subject:* Re: Why doesn't the index be used in my test?
> Hi Bob,
>
> If you put annotation on fields then need to use "CacheConfiguration.setIndexedTypes".
> But for Query Entity, you must discribe entiti in configuration
> (QueryEntity.setIndexes) without annotation.
>
> Please, look at [1]
>
> If it doesn't help, provide your query configuration.
>
> [1]: https://apacheignite.readme.io/docs/sql-queries
>
> On Mon, Oct 24, 2016 at 10:47 AM, 胡永亮/Bob <hu...@neusoft.com> wrote:
>
>> Hi everyone,
>>
>> I have a model Kc21, akc273 is its one String column .
>>
>> I create the index in this column, as the following:
>> @QuerySqlField(index = true)
>> private String akc273;
>>
>> Then I load data into cache from oracle, total 47535542 rows.
>>
>> I execute the sql query to get the execute plan:
>>
>>
>>
>> *SqlFieldsQuery sql = new SqlFieldsQuery(
>> "explain select BKC231 from Kc21 where akc273 = '王妍'"); logger.info
>> <http://logger.info>("execute plan:"+cache.query(sql).getAll());*
>>
>> The result is:
>> *execute plan:[[SELECT*
>>
>>
>>
>>
>>
>>
>>
>> * BKC231 AS __C0FROM "Kc21Cache".KC21 /* "Kc21Cache".KC21.__SCAN_ */WHERE AKC273 = STRINGDECODE('\u738b\u598d')], [SELECT __C0 AS BKC231FROM PUBLIC.__T0 /* "Kc21Cache"."merge_scan" */]]
>> *
>>
>> I think this tell me that the index is not used in this sql. Why?
>> And the query time also very long as the time before creating this
>> index.
>>
>> Thank your reply. ^V^
>>
>> Bob
>>
>> ------------------------------------------------------------
>> ---------------------------------------
>> Confidentiality Notice: The information contained in this e-mail and any
>> accompanying attachment(s)
>> is intended only for the use of the intended recipient and may be
>> confidential and/or privileged of
>> Neusoft Corporation, its subsidiaries and/or its affiliates. If any
>> reader of this communication is
>> not the intended recipient, unauthorized use, forwarding, printing,
>> storing, disclosure or copying
>> is strictly prohibited, and may be unlawful.If you have received this
>> communication in error,please
>> immediately notify the sender by return e-mail, and delete the original
>> message and all copies from
>> your system. Thank you.
>> ------------------------------------------------------------
>> ---------------------------------------
>>
>
>
>
> --
> Vladislav Pyatkov
>
>
> ------------------------------------------------------------
> ---------------------------------------
> Confidentiality Notice: The information contained in this e-mail and any
> accompanying attachment(s)
> is intended only for the use of the intended recipient and may be
> confidential and/or privileged of
> Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader
> of this communication is
> not the intended recipient, unauthorized use, forwarding, printing,
> storing, disclosure or copying
> is strictly prohibited, and may be unlawful.If you have received this
> communication in error,please
> immediately notify the sender by return e-mail, and delete the original
> message and all copies from
> your system. Thank you.
> ------------------------------------------------------------
> ---------------------------------------
>
--
Vladislav Pyatkov
Re: Re: Why doesn't the index be used in my test?
Posted by 胡永亮/Bob <hu...@neusoft.com>.
Hi Vladislav
First, thank you for you help, I am sorry I can't read document carefully.
After I added CacheConfiguration.setIndexedTypes, no use.
The result of explaining sql is also:
sql = new SqlFieldsQuery(
"explain select BKC231 from Kc21 where akc273 = '王妍'");
CacheConfiguration<K, V> ccfg = new CacheConfiguration<>(cacheName);
ccfg.setCacheStoreFactory(storeFactory);
ccfg.setReadThrough(true);
ccfg.setWriteThrough(true);
Collection<JdbcType> jdbcTypes = new ArrayList<>();
jdbcTypes.add(jdbcTypeKa02(cacheName));
jdbcTypes.add(jdbcTypeKa06(cacheName));
jdbcTypes.add(jdbcTypeKc21(cacheName));
jdbcTypes.add(jdbcTypeKc22(cacheName));
jdbcTypes.add(jdbcTypeKc24(cacheName));
jdbcTypes.add(jdbcTypeKc60(cacheName));
storeFactory.setTypes(jdbcTypes.toArray(new JdbcType[jdbcTypes.size()]));
// Configure query entities.
Collection<QueryEntity> qryEntities = new ArrayList<>();
qryEntities.add(queryEntityKa02());
qryEntities.add(queryEntityKa06());
qryEntities.add(queryEntityKc21());
qryEntities.add(queryEntityKc22());
qryEntities.add(queryEntityKc24());
qryEntities.add(queryEntityKc60());
ccfg.setQueryEntities(qryEntities);
cfg.setBackups(0);
cfg.setMemoryMode(CacheMemoryMode.OFFHEAP_TIERED);
cfg.setAtomicityMode(CacheAtomicityMode.ATOMIC);
cfg.setCacheMode(CacheMode.PARTITIONED);
cfg.setIndexedTypes(Kc21Key.class, Kc21.class);
cache = ignite.getOrCreateCache(cfg)
I don't where are the problems? Look forward to your help.
Thank you.
Bob
From: Vladislav Pyatkov
Date: 2016-10-24 17:05
To: user@ignite.apache.org
Subject: Re: Why doesn't the index be used in my test?
Hi Bob,
If you put annotation on fields then need to use "CacheConfiguration.setIndexedTypes". But for Query Entity, you must discribe entiti in configuration (QueryEntity.setIndexes) without annotation.
Please, look at [1]
If it doesn't help, provide your query configuration.
[1]: https://apacheignite.readme.io/docs/sql-queries
On Mon, Oct 24, 2016 at 10:47 AM, 胡永亮/Bob <hu...@neusoft.com> wrote:
Hi everyone,
I have a model Kc21, akc273 is its one String column .
I create the index in this column, as the following:
@QuerySqlField(index = true)
private String akc273;
Then I load data into cache from oracle, total 47535542 rows.
I execute the sql query to get the execute plan:
SqlFieldsQuery sql = new SqlFieldsQuery(
"explain select BKC231 from Kc21 where akc273 = '王妍'");
logger.info("execute plan:"+cache.query(sql).getAll());
The result is:
execute plan:[[SELECT
BKC231 AS __C0
FROM "Kc21Cache".KC21
/* "Kc21Cache".KC21.__SCAN_ */
WHERE AKC273 = STRINGDECODE('\u738b\u598d')], [SELECT
__C0 AS BKC231
FROM PUBLIC.__T0
/* "Kc21Cache"."merge_scan" */]]
I think this tell me that the index is not used in this sql. Why?
And the query time also very long as the time before creating this index.
Thank your reply. ^V^
Bob
---------------------------------------------------------------------------------------------------
Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s)
is intended only for the use of the intended recipient and may be confidential and/or privileged of
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of this communication is
not the intended recipient, unauthorized use, forwarding, printing, storing, disclosure or copying
is strictly prohibited, and may be unlawful.If you have received this communication in error,please
immediately notify the sender by return e-mail, and delete the original message and all copies from
your system. Thank you.
---------------------------------------------------------------------------------------------------
--
Vladislav Pyatkov
---------------------------------------------------------------------------------------------------
Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s)
is intended only for the use of the intended recipient and may be confidential and/or privileged of
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of this communication is
not the intended recipient, unauthorized use, forwarding, printing, storing, disclosure or copying
is strictly prohibited, and may be unlawful.If you have received this communication in error,please
immediately notify the sender by return e-mail, and delete the original message and all copies from
your system. Thank you.
---------------------------------------------------------------------------------------------------
Re: Why doesn't the index be used in my test?
Posted by Vladislav Pyatkov <vl...@gmail.com>.
Hi Bob,
If you put annotation on fields then need to use
"CacheConfiguration.setIndexedTypes". But for Query Entity, you must
discribe entiti in configuration (QueryEntity.setIndexes) without
annotation.
Please, look at [1]
If it doesn't help, provide your query configuration.
[1]: https://apacheignite.readme.io/docs/sql-queries
On Mon, Oct 24, 2016 at 10:47 AM, 胡永亮/Bob <hu...@neusoft.com> wrote:
> Hi everyone,
>
> I have a model Kc21, akc273 is its one String column .
>
> I create the index in this column, as the following:
> @QuerySqlField(index = true)
> private String akc273;
>
> Then I load data into cache from oracle, total 47535542 rows.
>
> I execute the sql query to get the execute plan:
>
>
>
> *SqlFieldsQuery sql = new SqlFieldsQuery(
> "explain select BKC231 from Kc21 where akc273 = '王妍'"); logger.info
> <http://logger.info>("execute plan:"+cache.query(sql).getAll());*
>
> The result is:
> *execute plan:[[SELECT*
>
>
>
>
>
>
>
> * BKC231 AS __C0FROM "Kc21Cache".KC21 /* "Kc21Cache".KC21.__SCAN_ */WHERE AKC273 = STRINGDECODE('\u738b\u598d')], [SELECT __C0 AS BKC231FROM PUBLIC.__T0 /* "Kc21Cache"."merge_scan" */]]
> *
>
> I think this tell me that the index is not used in this sql. Why?
> And the query time also very long as the time before creating this
> index.
>
> Thank your reply. ^V^
>
> Bob
>
> ------------------------------------------------------------
> ---------------------------------------
> Confidentiality Notice: The information contained in this e-mail and any
> accompanying attachment(s)
> is intended only for the use of the intended recipient and may be
> confidential and/or privileged of
> Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader
> of this communication is
> not the intended recipient, unauthorized use, forwarding, printing,
> storing, disclosure or copying
> is strictly prohibited, and may be unlawful.If you have received this
> communication in error,please
> immediately notify the sender by return e-mail, and delete the original
> message and all copies from
> your system. Thank you.
> ------------------------------------------------------------
> ---------------------------------------
>
--
Vladislav Pyatkov