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 < [email protected]> 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 <[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.705 >> 18.x6.nabble.com/Affinity-tp9744p9943.html >> Sent from the Apache Ignite Users mailing list archive at Nabble.com. >> > >
