Hi Himanshu,

You can also think of Purging and Archiving of data.

Regards,

Hrishikesh Vaidya
____________________________________________________________________________
________________________________________
From: Mujeeb [oracledba-ezmlmshield-x24777755.[Email address protected]
Sent: Monday, November 26, 2007 2:56 PM
To: LazyDBA Discussion
Subject: RE: SP optimization ideas needed

How about Partitioning the tables ?

Thanks & Regards
Mujeeb

-----Original Message-----
From: Himanshu Jani
[mailto:oracledba-ezmlmshield-x20550955.[Email address protected]
Sent: Sunday, November 25, 2007 3:54 PM
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




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

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

Oracle LazyDBA home page