Re: NOT NULL constraint and long parse time

[Michal Zaschke]

In both the tables should be data but for the test there are none. With full of data tables it gives me similar results.

The output of tkprof gave me these results:
Case 1 (create definition):
select tab2.col1, count(tab1.col1) pocet
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

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 17.14 17.14 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 17.14 17.14 0 3 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 57


Case 2 (alter table):
select tab2.col1, count(tab1.col1) pocet
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

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.02 0 3 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 57

Tkprof gave me no execution plan so I have done it using "explain plan". Both the plans are the same:
PLAN COST ROWS DATA_KB
------------------------------------------ ----- ----- -------
SELECT STATEMENT 3 1 ,1
SORT GROUP BY 3 1 ,1
NESTED LOOPS 1 1 ,1
TABLE ACCESS FULL TAB1 1 1 0
TABLE ACCESS BY INDEX ROWID TAB2 1 0
INDEX UNIQUE SCAN PK_TAB2 UNIQUE 1

Mike

Jonathan Lewis wrote:

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 <[EMAIL PROTECTED]>
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

--
 Ing. Michal Zaschke
   DB Administrator
Sokolovska uhelna, a.s.
tel.: +420 352 465417
e-mail: [EMAIL PROTECTED]

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Michal Zaschke
 INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).