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).
