Re: v$sql - executions vs loads vs invalidations
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "deshpande.subodh@xxxxxxxxx" <deshpande.subodh@xxxxxxxxx>, "gerry@xxxxxxxxxxxxxxxxxxx" <gerry@xxxxxxxxxxxxxxxxxxx>
- Date: Thu, 3 May 2012 20:07:23 -0700 (PDT)
"eg create table t1 as select * from t2 where 1=2 will create table t1
exactly like t2 including constraints without data in it."
No, it doesn't -- the constraints such as pk and fk constraints are not
replicated to the new table:
SQL>
SQL> --
SQL> -- Create source table
SQL> -- with a pk constraint
SQL> --
SQL>
SQL> create table yazzoo(
2 snarm number primary key,
3 vlapto varchar2(40),
4 orp number,
5 klop date,
6 bnurt number
7 );
Table created.
SQL>
SQL> --
SQL> -- Verify table and constraint existence
SQL> --
SQL>
SQL> select table_name
2 from user_tables;
TABLE_NAME
------------------------------
YAZZOO
SQL>
SQL> select constraint_name
2 from user_constraints
3 where table_name in ('YAZZOO','GRORT');
CONSTRAINT_NAME
------------------------------
SYS_C0014690
SQL>
SQL> --
SQL> -- Create destination table
SQL> -- See if PK constraint is created
SQL> --
SQL>
SQL> create table grort
2 as select * From yazzoo where 0 = 1;
Table created.
SQL>
SQL> --
SQL> -- Check for table and constraint existence
SQL> --
SQL>
SQL> select table_name
2 from user_tables;
TABLE_NAME
------------------------------
YAZZOO
GRORT
SQL>
SQL> select constraint_name
2 from user_constraints
3 where table_name in ('YAZZOO','GRORT');
CONSTRAINT_NAME
------------------------------
SYS_C0014690
SQL>
David Fitzjarrell
________________________________
From: Subodh Deshpande <deshpande.subodh@xxxxxxxxx>
To: gerry@xxxxxxxxxxxxxxxxxxx
Cc: oracle@xxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Thursday, May 3, 2012 8:26 PM
Subject: Re: v$sql - executions vs loads vs invalidations
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..
thanks...subodh
On 2 May 2012 17:07, Gerry Miller <gerry@xxxxxxxxxxxxxxxxxxx> 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.
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
=============================================
Love me or Hate me both are in my Favour.
Love me, I am in your Heart. Hate me, I am in your Mind.
=============================================
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
