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 <[email protected]>
wrote:
> Hi Tejas,
>
> Did you check the execution plan? Are there any scans?
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/Affinity-tp9744p9943.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
