We will have to agree to disagree.

M.

-----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


**********************************************************************
This e-mail is confidential and may be privileged.
It may only be read, copied and used by the intended recipient.
If you have received it in error please contact the sender immediately
by return e-mail. Please then delete the e-mail and any copies of it
and do not use or disclose its contents to any person.
**********************************************************************
Registered Office: Farncombe House, Broadway, Worcestershire, WR12 7LJ

AccuRead Limited Registered Number: 3076187 England
GSL UK Limited Registered Number: 3333860 England
Global Solutions Limited Registered Number: 3189802 England
**********************************************************************


Oracle LazyDBA home page