Home » RDBMS Server » Performance Tuning » Sql query is slow (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production)
| Sql query is slow [message #588550] |
Wed, 26 June 2013 08:04  |
prashanth7582
Messages: 34
Registered: October 2005
Location: Bangalore
|
Member |
|
|
Hi All,
I have below sql query which is taking more than 3mins to execute in Dev.
SELECT hd.cono,
hd.custno,
hd.externalcono,
cr.stocknosu,
cr.tourno
FROM cohead hd,a_coshipto cr,a_coflx cf
WHERE hd.cono = cr.cono
AND hd.otype = 61
AND EXISTS (SELECT 'x'
FROM corow cr2
WHERE cr2.rowstatus BETWEEN 790 AND 989
AND cr2.cono = cr.cono
AND cr2.tourno = cr.tourno
AND cr.coshiptoseqno = cr2.coshiptoseqno)
AND hd.cono = cf.cono
AND cr.coshiptoseqno = cf.coshiptoseqno
AND cf.corefqual = 'PRINT_FLAG'
AND cf.charvalue IS NULL;
The Explain plan for above query is :
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | 11634 |
| 1 | NESTED LOOPS SEMI | | 1 | 85 | 11634 |
| 2 | HASH JOIN | | 2703 | 176K| 3493 |
| 3 | TABLE ACCESS FULL | A_COFLX | 49602 | 1065K| 1090 |
| 4 | HASH JOIN | | 43144 | 1895K| 2197 |
| 5 | TABLE ACCESS FULL | A_COSHIPTO | 58677 | 916K| 651 |
| 6 | TABLE ACCESS FULL | COHEAD | 42334 | 1198K| 1377 |
| 7 | TABLE ACCESS BY INDEX ROWID| COROW | 10 | 180 | 3 |
| 8 | INDEX RANGE SCAN | COROW_PK | 2 | | 2 |
---------------------------------------------------------------------------
There is an Index on CONO in COHEAD,A_COSHIPTO and A_COFLX tables but it is going for full table scans as specified in above plan.
I tried using some hints to use the Index on A_COFLX which went very costly.
COHEAD table consists 1 row for every Customer Order(CO)
COROW table consists multiple records for every customer order
A_COSHIPTO table consists multiple records which is less than or equal to COROW
A_COFLX table consists multiple records which indicates some flags pertaining to CO.(Arnd 5).
I've attached the TKprof of the same.
Please let me know some ways to improve the above query.
|
|
|
|
| Re: Sql query is slow [message #588551 is a reply to message #588550] |
Wed, 26 June 2013 08:19  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
How many rows does the query return?
hOw many rows in each table?
How many rows in cohead where otype = 61?
How many rows in a_coflx where corefqual = 'PRINT_FLAG'?
|
|
|
|
|
|
| Re: Sql query is slow [message #588605 is a reply to message #588553] |
Wed, 26 June 2013 18:24 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi prashanth7582,
1. Please post results of:
select count(*) from cohead;
select count(*) from a_coshipto;
select count(*) from a_coflx ;
select count(*) from cohead where otype = 61;
select count(*) from a_coflx where corefqual = 'PRINT_FLAG';
select count(*) from a_coflx where charvalue IS NULL;
select count(*) from a_coflx where charvalue IS NULL and corefqual = 'PRINT_FLAG';
2.Please post indexes of each of this tables (name of index, columns indexed by which order) - you can use DBA_IND_COLUMNS for finding this out.
3. Please post for each of the columns in the query if there is a Histogram and what type on them - use DBA_TAB_COLUMNS for finding this info out.
I think this might give us something to start working with.
Regards,
Andrey
|
|
|
|
| Re: Sql query is slow [message #588624 is a reply to message #588605] |
Thu, 27 June 2013 03:14 |
prashanth7582
Messages: 34
Registered: October 2005
Location: Bangalore
|
Member |
|
|
Dear All,
Here is the DDL of Table and Indexes of all tables in the sql.
DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER)
--------------------------------------------------------------------------------
CREATE TABLE "EHDA"."A_COFLX"
( "CONO" NUMBER(8,0) NOT NULL ENABLE,
"COSHIPTOSEQNO" NUMBER(6,0) NOT NULL ENABLE,
"ROWPOS" NUMBER(6,0) NOT NULL ENABLE,
"ROWSUBPOS" NUMBER(2,0) NOT NULL ENABLE,
"ROWSEQ" NUMBER(2,0) NOT NULL ENABLE,
"COREFQUAL" VARCHAR2(30) NOT NULL ENABLE,
"COREFSEQNO" NUMBER(3,0) NOT NULL ENABLE,
"CHARVALUE" VARCHAR2(80),
"DATEVALUE" DATE,
"NUMVALUE" NUMBER(22,7),
"UPDDATE" DATE,
"USERID" VARCHAR2(40),
"LOGINID" VARCHAR2(40)
)
CREATE TABLE "EHDA"."A_COSHIPTO"
( "CONO" NUMBER(8,0) NOT NULL ENABLE,
"COSHIPTOSEQNO" NUMBER(6,0) NOT NULL ENABLE,
"CUSTNO" VARCHAR2(7),
"CUSTNOLVLID" NUMBER(2,0),
"SHIPNAME1" VARCHAR2(30) NOT NULL ENABLE,
"SHIPNAME2" VARCHAR2(30),
"SHIPSTREET1" VARCHAR2(30),
"SHIPSTREET2" VARCHAR2(30),
"SHIPSTREET3" VARCHAR2(30),
"USSTATECD" VARCHAR2(2),
"SHIPZIPCODE" VARCHAR2(20),
"USZIPCODE" VARCHAR2(20),
"SHIPTEL" VARCHAR2(20),
"COUNTRYCD" VARCHAR2(2) NOT NULL ENABLE,
"BUYCENTRE" NUMBER(6,0),
"REGION" VARCHAR2(6),
"DISTRICT" VARCHAR2(6),
"CUREF" VARCHAR2(30),
"TERMSDEL" NUMBER(3,0) NOT NULL ENABLE,
"TRSPMODE" VARCHAR2(2) NOT NULL ENABLE,
"COMARK" VARCHAR2(30),
"SHIPCOMM" VARCHAR2(50),
"SHIPTONO" NUMBER(6,0),
"ROUTINGCD" VARCHAR2(20),
"INVCOLLMARK" VARCHAR2(5),
"SHIPCITY" VARCHAR2(30),
"FREIGHTCHA" VARCHAR2(1),
"COFREIGHT" NUMBER(19,2),
"PACKCHA" VARCHAR2(1),
"COPACK" NUMBER(19,2),
"FREIGHTLATER" VARCHAR2(1),
"TAXCERTNO" VARCHAR2(25),
"REASTAXCD" VARCHAR2(2),
"OUTSIDECITYLMT" VARCHAR2(1),
"LBLIND" VARCHAR2(1),
"STOCKNOSU" NUMBER(3,0),
"TOURNO" NUMBER(10,0),
"WEEKDAYNO" NUMBER(1,0),
"STOPTIMEDATE" DATE,
"COAMT" NUMBER(19,2),
"COWEIGHT" NUMBER(10,3),
"COVOLUME" NUMBER(10,3),
"COQTY" NUMBER(12,3),
"COBRACKETTYPE" VARCHAR2(2),
"COBRACKETTYPEMODE" VARCHAR2(1),
"INSTNO" NUMBER(8,0),
"FUNCNAME" VARCHAR2(10),
"SOTYPECD" NUMBER(3,0),
"SOSYMPTOMCD" VARCHAR2(12),
"SOSERIOUSCD" NUMBER(1,0),
"PARTNO" VARCHAR2(20),
"PLTRSPSYSTEM" VARCHAR2(3),
"TAXFREEZONE" VARCHAR2(1),
"PLACELOAD" VARCHAR2(15),
"PLTRSPCARRIER" VARCHAR2(20),
"TERMSDELEXT" VARCHAR2(30),
"CZBOOKTIME" DATE,
"CZBOOKREF" VARCHAR2(40)
)
CREATE TABLE "EHDA"."COHEAD"
( "CONO" NUMBER(8,0) NOT NULL ENABLE,
"CUSTNO" VARCHAR2(7) NOT NULL ENABLE,
"CUSTNOINT" VARCHAR2(7),
"CUSTCAT" VARCHAR2(3),
"COOBJ" NUMBER(3,0),
"SALESMAN" VARCHAR2(5),
"OURREF" VARCHAR2(5) NOT NULL ENABLE,
"COWDATE" DATE,
"PRELDELAH" VARCHAR2(1),
"PRELREMNO" NUMBER(2,0) NOT NULL ENABLE,
"ROWPOSHIGH" NUMBER(6,0),
"STOCKNO" NUMBER(3,0),
"OTYPE" VARCHAR2(2) NOT NULL ENABLE,
"ACCOUNTCD" VARCHAR2(3),
"COSTATUS" NUMBER(3,0) NOT NULL ENABLE,
"INVSEQNO" NUMBER(6,0),
"SHIPSEQNO" NUMBER(3,0),
"INVCOLLMARK" VARCHAR2(5),
"INVNOLAST" NUMBER(9,0),
"INVDATELAST" DATE,
"DUEDATELAST" DATE,
"CODATE" DATE NOT NULL ENABLE,
"YOURDATE" DATE,
"CREDATE" DATE NOT NULL ENABLE,
"SHIPNAME1" VARCHAR2(30),
"SHIPNAME2" VARCHAR2(30),
"SHIPSTREET1" VARCHAR2(30),
"SHIPTONO" NUMBER(6,0),
"SHIPSTREET2" VARCHAR2(30),
"SHIPSTREET3" VARCHAR2(30),
"SHIPCITY" VARCHAR2(30),
"SHIPZIPCODE" VARCHAR2(20),
"USZIPCODE" VARCHAR2(20),
"USSTATECD" VARCHAR2(2),
"SHIPTEL" VARCHAR2(20),
"COUNTRYCD" VARCHAR2(2),
"CUREF" VARCHAR2(30),
"SHIPCOMM" VARCHAR2(50),
"COMPLDEL" VARCHAR2(1),
"TERMSDEL" NUMBER(3,0),
"COMARK" VARCHAR2(30),
"TERMSDEL1" VARCHAR2(3),
"ZONE" NUMBER(3,0),
"TOURNO" NUMBER(10,0),
"PACKCHA" VARCHAR2(1),
"COPACK" NUMBER(19,2),
"TRSPMODE" VARCHAR2(2),
"EXPCHA" VARCHAR2(1),
"COCHARGE" NUMBER(19,2),
"FREIGHTCHA" VARCHAR2(1),
"COFREIGHT" NUMBER(19,2),
"INSCHA" VARCHAR2(1),
"FORWARDER" VARCHAR2(7),
"DISCGRP" NUMBER(1,0),
"CODISC" NUMBER(5,2) NOT NULL ENABLE,
"INVENCL" VARCHAR2(1),
"DELSTOP" VARCHAR2(1),
"CREDSTOP" VARCHAR2(2),
"TERMSPAY" VARCHAR2(2),
"INVCHA" VARCHAR2(1),
"INVCHARGE" NUMBER(19,2),
"CUVATCD" VARCHAR2(2),
"VAT" VARCHAR2(6),
"VATAMTLAST" NUMBER(19,2),
"COAMTQUAL" NUMBER(10,0) NOT NULL ENABLE,
"COAMT" NUMBER(19,2) NOT NULL ENABLE,
"COWEIGHT" NUMBER(10,3) NOT NULL ENABLE,
"COVOLUME" NUMBER(10,3) NOT NULL ENABLE,
"EXPRESS" VARCHAR2(1),
"FREIGHTLATER" VARCHAR2(1),
"TEXTCD" VARCHAR2(1),
"TEXTNO" NUMBER(8,0),
"DELAYBILL" VARCHAR2(1),
"GLSUBNO" NUMBER(8,0),
"GLSUBTYPE" NUMBER(1,0),
"COCONFCD" VARCHAR2(1) NOT NULL ENABLE,
"RETSHIPNO" VARCHAR2(10),
"INVNOREF" NUMBER(9,0),
"SALESREF" VARCHAR2(5),
"RETRATE" NUMBER(3,1),
"RETPRICECD" VARCHAR2(1),
"RETPRICECOMPL" VARCHAR2(1),
"RETAUTHAMOUNT" NUMBER(19,2),
"INVSHIPDATE" DATE,
"COOBJMISS" VARCHAR2(1),
"REGION" VARCHAR2(6),
"CASHCD" VARCHAR2(1),
"UPDDATE" DATE,
"ACCOUNT" NUMBER(6,0),
"CENTRE" NUMBER(6,0),
"PRICELIST" VARCHAR2(7),
"YOURCONO" VARCHAR2(35),
"EXPORTMARK" VARCHAR2(1),
"PRODLINE" VARCHAR2(3),
"CURRENCY" VARCHAR2(3),
"TERMSPRICE" VARCHAR2(1),
"SOPLANDATE" DATE,
"FIXPRICEYN" VARCHAR2(1),
"PROJNO" NUMBER(8,0),
"PROJSUBNO" NUMBER(3,0),
"SAGREEMENTNO" NUMBER(8,0),
"INSTNO" NUMBER(8,0),
"GUARANTEE" VARCHAR2(1),
"WARRYN" VARCHAR2(1),
"BUYCENTRE" NUMBER(6,0),
"DISTRICT" VARCHAR2(6),
"SERVOFFICE" VARCHAR2(3),
"ORGUNIT" VARCHAR2(3),
"DEPTID" VARCHAR2(3),
"EMPGROUP" VARCHAR2(3),
"CUSTNOBILL" VARCHAR2(7),
"SCONO" NUMBER(8,0),
"SOPRINTDATE" DATE,
"SOVISITDATE" DATE,
"SOPLANSTARTDATE" DATE,
"QTYPLANTIME" NUMBER(9,1),
"TEXTCDINT" VARCHAR2(1),
"INTTEXTNO" NUMBER(8,0),
"ROUTINE" VARCHAR2(20),
"CODISCTYPE" VARCHAR2(1),
"CORETDATE" DATE,
"COMPONENTNO" NUMBER(8,0),
"ROUTINGCD" VARCHAR2(20),
"OINUPD" VARCHAR2(1),
"CUSTNOLVLID" NUMBER(2,0),
"UNITERMSPAY" VARCHAR2(1),
"COCREAMT" NUMBER(19,2),
"CUSTDEPT" VARCHAR2(30),
"TRANSFERCD" VARCHAR2(2),
"CODDATE" DATE,
"REMITADDRID" VARCHAR2(7),
"COSALESPGMID" VARCHAR2(8),
"COSOURCE" VARCHAR2(3),
"COYOURCONO" VARCHAR2(20),
"PRCDATE" DATE,
"METHOFREC" VARCHAR2(3),
"COBORULE" VARCHAR2(3),
"COCANCELDATE" DATE,
"UNITSELL" VARCHAR2(3),
"CUAPPDATE" DATE,
"ACTDELDATE" DATE,
"PROMID" VARCHAR2(8),
"DEALID" VARCHAR2(8),
"NDA" VARCHAR2(1),
"COPICKHLDCD" VARCHAR2(1),
"SOSERIOUSCD" NUMBER(1,0),
"SOERRORCD" NUMBER(3,0),
"SOACTIONCD" NUMBER(3,0),
"BCUSTNO" VARCHAR2(7),
"CCUSTNO" VARCHAR2(7),
"CASHDISCAMT" NUMBER(19,2),
"CASHDISCDATE" DATE,
"BANKREF" VARCHAR2(20),
"SUBNO" NUMBER(8,0),
"SUBTYPE" NUMBER(1,0),
"SOTYPECD" NUMBER(3,0),
"SOSYMPTOMCD" VARCHAR2(12),
"COMBINATIONID" NUMBER(9,0),
"STRUCTID" VARCHAR2(10),
"FUNCNAME" VARCHAR2(10),
"PARTNO" VARCHAR2(20),
"SOMAINTPRCLIST" VARCHAR2(7),
"SOUNITID" VARCHAR2(20),
"SOSCHEDULENO" NUMBER(3,0),
"SOSCHELINENO" NUMBER(3,0),
"SOBILLCHK" VARCHAR2(1),
"SOETADATE" DATE,
"SOSTOPDATE" DATE,
"SOCLOSEDATE" DATE,
"SODEFERCD" VARCHAR2(1),
"SODEFERREASCD" VARCHAR2(3),
"SODEFERSTDATE" DATE,
"SODEFERENDDATE" DATE,
"SOASSISTREASCD" VARCHAR2(3),
"SOREJECTREASCD" VARCHAR2(3),
"SOREJECT" NUMBER(3,0),
"SOENGFAIL" NUMBER(3,0),
"SOCUSTREPFLG" VARCHAR2(1),
"SOENGREPFLG" VARCHAR2(1),
"SERIALNUMBER" VARCHAR2(21),
"SOSTOCKSOURCECD" VARCHAR2(10),
"SOSUBCONTRACTNO" NUMBER(8,0),
"BLANKET" VARCHAR2(1),
"RETAPPROVAL" VARCHAR2(1),
"RETCAUSE" VARCHAR2(2),
"ARREFNO" VARCHAR2(14),
"EDIECNO" VARCHAR2(10),
"COQTY" NUMBER(12,3),
"REQDELDATE" DATE,
"COBRACKETTYPE" VARCHAR2(2),
"COBRACKETTYPEMODE" VARCHAR2(1),
"COCLASS" VARCHAR2(2),
"COPRMTID" NUMBER(10,0),
"COOPACCR" VARCHAR2(1),
"SOEVENTCD" VARCHAR2(3),
"SOASSISTENG" VARCHAR2(5),
"REQSHIPDATE" DATE,
"RPDESTSYSTEM" VARCHAR2(3),
"LUMPSUMMODE" VARCHAR2(1),
"LUMPSUMDISC" NUMBER(19,2),
"KILLDATE" DATE,
"WARRPRICELIST" VARCHAR2(7),
"SORESPENG" VARCHAR2(5),
"ORDSIZEALLOWMODE" VARCHAR2(1),
"ORDSIZEALLOWBRACK" VARCHAR2(2),
"COSALESPGMIDLS" VARCHAR2(8),
"COINVSTATUS" NUMBER(3,0),
"COINVTRGTYPE" NUMBER(1,0),
"REQINVDATE" DATE,
"SOPRIMEENG" VARCHAR2(1),
"COPLSYSREFNO" VARCHAR2(35),
"RDTYPE" VARCHAR2(1),
"XREFCONO" NUMBER(8,0),
"ORIGINVNO" NUMBER(9,0),
"WWSHOPID" VARCHAR2(3),
"WWLOGIN" VARCHAR2(31),
"EMAILLONG" VARCHAR2(254),
"EXTERNALCONO" VARCHAR2(35),
"ORDMAPTYPE" VARCHAR2(1),
"SHIPDATE" DATE,
"PORTALSTATUS" NUMBER(2,0),
"COCONFCDSTATUS" VARCHAR2(1)
)
CREATE TABLE "EHDA"."COROW"
( "CONO" NUMBER(8,0) NOT NULL ENABLE,
"ROWPOS" NUMBER(6,0) NOT NULL ENABLE,
"ROWSUBPOS" NUMBER(2,0) NOT NULL ENABLE,
"ROWSEQ" NUMBER(2,0) NOT NULL ENABLE,
"CONOSUB" NUMBER(6,0) NOT NULL ENABLE,
"OTYPE" VARCHAR2(2),
"SHIPDATE" DATE,
"CODATE" DATE,
"CREDATE" DATE NOT NULL ENABLE,
"TEXTCD" VARCHAR2(1),
"TEXTNO" NUMBER(8,0),
"STOCKNOPU" NUMBER(3,0) NOT NULL ENABLE,
"STOCKNOSU" NUMBER(3,0) NOT NULL ENABLE,
"TOURNO" NUMBER(10,0),
"WEEKDAYNO" NUMBER(1,0),
"PICKST" VARCHAR2(2),
"STOCKLOC" VARCHAR2(7),
"PARTNO" VARCHAR2(20) NOT NULL ENABLE,
"PARTNOCOM" VARCHAR2(20),
"PARTDESCR1" VARCHAR2(30),
"PARTDESCR2" VARCHAR2(30),
"CLASS5" VARCHAR2(4),
"CLASS6" VARCHAR2(4),
"UNITSELL" VARCHAR2(3),
"UNIT" VARCHAR2(3),
"UNITRELSS" NUMBER(16,12),
"QTYUNIT" NUMBER(12,3) NOT NULL ENABLE,
"QTYCO" NUMBER(12,3) NOT NULL ENABLE,
"LOTTYPE" VARCHAR2(1),
"QTYRES" NUMBER(12,3) NOT NULL ENABLE,
"QTYBACK" NUMBER(12,3),
"BACKCD" VARCHAR2(2),
"BOMESS" VARCHAR2(1),
"PICKLISTNO" NUMBER(8,0) NOT NULL ENABLE,
"QTYPICK" NUMBER(12,3) NOT NULL ENABLE,
"COMPLDEL" VARCHAR2(1),
"QTYSHIP" NUMBER(12,3) NOT NULL ENABLE,
"COWDATE" DATE NOT NULL ENABLE,
"COADATE" DATE,
"CUSTROWPOS" VARCHAR2(16),
"PRSALES" NUMBER(22,7) NOT NULL ENABLE,
"PRUNIT" NUMBER(3,0),
"PRLCCMAH" NUMBER(22,7),
"POROWAMT" NUMBER(19,2),
"CODISC" NUMBER(5,2) NOT NULL ENABLE,
"COROWDISC1" NUMBER(5,2) NOT NULL ENABLE,
"COROWDISC2" NUMBER(5,2),
"COROWDISC3" NUMBER(5,2),
"PRMANUAL" NUMBER(22,7),
"COROWAMT" NUMBER(19,2),
"PRICERIND" VARCHAR2(1),
"ROWWEIGHT" NUMBER(10,3),
"ROWVOL" NUMBER(10,3),
"VENDNO" VARCHAR2(7),
"PONO" NUMBER(8,0),
"PONOVEND" VARCHAR2(20),
"PRVEND" NUMBER(22,7),
"VENDDISC1" NUMBER(5,2) NOT NULL ENABLE,
"VENDDISC2" NUMBER(5,2),
"ROWSTATUS" NUMBER(3,0) NOT NULL ENABLE,
"DELNOTENO" VARCHAR2(20),
"INVSEQNO" NUMBER(6,0),
"SHIPSEQNO" NUMBER(3,0),
"INVNO" NUMBER(9,0),
"VENDOR_REF" VARCHAR2(20),
"ACCOUNT" NUMBER(6,0),
"CENTRE" NUMBER(6,0),
"QTYDEL" NUMBER(12,3) NOT NULL ENABLE,
"FINALDELCD" VARCHAR2(1),
"POWDATE" DATE,
"CONFIRMED" VARCHAR2(1),
"QTYPORES" NUMBER(12,3) NOT NULL ENABLE,
"QTYCORES" NUMBER(12,3),
"STOCKNOSHIP" NUMBER(3,0),
"POADATE" DATE,
"ASSEMBLECD" VARCHAR2(1),
"STOCKNOASS" NUMBER(3,0),
"AUTHDATE" DATE,
"RESDATE" DATE,
"RETARRIVE" VARCHAR2(1),
"RETCAUSE" VARCHAR2(2),
"RETMEASURE" VARCHAR2(2),
"RETAMOUNT" NUMBER(19,2),
"RETRATE" NUMBER(3,1),
"RETCONO" NUMBER(8,0),
"LENDCONO" NUMBER(8,0),
"QTYDET" NUMBER(12,3),
"SHIPNO" NUMBER(10,0),
"INVDATE" DATE,
"DELDATE" DATE,
"LIMITDEF" NUMBER(1,0),
"DETTYPE" VARCHAR2(1),
"AUTH" VARCHAR2(5),
"MTRLAV" NUMBER(19,2),
"PRAUTH" VARCHAR2(5),
"RETROWPOS" NUMBER(6,0),
"UPDDATE" DATE,
"OINUPD" VARCHAR2(1),
"PRICECD" VARCHAR2(3),
"SHIPMENTNO" NUMBER(6,0),
"EXPRESAUTH" VARCHAR2(5),
"INSTNO" NUMBER(8,0),
"FUNCNAME" VARCHAR2(10),
"LOTNO" VARCHAR2(40),
"SUBLOT" VARCHAR2(40),
"LOTCD" VARCHAR2(1),
"EXPIREDATE" DATE,
"SALESMAN" VARCHAR2(5),
"GUARANTEE" VARCHAR2(1),
"SODEBTYPE" VARCHAR2(1),
"PROJACTNO" NUMBER(4,0),
"PROJSHIPNO" NUMBER(3,0),
"PROJVENDNO" VARCHAR2(7),
"MARKINGTEXT" VARCHAR2(10),
"MARKINGNO" NUMBER(3,0),
"AUTHFINAL" VARCHAR2(1),
"PRODLINE" VARCHAR2(3),
"CORETDATE" DATE,
"INTINVNO" NUMBER(9,0),
"VAT" VARCHAR2(6),
"VATAMT" NUMBER(19,2),
"AUTOCODISC" VARCHAR2(1),
"COMPLROWCD" VARCHAR2(1),
"MARKINGADDR" VARCHAR2(30),
"COSHIPTOSEQNO" NUMBER(6,0),
"PRICELIST" VARCHAR2(7),
"TERMSPAY" VARCHAR2(2),
"CREHLDCD" NUMBER(1,0),
"PRICELVLID" NUMBER(2,0),
"USTAXCODE" VARCHAR2(10),
"IMRSRVTYPE" VARCHAR2(1),
"COPRMTID" NUMBER(10,0),
"PRIODATE" DATE,
"COADATE1" DATE,
"COBODATE" DATE,
"GRSPRICE" NUMBER(22,7),
"PRFRORDNO" NUMBER(22,7),
"RETTOTLNAMT" NUMBER(19,2),
"ORIGINVNO" NUMBER(9,0),
"ORIGCONO" NUMBER(8,0),
"COSALESPGMID" VARCHAR2(8),
"DISCTYPERDISC1" NUMBER(3,0),
"DISCTYPERDISC2" NUMBER(3,0),
"DISCTYPERDISC3" NUMBER(3,0),
"DISCTYPEPRICE" NUMBER(3,0),
"DISCTYPEODISC" NUMBER(3,0),
"EXPRETDATE" DATE,
"RCVRETDATE" DATE,
"EXPRETQTY" NUMBER(12,3),
"RCVRETQTY" NUMBER(12,3),
"COADJCAUSE" VARCHAR2(2),
"COAPPHLDCD" NUMBER(1,0),
"COGMHLDCD" NUMBER(1,0),
"CORETHLDCD" NUMBER(1,0),
"COMANPRHLDCD" NUMBER(1,0),
"COSALESPGMIDDISC1" VARCHAR2(8),
"COSALESPGMIDDISC2" VARCHAR2(8),
"COSALESPGMIDDISC3" VARCHAR2(8),
"COSALESPGMIDCODISC" VARCHAR2(8),
"PRPRICELIST" NUMBER(22,7),
"DISCTYPEPLPRICE" NUMBER(3,0),
"COSALESPGMIDPLPR" VARCHAR2(8),
"COOPACCR" VARCHAR2(1),
"PRELDELAH" VARCHAR2(1),
"PRCDATE" DATE,
"REQDELDATE" DATE,
"COCANCELDATE" DATE,
"CUAPPDATE" DATE,
"ACTDELDATE" DATE,
"COBORULE" VARCHAR2(3),
"VARIND" VARCHAR2(1),
"SUBTIND" VARCHAR2(1),
"REPLIND" VARCHAR2(1),
"ORDEREDPARTNO" VARCHAR2(20),
"CORELDATE" DATE,
"PROMID" VARCHAR2(8),
"DEALID" VARCHAR2(8),
"NDA" VARCHAR2(1),
"IMFCSTLVLTYPE" VARCHAR2(2),
"IMFCSTLVLID" VARCHAR2(10),
"COTRANSHLDCD" NUMBER(1,0),
"SELLPACK" VARCHAR2(5),
"COPICKHLDCD" VARCHAR2(1),
"COTEXTID" VARCHAR2(8),
"TAXFLG" VARCHAR2(1),
"SUBNO" NUMBER(8,0),
"SUBTYPE" NUMBER(1,0),
"SOACTIVITYCD" VARCHAR2(3),
"COMBINATIONID" NUMBER(9,0),
"STRUCTID" VARCHAR2(10),
"SCONO" NUMBER(8,0),
"SOTOOLKIT" VARCHAR2(20),
"SOSCHELINENO" NUMBER(3,0),
"SOINVSTARTDATE" DATE,
"SOINVENDDATE" DATE,
"SOREQSTATUS" NUMBER(2,0),
"SOVISITDATE" DATE,
"SOETADATE" DATE,
"PARTPACK" VARCHAR2(15),
"STOCKNOPICKCOMP" NUMBER(3,0),
"STOPTIMEDATE" DATE,
"SRVLVLUPD" NUMBER(1,0),
"SORETURNCD" NUMBER(3,0),
"PRINVNO" NUMBER(9,0),
"PRCONO" NUMBER(8,0),
"ARREFNO" VARCHAR2(14),
"PROMCLOF" VARCHAR2(1),
"IMSCRCLVLTYPE" VARCHAR2(2),
"IMSCRCLVLID" VARCHAR2(10),
"COANNATTEST" VARCHAR2(1),
"COBRACKETTYPE" VARCHAR2(2),
"COBRACKETTYPEMODE" VARCHAR2(1),
"ACKDELIVDATE" DATE,
"SEQNO" NUMBER(3,0),
"BUUNIT" VARCHAR2(3),
"CHILEV" NUMBER(2,0),
"CHISEL" VARCHAR2(20),
"CUSTRESQTYUSED" NUMBER(12,3),
"CHANGEQTYCD" VARCHAR2(3),
"CHANGETEXTFLG" VARCHAR2(1),
"CHANGETEXTNO" VARCHAR2(8),
"SOSWAPPARTNO" VARCHAR2(20),
"COPRLCW" NUMBER(19,2),
"RETAPPROVAL" VARCHAR2(1),
"REQSHIPDATE" DATE,
"ACKSHIPDATE" DATE,
"ACKSHIPDATEORIGIN" DATE,
"WARRPERIOD" NUMBER(4,1),
"SOACTUALTIME" NUMBER(13,3),
"UNITPRICE" VARCHAR2(3),
"QTYCOUNITPRICE" NUMBER(12,3),
"QTYPICKUNITPRICE" NUMBER(12,3),
"QTYSHIPUNITPRICE" NUMBER(12,3),
"PRICEUNITPRICE" NUMBER(22,7),
"ROWINVSTATUS" NUMBER(3,0),
"REQINVDATE" DATE,
"COINVHLDFLG" NUMBER(1,0),
"ORIGROWSEQ" NUMBER(2,0),
"ORIGROWPOS" NUMBER(6,0),
"SCACCODE" VARCHAR2(12),
"FORWARDER" VARCHAR2(7),
"BILLOFLADING" VARCHAR2(30),
"EDPICKTICKSTATUS" VARCHAR2(1),
"RELTOOWSEQNO" NUMBER(6,0),
"EXPPRICE" NUMBER(17,2),
"EXPUOM" VARCHAR2(3),
"WTCODE" VARCHAR2(2),
"TAXLOC" VARCHAR2(1),
"INTRASTATPROCCD" VARCHAR2(6),
"WTVATPERCENT" NUMBER(4,2),
"STATSHIPFLG" NUMBER(1,0),
"COODATE" DATE,
"RSRVDQUALSUPPLY" VARCHAR2(1),
"EARLYRELWHSE" NUMBER(2,0),
"RETTRANSITCD" NUMBER(1,0),
"COACCEPTACKDATECNT" NUMBER(5,0),
"CZTMPLANSTART" DATE,
"CZTMPLANEND" DATE,
"CZTRANSPORTORDERNO" NUMBER(8,0),
"CZFIRSTLEG" NUMBER(10,0)
)
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER)
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "EHDA"."A_COFLX_PK" ON "EHDA"."A_COFLX" ("CONO", "COSHIPTOSEQNO", "ROWPOS", "ROWSUBPOS", "ROWSEQ", "COREFQUAL", "COREFSEQNO")
CREATE UNIQUE INDEX "EHDA"."A_COSHIPTO_PK" ON "EHDA"."A_COSHIPTO" ("CONO", "COSHIPTOSEQNO")
CREATE INDEX "EHDA"."A_COSHIPTO_X1" ON "EHDA"."A_COSHIPTO" ("CUSTNO")
CREATE UNIQUE INDEX "EHDA"."COHEAD_PK" ON "EHDA"."COHEAD" ("CONO")
CREATE INDEX "EHDA"."COHEAD_X1" ON "EHDA"."COHEAD" ("COSTATUS")
CREATE INDEX "EHDA"."COHEAD_X2" ON "EHDA"."COHEAD" ("CUSTNO", "OTYPE")
CREATE INDEX "EHDA"."COHEAD_X3" ON "EHDA"."COHEAD" ("COCONFCD")
CREATE INDEX "EHDA"."COHEAD_X4" ON "EHDA"."COHEAD" ("OTYPE", "CONO", "COSTATUS")
CREATE INDEX "EHDA"."COHEAD_X5" ON "EHDA"."COHEAD" ("CUSTNOBILL", "ORGUNIT", "COCREAMT")
CREATE INDEX "EHDA"."COHEAD_X6" ON "EHDA"."COHEAD" ("YOURCONO")
CREATE INDEX "EHDA"."COHEAD_X7" ON "EHDA"."COHEAD" ("COINVSTATUS")
CREATE INDEX "EHDA"."COROW_CZ1" ON "EHDA"."COROW" ("STOCKNOSU", "TOURNO")
CREATE INDEX "EHDA"."COROW_X2" ON "EHDA"."COROW" ("PARTNO", "STOCKNOPU", "ROWSTATUS", "OTYPE")
CREATE INDEX "EHDA"."COROW_X9" ON "EHDA"."COROW" ("ORIGCONO")
CREATE INDEX "EHDA"."COROW_X1" ON "EHDA"."COROW" ("ROWSTATUS", "ACKSHIPDATE", "STOCKNOPU")
CREATE UNIQUE INDEX "EHDA"."COROW_PK" ON "EHDA"."COROW" ("CONO", "ROWPOS", "ROWSUBPOS", "ROWSEQ")
CREATE INDEX "EHDA"."COROW_X3" ON "EHDA"."COROW" ("PICKLISTNO", "STOCKNOPU")
CREATE INDEX "EHDA"."COROW_X4" ON "EHDA"."COROW" ("PONO")
CREATE INDEX "EHDA"."COROW_X5" ON "EHDA"."COROW" ("RETCONO")
CREATE INDEX "EHDA"."COROW_X6" ON "EHDA"."COROW" ("OTYPE", "ROWSTATUS")
CREATE INDEX "EHDA"."COROW_X7" ON "EHDA"."COROW" ("STOCKNOPICKCOMP", "TOURNO","WEEKDAYNO", "ACKSHIPDATE")
CREATE INDEX "EHDA"."COROW_X8" ON "EHDA"."COROW" ("ROWINVSTATUS")
[b]ALL_IND_COLUMNS[/b]
TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
A_COFLX A_COFLX_PK CONO 1
A_COFLX A_COFLX_PK COSHIPTOSEQNO 2
A_COFLX A_COFLX_PK ROWPOS 3
A_COFLX A_COFLX_PK ROWSUBPOS 4
A_COFLX A_COFLX_PK ROWSEQ 5
A_COFLX A_COFLX_PK COREFQUAL 6
A_COFLX A_COFLX_PK COREFSEQNO 7
A_COSHIPTO A_COSHIPTO_PK CONO 1
A_COSHIPTO A_COSHIPTO_PK COSHIPTOSEQNO 2
A_COSHIPTO A_COSHIPTO_X1 CUSTNO 1
COHEAD COHEAD_PK CONO 1
COHEAD COHEAD_X1 COSTATUS 1
COHEAD COHEAD_X2 CUSTNO 1
COHEAD COHEAD_X2 OTYPE 2
COHEAD COHEAD_X3 COCONFCD 1
COHEAD COHEAD_X4 OTYPE 1
COHEAD COHEAD_X4 CONO 2
COHEAD COHEAD_X4 COSTATUS 3
COHEAD COHEAD_X5 CUSTNOBILL 1
COHEAD COHEAD_X5 ORGUNIT 2
COHEAD COHEAD_X5 COCREAMT 3
COHEAD COHEAD_X6 YOURCONO 1
COHEAD COHEAD_X7 COINVSTATUS 1
COROW COROW_CZ1 STOCKNOSU 1
COROW COROW_CZ1 TOURNO 2
COROW COROW_PK CONO 1
COROW COROW_PK ROWPOS 2
COROW COROW_PK ROWSUBPOS 3
COROW COROW_PK ROWSEQ 4
COROW COROW_X1 ROWSTATUS 1
COROW COROW_X1 ACKSHIPDATE 2
COROW COROW_X1 STOCKNOPU 3
COROW COROW_X2 PARTNO 1
COROW COROW_X2 STOCKNOPU 2
COROW COROW_X2 ROWSTATUS 3
COROW COROW_X2 OTYPE 4
COROW COROW_X3 PICKLISTNO 1
COROW COROW_X3 STOCKNOPU 2
COROW COROW_X4 PONO 1
COROW COROW_X5 RETCONO 1
COROW COROW_X6 OTYPE 1
COROW COROW_X6 ROWSTATUS 2
COROW COROW_X7 STOCKNOPICKCOMP 1
COROW COROW_X7 TOURNO 2
COROW COROW_X7 WEEKDAYNO 3
COROW COROW_X7 ACKSHIPDATE 4
COROW COROW_X8 ROWINVSTATUS 1
COROW COROW_X9 ORIGCONO 1
Also here is the count of records in each table.
select count(*) from cohead; 84007
select count(*) from a_coshipto; 87514
select count(*) from a_coflx 492846;
select count(*) from cohead where otype = 61; 42695
select count(*) from a_coflx where corefqual = 'PRINT_FLAG'; 81149
select count(*) from a_coflx where charvalue IS NULL; 327917
select count(*) from a_coflx where charvalue IS NULL and corefqual = 'PRINT_FLAG'; 48843
Thanks for reverting back..
|
|
|
|
| Re: Sql query is slow [message #588629 is a reply to message #588624] |
Thu, 27 June 2013 03:53 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi
Please additionally post the results for the following queries:
select * from
(select cono, count(*)
from cohead
group by rollup(cono)
order by 2 desc)
where rownum<20;
select * from
(select cono, count(*)
from a_coshipto
group by rollup(cono)
order by 2 desc)
where rownum<20;
select * from
(select cono, count(*)
from a_coflx
group by rollup(cono)
order by 2 desc)
where rownum<20;
select * from
(select cono, count(*)
from a_coflx
where charvalue IS NULL and corefqual = 'PRINT_FLAG'
group by rollup(cono)
order by 2 desc)
where rownum<20;
select * from
(select coshiptoseqno, count(*)
from a_coflx
group by rollup(coshiptoseqno)
order by 2 desc)
where rownum<20;
select * from
(select coshiptoseqno, count(*)
from a_coshipto
group by rollup(coshiptoseqno)
order by 2 desc)
where rownum<20;
2. Please also look if the results of number of records for each table is correct in comparison with what you see in DBA_TABLES => NUM_ROWS column for each table.
3.Also check for histograms in DBA_TAB_COLUMNS (column HISTOGRAM) for the columns in the where clause of this table.
Post the results here.
Regards,
Andrey
[Updated on: Thu, 27 June 2013 03:53] Report message to a moderator |
|
|
|
| Re: Sql query is slow [message #588646 is a reply to message #588629] |
Thu, 27 June 2013 04:50 |
prashanth7582
Messages: 34
Registered: October 2005
Location: Bangalore
|
Member |
|
|
Hi Andrey,
Here is the data required.
1.
SELECT * FROM
(SELECT CONO, count(*)
FROM cohead
GROUP BY ROLLUP(CONO)
ORDER BY 2 desc)
WHERE ROWNUM<20;
CONO COUNT
84007
2000006 1
2000007 1
2000021 1
2000030 1
2000031 1
2000037 1
2000038 1
2000039 1
2000089 1
2000109 1
2000118 1
2000120 1
2000144 1
2000186 1
2000200 1
2000211 1
2000215 1
2000238 1
select * from
(select cono, count(*)
from a_coshipto
group by rollup(cono)
order by 2 desc)
where rownum<20;
CONO COUNT
87514
15840239 37
15840276 37
15847933 31
15847953 31
15839375 12
15856435 12
15844517 11
15854094 11
15831450 10
15833501 10
15844034 10
15837864 10
15856433 10
15839374 10
15831449 9
15844033 9
15854093 9
15849619 9
select * from
(select cono, count(*)
from a_coflx
group by rollup(cono)
order by 2 desc)
where rownum<20;
CONO COUNT
492846
15840239 78
15840276 78
15847933 66
15847953 66
15839375 52
15856435 50
15844517 48
15854094 48
15831450 44
15833501 44
15844034 44
15839374 44
15837864 44
15856433 42
15831449 40
15837863 40
15854093 40
15844515 40
select * from
(select cono, count(*)
from a_coflx
where charvalue IS NULL and corefqual = 'PRINT_FLAG'
group by rollup(cono)
order by 2 desc)
where rownum<20;
CONO COUNT
48843
15840239 37
15840276 37
15847933 31
15847953 31
15860157 8
15860155 7
15846245 6
15836620 5
15859690 5
15846029 5
15831148 4
15831149 4
15833949 4
15835354 4
15860153 4
15860151 4
15860141 4
15859700 4
select * from
(select coshiptoseqno, count(*)
from a_coflx
group by rollup(coshiptoseqno)
order by 2 desc)
where rownum<20;
SEQNO COUNT
492846
0 407256
2 47624
1 22508
4 6874
3 3738
5 1954
6 1138
7 724
8 412
9 240
10 86
11 48
12 26
13 14
14 6
15 6
16 6
17 6
select * from
(select coshiptoseqno, count(*)
from a_coshipto
group by rollup(coshiptoseqno)
order by 2 desc)
where rownum<20;
SEQNO COUNT
87514
1 65802
2 17533
4 1784
3 1069
5 510
6 302
7 190
8 108
9 63
10 24
11 14
12 8
13 5
14 3
15 3
16 3
17 3
18 3
2. Please also look if the results of number of records for each table is correct in comparison with what you see in DBA_TABLES => NUM_ROWS column for each table.
Count in user_tables
TABLENAME COUNT
A_COFLX 475753
A_COSHIPTO 87222
COHEAD 83762
COROW 1389196
Count as per tables
TABLENAME COUNT
A_COFLX 492846
A_COSHIPTO 87514
COHEAD 84007
COROW 1396413
3.Also check for histograms in DBA_TAB_COLUMNS (column HISTOGRAM) for the columns in the where clause of this table.
TABLE_NAME COLUMN_NAME LAST_ANALYZED GLOBAL_STATS HISTOGRAM
A_COFLX CONO 11/06/2013 22:55:35 YES HEIGHT BALANCED
A_COFLX COSHIPTOSEQNO 11/06/2013 22:55:35 YES FREQUENCY
A_COFLX ROWPOS 11/06/2013 22:55:35 YES FREQUENCY
A_COFLX ROWSUBPOS 11/06/2013 22:55:35 YES FREQUENCY
A_COFLX ROWSEQ 11/06/2013 22:55:35 YES FREQUENCY
A_COFLX COREFQUAL 11/06/2013 22:55:35 YES FREQUENCY
A_COFLX COREFSEQNO 11/06/2013 22:55:35 YES FREQUENCY
A_COFLX CHARVALUE 11/06/2013 22:55:35 YES HEIGHT BALANCED
A_COFLX DATEVALUE 11/06/2013 22:55:35 YES NONE
A_COFLX NUMVALUE 11/06/2013 22:55:35 YES NONE
A_COFLX UPDDATE 11/06/2013 22:55:35 YES HEIGHT BALANCED
A_COFLX USERID 11/06/2013 22:55:35 YES NONE
A_COFLX LOGINID 11/06/2013 22:55:35 YES NONE
A_COSHIPTO FREIGHTLATER 14/06/2013 22:24:04 YES NONE
A_COSHIPTO COQTY 14/06/2013 22:24:04 YES HEIGHT BALANCED
A_COSHIPTO TAXFREEZONE 14/06/2013 22:24:04 YES NONE
A_COSHIPTO PLACELOAD 14/06/2013 22:24:04 YES NONE
A_COSHIPTO PLTRSPCARRIER 14/06/2013 22:24:04 YES NONE
A_COSHIPTO TERMSDELEXT 14/06/2013 22:24:04 YES NONE
A_COSHIPTO CZBOOKTIME 14/06/2013 22:24:04 YES HEIGHT BALANCED
A_COSHIPTO CZBOOKREF 14/06/2013 22:24:04 YES HEIGHT BALANCED
A_COSHIPTO COUNTRYCD 14/06/2013 22:24:04 YES NONE
A_COSHIPTO COBRACKETTYPE 14/06/2013 22:24:04 YES NONE
A_COSHIPTO COSHIPTOSEQNO 14/06/2013 22:24:04 YES FREQUENCY
A_COSHIPTO CONO 14/06/2013 22:24:04 YES HEIGHT BALANCED
A_COSHIPTO CUSTNO 14/06/2013 22:24:04 YES HEIGHT BALANCED
A_COSHIPTO CUSTNOLVLID 14/06/2013 22:24:04 YES NONE
A_COSHIPTO SHIPNAME1 14/06/2013 22:24:04 YES NONE
A_COSHIPTO SHIPNAME2 14/06/2013 22:24:04 YES NONE
A_COSHIPTO SHIPSTREET1 14/06/2013 22:24:04 YES NONE
A_COSHIPTO SHIPSTREET2 14/06/2013 22:24:04 YES NONE
A_COSHIPTO SHIPSTREET3 14/06/2013 22:24:04 YES NONE
A_COSHIPTO USSTATECD 14/06/2013 22:24:04 YES NONE
A_COSHIPTO SHIPZIPCODE 14/06/2013 22:24:04 YES NONE
A_COSHIPTO USZIPCODE 14/06/2013 22:24:04 YES NONE
A_COSHIPTO SHIPTEL 14/06/2013 22:24:04 YES NONE
A_COSHIPTO BUYCENTRE 14/06/2013 22:24:04 YES NONE
A_COSHIPTO REGION 14/06/2013 22:24:04 YES NONE
A_COSHIPTO DISTRICT 14/06/2013 22:24:04 YES NONE
A_COSHIPTO CUREF 14/06/2013 22:24:04 YES NONE
A_COSHIPTO TERMSDEL 14/06/2013 22:24:04 YES NONE
A_COSHIPTO TRSPMODE 14/06/2013 22:24:04 YES FREQUENCY
A_COSHIPTO COMARK 14/06/2013 22:24:04 YES FREQUENCY
A_COSHIPTO SHIPCOMM 14/06/2013 22:24:04 YES NONE
A_COSHIPTO SHIPTONO 14/06/2013 22:24:04 YES FREQUENCY
A_COSHIPTO ROUTINGCD 14/06/2013 22:24:04 YES HEIGHT BALANCED
A_COSHIPTO INVCOLLMARK 14/06/2013 22:24:04 YES NONE
A_COSHIPTO SHIPCITY 14/06/2013 22:24:04 YES NONE
A_COSHIPTO FREIGHTCHA 14/06/2013 22:24:04 YES NONE
A_COSHIPTO COFREIGHT 14/06/2013 22:24:04 YES NONE
A_COSHIPTO PACKCHA 14/06/2013 22:24:04 YES NONE
A_COSHIPTO COPACK 14/06/2013 22:24:04 YES NONE
A_COSHIPTO TAXCERTNO 14/06/2013 22:24:04 YES NONE
A_COSHIPTO REASTAXCD 14/06/2013 22:24:04 YES NONE
A_COSHIPTO OUTSIDECITYLMT 14/06/2013 22:24:04 YES NONE
A_COSHIPTO LBLIND 14/06/2013 22:24:04 YES NONE
A_COSHIPTO STOCKNOSU 14/06/2013 22:24:04 YES FREQUENCY
A_COSHIPTO TOURNO 14/06/2013 22:24:04 YES HEIGHT BALANCED
A_COSHIPTO WEEKDAYNO 14/06/2013 22:24:04 YES FREQUENCY
A_COSHIPTO STOPTIMEDATE 14/06/2013 22:24:04 YES HEIGHT BALANCED
A_COSHIPTO COAMT 14/06/2013 22:24:04 YES NONE
A_COSHIPTO COWEIGHT 14/06/2013 22:24:04 YES NONE
A_COSHIPTO COVOLUME 14/06/2013 22:24:04 YES NONE
A_COSHIPTO COBRACKETTYPEMODE 14/06/2013 22:24:04 YES NONE
A_COSHIPTO INSTNO 14/06/2013 22:24:04 YES NONE
A_COSHIPTO FUNCNAME 14/06/2013 22:24:04 YES NONE
A_COSHIPTO SOTYPECD 14/06/2013 22:24:04 YES NONE
A_COSHIPTO SOSYMPTOMCD 14/06/2013 22:24:04 YES NONE
A_COSHIPTO SOSERIOUSCD 14/06/2013 22:24:04 YES NONE
A_COSHIPTO PARTNO 14/06/2013 22:24:04 YES NONE
A_COSHIPTO PLTRSPSYSTEM 14/06/2013 22:24:04 YES NONE
COHEAD ACCOUNT 14/06/2013 22:25:09 YES NONE
COHEAD CURRENCY 14/06/2013 22:25:09 YES FREQUENCY
COHEAD LUMPSUMMODE 14/06/2013 22:25:09 YES NONE
COHEAD LUMPSUMDISC 14/06/2013 22:25:09 YES NONE
COHEAD KILLDATE 14/06/2013 22:25:09 YES NONE
COHEAD WARRPRICELIST 14/06/2013 22:25:09 YES NONE
COHEAD SORESPENG 14/06/2013 22:25:09 YES NONE
COHEAD ORDSIZEALLOWMODE 14/06/2013 22:25:09 YES NONE
COHEAD ORDSIZEALLOWBRACK 14/06/2013 22:25:09 YES NONE
COHEAD COSALESPGMIDLS 14/06/2013 22:25:09 YES NONE
COHEAD COINVSTATUS 14/06/2013 22:25:09 YES NONE
COHEAD COINVTRGTYPE 14/06/2013 22:25:09 YES FREQUENCY
COHEAD REQINVDATE 14/06/2013 22:25:09 YES NONE
COHEAD SOPRIMEENG 14/06/2013 22:25:09 YES NONE
COHEAD COPLSYSREFNO 14/06/2013 22:25:09 YES NONE
COHEAD RDTYPE 14/06/2013 22:25:09 YES NONE
COHEAD XREFCONO 14/06/2013 22:25:09 YES NONE
COHEAD ORIGINVNO 14/06/2013 22:25:09 YES NONE
COHEAD WWSHOPID 14/06/2013 22:25:09 YES NONE
COHEAD WWLOGIN 14/06/2013 22:25:09 YES NONE
COHEAD EMAILLONG 14/06/2013 22:25:09 YES NONE
COHEAD EXTERNALCONO 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD ORDMAPTYPE 14/06/2013 22:25:09 YES FREQUENCY
COHEAD SHIPDATE 14/06/2013 22:25:09 YES NONE
COHEAD PORTALSTATUS 14/06/2013 22:25:09 YES NONE
COHEAD COCONFCDSTATUS 14/06/2013 22:25:09 YES NONE
COHEAD BUYCENTRE 14/06/2013 22:25:09 YES NONE
COHEAD DISTRICT 14/06/2013 22:25:09 YES NONE
COHEAD SERVOFFICE 14/06/2013 22:25:09 YES NONE
COHEAD ORGUNIT 14/06/2013 22:25:09 YES FREQUENCY
COHEAD DEPTID 14/06/2013 22:25:09 YES FREQUENCY
COHEAD EMPGROUP 14/06/2013 22:25:09 YES FREQUENCY
COHEAD CUSTNOBILL 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD SCONO 14/06/2013 22:25:09 YES NONE
COHEAD SOPRINTDATE 14/06/2013 22:25:09 YES NONE
COHEAD SOVISITDATE 14/06/2013 22:25:09 YES NONE
COHEAD SOPLANSTARTDATE 14/06/2013 22:25:09 YES NONE
COHEAD QTYPLANTIME 14/06/2013 22:25:09 YES NONE
COHEAD TEXTCDINT 14/06/2013 22:25:09 YES NONE
COHEAD INTTEXTNO 14/06/2013 22:25:09 YES NONE
COHEAD ROUTINE 14/06/2013 22:25:09 YES NONE
COHEAD CODISCTYPE 14/06/2013 22:25:09 YES NONE
COHEAD CORETDATE 14/06/2013 22:25:09 YES NONE
COHEAD COMPONENTNO 14/06/2013 22:25:09 YES NONE
COHEAD ROUTINGCD 14/06/2013 22:25:09 YES NONE
COHEAD OINUPD 14/06/2013 22:25:09 YES NONE
COHEAD CUSTNOLVLID 14/06/2013 22:25:09 YES NONE
COHEAD UNITERMSPAY 14/06/2013 22:25:09 YES NONE
COHEAD COCREAMT 14/06/2013 22:25:09 YES NONE
COHEAD CUSTDEPT 14/06/2013 22:25:09 YES NONE
COHEAD TRANSFERCD 14/06/2013 22:25:09 YES NONE
COHEAD CODDATE 14/06/2013 22:25:09 YES NONE
COHEAD REMITADDRID 14/06/2013 22:25:09 YES NONE
COHEAD COSALESPGMID 14/06/2013 22:25:09 YES NONE
COHEAD COSOURCE 14/06/2013 22:25:09 YES FREQUENCY
COHEAD COYOURCONO 14/06/2013 22:25:09 YES NONE
COHEAD PRCDATE 14/06/2013 22:25:09 YES NONE
COHEAD METHOFREC 14/06/2013 22:25:09 YES FREQUENCY
COHEAD COBORULE 14/06/2013 22:25:09 YES NONE
COHEAD COCANCELDATE 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD UNITSELL 14/06/2013 22:25:09 YES NONE
COHEAD CUAPPDATE 14/06/2013 22:25:09 YES NONE
COHEAD ACTDELDATE 14/06/2013 22:25:09 YES NONE
COHEAD PROMID 14/06/2013 22:25:09 YES NONE
COHEAD DEALID 14/06/2013 22:25:09 YES NONE
COHEAD NDA 14/06/2013 22:25:09 YES NONE
COHEAD COPICKHLDCD 14/06/2013 22:25:09 YES FREQUENCY
COHEAD SOSERIOUSCD 14/06/2013 22:25:09 YES NONE
COHEAD SOERRORCD 14/06/2013 22:25:09 YES NONE
COHEAD SOACTIONCD 14/06/2013 22:25:09 YES NONE
COHEAD BCUSTNO 14/06/2013 22:25:09 YES NONE
COHEAD CCUSTNO 14/06/2013 22:25:09 YES NONE
COHEAD CASHDISCAMT 14/06/2013 22:25:09 YES NONE
COHEAD CASHDISCDATE 14/06/2013 22:25:09 YES NONE
COHEAD BANKREF 14/06/2013 22:25:09 YES NONE
COHEAD SUBNO 14/06/2013 22:25:09 YES NONE
COHEAD SUBTYPE 14/06/2013 22:25:09 YES NONE
COHEAD SOTYPECD 14/06/2013 22:25:09 YES NONE
COHEAD SOSYMPTOMCD 14/06/2013 22:25:09 YES NONE
COHEAD COMBINATIONID 14/06/2013 22:25:09 YES NONE
COHEAD STRUCTID 14/06/2013 22:25:09 YES NONE
COHEAD FUNCNAME 14/06/2013 22:25:09 YES NONE
COHEAD PARTNO 14/06/2013 22:25:09 YES NONE
COHEAD SOMAINTPRCLIST 14/06/2013 22:25:09 YES NONE
COHEAD SOUNITID 14/06/2013 22:25:09 YES NONE
COHEAD SOSCHEDULENO 14/06/2013 22:25:09 YES NONE
COHEAD SOSCHELINENO 14/06/2013 22:25:09 YES NONE
COHEAD SOBILLCHK 14/06/2013 22:25:09 YES NONE
COHEAD SOETADATE 14/06/2013 22:25:09 YES NONE
COHEAD SOSTOPDATE 14/06/2013 22:25:09 YES NONE
COHEAD SOCLOSEDATE 14/06/2013 22:25:09 YES NONE
COHEAD SODEFERCD 14/06/2013 22:25:09 YES NONE
COHEAD SODEFERREASCD 14/06/2013 22:25:09 YES NONE
COHEAD SODEFERSTDATE 14/06/2013 22:25:09 YES NONE
COHEAD SODEFERENDDATE 14/06/2013 22:25:09 YES NONE
COHEAD SOASSISTREASCD 14/06/2013 22:25:09 YES NONE
COHEAD SOREJECTREASCD 14/06/2013 22:25:09 YES NONE
COHEAD SOREJECT 14/06/2013 22:25:09 YES NONE
COHEAD SOENGFAIL 14/06/2013 22:25:09 YES NONE
COHEAD SOCUSTREPFLG 14/06/2013 22:25:09 YES NONE
COHEAD SOENGREPFLG 14/06/2013 22:25:09 YES NONE
COHEAD SERIALNUMBER 14/06/2013 22:25:09 YES NONE
COHEAD SOSTOCKSOURCECD 14/06/2013 22:25:09 YES NONE
COHEAD SOSUBCONTRACTNO 14/06/2013 22:25:09 YES NONE
COHEAD BLANKET 14/06/2013 22:25:09 YES NONE
COHEAD RETAPPROVAL 14/06/2013 22:25:09 YES NONE
COHEAD RETCAUSE 14/06/2013 22:25:09 YES NONE
COHEAD ARREFNO 14/06/2013 22:25:09 YES NONE
COHEAD EDIECNO 14/06/2013 22:25:09 YES NONE
COHEAD COQTY 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD REQDELDATE 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD COBRACKETTYPE 14/06/2013 22:25:09 YES NONE
COHEAD COBRACKETTYPEMODE 14/06/2013 22:25:09 YES NONE
COHEAD COCLASS 14/06/2013 22:25:09 YES FREQUENCY
COHEAD COPRMTID 14/06/2013 22:25:09 YES NONE
COHEAD COOPACCR 14/06/2013 22:25:09 YES NONE
COHEAD SOEVENTCD 14/06/2013 22:25:09 YES NONE
COHEAD SOASSISTENG 14/06/2013 22:25:09 YES NONE
COHEAD REQSHIPDATE 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD RPDESTSYSTEM 14/06/2013 22:25:09 YES NONE
COHEAD CONO 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD CUSTNO 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD CUSTNOINT 14/06/2013 22:25:09 YES FREQUENCY
COHEAD CUSTCAT 14/06/2013 22:25:09 YES FREQUENCY
COHEAD COOBJ 14/06/2013 22:25:09 YES NONE
COHEAD SALESMAN 14/06/2013 22:25:09 YES FREQUENCY
COHEAD OURREF 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD COWDATE 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD PRELDELAH 14/06/2013 22:25:09 YES FREQUENCY
COHEAD PRELREMNO 14/06/2013 22:25:09 YES NONE
COHEAD ROWPOSHIGH 14/06/2013 22:25:09 YES NONE
COHEAD STOCKNO 14/06/2013 22:25:09 YES FREQUENCY
COHEAD OTYPE 14/06/2013 22:25:09 YES FREQUENCY
COHEAD ACCOUNTCD 14/06/2013 22:25:09 YES NONE
COHEAD COSTATUS 14/06/2013 22:25:09 YES FREQUENCY
COHEAD INVSEQNO 14/06/2013 22:25:09 YES NONE
COHEAD SHIPSEQNO 14/06/2013 22:25:09 YES NONE
COHEAD INVCOLLMARK 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD INVNOLAST 14/06/2013 22:25:09 YES NONE
COHEAD INVDATELAST 14/06/2013 22:25:09 YES FREQUENCY
COHEAD DUEDATELAST 14/06/2013 22:25:09 YES NONE
COHEAD CODATE 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD YOURDATE 14/06/2013 22:25:09 YES NONE
COHEAD CREDATE 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD SHIPNAME1 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD SHIPNAME2 14/06/2013 22:25:09 YES NONE
COHEAD SHIPSTREET1 14/06/2013 22:25:09 YES NONE
COHEAD SHIPTONO 14/06/2013 22:25:09 YES NONE
COHEAD SHIPSTREET2 14/06/2013 22:25:09 YES NONE
COHEAD SHIPSTREET3 14/06/2013 22:25:09 YES NONE
COHEAD SHIPCITY 14/06/2013 22:25:09 YES NONE
COHEAD SHIPZIPCODE 14/06/2013 22:25:09 YES NONE
COHEAD USZIPCODE 14/06/2013 22:25:09 YES NONE
COHEAD USSTATECD 14/06/2013 22:25:09 YES NONE
COHEAD SHIPTEL 14/06/2013 22:25:09 YES NONE
COHEAD COUNTRYCD 14/06/2013 22:25:09 YES FREQUENCY
COHEAD CUREF 14/06/2013 22:25:09 YES HEIGHT BALANCED
COHEAD SHIPCOMM 14/06/2013 22:25:09 YES NONE
COHEAD COMPLDEL 14/06/2013 22:25:09 YES NONE
COHEAD TERMSDEL 14/06/2013 22:25:09 YES NONE
COHEAD COMARK 14/06/2013 22:25:09 YES NONE
COHEAD TERMSDEL1 14/06/2013 22:25:09 YES NONE
COHEAD ZONE 14/06/2013 22:25:09 YES NONE
COHEAD TOURNO 14/06/2013 22:25:09 YES NONE
COHEAD PACKCHA 14/06/2013 22:25:09 YES NONE
COHEAD COPACK 14/06/2013 22:25:09 YES NONE
COHEAD TRSPMODE 14/06/2013 22:25:09 YES NONE
COHEAD EXPCHA 14/06/2013 22:25:09 YES NONE
COHEAD COCHARGE 14/06/2013 22:25:09 YES NONE
COHEAD FREIGHTCHA 14/06/2013 22:25:09 YES NONE
COHEAD COFREIGHT 14/06/2013 22:25:09 YES NONE
COHEAD INSCHA 14/06/2013 22:25:09 YES NONE
COHEAD FORWARDER 14/06/2013 22:25:09 YES NONE
COHEAD DISCGRP 14/06/2013 22:25:09 YES NONE
COHEAD CODISC 14/06/2013 22:25:09 YES NONE
COHEAD INVENCL 14/06/2013 22:25:09 YES NONE
COHEAD DELSTOP 14/06/2013 22:25:09 YES NONE
COHEAD CREDSTOP 14/06/2013 22:25:09 YES NONE
COHEAD TERMSPAY 14/06/2013 22:25:09 YES FREQUENCY
COHEAD INVCHA 14/06/2013 22:25:09 YES NONE
COHEAD INVCHARGE 14/06/2013 22:25:09 YES NONE
COHEAD CUVATCD 14/06/2013 22:25:09 YES NONE
COHEAD VAT 14/06/2013 22:25:09 YES NONE
COHEAD VATAMTLAST 14/06/2013 22:25:09 YES NONE
COHEAD COAMTQUAL 14/06/2013 22:25:09 YES NONE
COHEAD COAMT 14/06/2013 22:25:09 YES NONE
COHEAD COWEIGHT 14/06/2013 22:25:09 YES NONE
COHEAD COVOLUME 14/06/2013 22:25:09 YES NONE
COHEAD EXPRESS 14/06/2013 22:25:09 YES NONE
COHEAD FREIGHTLATER 14/06/2013 22:25:09 YES NONE
COHEAD TEXTCD 14/06/2013 22:25:09 YES FREQUENCY
COHEAD TEXTNO 14/06/2013 22:25:09 YES NONE
COHEAD DELAYBILL 14/06/2013 22:25:09 YES NONE
COHEAD GLSUBNO 14/06/2013 22:25:14 YES NONE
COHEAD GLSUBTYPE 14/06/2013 22:25:14 YES NONE
COHEAD COCONFCD 14/06/2013 22:25:14 YES NONE
COHEAD RETSHIPNO 14/06/2013 22:25:14 YES NONE
COHEAD INVNOREF 14/06/2013 22:25:14 YES NONE
COHEAD SALESREF 14/06/2013 22:25:14 YES HEIGHT BALANCED
COHEAD RETRATE 14/06/2013 22:25:14 YES NONE
COHEAD RETPRICECD 14/06/2013 22:25:14 YES NONE
COHEAD RETPRICECOMPL 14/06/2013 22:25:14 YES NONE
COHEAD RETAUTHAMOUNT 14/06/2013 22:25:14 YES HEIGHT BALANCED
COHEAD INVSHIPDATE 14/06/2013 22:25:14 YES NONE
COHEAD COOBJMISS 14/06/2013 22:25:14 YES NONE
COHEAD REGION 14/06/2013 22:25:14 YES NONE
COHEAD CASHCD 14/06/2013 22:25:14 YES NONE
COHEAD UPDDATE 14/06/2013 22:25:14 YES NONE
COHEAD CENTRE 14/06/2013 22:25:14 YES NONE
COHEAD PRICELIST 14/06/2013 22:25:14 YES NONE
COHEAD YOURCONO 14/06/2013 22:25:14 YES HEIGHT BALANCED
COHEAD EXPORTMARK 14/06/2013 22:25:14 YES NONE
COHEAD PRODLINE 14/06/2013 22:25:14 YES NONE
COHEAD TERMSPRICE 14/06/2013 22:25:14 YES NONE
COHEAD SOPLANDATE 14/06/2013 22:25:14 YES NONE
COHEAD FIXPRICEYN 14/06/2013 22:25:14 YES NONE
COHEAD PROJNO 14/06/2013 22:25:14 YES NONE
COHEAD PROJSUBNO 14/06/2013 22:25:14 YES NONE
COHEAD SAGREEMENTNO 14/06/2013 22:25:14 YES NONE
COHEAD INSTNO 14/06/2013 22:25:14 YES NONE
COHEAD GUARANTEE 14/06/2013 22:25:14 YES NONE
COHEAD WARRYN 14/06/2013 22:25:14 YES NONE
COROW CZTMPLANSTART 14/06/2013 22:47:26 YES NONE
COROW CZTMPLANEND 14/06/2013 22:47:26 YES NONE
COROW CZTRANSPORTORDERNO 14/06/2013 22:47:26 YES NONE
COROW CZFIRSTLEG 14/06/2013 22:47:26 YES NONE
COROW IMSCRCLVLTYPE 14/06/2013 22:47:26 YES NONE
COROW PROMCLOF 14/06/2013 22:47:26 YES NONE
COROW RETTRANSITCD 14/06/2013 22:47:26 YES NONE
COROW COACCEPTACKDATECNT 14/06/2013 22:47:26 YES NONE
COROW ROWWEIGHT 14/06/2013 22:47:26 YES NONE
COROW ROWVOL 14/06/2013 22:47:26 YES NONE
COROW VENDNO 14/06/2013 22:47:26 YES NONE
COROW PONO 14/06/2013 22:47:26 YES NONE
COROW PONOVEND 14/06/2013 22:47:26 YES NONE
COROW PRVEND 14/06/2013 22:47:26 YES NONE
COROW VENDDISC1 14/06/2013 22:47:26 YES NONE
COROW VENDDISC2 14/06/2013 22:47:26 YES NONE
COROW ROWSTATUS 14/06/2013 22:47:26 YES FREQUENCY
COROW DELNOTENO 14/06/2013 22:47:26 YES NONE
COROW INVSEQNO 14/06/2013 22:47:26 YES NONE
COROW SHIPSEQNO 14/06/2013 22:47:26 YES FREQUENCY
COROW INVNO 14/06/2013 22:47:26 YES NONE
COROW VENDOR_REF 14/06/2013 22:47:26 YES NONE
COROW ACCOUNT 14/06/2013 22:47:26 YES NONE
COROW CENTRE 14/06/2013 22:47:26 YES NONE
COROW QTYDEL 14/06/2013 22:47:26 YES NONE
COROW FINALDELCD 14/06/2013 22:47:26 YES NONE
COROW POWDATE 14/06/2013 22:47:26 YES NONE
COROW CONFIRMED 14/06/2013 22:47:26 YES NONE
COROW QTYPORES 14/06/2013 22:47:26 YES FREQUENCY
COROW QTYCORES 14/06/2013 22:47:26 YES NONE
COROW STOCKNOSHIP 14/06/2013 22:47:26 YES FREQUENCY
COROW POADATE 14/06/2013 22:47:26 YES NONE
COROW ASSEMBLECD 14/06/2013 22:47:26 YES NONE
COROW STOCKNOASS 14/06/2013 22:47:26 YES NONE
COROW AUTHDATE 14/06/2013 22:47:26 YES NONE
COROW RESDATE 14/06/2013 22:47:26 YES NONE
COROW RETARRIVE 14/06/2013 22:47:26 YES NONE
COROW RETCAUSE 14/06/2013 22:47:26 YES NONE
COROW RETMEASURE 14/06/2013 22:47:26 YES FREQUENCY
COROW RETAMOUNT 14/06/2013 22:47:26 YES NONE
COROW RETRATE 14/06/2013 22:47:26 YES NONE
COROW RETCONO 14/06/2013 22:47:26 YES NONE
COROW LENDCONO 14/06/2013 22:47:26 YES NONE
COROW QTYDET 14/06/2013 22:47:26 YES NONE
COROW SHIPNO 14/06/2013 22:47:26 YES NONE
COROW INVDATE 14/06/2013 22:47:26 YES NONE
COROW DELDATE 14/06/2013 22:47:26 YES NONE
COROW LIMITDEF 14/06/2013 22:47:26 YES NONE
COROW DETTYPE 14/06/2013 22:47:26 YES NONE
COROW AUTH 14/06/2013 22:47:26 YES NONE
COROW MTRLAV 14/06/2013 22:47:26 YES NONE
COROW PRAUTH 14/06/2013 22:47:26 YES NONE
COROW RETROWPOS 14/06/2013 22:47:26 YES NONE
COROW UPDDATE 14/06/2013 22:47:26 YES NONE
COROW OINUPD 14/06/2013 22:47:26 YES NONE
COROW PRICECD 14/06/2013 22:47:26 YES FREQUENCY
COROW SHIPMENTNO 14/06/2013 22:47:26 YES NONE
COROW EXPRESAUTH 14/06/2013 22:47:26 YES NONE
COROW INSTNO 14/06/2013 22:47:26 YES NONE
COROW FUNCNAME 14/06/2013 22:47:26 YES NONE
COROW LOTNO 14/06/2013 22:47:26 YES NONE
COROW SUBLOT 14/06/2013 22:47:26 YES NONE
COROW LOTCD 14/06/2013 22:47:26 YES NONE
COROW EXPIREDATE 14/06/2013 22:47:26 YES NONE
COROW SALESMAN 14/06/2013 22:47:26 YES NONE
COROW GUARANTEE 14/06/2013 22:47:26 YES NONE
COROW SODEBTYPE 14/06/2013 22:47:26 YES NONE
COROW PROJACTNO 14/06/2013 22:47:26 YES NONE
COROW PROJSHIPNO 14/06/2013 22:47:26 YES NONE
COROW PROJVENDNO 14/06/2013 22:47:26 YES NONE
COROW MARKINGTEXT 14/06/2013 22:47:26 YES NONE
COROW MARKINGNO 14/06/2013 22:47:26 YES NONE
COROW AUTHFINAL 14/06/2013 22:47:26 YES NONE
COROW PRODLINE 14/06/2013 22:47:26 YES NONE
COROW CORETDATE 14/06/2013 22:47:26 YES NONE
COROW INTINVNO 14/06/2013 22:47:26 YES NONE
COROW VAT 14/06/2013 22:47:26 YES FREQUENCY
COROW VATAMT 14/06/2013 22:47:26 YES NONE
COROW AUTOCODISC 14/06/2013 22:47:26 YES NONE
COROW COMPLROWCD 14/06/2013 22:47:26 YES NONE
COROW MARKINGADDR 14/06/2013 22:47:26 YES NONE
COROW COSHIPTOSEQNO 14/06/2013 22:47:26 YES FREQUENCY
COROW PRICELIST 14/06/2013 22:47:26 YES NONE
COROW TERMSPAY 14/06/2013 22:47:26 YES FREQUENCY
COROW CREHLDCD 14/06/2013 22:47:26 YES FREQUENCY
COROW PRICELVLID 14/06/2013 22:47:26 YES NONE
COROW USTAXCODE 14/06/2013 22:47:26 YES NONE
COROW IMRSRVTYPE 14/06/2013 22:47:26 YES NONE
COROW COPRMTID 14/06/2013 22:47:26 YES NONE
COROW PRIODATE 14/06/2013 22:47:26 YES NONE
COROW COADATE1 14/06/2013 22:47:26 YES NONE
COROW COBODATE 14/06/2013 22:47:26 YES NONE
COROW ARREFNO 14/06/2013 22:47:26 YES NONE
COROW CONO 14/06/2013 22:47:26 YES HEIGHT BALANCED
COROW ROWPOS 14/06/2013 22:47:26 YES FREQUENCY
COROW ROWSUBPOS 14/06/2013 22:47:26 YES FREQUENCY
COROW ROWSEQ 14/06/2013 22:47:26 YES FREQUENCY
COROW CONOSUB 14/06/2013 22:47:26 YES FREQUENCY
COROW OTYPE 14/06/2013 22:47:26 YES FREQUENCY
COROW SHIPDATE 14/06/2013 22:47:26 YES HEIGHT BALANCED
COROW CODATE 14/06/2013 22:47:26 YES HEIGHT BALANCED
COROW CREDATE 14/06/2013 22:47:26 YES HEIGHT BALANCED
COROW TEXTCD 14/06/2013 22:47:26 YES FREQUENCY
COROW TEXTNO 14/06/2013 22:47:26 YES NONE
COROW STOCKNOPU 14/06/2013 22:47:26 YES FREQUENCY
COROW STOCKNOSU 14/06/2013 22:47:26 YES FREQUENCY
COROW TOURNO 14/06/2013 22:47:26 YES HEIGHT BALANCED
COROW WEEKDAYNO 14/06/2013 22:47:26 YES FREQUENCY
COROW PICKST 14/06/2013 22:47:26 YES NONE
COROW STOCKLOC 14/06/2013 22:47:26 YES NONE
COROW PARTNO 14/06/2013 22:47:26 YES HEIGHT BALANCED
COROW PARTNOCOM 14/06/2013 22:47:26 YES NONE
COROW PARTDESCR1 14/06/2013 22:47:26 YES NONE
COROW PARTDESCR2 14/06/2013 22:47:26 YES NONE
COROW CLASS5 14/06/2013 22:47:26 YES NONE
COROW CLASS6 14/06/2013 22:47:26 YES NONE
COROW UNITSELL 14/06/2013 22:47:26 YES FREQUENCY
COROW UNIT 14/06/2013 22:47:26 YES FREQUENCY
COROW UNITRELSS 14/06/2013 22:47:26 YES NONE
COROW QTYUNIT 14/06/2013 22:47:26 YES FREQUENCY
COROW QTYCO 14/06/2013 22:47:26 YES FREQUENCY
COROW LOTTYPE 14/06/2013 22:47:26 YES FREQUENCY
COROW QTYRES 14/06/2013 22:47:26 YES FREQUENCY
COROW QTYBACK 14/06/2013 22:47:26 YES FREQUENCY
COROW BACKCD 14/06/2013 22:47:26 YES NONE
COROW BOMESS 14/06/2013 22:47:26 YES NONE
COROW PICKLISTNO 14/06/2013 22:47:26 YES NONE
COROW QTYPICK 14/06/2013 22:47:26 YES FREQUENCY
COROW COMPLDEL 14/06/2013 22:47:26 YES HEIGHT BALANCED
COROW QTYSHIP 14/06/2013 22:47:26 YES FREQUENCY
COROW COWDATE 14/06/2013 22:47:26 YES HEIGHT BALANCED
COROW COADATE 14/06/2013 22:47:26 YES FREQUENCY
COROW CUSTROWPOS 14/06/2013 22:47:26 YES FREQUENCY
COROW PRSALES 14/06/2013 22:47:26 YES NONE
COROW PRUNIT 14/06/2013 22:47:26 YES NONE
COROW PRLCCMAH 14/06/2013 22:47:26 YES NONE
COROW POROWAMT 14/06/2013 22:47:26 YES NONE
COROW CODISC 14/06/2013 22:47:26 YES NONE
COROW COROWDISC1 14/06/2013 22:47:26 YES NONE
COROW COROWDISC2 14/06/2013 22:47:26 YES NONE
COROW COROWDISC3 14/06/2013 22:47:26 YES NONE
COROW PRMANUAL 14/06/2013 22:47:26 YES NONE
COROW COROWAMT 14/06/2013 22:47:26 YES HEIGHT BALANCED
COROW PRICERIND 14/06/2013 22:47:26 YES FREQUENCY
COROW IMSCRCLVLID 14/06/2013 22:47:26 YES NONE
COROW COANNATTEST 14/06/2013 22:47:26 YES NONE
COROW COBRACKETTYPE 14/06/2013 22:47:26 YES NONE
COROW COBRACKETTYPEMODE 14/06/2013 22:47:26 YES NONE
COROW ACKDELIVDATE 14/06/2013 22:47:26 YES NONE
COROW SEQNO 14/06/2013 22:47:26 YES NONE
COROW BUUNIT 14/06/2013 22:47:26 YES NONE
COROW CHILEV 14/06/2013 22:47:26 YES NONE
COROW CHISEL 14/06/2013 22:47:26 YES NONE
COROW CUSTRESQTYUSED 14/06/2013 22:47:26 YES NONE
COROW CHANGEQTYCD 14/06/2013 22:47:26 YES FREQUENCY
COROW CHANGETEXTFLG 14/06/2013 22:47:26 YES FREQUENCY
COROW CHANGETEXTNO 14/06/2013 22:47:26 YES NONE
COROW SOSWAPPARTNO 14/06/2013 22:47:26 YES NONE
COROW COPRLCW 14/06/2013 22:47:26 YES NONE
COROW RETAPPROVAL 14/06/2013 22:47:26 YES FREQUENCY
COROW REQSHIPDATE 14/06/2013 22:47:26 YES HEIGHT BALANCED
COROW ACKSHIPDATE 14/06/2013 22:47:26 YES FREQUENCY
COROW ACKSHIPDATEORIGIN 14/06/2013 22:47:26 YES NONE
COROW WARRPERIOD 14/06/2013 22:47:26 YES NONE
COROW SOACTUALTIME 14/06/2013 22:47:26 YES NONE
COROW UNITPRICE 14/06/2013 22:47:26 YES NONE
COROW QTYCOUNITPRICE 14/06/2013 22:47:26 YES NONE
COROW QTYPICKUNITPRICE 14/06/2013 22:47:26 YES NONE
COROW QTYSHIPUNITPRICE 14/06/2013 22:47:26 YES NONE
COROW PRICEUNITPRICE 14/06/2013 22:47:26 YES NONE
COROW ROWINVSTATUS 14/06/2013 22:47:26 YES NONE
COROW REQINVDATE 14/06/2013 22:47:26 YES NONE
COROW COINVHLDFLG 14/06/2013 22:47:26 YES FREQUENCY
COROW ORIGROWSEQ 14/06/2013 22:47:26 YES NONE
COROW ORIGROWPOS 14/06/2013 22:47:26 YES NONE
COROW SCACCODE 14/06/2013 22:47:26 YES NONE
COROW FORWARDER 14/06/2013 22:47:26 YES NONE
COROW BILLOFLADING 14/06/2013 22:47:26 YES NONE
COROW EDPICKTICKSTATUS 14/06/2013 22:47:26 YES FREQUENCY
COROW RELTOOWSEQNO 14/06/2013 22:47:26 YES FREQUENCY
COROW EXPPRICE 14/06/2013 22:47:26 YES NONE
COROW EXPUOM 14/06/2013 22:47:26 YES NONE
COROW WTCODE 14/06/2013 22:47:26 YES NONE
COROW TAXLOC 14/06/2013 22:47:26 YES NONE
COROW INTRASTATPROCCD 14/06/2013 22:47:26 YES NONE
COROW WTVATPERCENT 14/06/2013 22:47:26 YES NONE
COROW STATSHIPFLG 14/06/2013 22:47:26 YES NONE
COROW COODATE 14/06/2013 22:47:26 YES NONE
COROW RSRVDQUALSUPPLY 14/06/2013 22:47:26 YES NONE
COROW EARLYRELWHSE 14/06/2013 22:47:26 YES FREQUENCY
COROW GRSPRICE 14/06/2013 22:47:26 YES NONE
COROW PRFRORDNO 14/06/2013 22:47:26 YES NONE
COROW RETTOTLNAMT 14/06/2013 22:47:26 YES NONE
COROW ORIGINVNO 14/06/2013 22:47:26 YES NONE
COROW ORIGCONO 14/06/2013 22:47:26 YES NONE
COROW COSALESPGMID 14/06/2013 22:47:26 YES FREQUENCY
COROW DISCTYPERDISC1 14/06/2013 22:47:26 YES NONE
COROW DISCTYPERDISC2 14/06/2013 22:47:26 YES NONE
COROW DISCTYPERDISC3 14/06/2013 22:47:26 YES NONE
COROW DISCTYPEPRICE 14/06/2013 22:47:26 YES NONE
COROW DISCTYPEODISC 14/06/2013 22:47:26 YES NONE
COROW EXPRETDATE 14/06/2013 22:47:26 YES NONE
COROW RCVRETDATE 14/06/2013 22:47:26 YES NONE
COROW EXPRETQTY 14/06/2013 22:50:02 YES NONE
COROW RCVRETQTY 14/06/2013 22:50:02 YES NONE
COROW COADJCAUSE 14/06/2013 22:50:02 YES FREQUENCY
COROW COAPPHLDCD 14/06/2013 22:50:02 YES NONE
COROW COGMHLDCD 14/06/2013 22:50:02 YES NONE
COROW CORETHLDCD 14/06/2013 22:50:02 YES NONE
COROW COMANPRHLDCD 14/06/2013 22:50:02 YES NONE
COROW COSALESPGMIDDISC1 14/06/2013 22:50:02 YES NONE
COROW COSALESPGMIDDISC2 14/06/2013 22:50:02 YES NONE
COROW COSALESPGMIDDISC3 14/06/2013 22:50:02 YES NONE
COROW COSALESPGMIDCODISC 14/06/2013 22:50:02 YES NONE
COROW PRPRICELIST 14/06/2013 22:50:02 YES NONE
COROW DISCTYPEPLPRICE 14/06/2013 22:50:02 YES NONE
COROW COSALESPGMIDPLPR 14/06/2013 22:50:02 YES FREQUENCY
COROW COOPACCR 14/06/2013 22:50:02 YES NONE
COROW PRELDELAH 14/06/2013 22:50:02 YES NONE
COROW PRCDATE 14/06/2013 22:50:02 YES NONE
COROW REQDELDATE 14/06/2013 22:50:02 YES HEIGHT BALANCED
COROW COCANCELDATE 14/06/2013 22:50:02 YES HEIGHT BALANCED
COROW CUAPPDATE 14/06/2013 22:50:02 YES NONE
COROW ACTDELDATE 14/06/2013 22:50:02 YES NONE
COROW COBORULE 14/06/2013 22:50:02 YES FREQUENCY
COROW VARIND 14/06/2013 22:50:02 YES NONE
COROW SUBTIND 14/06/2013 22:50:02 YES NONE
COROW REPLIND 14/06/2013 22:50:02 YES NONE
COROW ORDEREDPARTNO 14/06/2013 22:50:02 YES NONE
COROW CORELDATE 14/06/2013 22:50:02 YES NONE
COROW PROMID 14/06/2013 22:50:02 YES NONE
COROW DEALID 14/06/2013 22:50:02 YES NONE
COROW NDA 14/06/2013 22:50:02 YES NONE
COROW IMFCSTLVLTYPE 14/06/2013 22:50:02 YES NONE
COROW IMFCSTLVLID 14/06/2013 22:50:02 YES NONE
COROW COTRANSHLDCD 14/06/2013 22:50:02 YES FREQUENCY
COROW SELLPACK 14/06/2013 22:50:02 YES NONE
COROW COPICKHLDCD 14/06/2013 22:50:02 YES FREQUENCY
COROW COTEXTID 14/06/2013 22:50:02 YES NONE
COROW TAXFLG 14/06/2013 22:50:02 YES NONE
COROW SUBNO 14/06/2013 22:50:02 YES NONE
COROW SUBTYPE 14/06/2013 22:50:02 YES NONE
COROW SOACTIVITYCD 14/06/2013 22:50:02 YES NONE
COROW COMBINATIONID 14/06/2013 22:50:02 YES NONE
COROW STRUCTID 14/06/2013 22:50:02 YES NONE
COROW SCONO 14/06/2013 22:50:02 YES NONE
COROW SOTOOLKIT 14/06/2013 22:50:02 YES NONE
COROW SOSCHELINENO 14/06/2013 22:50:02 YES NONE
COROW SOINVSTARTDATE 14/06/2013 22:50:02 YES NONE
COROW SOINVENDDATE 14/06/2013 22:50:02 YES NONE
COROW SOREQSTATUS 14/06/2013 22:50:02 YES NONE
COROW SOVISITDATE 14/06/2013 22:50:02 YES NONE
COROW SOETADATE 14/06/2013 22:50:02 YES NONE
COROW PARTPACK 14/06/2013 22:50:02 YES NONE
COROW STOCKNOPICKCOMP 14/06/2013 22:50:02 YES NONE
COROW STOPTIMEDATE 14/06/2013 22:50:02 YES HEIGHT BALANCED
COROW SRVLVLUPD 14/06/2013 22:50:02 YES NONE
COROW SORETURNCD 14/06/2013 22:50:02 YES NONE
COROW PRINVNO 14/06/2013 22:50:02 YES NONE
COROW PRCONO 14/06/2013 22:50:02 YES NONE
Hope above data helps..
|
|
|
|
| Re: Sql query is slow [message #588680 is a reply to message #588646] |
Thu, 27 June 2013 06:49 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi prashanth7582,
This is strange... I would expect it will use the indexes each table has on the column named CONO, as it appears to be highly selective.
1. What happens if you try to force the query to be executed with multiple indexes, like this?
SELECT /*+ index(hd COHEAD_PK) index(cr A_COSHIPTO_PK) index (cf A_COFLX_PK) */
hd.cono,
hd.custno,
hd.externalcono,
cr.stocknosu,
cr.tourno
FROM cohead hd,a_coshipto cr,a_coflx cf
WHERE hd.cono = cr.cono
AND hd.otype = 61
AND EXISTS (SELECT 'x'
FROM corow cr2
WHERE cr2.rowstatus BETWEEN 790 AND 989
AND cr2.cono = cr.cono
AND cr2.tourno = cr.tourno
AND cr.coshiptoseqno = cr2.coshiptoseqno)
AND hd.cono = cf.cono
AND cr.coshiptoseqno = cf.coshiptoseqno
AND cf.corefqual = 'PRINT_FLAG'
AND cf.charvalue IS NULL;
2. What if we re-write the query, some combination like the example:
SELECT hd.cono, hd.custno, hd.externalcono, cr.stocknosu, cr.tourno
FROM cohead hd, a_coshipto cr, a_coflx cf
WHERE hd.cono = cr.cono
AND hd.otype = 61
AND cr.cono in (SELECT cr2.cono
FROM corow cr2
WHERE cr2.rowstatus BETWEEN 790 AND 989
AND cr2.tourno = cr.tourno
AND cr.coshiptoseqno = cr2.coshiptoseqno)
AND hd.cono = cf.cono
AND cr.coshiptoseqno = cf.coshiptoseqno
AND cf.corefqual = 'PRINT_FLAG'
AND cf.charvalue IS NULL;
Regards,
Andrey
|
|
|
|
| Re: Sql query is slow [message #588686 is a reply to message #588680] |
Thu, 27 June 2013 07:09 |
prashanth7582
Messages: 34
Registered: October 2005
Location: Bangalore
|
Member |
|
|
Hi Andrey,
1. I had tried having Hints to inform the optimiser to use indixes but the plan is bad compared to what it is used currently.I think that is the reason why Optimiser isnt using that.
SELECT /*+ index(hd COHEAD_PK) index(cr A_COSHIPTO_PK) index (cf A_COFLX_PK) */
HD.CONO,
HD.CUSTNO,
HD.EXTERNALCONO,
CR.STOCKNOSU,
CR.TOURNO
FROM cohead HD,a_coshipto CR,a_coflx CF
WHERE HD.CONO = CR.CONO
AND HD.OTYPE = 61
AND EXISTS (SELECT 'x'
FROM corow CR2
WHERE CR2.ROWSTATUS BETWEEN 790 AND 989
AND CR2.CONO = CR.CONO
AND CR2.TOURNO = CR.TOURNO
AND CR.COSHIPTOSEQNO = CR2.COSHIPTOSEQNO)
AND HD.CONO = CF.CONO
AND CR.COSHIPTOSEQNO = CF.COSHIPTOSEQNO
AND CF.COREFQUAL = 'PRINT_FLAG'
AND CF.CHARVALUE IS NULL;
Explain Plan :
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | | 61191 (4)|
|* 1 | TABLE ACCESS BY INDEX ROWID | A_COFLX | 1 | 22 | | 3 (0)|
| 2 | NESTED LOOPS | | 1 | 85 | | 61191 (4)|
| 3 | NESTED LOOPS | | 1 | 63 | | 61188 (4)|
|* 4 | HASH JOIN SEMI | | 1 | 34 | 1608K| 61186 (4)|
|* 5 | TABLE ACCESS BY INDEX ROWID| A_COSHIPTO | 58677 | 916K| | 28604 (1)|
| 6 | INDEX FULL SCAN | A_COSHIPTO_PK | 87222 | | | 1554 (1)|
|* 7 | TABLE ACCESS FULL | COROW | 901K| 15M| | 31138 (6)|
|* 8 | TABLE ACCESS BY INDEX ROWID | COHEAD | 1 | 29 | | 2 (0)|
|* 9 | INDEX UNIQUE SCAN | COHEAD_PK | 1 | | | 1 (0)|
|* 10 | INDEX RANGE SCAN | A_COFLX_PK | 1 | | | 2 (0)|
----------------------------------------------------------------------------------------------
2. Changing EXISTS to IN clause.
[
SELECT hd.cono, hd.custno, hd.externalcono, cr.stocknosu, cr.tourno
FROM cohead hd, a_coshipto cr, a_coflx cf
WHERE hd.cono = cr.cono
AND hd.otype = 61
AND cr.cono in (SELECT cr2.cono
FROM corow cr2
WHERE cr2.rowstatus BETWEEN 790 AND 989
AND cr2.tourno = cr.tourno
AND cr.coshiptoseqno = cr2.coshiptoseqno)
AND hd.cono = cf.cono
AND cr.coshiptoseqno = cf.coshiptoseqno
AND cf.corefqual = 'PRINT_FLAG'
AND cf.charvalue IS NULL;
Explain Plan :
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | | 11634 (3)|
| 1 | NESTED LOOPS SEMI | | 1 | 85 | | 11634 (3)|
|* 2 | HASH JOIN | | 2703 | 176K| 1648K| 3493 (8)|
|* 3 | TABLE ACCESS FULL | A_COFLX | 49602 | 1065K| | 1090 (8)|
|* 4 | HASH JOIN | | 43144 | 1895K| 1608K| 2197 (8)|
|* 5 | TABLE ACCESS FULL | A_COSHIPTO | 58677 | 916K| | 651 (7)|
|* 6 | TABLE ACCESS FULL | COHEAD | 42334 | 1198K| | 1377 (9)|
|* 7 | TABLE ACCESS BY INDEX ROWID| COROW | 10 | 180 | | 3 (0)|
|* 8 | INDEX RANGE SCAN | COROW_PK | 2 | | | 2 (0)|
----------------------------------------------------------------------------------------
Seems to remain same.
If I remove the exists clause,query is faster and gets executed in 13secs but that is very much reqd..So confused how to improve the above query.
CM: fixed the code tags
[Updated on: Thu, 27 June 2013 07:16] by Moderator Report message to a moderator |
|
|
|
| Re: Sql query is slow [message #588694 is a reply to message #588686] |
Thu, 27 June 2013 07:49 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi prashanth7582 ,
How much time does it take to execute the query(from SQL*Plus issue "set timing on":
SELECT /*+ index(hd COHEAD_PK) index(cr A_COSHIPTO_PK) index (cf A_COFLX_PK) index (CR2 COROW_PK) */
HD.CONO,
HD.CUSTNO,
HD.EXTERNALCONO,
CR.STOCKNOSU,
CR.TOURNO
FROM cohead HD,a_coshipto CR,a_coflx CF
WHERE HD.CONO = CR.CONO
AND HD.OTYPE = 61
AND EXISTS (SELECT 'x'
FROM corow CR2
WHERE CR2.ROWSTATUS BETWEEN 790 AND 989
AND CR2.CONO = CR.CONO
AND CR2.TOURNO = CR.TOURNO
AND CR.COSHIPTOSEQNO = CR2.COSHIPTOSEQNO)
AND HD.CONO = CF.CONO
AND CR.COSHIPTOSEQNO = CF.COSHIPTOSEQNO
AND CF.COREFQUAL = 'PRINT_FLAG'
AND CF.CHARVALUE IS NULL;
2. If you want to see real execution plan and actual cardinality costs e.t.c you need to run a query with /*+ gather_plan_statistics */
Hint and then use dbms_xplan.display_cursor in order to query the plan table, while it is still in the shared pool.
Regards,
Andrey
[Updated on: Thu, 27 June 2013 07:50] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Wed Jan 29 15:45:47 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
