| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query performance improvement ?
David
Avoid joins that require the DISTINCT qualifier on the SELECT list when you submit queries used to determine information at the owner end of a one-to-many relationship (e.g. departments that have many employees).
For example:
Least Efficient :
SELECT DISTINCT DEPT_NO, DEPT_NAME
FROM DEPT D, EMP E
WHERE D.DEPT_NO = E.DEPT_NO
Most Efficient :
SELECT DEPT_NO, DEPT_NAME
FROM DEPT D
WHERE EXISTS (SELECT 'X'
FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO);
EXISTS is a faster alternative because the RDBMS kernel realizes that when
the sub-query has been satisfied once, the query can be terminated.
-----Original Message-----
Sent: Wednesday, September 18, 2002 8:08 PM
To: Multiple recipients of list ORACLE-L
Dennis:
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:
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>
-----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=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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.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). _________________________________________________________________ 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.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 Thu Sep 19 2002 - 10:03:36 CDT
 |
 |