Fetch table names from query

#21

Shakespeare escribió:

Quote:

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

#22

Dan Blum wrote:

Quote:

DA Morgan <damorgan (AT) psoug (DOT) 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

#23

Dan Blum wrote:

Quote:

DA Morgan <damorgan (AT) psoug (DOT) 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

#24

Dan Blum wrote:

Quote:

DA Morgan <damorgan (AT) psoug (DOT) 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

#25

Dan Blum wrote:

Quote:

DA Morgan <damorgan (AT) psoug (DOT) 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

#26

Álvaro G. Vicario wrote:

Quote:

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

#27

Álvaro G. Vicario wrote:

Quote:

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

#28

Álvaro G. Vicario wrote:

Quote:

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

#29

Álvaro G. Vicario wrote:

Quote:

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