| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: NOT NULL constraint and long parse time
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 <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
>>if it is a bug or what. >>Suppose two tables: tab1 and tab2 on my database 9.0.1.3.0. These
>>both empty. Have a look at the column tab1.col1. The NOT NULL
>>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
>>col1 char(12) not null, col2 number(4) not null); >>--create table tab1 (id number(10) not null, tab2id number(10) not
>>col1 char(12), col2 number(4) not null); >>--alter table tab1 add constraint cns_tab1_col1 check (col1 is not
>>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
>>in IN >>condition. While in case 2) the parse time doesn't depend on the
>>values in IN condition. >> >>I traced the query in both cases and the only difference was in
>>more SELECT in case 2) just before executing my query. The query
>>before was: >>select condition >>from >> cdef$ where rowid=:1 >> >>And a note: Without ANALYZE on the tables (or with /*+RULE*/ hint) my
>>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 Mon Feb 10 2003 - 01:28:37 CST
 |
 |