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>


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.org
Received on Thu May 22 2008 - 13:48:22 CDT

Original text of this message