Dennis: 1. The distinct seems difficult to avoid. 2. SQL> select count(*) from podata;
COUNT(*) ---------- 18679 SQL> select count(*) from InvData; COUNT(*) ---------- 83315 3. The query returned no rows at this moment, the reason I want to tune this query is because it has been recorded by statspack. Buffer Gets Executions Gets per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------ 233,835 357 655.0 21.3 1783599440 select distinct A.* from POData A , InvData B where A.ID = B.PURCHASEORDERID AND A.OWNERID=B.OWNERID and A.ownerId = 1 a nd B.Status = 12 order by A.ID 4. This query is running under a production system 5. The table just been analyzed recently SQL> select TABLE_NAME, LAST_ANALYZED from user_tables where table_name in ('PODATA', 'INVDATA'); TABLE_NAME LAST_ANAL ---------- --------- INVDATA 15-SEP-02 PODATA 15-SEP-02 David Jones ITResource -----Original Message----- WILLIAMS Sent: Wednesday, September 18, 2002 3:28 PM To: Multiple recipients of list ORACLE-L David A couple of observations and questions: - The distinct statement looks fairly expensive. Is there a way to avoid it? - Are there any columns on POData? How many rows are in this table? - How many rows does this query return? - Do the tables contain the number of rows they will in production? - Have you analyzed the tables recently? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> -----Original Message----- Sent: Wednesday, September 18, 2002 4:23 PM To: Multiple recipients of list ORACLE-L Dear Lister: Is there any way to further improve the following query performance ? Thanks David Jones ITResource SQL> select distinct A.* >from POData A , InvData B where A.OWNERID=B.OWNERID AND A.ID = >B.PURCHASEORDERID >and B.Status = 12 and A.ownerId = 1 order by A.ID; ================================================================ Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=398 Card=8971 Bytes=663854) 1 0 SORT (UNIQUE) (Cost=284 Card=8971 Bytes=663854) 2 1 HASH JOIN (Cost=170 Card=8971 Bytes=663854) 3 2 INDEX (FAST FULL SCAN) OF 'INVDATA_2' (NON-UNIQUE) (Cost=99 Card=8971 Bytes=71768) 4 2 TABLE ACCESS (FULL) OF 'PODATA' (Cost=36 Card=18152 Bytes=1198032) Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 656 consistent gets 0 physical reads 0 redo size 1356 bytes sent via SQL*Net to client 314 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 0 rows processed SQL> desc PODATA Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(38) HID NUMBER(38) OWNERID NOT NULL NUMBER(38) COMPANYID NUMBER(38) REFERENCE VARCHAR2(50) STATUS NUMBER(38) APPROVEDAMOUNT FLOAT(126) PAIDAMOUNT FLOAT(126) TOTALINVOICEDAMOUNT FLOAT(126) APPROVEDINVOICEDAMOUNT FLOAT(126) APPROVEDDATE DATE INITIATORCOMMENT VARCHAR2(1000) AUTOAPPROVAL CHAR(1) AUTOAPPROVALAMOUNT FLOAT(126) AUTOAPPROVALPERCENTAGE FLOAT(126) AUTOAPPROVALGRACEPERIOD NUMBER(38) RETAINPERCENT NUMBER(38) APPROVEDBY NUMBER(38) PROJECTSCOST NUMBER(38) AUTOAPPROVALTOTALAMOUNT FLOAT(126) GLTYPE NUMBER(38) SQL> desc InvData Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(38) HID NUMBER(38) REFERENCE VARCHAR2(50) OWNERID NOT NULL NUMBER(38) COMPANYID NUMBER(38) PURCHASEORDERID NUMBER(38) PURCHASEORDERREFERENCE VARCHAR2(50) STATUS NOT NULL NUMBER(38) INITIATORCOMMENT VARCHAR2(1000) VENDORCOMMENT VARCHAR2(1000) PAYMENTTERMS NUMBER(38) INVOICEAMOUNT FLOAT(126) PAIDAMOUNT FLOAT(126) AMOUNTRETAINED FLOAT(126) SUBMITTEDDATE DATE APPROVEDDATE DATE PAIDDATE DATE AUTOPROCESSED CHAR(1) APPROVEDBY NUMBER(38) INVOICETYPE NUMBER(38) PAYEE NUMBER(38) LOGIDSENTTOSAP NUMBER(38) LOGIDRECONCILERECEIVED NUMBER(38) ESTIMATEBY NOT NULL VARCHAR2(1) LOGIDFOREDI NUMBER(38) CREATEDBY NUMBER(38) SQL> select * from user_ind_columns where index_name = 'INVDATA_2'; INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION DESC ---------- ---------- --------------- --------------- ---- INVDATA_2 INVDATA OWNERID 1 ASC INVDATA_2 INVDATA PURCHASEORDERID 2 ASC INVDATA_2 INVDATA STATUS 3 ASC _________________________________________________________________ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Jones INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _________________________________________________________________ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Jones INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).