index not analyze after using DBMS_STATS to update stats on the table

[hannah00]

Hi all,

I have run DBMS_STATS on the schemas level and all of the tables have been analyzed; however, the indexes are not, WHY???

    Statement Id=4203110   Type=
  Cost=2.64022960303481E-308  TimeStamp=18-01-06::14::57:27
  
       (1)  SELECT STATEMENT  CHOOSE 
     Est. Rows: 1  Cost: 160
       (9)  TABLE ACCESS BY INDEX ROWID test.DOE_TOT  [Analyzed] 
       (9)   Blocks: 10,441 Est. Rows: 1 of 289,955 
     Tablespace: test_data
           (8)  NESTED LOOPS 
                Est. Rows: 1  Cost: 160
               (6)  NESTED LOOPS 
                    Est. Rows: 106,952  Cost: 160
                   (3)  TABLE ACCESS BY INDEX ROWID test.ALTRAN_TOT_SEL  [Analyzed] 
                   (3)   Blocks: 122 Est. Rows: 75 of 7,265  Cost: 10 
                        Tablespace: test_data
                       (2)  NON-UNIQUE INDEX RANGE SCAN test.IX3_ALTRAN_TOT_SEL  [Not Analyzed] 
                            Est. Rows: 75  Cost: 1
                   (5)  TABLE ACCESS BY INDEX ROWID test.DOE_TOT  [Analyzed] 
                   (5)   Blocks: 10,441 Est. Rows: 1,435 of 289,955  Cost: 2 
                        Tablespace: test_data
                       (4)  NON-UNIQUE INDEX RANGE SCAN test.IX4_DOE_TOT  [Not Analyzed] 
                            Est. Rows: 1,435  Cost: 1
               (7)  UNIQUE INDEX RANGE SCAN test.AK1_DOE_TOT  [Not Analyzed]

[davey23uk]

because you didnt ask them to be i would think

[hannah00]

Thanks for your input Dave,

you mean I have to specify DBMS_STATS to update STATS on table as well as index???

[davey23uk]

well what command did you use

[hannah00]

Dave,

begin
dbms_stats.gather_schema_stats('TEST');
end;
/

do you have any recommedation???

thanks so much Dave

[davey23uk]

add cascade=> true

[slimdave]

... and review all the other options as well

http://download-west.oracle.com/docs...s2.htm#1003995