OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » QUERY REWRITING (9.2.0.7.WONDOWS SERVER 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

QUERY REWRITING [message #338085]

Sun, 03 August 2008 04:57

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

Report message to a moderator

Re: QUERY REWRITING [message #338086 is a reply to message #338085]

Sun, 03 August 2008 04:59

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: QUERY REWRITING [message #338618 is a reply to message #338085]

Tue, 05 August 2008 08:24

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.

Report message to a moderator

Previous Topic:	DB link query.
Next Topic:	selectivity in index usage

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 15:05:50 CST 2024