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
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 #338086 is a reply to message #338085] Sun, 03 August 2008 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, you broke many rules.

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: QUERY REWRITING [message #338618 is a reply to message #338085] Tue, 05 August 2008 08:24 Go to previous message
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.
Previous Topic: DB link query.
Next Topic: selectivity in index usage
Goto Forum:
  


Current Time: Fri Dec 09 21:42:53 CST 2016

Total time taken to generate the page: 0.13428 seconds