Re: v$sql - executions vs loads vs invalidations

From: Subodh Deshpande <>
Date: Fri, 4 May 2012 07:56:17 +0530
Message-ID: <>

select schema_name.<table_name>.*, rowid from schema.table_name where 1=0 this will not give error..but will not return any rows too. I had seen many developers using such sentences to create staging/holding tables in oracle 7 and 8 era and also in the era of pb and vb as front end.

eg create table t1 as select * from t2 where 1=2 will create table t1 exactly like t2 including constraints without data in it.

from after 8i, 9i onwards with introduction of global temporary tables, external tables, mviews, and sub queries, I think use of such create statements can be or should be minimised..


On 2 May 2012 17:07, Gerry Miller <> wrote:

> Evening Norman,
> I suspect you are correct.
> Regards
> Gerry
> Norman Dunbar wrote: Morning Gerry, On 02/05/12 08:31, Gerry Miller wrote:
> AsI mentioned in my reply to Carlos, there are over 6000 such queries in
> theshared pool, each with the structure:
> SELECT<schema_name>.<table_name>.*,
> rowid from<schema>.<table_name> WHERE1=0; I think I should quiz the
> developers as to what they are trying to do with these queries. I've seen
> something like that before. I used to work in a software house where
> Uniface
> was the development tool of choice. It was diabolical, but excellent for
> "database agnostic" applications as it used the best of each database.
> Anyway, before a "table" could be used, it had to be "opened". The query
> there was "select * from schema.table" which was parsed only, but never
> executed. If the parse failed, then the table probably didn't exists and
> the
> application would report a problem. I suspect your queries above are
> something similar? Cheers, Norm.
> --

Love me or Hate me both are in my Favour.
Love me, I am in your Heart. Hate me, I am in your Mind.

Received on Thu May 03 2012 - 21:26:17 CDT

Original text of this message