Re: NOT NULL constraint and long parse time

I've tried your test case on 9.2.0.2 but it didn't reproduce. Should there any data in the tables ?

How did you determine that the time was
spent on the parse - and could you tell us your CPU speed and the times involved.

Also, was there ultimately any difference in the execution plan ?

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 07 February 2003 10:26

>Hi all,
>
>I had a problem with long parse time on one of my queries and want to
know
>if it is a bug or what.
>Suppose two tables: tab1 and tab2 on my database 9.0.1.3.0. These
tables are
>both empty. Have a look at the column tab1.col1. The NOT NULL
constraint on
>it can be defined in two ways:
>1) in a create table definition
>2) with alter table add constraint
>
>create table tab2 (id number(10) not null, col1 number(1) not null);
>create table tab1 (id number(10) not null, tab2id number(10) not
null,
>col1 char(12) not null, col2 number(4) not null);
>--create table tab1 (id number(10) not null, tab2id number(10) not
null,
>col1 char(12), col2 number(4) not null);
>--alter table tab1 add constraint cns_tab1_col1 check (col1 is not
null);
>alter table tab1 add constraint pk_tab1 primary key (id);
>alter table tab2 add constraint pk_tab2 primary key (id);
>analyze table tab1 compute statistics;
>analyze table tab2 compute statistics;
>
>
>Now I want to execute SELECT:
>
>select tab2.col1, count(tab1.col1)
> from tab2, tab1
> where (tab1.tab2id=tab2.id) and (tab1.col2 in
>(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25))
> group by tab2.col1;
>
>In case 1) the parse time is too long and gets longer with more
numbers
>in IN
>condition. While in case 2) the parse time doesn't depend on the
number of
>values in IN condition.
>
>I traced the query in both cases and the only difference was in
calling one
>more SELECT in case 2) just before executing my query. The query
executed
>before was:
>select condition
>from
> cdef$ where rowid=:1
>
>And a note: Without ANALYZE on the tables (or with /*+RULE*/ hint) my
query
>is quick in both cases.
>
>If you have any info about this behaviour, tell me please.
>
>Thanks
> Mike

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).