Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id CA896200B5A for ; Thu, 4 Aug 2016 10:22:40 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id C92D4160AAB; Thu, 4 Aug 2016 08:22:40 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id D1E2B160A7C for ; Thu, 4 Aug 2016 10:22:39 +0200 (CEST) Received: (qmail 29311 invoked by uid 500); 4 Aug 2016 08:22:38 -0000 Mailing-List: contact user-help@ignite.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@ignite.apache.org Delivered-To: mailing list user@ignite.apache.org Received: (qmail 29301 invoked by uid 99); 4 Aug 2016 08:22:38 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd4-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 04 Aug 2016 08:22:38 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd4-us-west.apache.org (ASF Mail Server at spamd4-us-west.apache.org) with ESMTP id 79007C023B for ; Thu, 4 Aug 2016 08:22:38 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -1.727 X-Spam-Level: X-Spam-Status: No, score=-1.727 tagged_above=-999 required=6.31 tests=[HTML_MESSAGE=2, RCVD_IN_DNSWL_MED=-2.3, RP_MATCHES_RCVD=-1.426, SPF_PASS=-0.001] autolearn=disabled Received: from mx2-lw-us.apache.org ([10.40.0.8]) by localhost (spamd4-us-west.apache.org [10.40.0.11]) (amavisd-new, port 10024) with ESMTP id PlP4YQKH9VLp for ; Thu, 4 Aug 2016 08:22:35 +0000 (UTC) Received: from szxga02-in.huawei.com (szxga02-in.huawei.com [119.145.14.65]) by mx2-lw-us.apache.org (ASF Mail Server at mx2-lw-us.apache.org) with ESMTPS id 0716B60E8D for ; Thu, 4 Aug 2016 08:22:33 +0000 (UTC) Received: from 172.24.1.137 (EHLO nkgeml411-hub.china.huawei.com) ([172.24.1.137]) by szxrg02-dlp.huawei.com (MOS 4.3.7-GA FastPath queued) with ESMTP id DLG15910; Thu, 04 Aug 2016 16:19:20 +0800 (CST) Received: from NKGEML513-MBX.china.huawei.com ([169.254.1.179]) by nkgeml411-hub.china.huawei.com ([10.98.56.70]) with mapi id 14.03.0235.001; Thu, 4 Aug 2016 16:19:16 +0800 From: Zhengqingzheng To: user Subject: Ignite Sql query problem using IN clause Thread-Topic: Ignite Sql query problem using IN clause Thread-Index: AdHuKONiRkh79URcTMmmq+gTUdWVWg== Date: Thu, 4 Aug 2016 08:19:16 +0000 Message-ID: Accept-Language: zh-CN, en-US Content-Language: zh-CN X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.135.66.169] Content-Type: multipart/alternative; boundary="_000_A523DFF035BBAE4D9EA7A3961C3308BC48046E06nkgeml513mbxchi_" MIME-Version: 1.0 X-CFilter-Loop: Reflected X-Mirapoint-Virus-RAPID-Raw: score=unknown(0), refid=str=0001.0A020201.57A2FB40.0096,ss=1,re=0.000,recu=0.000,reip=0.000,cl=1,cld=1,fgs=0, ip=169.254.1.179, so=2013-06-18 04:22:30, dmn=2013-03-21 17:37:32 X-Mirapoint-Loop-Id: 2560417b62ab0dc1c1fa823cc9f5d55b archived-at: Thu, 04 Aug 2016 08:22:41 -0000 --_000_A523DFF035BBAE4D9EA7A3961C3308BC48046E06nkgeml513mbxchi_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi there, When we using ignite sql query, we find that IN clause cannot use indexing = when adding extra 'and xxx' clause. Here are the descriptions : If using 'IN' clause like this, and set col1 to be indexed. It works fine. select * from table1 where col1 in (?,?,?); However, if you add more 'and' clause like this: select * from table1 where col1 in (?,?,?) and col2=3D?; it will scan the table without going into indexing search. Any suggestions? Best regards, Kevin --_000_A523DFF035BBAE4D9EA7A3961C3308BC48046E06nkgeml513mbxchi_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi there,

When we using ignite sql query,= we find that IN clause cannot use indexing when adding extra ‘and xx= x’ clause.

Here are the descriptions :

If using ‘IN’ claus= e like this, and set col1 to be indexed. It works fine.

select * from ta= ble1 where col1 in (?,?,?);

However, if you add more ‘= ;and’ clause like this:

select * from ta= ble1 where col1 in (?,?,?) and col2=3D?;

it will scan the table without = going into indexing search.

 

Any suggestions?

 

Best regards,=

Kevin

 

--_000_A523DFF035BBAE4D9EA7A3961C3308BC48046E06nkgeml513mbxchi_--