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,


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

Á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.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan <damorgan@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.

--
__________________________________________________ _____________________
Dan Blum tool@panix.com
"I wouldn't have believed it myself if I hadn't just made it up."

Dan Blum escribió:
>> This is horrifying. Obsolete data is defined by an SLA with the customer
>> not by whether it is accessed.

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

Er... Yes, that's it. I can't even understand what Morgan means. A
results cache must not be such a bad idea if many major DBMS implement
it in recent versions, including Oracle itself.


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

However, the database server runs 10g. If I tell the customer that in
order to run an auxiliary web reports application they need to buy a
licence for 11g, upgrade their server, check that all existing apps
(functions, stored procedures, ERP, several custom Oracle Forms
utilities, online orders web site, backup system...) do not break... I'd
say they won't be amused.

So I assume the only way to obtaining table names is explain plan :-? I
guess I'll stick to my PHP-side solution by now.


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

""Álvaro G. Vicario"" <alvaroNOSPAMTHANKS@demogracia.com> schreef in bericht
news:g139q7$j7j$1@huron.algomas.org...
> Dan Blum escribió:
>>> This is horrifying. Obsolete data is defined by an SLA with the customer
>>> not by whether it is accessed.
>
>> 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.
>
> Er... Yes, that's it. I can't even understand what Morgan means. A results
> cache must not be such a bad idea if many major DBMS implement it in
> recent versions, including Oracle itself.
>
>
>> 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.
>
> However, the database server runs 10g. If I tell the customer that in
> order to run an auxiliary web reports application they need to buy a
> licence for 11g, upgrade their server, check that all existing apps
> (functions, stored procedures, ERP, several custom Oracle Forms utilities,
> online orders web site, backup system...) do not break... I'd say they
> won't be amused.
>
> So I assume the only way to obtaining table names is explain plan :-? I
> guess I'll stick to my PHP-side solution by now.
>
>
> --
> -- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
> -- Mi sitio sobre programación web: http://bits.demogracia.com
> -- Mi web de humor al baño María: http://www.demogracia.com
> --

You could make life a little bit simpler by storing relevant tablenames in a
table (!) and scanning your queries and cache for these names. Not 100%
accurate, but it might help a little.

Shakespeare

Shakespeare escribió:
> You could make life a little bit simpler by storing relevant tablenames in a
> table (!) and scanning your queries and cache for these names. Not 100%
> accurate, but it might help a little.

Actually, that's exactly my current PHP-side solution ;-)

<?php

$regex_tables = implode('|', $tables); // $tables contains table names
$regex = '/\W(FROM|JOIN)\s+(' . $regex_tables . ')(?:\W|$)/i';
preg_match_all($regex, $sql, $matches);

if(isset($matches[2]) && count($matches[2])>0){
$tables = array_unique($matches[2]);
sort($tables);
}

?>



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

Dan Blum wrote:
> DA Morgan <damorgan@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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Álvaro G. Vicario wrote:
> Dan Blum escribió:
>>> This is horrifying. Obsolete data is defined by an SLA with the customer
>>> not by whether it is accessed.
>
>> 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.
>
> Er... Yes, that's it. I can't even understand what Morgan means. A
> results cache must not be such a bad idea if many major DBMS implement
> it in recent versions, including Oracle itself.
>
>
>> 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.
>
> However, the database server runs 10g. If I tell the customer that in
> order to run an auxiliary web reports application they need to buy a
> licence for 11g, upgrade their server, check that all existing apps
> (functions, stored procedures, ERP, several custom Oracle Forms
> utilities, online orders web site, backup system...) do not break... I'd
> say they won't be amused.
>
Consider Web cache (which you probably have,
but not installed/activated?)

--

Regards,
Frank van Bortel