Hello:
I am having issues performance tuning sql scripts that I am creating. I want to know how to add indexing and any other methods to better execute my scripts. I think my biggest problem is big tables and full table scans. Please advise if you can advise how i can apply coding to my existing sql statements. Below is an example of a query i created that take long execution times..
SELECT
o.attribute5 "Theater",
o.attribute4 "CUIC" ,
o.name "Contract Operating Unit",
h.SHORT_DESCRIPTION "Short Description",
okh.contract_number "Contract Number",
okh.contract_number_modifier "Contract Number Modifier",
okh.start_date "Contract Start Date",
okh.end_date "Contract End Date",
okh.currency_code "Currency",
okh.estimated_amount "Total Amount",
okh.USER_ESTIMATED_AMOUNT "Comments",
okh.attribute13 "Auto Consolidate",
H.DESCRIPTION "Contract Instructions",
h.comments "Conbil Text",
h.cognomen "User",
hv.STATUS "Status Code",
hv.PARTY_NUMBER "Party Number",
hv.PARTY_NAME,
BT.SITE_USE_CODE,
BT.LOCATION "BT Location",
ST.SITE_USE_CODE,
ST.LOCATION "IA Location Header",
hps.PARTY_SITE_NUMBER "IA Location Line",
OKL.STS_CODE "Line Status",
okl.line_number line,
oksl.line_number subline,
to_char(okl.line_number)||'.'||to_char(oksl.line_number)"Subline",
OKSL.STS_CODE "Subline Status",
d.ETS_COST_CENTER,
o2.name "IB Country Org Unit",
oki.object1_id1 product_number,
cii.SERIAL_NUMBER "Unisys Serial Number",
cii.EXTERNAL_REFERENCE "Vendor Serial Number",
cii.INSTANCE_TYPE_CODE "Product Type",
cii.ACCOUNTING_CLASS_CODE "Accounting Classification",
cii.INSTANCE_USAGE_CODE,
cii.LOT_NUMBER "Lot Number",
cii.ATTRIBUTE2 "Coverage Status/Unisys Status",
DECODE(cii.ATTRIBUTE2,'01','MA - Maintenance'
,'02','LE - Lease'
,'03','WU - Warranty Upgrade'
,'04','IN/UE - Internal Use Equipment'
,'05','RE - Repair'
,'06','OP - Open'
,'07','DC - Data Center'
,'08','LN - Loan'
,'09','WA - Warranty'
,'10','DE - Delete',cii.ATTRIBUTE2) "Unisys Status Description",
cii.ATTRIBUTE3 "Unisys Usage",
cii.ATTRIBUTE5 "IB Load Identifier",
cii.ATTRIBUTE8 "Conversion Control",
cii.ATTRIBUTE9 "Conversion Reference",
cii.ATTRIBUTE7 "SW Product Type",
cii.ATTRIBUTE11 "SW Distr Type",
cii.ATTRIBUTE12 "URC",
cii.ATTRIBUTE14 "SCN",
msi.segment1 STYLE,
msi.DESCRIPTION,
cii.INSTALL_DATE "Installation Date",
oksl.START_DATE "Item Start Date",
oksl.END_DATE "Item End Date",
cii.QUANTITY,
cii.UNIT_OF_MEASURE,
oksl.PRICE_UNIT,
oksl.PRICE_NEGOTIATED,
cv.name "Line Name",
cv.description "Line Coverage Description"
from
apps.okc_k_headers_b okh,
APPS.OKC_K_HEADERS_V H,
apps.okc_k_lines_V okl,
apps.okc_k_lines_V oksl,
apps.okc_k_items oki,
apps.csi_item_instances cii,
apps.hz_party_sites hps,
apps.mtl_system_items_b msi,
apps.hr_all_organization_units o,
apps.hr_all_organization_units o2,
apps.oks_headers_v hv,
apps.oks_ent_headers_v e,
APPS.RA_SITE_USES_MORG ST,
APPS.RA_SITE_USES_MORG BT,
apps.oks_coverages_v cv,
APPS.OKC_K_LINES_B_DFV D,
APPS.OKC_K_HEADERS_V H2
WHERE 1=1 --oki.cle_id = okl.id
and cii.location_id = hps.party_site_id
and cii.last_vld_organization_id = msi.organization_id
and msi.inventory_item_id = cii.inventory_item_id
and cii.instance_id = oki.object1_id1
and oki.cle_id = oksl.id
and oksl.cle_id = okl.id
and oksl.dnz_chr_id = okl.chr_id
and okl.chr_id = okh.id
--and hv.PARTY_NUMBER = 'HX0001530516'
and okh.contract_number = '29933'
--and (msi.segment1 like 'NXP%' or msi.segment1 like 'CSP1%' or msi.segment1 like 'NX 10%' or msi.segment1 like 'CS 10%')
--and (cii.SERIAL_NUMBER in ('93081701386','93083701461','93083701463','93084700528','93084700530','93084700532','93091800228','93091800376','7H041101424','9N020 201577','9N020201575','7H041101528','7H041101142','7H030301970','93063801180','93064000478','7H050700698','7H050600180','7H054600434' ,'7H042800594','7H031401100','7H031401076','93083600262','93083500176','93083400010','93084500374','93084500370','93063100572','93073 100592','7H033702280','93062900268','93082700367','93062900266','93062800274','93062800270','7H034100042','7H033901813','7H043100404' ,'7H043100402','7H030601170','7H030601168','93062800276','9N023200654','9N020300761','93063800950','9N025102458','93084200132','93071 600830','93073400432','93073400394','7H054800080','93080400904','93074700268','93073801172','93073801168','93074301437','93074301417' ,'93073300024','93073300022','93072101588','93072101586','93072101576','93072101572','7H043500642','7H043301170','7H052100992','7H052 100982','7H052100974','7H051900863','7H051900855','7H051101498','7H051101496','7H051101494','7H051101490','7H051101488','7H051101484' ,'7H051101482','7H051101478','7H051101476','7H051101474','7H051101470','7H043701016','7H043201389','7H043200102','7H043100850','7H043 100846','7H043001081','7H043001079','7H043001075','7H043001071','7H043001069','7H043001067','7H043001065','7H043001061','7H043001059' ,'7H043001055','7H043001051','7H043000885','7H034101124','7H033700894','7H033001078','7H033001076','93082601396','93082601392','93082 601390','93082601386','93082501237','93072700270','93072700266','93071301392','93071301388','93072101214','93063600632','7H034101542' ,'7H032600864','93065100014','93074600396','93072501582','93072501540','93092800216','93062100322','93071000222','93071000220','93071 000200','93083000371','93083000272','9N024300906','93061600782','7H050100969','93082900531','93083400020','93083400038','93083400008' ,'93082000621','93082000619','93073100598','93091200338','93091200330','7H030501214','7H052000448','7H040200092','7H052000508','9N024 001098','93072501304','93072501302','93072501300','7H031900870','93082400562','93082400560','93062200982','93062200980','7H053500822' ,'7H053401360','7H053300142','7H053300136','7H042000438','7H042000416','93062100264','7H045001025','7H045001011','7H040501000','93062 000560','93062000562','93062201868','93062400746','93062400748','93061800856','93061900228','93061902286','93062000554','93062000558' ,'93062400998','93062401000','93062401024','93062401026','93062401030','93062400752','93062400866','93062400872','93062400878','93062 400980','7H040200818','7H040601464','7H041100822','7H041601226','7H041601276','7H032500460','93064400608','7H033401188','7H033701268' ,'7H033901270','7H050500990','9N023100512','9N024300158','9N024300398','7H033300424','7H031100730','7H031800860','7H031800864','7H032 601794','7H050100971','7H050101837','7H052100140','7H052700641','7H052900301','7H043200779','7H043301238','7H043600038','7H043701190' ,'93062400590','7H052200759','7H053201043','7H053801458','7H052401027','7H042700524','93061900292','93062400588','7H043100942','93063 601464','93063601466','93063601468','93063701370','93063701376','93062900772','93063101478','93063501386','93063501388','93063501392' ,'93064800648','93064800650','93064802756','93065000498','7H042700120','93063701382','93064701826','93064800616','93064800618','9N023 501263','7H050400868','7H050400866','7H050400864','7H044901429','7H044901335','7H044301342','7H043300962','7H045001575','7H052700629' ,'7H041900684','7H041900658','7H041501060','7H040500250','93074300846','93073600160','93071000330','93063001634','9N024201648','93061 600446','93091300096','93091300078','7H034301334','93071600010','93063801470','93072900502','93071800246','93081900037','7H045002527' ,'7H045002467','7H042600490','7H042600488','93071700402','93083000116','93082700345','7H052300738','7H051701234','93062301108','9N024 300436','9N024300428','7H052701370','7H053801448','7H052900615','7H052600810','93082900787','93082101193','93082101187','93082101185' ,'9N021701542','9N021701310','7H033802162','7H032301096','7H054001055','7H051301819','7H054500038','7H041801314','7H042600486','7H045 100295','7H042700508','7H053900644','7H041900140','93071300860','93071300854','93062501356','93083100536','93074900827','93073500325' ,'93073500323','9N020600004','93070600018','93062900554','93064904687','93063200068','93063000826','93062601168','9N025101276','9N025 101274','7H052600870','7H034801061','7H044901138','7H052600856','93065301632','7H034801061','7H041700822','7H041700820','7H044300958' ,'7H044100362','93062601080','7H042800826','7H041201722','7H050801350','7H040401308','7H041200726','93090900202','93062000637','7H052 900509','7H052900505','7H053300108','7H035102325','7H033400350','7H031800280','7H031800264','7H031700236','7H030300122','7H042101396' ,'7H033500468','7H033101224','7H031800270','7H030301978','93093000094','93092600442','93091400026','93091400032','7H054800906','7H054 800880','7H033600468','7H033500462','93072000804','7H043501120','7H043501114','7H043301242','93081100859','93081100853','93080300016' ,'93080300014','7H050200038','7H045200402','7H045101130','7H045101124','7H045100825','93071500182','93071500114','93072500230') OR
--cii.EXTERNAL_REFERENCE in ('93081701386','93083701461','93083701463','93084700528','93084700530','93084700532','93091800228','93091800376','7H041101424','9N020 201577','9N020201575','7H041101528','7H041101142','7H030301970','93063801180','93064000478','7H050700698','7H050600180','7H054600434' ,'7H042800594','7H031401100','7H031401076','93083600262','93083500176','93083400010','93084500374','93084500370','93063100572','93073 100592','7H033702280','93062900268','93082700367','93062900266','93062800274','93062800270','7H034100042','7H033901813','7H043100404' ,'7H043100402','7H030601170','7H030601168','93062800276','9N023200654','9N020300761','93063800950','9N025102458','93084200132','93071 600830','93073400432','93073400394','7H054800080','93080400904','93074700268','93073801172','93073801168','93074301437','93074301417' ,'93073300024','93073300022','93072101588','93072101586','93072101576','93072101572','7H043500642','7H043301170','7H052100992','7H052 100982','7H052100974','7H051900863','7H051900855','7H051101498','7H051101496','7H051101494','7H051101490','7H051101488','7H051101484' ,'7H051101482','7H051101478','7H051101476','7H051101474','7H051101470','7H043701016','7H043201389','7H043200102','7H043100850','7H043 100846','7H043001081','7H043001079','7H043001075','7H043001071','7H043001069','7H043001067','7H043001065','7H043001061','7H043001059' ,'7H043001055','7H043001051','7H043000885','7H034101124','7H033700894','7H033001078','7H033001076','93082601396','93082601392','93082 601390','93082601386','93082501237','93072700270','93072700266','93071301392','93071301388','93072101214','93063600632','7H034101542' ,'7H032600864','93065100014','93074600396','93072501582','93072501540','93092800216','93062100322','93071000222','93071000220','93071 000200','93083000371','93083000272','9N024300906','93061600782','7H050100969','93082900531','93083400020','93083400038','93083400008' ,'93082000621','93082000619','93073100598','93091200338','93091200330','7H030501214','7H052000448','7H040200092','7H052000508','9N024 001098','93072501304','93072501302','93072501300','7H031900870','93082400562','93082400560','93062200982','93062200980','7H053500822' ,'7H053401360','7H053300142','7H053300136','7H042000438','7H042000416','93062100264','7H045001025','7H045001011','7H040501000','93062 000560','93062000562','93062201868','93062400746','93062400748','93061800856','93061900228','93061902286','93062000554','93062000558' ,'93062400998','93062401000','93062401024','93062401026','93062401030','93062400752','93062400866','93062400872','93062400878','93062 400980','7H040200818','7H040601464','7H041100822','7H041601226','7H041601276','7H032500460','93064400608','7H033401188','7H033701268' ,'7H033901270','7H050500990','9N023100512','9N024300158','9N024300398','7H033300424','7H031100730','7H031800860','7H031800864','7H032 601794','7H050100971','7H050101837','7H052100140','7H052700641','7H052900301','7H043200779','7H043301238','7H043600038','7H043701190' ,'93062400590','7H052200759','7H053201043','7H053801458','7H052401027','7H042700524','93061900292','93062400588','7H043100942','93063 601464','93063601466','93063601468','93063701370','93063701376','93062900772','93063101478','93063501386','93063501388','93063501392' ,'93064800648','93064800650','93064802756','93065000498','7H042700120','93063701382','93064701826','93064800616','93064800618','9N023 501263','7H050400868','7H050400866','7H050400864','7H044901429','7H044901335','7H044301342','7H043300962','7H045001575','7H052700629' ,'7H041900684','7H041900658','7H041501060','7H040500250','93074300846','93073600160','93071000330','93063001634','9N024201648','93061 600446','93091300096','93091300078','7H034301334','93071600010','93063801470','93072900502','93071800246','93081900037','7H045002527' ,'7H045002467','7H042600490','7H042600488','93071700402','93083000116','93082700345','7H052300738','7H051701234','93062301108','9N024 300436','9N024300428','7H052701370','7H053801448','7H052900615','7H052600810','93082900787','93082101193','93082101187','93082101185' ,'9N021701542','9N021701310','7H033802162','7H032301096','7H054001055','7H051301819','7H054500038','7H041801314','7H042600486','7H045 100295','7H042700508','7H053900644','7H041900140','93071300860','93071300854','93062501356','93083100536','93074900827','93073500325' ,'93073500323','9N020600004','93070600018','93062900554','93064904687','93063200068','93063000826','93062601168','9N025101276','9N025 101274','7H052600870','7H034801061','7H044901138','7H052600856','93065301632','7H034801061','7H041700822','7H041700820','7H044300958' ,'7H044100362','93062601080','7H042800826','7H041201722','7H050801350','7H040401308','7H041200726','93090900202','93062000637','7H052 900509','7H052900505','7H053300108','7H035102325','7H033400350','7H031800280','7H031800264','7H031700236','7H030300122','7H042101396' ,'7H033500468','7H033101224','7H031800270','7H030301978','93093000094','93092600442','93091400026','93091400032','7H054800906','7H054 800880','7H033600468','7H033500462','93072000804','7H043501120','7H043501114','7H043301242','93081100859','93081100853','93080300016' ,'93080300014','7H050200038','7H045200402','7H045101130','7H045101124','7H045100825','93071500182','93071500114','93072500230'))
--and hv.STATUS in ('ACTIVE', 'SIGNED', 'ENTERED')
--and OKL.STS_CODE in ('ACTIVE', 'SIGNED', 'ENTERED')
--and OKSL.STS_CODE in ('ACTIVE', 'SIGNED', 'ENTERED')
and h.ID = okh.ID
and okh.id = e.contract_id
and okl.chr_id = e.contract_id
and e.ship_to_site_use_id = ST.site_use_id
and e.bill_to_site_use_id = BT.site_use_id
and okh.authoring_org_id = o.organization_id
and cii.last_vld_organization_id = o2.organization_id
and hv.contract_id = h.ID
and okl.id = cv.SERVICE_CLE_ID
AND oksl.ROW_ID = D.ROW_ID
and h2.id = h.id
group by
okh.contract_number,
OKH.ID,
oki.object1_id1 ,
cii.SERIAL_NUMBER,
cii.EXTERNAL_REFERENCE,
msi.segment1 ,
oksl.START_DATE,
oksl.END_DATE,
oksl.PRICE_NEGOTIATED,
OKL.STS_CODE,
OKSL.STS_CODE,
d.ETS_COST_CENTER,
okl.line_number ,
oksl.line_number ,
hps.party_site_number ,
o.attribute5,
o.attribute4,
o.name,
o2.name,
h.SHORT_DESCRIPTION,
okh.contract_number,
okh.contract_number_modifier,
okh.start_date,
okh.end_date,
okh.currency_code,
okh.estimated_amount,
okh.USER_ESTIMATED_AMOUNT,
okh.attribute13,
H.DESCRIPTION,
h.comments,
h.cognomen,
hv.STATUS,
hv.PARTY_NUMBER,
hv.PARTY_NAME,
ST.LOCATION,
ST.SITE_USE_CODE,
BT.LOCATION,
BT.SITE_USE_CODE,
cv.name,
cv.description,
oksl.PRICE_UNIT,
oksl.PRICE_NEGOTIATED,
cii.QUANTITY,
msi.DESCRIPTION,
cii.ATTRIBUTE2,
cii.ATTRIBUTE3,
cii.ATTRIBUTE8,
cii.LOT_NUMBER ,
cii.INSTALL_DATE ,
cii.ACCOUNTING_CLASS_CODE,
cii.ATTRIBUTE9,
cii.ATTRIBUTE12,
cii.INSTANCE_USAGE_CODE,
cii.UNIT_OF_MEASURE,
cii.ATTRIBUTE14,
cii.ATTRIBUTE5,
cii.INSTANCE_TYPE_CODE,
cii.ATTRIBUTE11,
cii.ATTRIBUTE7,
to_char(okl.line_number)||'.'||to_char(oksl.line_number)
|
] 
Blog
Search
Help
Members
Register
Login
Home
