Option-1 If it is a Data ware housing environment- Increase the Hash
size, use use_hash hint and make database parameters which will
generated hash join type. All_rows, use hash hint is a good option

Option-2 If it is a online application if index is a good option-(a) Go
for caching the small tables, (b) try to force index by setting the
database parameter for First rows. You can use hint /*+ first_rows(..)
Use_nl(..) ordered(..) */ hint to optimise the query.



Thanks & Regards,
Himanshu Mahapatra
-----Original Message-----
From: Himanshu Jani
[mailto:oracledba-ezmlmshield-x20550955.[Email address protected]
Sent: 25 November 2007 10:24
To: LazyDBA Discussion
Subject: SP optimization ideas needed

Hello DBAs,

I am stuck in a situation where my client wants to optimize the queries
without adding any more indexed to be added to the tables or changing
any
configuration parameter to the server or dbs.

The system in designed to be very high OLTP, 60-100 TPS. so it has very
less
indexes, majority of the imp tables are heap. Now the batch processing
queries are taking longer and longer day by day.
Many queries are having same problem, fetching millions of rows from a
heap
table (same imp table).

Now, the conditions are,
- you cant add any index
- you cant modify any index
- dont look at db and srvr configuration
- dont think abt network performance
- dont think abt server memory and cache

Just modify the query and make it work.

Most typical queries looks like this

select id, ver, val
from table
where id >= 1200000000000 and id <= 1500000000000
and val & 123 = 0 and val &34534 > 0

There is an index on (id,ver).

There are around 8 million rows for the above range and in total 35
million
in table.

Please give any ideas or suggestion to improve this query.

Following suggestions are already given by intellectuals
a - sack your data modeler
b - prayers are very powerful
c - live with the problem


Thank you very much and best regards
Himanshu Jani




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



Oracle LazyDBA home page