Oracle-L: NOT NULL constraint and long parse time

From: Michal Zaschke <zaschke_at_suas.cz>
Date: Fri, 07 Feb 2003 01:28:48 -0800
Message-ID: <F001.00546C92.20030207012848@fatcity.com>

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

--

Ing. Michal Zaschke
DB Administrator
Sokolovska uhelna, a.s.
tel.: +420 352 465417
e-mail: zaschke_at_suas.cz

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Michal Zaschke
INET: zaschke_at_suas.cz

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). Received on Fri Feb 07 2003 - 03:28:48 CST