Dennis:
Thanks for those helpful suggestions. I'll give it a try.
David Jones
ITResource
-----Original Message-----
WILLIAMS
Sent: Thursday, September 19, 2002 8:04 AM
To: Multiple recipients of list ORACLE-L
David
- I'm assuming this query is being run interactively?
- I recently posted a request for help on a query, and the winning
solution was to add a FIRST_ROWS hint, so naturally at this point I'm
convinced this will solve all performance issues.
- Knowing how many rows are being returned will be helpful in planning
alternate strategies.
- Try putting an index on your podata table.
- How selective is your ownerid column? That is, how many distinct values
are in that column?
- Have you considered bitmapped indexes?
- I get by implication that you found this query in the SQL buffer and
have started there. I would suggest tracking down the application and users
to find out more about how and when they use this query. It also makes you
seem more proactive, which you are being, so you might as well get credit.
- As to the distinct qualifier, here is a tip I found via Google:
21. Use EXISTS in Place of DISTINCT
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.
�
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
-----Original Message-----
Sent: Wednesday, September 18, 2002 8:08 PM
To: Multiple recipients of list ORACLE-L
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).
--
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).
