| VERY URGENT -- SQL- TUNING [message #124027] |
Wed, 15 June 2005 19:49  |
Fl_reader
Messages: 15
Registered: March 2005
|
Junior Member |
|
|
How to tune the query to select 80 Million data.
Design of the table is as follows:
Cust_no ,Inv_no,Inv_dt,pay_dt, Amt,Py_no,cr_no,adj_no,pay_mtd
1 01 2/9/05 2/09/05 10 CK
1 01 2/9/05 2/25/05 5 CC
1 01 2/9/05 3/03/05 -15 1233 CC
1 02 3/5/05 4/25/05 25 CKC
1 02 3/5/05 5/25/05 -25 455 CK
2 11 3/5/05 4/9/05 30 CC
2 12 3/5/05 5/23/05 -30 4478 CHK
---
My output should be
Cust_no Bill_amt Bill_dt Pay_amt Pay_dt Pay_mthd
1 15 2/9/05 -15 3/3/05 CC
1 25 3/5/05 -25 5/25/05 CK
2 30 3/5/05 -30 5/23/05 CHK
|
|
|
|
|
|
|
|
| Re: VERY URGENT -- SQL- TUNING [message #124174 is a reply to message #124047] |
Thu, 16 June 2005 12:50  |
Fl_reader
Messages: 15
Registered: March 2005
|
Junior Member |
|
|
OPERATION OPTIONS OPERATION_1 OPTIMIZER COST
SELECT STATEMENT SELECT STATEMENT HINT: RULE
SORT GROUP BY SORT
NESTED LOOPS NESTED LOOPS
NESTED LOOPS NESTED LOOPS
NESTED LOOPS NESTED LOOPS
TABLE ACCESS BY INDEX ROWID TABLE ACCESS ANALYZED
INDEX RANGE SCAN INDEX
TABLE ACCESS BY INDEX ROWID TABLE ACCESS ANALYZED
AND-EQUAL AND-EQUAL
INDEX RANGE SCAN INDEX
INDEX RANGE SCAN INDEX
TABLE ACCESS BY INDEX ROWID TABLE ACCESS ANALYZED
INDEX RANGE SCAN INDEX
TABLE ACCESS BY INDEX ROWID TABLE ACCESS ANALYZED
INDEX RANGE SCAN INDEX |
|
|
|
| Re: VERY URGENT -- SQL- TUNING [message #124179 is a reply to message #124174] |
Thu, 16 June 2005 14:03 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
We still don't know anything about your query or your Oracle version.
However my #1 top SQL tuning tip, which routinely works miracles (I'm serious) is Remove any Optimiser Hints. Is there by any chance a /*+ RULE */ hint? You will also want to analyze the table(s) involved in the query, unless for some reason you cannot use the CBO in which case it's a whole different ball game.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
