ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tejashwa Kumar Verma <tejashwa.ve...@gmail.com>
Subject Re: Affinity
Date Mon, 09 Jan 2017 15:54:03 GMT
Hi Val,


Did you got the chance to have a look of explain plan?
Please provide your comments. It will be great help thanks

-Tejas

On Mon, Jan 9, 2017 at 11:55 AM, Tejashwa Kumar Verma <
tejashwa.verma@gmail.com> wrote:

> Hi Val,
>
> Please have a look in execution plan of the query and please suggest me if
> you see any improvement.
>
>
> SELECT
>     P._KEY AS __C0,
>     P._VAL AS __C1,
>     P.EQUIPMENTID AS __C2,
>     P.INSTALLBASEID AS __C3,
>     P.MATERIALID AS __C4,
>     P.OVERLENGTHMATERIALID AS __C5,
>     P.DEVICENAME AS __C6,
>     P.MODULE AS __C7,
>     P.SUBMODULE AS __C8,
>     P.SUBSUBMODULE AS __C9,
>     P.SUBSUBSUBMODULE AS __C10,
>     P.PARTNUMBER AS __C11,
>     P.REVISION AS __C12,
>     P.MODELNUMBER AS __C13,
>     P.SERIALNUMBER AS __C14,
>     P.DESCRIPTION AS __C15,
>     P.SOFTWARERELEASE AS __C16,
>     P.MATERIALSTATUS AS __C17,
>     P.ROHS2DEPSITION AS __C18,
>     P.LASTORDERTIME AS __C19,
>     P.EOSTIME AS __C20,
>     P.EOSFLAG AS __C21,
>     P.ITEMGROUPDESCRIPTION AS __C22,
>     P.PRODUCTCATEGORY AS __C23,
>     P.PRODUCTFAMILY AS __C24,
>     P.PRODUCTLINE AS __C25,
>     P.PRODUCTNAME AS __C26,
>     P.ISPARENTIBASE AS __C27,
>     P.SERVICEDECLINE AS __C28,
>     P.SERVICEELIGIBLE AS __C29,
>     P.SERVICEOPTIONAL AS __C30,
>     P.SHIPQTR AS __C31,
>     P.SHIPTIME AS __C32,
>     P.DEADWARRANTYTIME AS __C33,
>     P.SOFTWAREWARRANTYTIME AS __C34,
>     P.WARRANTYSTARTTIME AS __C35,
>     P.WARRANTYENDTIME AS __C36,
>     P.WARRANTYTYPE AS __C37,
>     P.DTWARRANTYTYPE AS __C38,
>     P.REGISTRATIONTIME AS __C39,
>     P.MATERIALGROUP AS __C40,
>     P.MATERIALTYPE AS __C41,
>     P.COMPONENTNUMBER AS __C42,
>     P.TOPCOMPONENTIBASE AS __C43,
>     P.INSTALLEDATTHEATER AS __C44,
>     P.INSTALLEDAT AS __C45,
>     P.INSTALLEDATBPID AS __C46,
>     P.MANAGEDRESLLERBPID AS __C47,
>     P.INSTALLEDTIME AS __C48,
>     P.SERVICEDECLINEREASON AS __C49,
>     P.ASSEMBLYNUMBER AS __C50,
>     P.ASSEMBLYREV AS __C51,
>     P.ASSEMPLYHWREV AS __C52,
>     P.RMANUMBER AS __C53,
>     P.CUSTOMERPONUMBER AS __C54,
>     P.SALESORDERNUMBER AS __C55,
>     P.SALESORDERLINENUMBER AS __C56,
>     P.POSDISTRIBUTOR AS __C57,
>     P.RESELLER AS __C58,
>     P.ENDUSERPARENTNAME AS __C59,
>     P.ENDUSERBPID AS __C60,
>     P.ENDUSERNAME AS __C61,
>     P.ENDUSERPARENTBPID AS __C62,
>     P.INSTALLEDATADDRESSLINE AS __C63,
>     P.INSTALLEDATCITY AS __C64,
>     P.INSTALLEDATCOUNTRY AS __C65,
>     P.INSTALLEDATSTATE AS __C66,
>     P.TYPE AS __C67,
>     P.FPCLCC AS __C68,
>     P.PARENTSERIALNUMBER AS __C69,
>     P.PARENTCHASSISTYPE AS __C70,
>     P.PARENTCHASSISNAME AS __C71,
>     P.PARENTCHASSISRELEASE AS __C72,
>     P.JUNOS AS __C73,
>     P.HASFRU AS __C74,
>     P.HASINVENTORY AS __C75,
>     P.HASVERSION AS __C76,
>     P.HASFPC AS __C77,
>     P.SOURCE AS __C78,
>     P.REMARKS AS __C79,
>     P.DELETED AS __C80,
>     P.HASCHILDS AS __C81,
>     P.CONTRACTID AS __C82,
>     P.CONTRACTSTATUS AS __C83,
>     P.CONTRACTSTARTTIME AS __C84,
>     P.CONTRACTENDTIME AS __C85,
>     P.SUPPORTCOVERAGETYPE AS __C86,
>     P.COVERAGETYPE AS __C87,
>     P.PRODDESCRIPTION AS __C88,
>     P.DUAL AS __C89,
>     P.INSTALLEDATACCID AS __C90,
>     P.MANAGEDRESLLERACCID AS __C91,
>     P.ENDUSERACCID AS __C92,
>     CNT.CONTRACTID AS __C93,
>     CNT.STARTDATE AS __C94,
>     CNT.ENDDATE AS __C95,
>     CNT.SERVICESKU AS __C96,
>     CNT.CONTRACTSTATUS AS __C97,
>     IIB.COUNT AS __C98
> FROM CACHE2.CONTRACT CNT
>     /* CACHE2."contractStatus_idx": CONTRACTSTATUS = 'Active' */
>     /* WHERE CNT.CONTRACTSTATUS = 'Active'
>     */
> INNER JOIN TABLE(JOINID VARCHAR(50)=?1) J
>     /* function */
>     ON 1=1
> INNER JOIN CACHE1.INSTALLBASE P
>     /* CACHE1."installedAtBpid_idx": INSTALLEDATBPID = J.JOINID */
>     ON 1=1
>     /* WHERE (J.JOINID = P.INSTALLEDATBPID)
>         AND ((P.EQUIPMENTID = CNT.EQUIPMENTID)
>         AND (LOWER(P.SERIALNUMBER) LIKE '%d2300%'))
>     */
> INNER JOIN (
>     SELECT
>         SERIALNUMBER,
>         COUNT(*) AS COUNT
>     FROM CACHE1.INSTALLBASE
>     GROUP BY SERIALNUMBER
> ) IIB
>     /* SELECT
>         SERIALNUMBER,
>         COUNT(*) AS COUNT
>     FROM CACHE1.INSTALLBASE
>         /++ CACHE1."serialNumber_idx": SERIALNUMBER IS ?3 ++/
>     WHERE SERIALNUMBER IS ?3
>     GROUP BY SERIALNUMBER
>     /++ group sorted ++/: SERIALNUMBER = P.SERIALNUMBER
>      */
>     ON 1=1
> WHERE ((CNT.CONTRACTSTATUS = 'Active')
>     AND (LOWER(P.SERIALNUMBER) LIKE '%d2300%'))
>     AND ((P.EQUIPMENTID = CNT.EQUIPMENTID)
>     AND ((P.SERIALNUMBER = IIB.SERIALNUMBER)
>     AND (J.JOINID = P.INSTALLEDATBPID)))
> ORDER BY 15 DESC
> LIMIT 10
> Count - SELECT
>     __C0 AS _KEY,
>     __C1 AS _VAL,
>     __C2 AS EQUIPMENTID,
>     __C3 AS INSTALLBASEID,
>     __C4 AS MATERIALID,
>     __C5 AS OVERLENGTHMATERIALID,
>     __C6 AS DEVICENAME,
>     __C7 AS MODULE,
>     __C8 AS SUBMODULE,
>     __C9 AS SUBSUBMODULE,
>     __C10 AS SUBSUBSUBMODULE,
>     __C11 AS PARTNUMBER,
>     __C12 AS REVISION,
>     __C13 AS MODELNUMBER,
>     __C14 AS SERIALNUMBER,
>     __C15 AS DESCRIPTION,
>     __C16 AS SOFTWARERELEASE,
>     __C17 AS MATERIALSTATUS,
>     __C18 AS ROHS2DEPSITION,
>     __C19 AS LASTORDERTIME,
>     __C20 AS EOSTIME,
>     __C21 AS EOSFLAG,
>     __C22 AS ITEMGROUPDESCRIPTION,
>     __C23 AS PRODUCTCATEGORY,
>     __C24 AS PRODUCTFAMILY,
>     __C25 AS PRODUCTLINE,
>     __C26 AS PRODUCTNAME,
>     __C27 AS ISPARENTIBASE,
>     __C28 AS SERVICEDECLINE,
>     __C29 AS SERVICEELIGIBLE,
>     __C30 AS SERVICEOPTIONAL,
>     __C31 AS SHIPQTR,
>     __C32 AS SHIPTIME,
>     __C33 AS DEADWARRANTYTIME,
>     __C34 AS SOFTWAREWARRANTYTIME,
>     __C35 AS WARRANTYSTARTTIME,
>     __C36 AS WARRANTYENDTIME,
>     __C37 AS WARRANTYTYPE,
>     __C38 AS DTWARRANTYTYPE,
>     __C39 AS REGISTRATIONTIME,
>     __C40 AS MATERIALGROUP,
>     __C41 AS MATERIALTYPE,
>     __C42 AS COMPONENTNUMBER,
>     __C43 AS TOPCOMPONENTIBASE,
>     __C44 AS INSTALLEDATTHEATER,
>     __C45 AS INSTALLEDAT,
>     __C46 AS INSTALLEDATBPID,
>     __C47 AS MANAGEDRESLLERBPID,
>     __C48 AS INSTALLEDTIME,
>     __C49 AS SERVICEDECLINEREASON,
>     __C50 AS ASSEMBLYNUMBER,
>     __C51 AS ASSEMBLYREV,
>     __C52 AS ASSEMPLYHWREV,
>     __C53 AS RMANUMBER,
>     __C54 AS CUSTOMERPONUMBER,
>     __C55 AS SALESORDERNUMBER,
>     __C56 AS SALESORDERLINENUMBER,
>     __C57 AS POSDISTRIBUTOR,
>     __C58 AS RESELLER,
>     __C59 AS ENDUSERPARENTNAME,
>     __C60 AS ENDUSERBPID,
>     __C61 AS ENDUSERNAME,
>     __C62 AS ENDUSERPARENTBPID,
>     __C63 AS INSTALLEDATADDRESSLINE,
>     __C64 AS INSTALLEDATCITY,
>     __C65 AS INSTALLEDATCOUNTRY,
>     __C66 AS INSTALLEDATSTATE,
>     __C67 AS TYPE,
>     __C68 AS FPCLCC,
>     __C69 AS PARENTSERIALNUMBER,
>     __C70 AS PARENTCHASSISTYPE,
>     __C71 AS PARENTCHASSISNAME,
>     __C72 AS PARENTCHASSISRELEASE,
>     __C73 AS JUNOS,
>     __C74 AS HASFRU,
>     __C75 AS HASINVENTORY,
>     __C76 AS HASVERSION,
>     __C77 AS HASFPC,
>     __C78 AS SOURCE,
>     __C79 AS REMARKS,
>     __C80 AS DELETED,
>     __C81 AS HASCHILDS,
>     __C82 AS CONTRACTID,
>     __C83 AS CONTRACTSTATUS,
>     __C84 AS CONTRACTSTARTTIME,
>     __C85 AS CONTRACTENDTIME,
>     __C86 AS SUPPORTCOVERAGETYPE,
>     __C87 AS COVERAGETYPE,
>     __C88 AS PRODDESCRIPTION,
>     __C89 AS DUAL,
>     __C90 AS INSTALLEDATACCID,
>     __C91 AS MANAGEDRESLLERACCID,
>     __C92 AS ENDUSERACCID,
>     __C93 AS CONTID,
>     __C94 AS CONTSTARTDATE,
>     __C95 AS CONTENDDATE,
>     __C96 AS CONTSERVICESKU,
>     __C97 AS CONTSTATUS,
>     __C98 AS COUNT
> FROM PUBLIC.__T0
>     /* CACHE1."merge_scan" */
> ORDER BY 15 DESC
> LIMIT 10 OFFSET 0
>
>
>
> -Tejas
>
> On Sat, Jan 7, 2017 at 1:07 AM, vkulichenko <valentin.kulichenko@gmail.com
> > wrote:
>
>> Hi Tejas,
>>
>> Did you check the execution plan? Are there any scans?
>>
>> -Val
>>
>>
>>
>> --
>> View this message in context: http://apache-ignite-users.705
>> 18.x6.nabble.com/Affinity-tp9744p9943.html
>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>
>

Mime
View raw message