I have a table with 460+ million plus rows. This table is read "once" a day to retrieve a single maxrownum used for downstream processing. The problem I am having is that this select SQL is taking about 1 hr 10-20 minutes to retrieve this one value! How can that be? I have only three indexes one of which is keyed off of the maxrownum.
Can anyone suggest/recommend a performance tip/strategy that I can use to improve my retrieval process? This may not be the best plan, but I was thinking perhaps I could create a tiny whinny table to store the maxrownum value and read from the small table for theis value. But, then I have to introduce another table, additional I/O, extra storage and maintenance, etc, etc in to the equation...So, what does anyone think about that?
Please do send in your comments, suggestion and recommendations. Articles and other pointers are very much welcomed.