Home » RDBMS Server » Performance Tuning » QUERY REWRITING (9.2.0.7.WONDOWS SERVER 2003)
QUERY REWRITING [message #338085] |
Sun, 03 August 2008 04:57 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
jayarajkrishna
Messages: 7 Registered: August 2008 Location: Mumbai,India
|
Junior Member |
|
|
HI ALL,
BELOW QUERY TAKING TOO MUCH TEMPORARY TABLE SPACE AND IT GOING FOR FULL TABLE SCAN.SO IT TAKING MORE TIME TO EXECUTE.i WANT TO REWRITE THE QUERY..PLZZ HELP ME..
OS:WINDOWS SERVER 2003,VERSION:9.2.0.7
SELECT /*+ ORDERED NO_EXPAND USE_MERGE (a2) */ a2.c0 c0, a2.c1 c1, a1.c0 c2,a1.c1 c3, a1.c2 c4, a1.c3 c5, a1.c4 c6, a1.c5 c7, a1.c6 c8, a1.c7 c9,a1.c8 c10, a1.c9 c11, a1.c10 c12, a1.c11 c13, a1.c12 c14, a1.c13 c15,a1.c14 c16, a1.c15 c17, a1.c16 c18, a1.c17 c19, a1.c18 c20, a1.c19 c21,a1.c20 c22, a1.c21 c23, a1.c22 c24, a1.c23 c25, a1.c24 c26, a1.c25 c27,a1.c26 c28, a1.c27 c29, a1.c28 c30, a1.c29 c31, a1.c30 c32, a1.c31 c33,
a1.c32 c34, a1.c33 c35, a1.c34 c36, a1.c35 c37, a1.c36 c38, a1.c37 c39,a1.c38 c40, a2.c4 c41, a2.c3 c42, a2.c2 c43
FROM :q10212000 a1, (SELECT /*+ NO_EXPAND ROWID (a3) */
a3.ind_inh_invce_nmbr c0, a3.ind_inh_tr_code c1,
a3.ind_vim_item_code c2, a3.ind_vim_item_vrsn c3, a3.ind_qnty c4
FROM csd.c_invce_dtl PX_GRANULE (0,BLOCK_RANGE,DYNAMIC) a3) a2
|
|
|
|
Re: QUERY REWRITING [message #338618 is a reply to message #338085] |
Tue, 05 August 2008 08:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Some sort of Join between A1 and A2 would probably prevent a cartesian product of your result set.
If you have no WHERE clause that restrict the rows returned from a table, you can hardly be suprised when Oracle uses a Full table scan to access the table.
Personally, I'd loose the Hints, make sure the stats are up to date, and see what happens.
Further information will be provided when you've given us things like an Explain Plan, that the Sticky at the top of this forum asks for.
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:00:12 CDT 2024
|