| Query Tunning [message #372075] |
Tue, 09 January 2001 06:43  |
sandeep
Messages: 110
Registered: October 2000
|
Senior Member |
|
|
I want this query to be optimised. Is there any way I can do it?
The query goes like this.
SELECT DISTINCT toh.order_nb,"
toh.compaq_sales_organization_cd "SalesOrgCode",
toh.cust_ship_to_id "ShipID",
c1.detail_tx "Status",
toh.order_dt "OrderDate",
toh.order_close_dt "CloseDate",
toh.sold_to_party "SoldtoId",
tod.cust_po_nb "PoNB",
ts.SHIP_FROM_ID "ShipFromId",
ts.CARRIER_CD "CarrierId"
FROM tms_order_header toh,
tms_code_table_detail c1,
tms_order_detail tod,
TMS_SHIPMENT ts,
TMS_ORDER tmo
WHERE c1.detail_cd(+) = toh.order_status_cd
AND c1.group_cd (+)= 'OSD'
AND tod.compaq_sales_organization_cd = toh.compaq_sales_organization_cd
AND toh.order_dt > ( SELECT ADD_MONTHS(SYSDATE,-3)
FROM dual )
AND tod.order_nb = toh.order_nb
AND ts.SHIPMENT_ID = tmo.SHIPMENT_ID
AND tmo.ORDER_NB = toh.ORDER_NB
1. All the columns in the query are indexed.
2. All the big tables are in the left side of the join.
3.
|
|
|
|
| Re: Query Tunning - some pointers... [message #372086 is a reply to message #372075] |
Tue, 09 January 2001 13:06  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
1.) make sure that the tables are analyzed (analyze table tms_order_header estimate statistics; etc.)
2.) replace the unnecessary subquery with:
> add_months(sysdate, -3). (This wont help much though).
3.) Do an explain plan on the query (TOAD is great for this, else use "set autotrace on" in sql*plus). "set timing on" to get duration.
4.) remember that the where clause (apart from the join) gets evaluated from the bottom up. Place most selective statement at the bottom.
5.) The order of the tables in the FROM clause can make a difference. Smallest ones first (or ones with least refererential integrity???)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
