Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Query performance improvement ?
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=18152Bytes=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: djones1688_at_hotmail.com 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: ListGuru_at_fatcity.com (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).Received on Wed Sep 18 2002 - 16:23:22 CDT
![]() |
![]() |