Re: Fetch table names from query
From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 22 May 2008 11:48:22 -0700
Message-ID: <1211482117.135858@bubbleator.drizzle.com>
>
>
>
>
> I think he means he wants to identify obsolete data in his results cache,
> so he can refresh it from the database, not delete data from the database.
>
> However, I agree that this is not a wonderful idea, as it is essentially
> recreating Oracle functionality. If results caching is that critical,
> I would suggest running 11g, which implements it.
Date: Thu, 22 May 2008 11:48:22 -0700
Message-ID: <1211482117.135858@bubbleator.drizzle.com>
Dan Blum wrote:
> DA Morgan <damorgan_at_psoug.org> wrote:
>> ?lvaro G. Vicario wrote: >>> Is there any simple way to extract the table names of a given SELECT >>> query without actually running it? >>> >>> I've implemented a very simple results cache in my PHP application but >>> I'd like to improve it. And one of the key points is finding out what >>> tables a query reads from so I can handle obsolete data. Writing a >>> reliable SQL parser in PHP looks like a hard task and obtaining a full >>> explain plan from the Oracle server looks like an overkill. >>> >>> Any ideas? >>> >>> Thank you in advance,
>
>> This is horrifying. Obsolete data is defined by an SLA with the customer >> not by whether it is accessed.
>
>> For example I have organizations here in the US that are, by law, >> required to keep data online for 7 years. It hopefully will never be >> accessed. But on audit, if it isn't there, they are in a boatload of >> trouble.
>
>> This is a really bad idea, for many reasons, you should drop >> immediately. Leave managing the database to the database professionals. >> It is not something someone writing PHP should involve themselves in.
>
> I think he means he wants to identify obsolete data in his results cache,
> so he can refresh it from the database, not delete data from the database.
>
> However, I agree that this is not a wonderful idea, as it is essentially
> recreating Oracle functionality. If results caching is that critical,
> I would suggest running 11g, which implements it.
Then, assuming we are talking about 11g, which is the only version that has a result cache that is automatically implemented by Oracle and not subject to developer meddling except via the DBMS_RESULT_CACHE built-in package. http://www.psoug.org/reference/dbms_result_cache.html
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Thu May 22 2008 - 13:48:22 CDT