Range partitioning on id?

Regards,

Simon Palmer




"Himanshu Jani " <oracledba-ezmlmshield-x20550955.[Email address protected]

25/11/2007 10:23

To
"LazyDBA Discussion" <[Email address protected]
cc

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 message and any attachment are confidential and may be privileged or otherwise protected from disclosure.
If you are not the intended recipient, please telephone or email the sender and delete this message and any attachment from your system.
If you are not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.
Please consider the environmental impact before printing this document and its attachment(s). Print black and white and double-sided where possible.
------------------------------------------------------------------------------

Oracle LazyDBA home page