Sounds like they're not giving you much wiggle room.
Based on your estimate concerning counts. 8 million in the range and 35
million total, you will be returning slightly over 25 percent of the
records in the table. Unless the data is ordered in the database by id,
ver, those records are probably distributed randomly across the blocks.
In other words, you may do just as well with a full tablescan as an
index lookup, however this will continue to incur degraded performance
as more and more records are added.
There is no mentioning of partitioning, there is no explain plan, no
reference to when the last time the table/indexes were analyzed, no
mention of how the table is growing over time, no mention of how data is
being removed from the table, etc.
In my experience, batch processing is generally associated with two
types of activities. Data loads, or report generation. Typically,
report generation would not be expected from an OLTP type of database,
and a select statement not associated with a data load.
Based on these items and lack of insight, your options are
a - sack your data modeler
b - prayers are very powerful
c - live with the problem
More details may or may not lead to better options.
Thanks
Dave
-----Original Message-----
From: Himanshu Jani
[mailto:oracledba-ezmlmshield-x20550955.[Email address protected]
Sent: Sunday, November 25, 2007 5:24 AM
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
______________
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________
Oracle LazyDBA home page