Fetch table names from query

#1

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

#2

Álvaro G. Vicario wrote:

Quote:

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

#3

Álvaro G. Vicario wrote:

Quote:

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

#4

Álvaro G. Vicario wrote:

Quote:

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

#5

Álvaro G. Vicario wrote:

Quote:

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

#6

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:

Quote:

?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 (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#7

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:

Quote:

?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 (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#8

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:

Quote:

?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 (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#9

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:

Quote:

?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 (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#10

Dan Blum escribió:

Quote:

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.

Quote:

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