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 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #588553 is a reply to message #588550] Wed, 26 June 2013 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Sql query is slow [message #588605 is a reply to message #588553] Wed, 26 June 2013 18:24 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: Different Timings between 2 Nodes
Next Topic: performance issue with the query
Goto Forum:
  


Current Time: Thu Mar 28 08:38:34 CDT 2024