index not analyze after using DBMS_STATS to update stats on the table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

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

  1. #1
    Join Date
    Jan 2005
    Posts
    221

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

    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???

    Code:
        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]
    Last edited by hannah00; 01-18-2006 at 04:32 PM.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    because you didnt ask them to be i would think

  3. #3
    Join Date
    Jan 2005
    Posts
    221
    Thanks for your input Dave,

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

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    well what command did you use

  5. #5
    Join Date
    Jan 2005
    Posts
    221
    Dave,

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

    do you have any recommedation???

    thanks so much Dave

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    add cascade=> true

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    ... and review all the other options as well

    http://download-west.oracle.com/docs...s2.htm#1003995
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width