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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).