RE: full-scan vs index for "small" tables

we are both right. 
In the real life I had kind of
 
select where type in ('OPENED', 'ACTIVE') 
 
Anyway, this does not change anything regarding the CBO darned undeterministic 
nature. 
I did a small test to reproduce my case. The results are more than funny. 
I really do not want to examine whatever traces. I see no sense in that. If 
it's bug then Oracle's not gonna pay me for finding it. If it is not then what 
for I have to dig? The Oracle version is not the last (9206) also I assume many 
people are still running v7.3. Here bellow is my test. Someone claimed CBO has 
a sense of humor? A black humor, I have to say.

 
Rem drop table test_cbo;

create table test_cbo (id number, type varchar2(64));

alter table test_cbo add constraint test_cbo_pk primary key (id);

create index test_cbo_type on test_cbo (type);

begin
for m in 1..9999 loop
        insert into test_cbo values (m, 'CLOSED');
end loop;
commit;
end;
/

begin dbms_stats.gather_table_stats (user, 'TEST_CBO', method_opt=>'FOR ALL 
COLUMNS SIZE AUTO', cascade=>true); end;
/

  select * from TEST_CBO where type='CLOSED';
  ------------------------------------------------------------
    
  Statement Id=0   Type=SELECT STATEMENT
  Cost=8  TimeStamp=29-06-06::17::32:08
  
       (1)  SELECT STATEMENT  CHOOSE 
     Est. Rows: 9.999  Cost: 8
       (2)  TABLE ACCESS FULL LTDLNE.TEST_CBO  [Analyzed] 
       (2)   Blocks: 28 Est. Rows: 9.999 of 9.999  Cost: 8 
     Tablespace: USERS


  select * from TEST_CBO where type in ('OPENED' , 'OPENED2')
  ------------------------------------------------------------
    
  Statement Id=0   Type=SELECT STATEMENT
  Cost=8  TimeStamp=29-06-06::17::32:45
  
       (1)  SELECT STATEMENT  CHOOSE 
     Est. Rows: 9.999  Cost: 8
       (2)  TABLE ACCESS FULL LTDLNE.TEST_CBO  [Analyzed] 
       (2)   Blocks: 28 Est. Rows: 9.999 of 9.999  Cost: 8 
     Tablespace: USERS


  select * from TEST_CBO where type =  'OPENED' 
  union 
  select * from TEST_CBO where type =  'OPENED2'
  ------------------------------------------------------------
    
  Statement Id=2   Type=UNION-ALL
  Cost=0  TimeStamp=29-06-06::17::33:08
  
       (1)  SELECT STATEMENT  CHOOSE 
     Est. Rows: 10.000  Cost: 16
       (6)  SORT UNIQUE 
     Est. Rows: 10.000  Cost: 16
           (5)  UNION-ALL
               (3)  TABLE ACCESS BY INDEX ROWID LTDLNE.TEST_CBO  [Analyzed] 
               (3)   Blocks: 28 Est. Rows: 1 of 9.999  Cost: 3 
                    Tablespace: USERS
                   (2)  NON-UNIQUE INDEX RANGE SCAN LTDLNE.TEST_CBO_TYPE  
[Analyzed] 
                        Est. Rows: 1  Cost: 2
               (4)  TABLE ACCESS FULL LTDLNE.TEST_CBO  [Analyzed] 
               (4)   Blocks: 28 Est. Rows: 9.999 of 9.999  Cost: 8 
                    Tablespace: USERS
 


  SQL Statement from editor:
   
   
  select * from TEST_CBO where type =  'OPENED'
  union
  select * from TEST_CBO where type =  'OPENE2'
  
  ------------------------------------------------------------
    
  Statement Id=3   Type=TABLE ACCESS
  Cost=3  TimeStamp=29-06-06::17::34:04
  
       (1)  SELECT STATEMENT  CHOOSE 
     Est. Rows: 2  Cost: 8
       (7)  SORT UNIQUE 
     Est. Rows: 2  Cost: 8
           (6)  UNION-ALL
               (3)  TABLE ACCESS BY INDEX ROWID LTDLNE.TEST_CBO  [Analyzed] 
               (3)   Blocks: 28 Est. Rows: 1 of 9.999  Cost: 3 
                    Tablespace: USERS
                   (2)  NON-UNIQUE INDEX RANGE SCAN LTDLNE.TEST_CBO_TYPE  
[Analyzed] 
                        Est. Rows: 1  Cost: 2
               (5)  TABLE ACCESS BY INDEX ROWID LTDLNE.TEST_CBO  [Analyzed] 
               (5)   Blocks: 28 Est. Rows: 1 of 9.999  Cost: 3 
                    Tablespace: USERS
                   (4)  NON-UNIQUE INDEX RANGE SCAN LTDLNE.TEST_CBO_TYPE  
[Analyzed] 
                        Est. Rows: 1  Cost: 2

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Cary Millsap
Sent: 29. júní 2006 16:54
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: full-scan vs index for "small" tables



Exactly. That's a place where CBO should have no trouble whatsoever making the 
right decision (index access).

 

 

Cary Millsap

Hotsos Enterprises, Ltd.

http://www.hotsos.com

Nullius in verba

 

Hotsos Symposium 2007 / March 4-8 / Dallas

Visit www.hotsos.com for curriculum and schedule details...

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Wolfgang Breitling
Sent: Thursday, June 29, 2006 6:44 AM
To: Laimutis.Nedzinskas@xxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: full-scan vs index for "small" tables

 

At 03:20 AM 6/29/2006, Laimutis Nedzinskas wrote:




I can illustrate my point using one example (which seems to be one the the 
biggest CBO issues actually, discussed many times before):
 
I have a "select where type='OPENED' " 
 
No need to say that type='OPENED' is 0.1% of all records, the rest are 'CLOSED'.
 
I built a histogram. Value 'OPENED' happened not to be there because... the 
were no records with type='OPENED' at the time. Plan was full scan of course.


That is not my experience at all. If the value in the predicate is NOT in the 
(frequency) histogram then in my experience hat is synonymous for "very rare" 
for the CBO and it uses the index. 
From all you are saying I get the impression that you are using a different CBO 
from the one I am using.




Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com 

Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
http://www.freelists.org/webpage/oracle-l

Other related posts: