DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: dbms_stats AND external TABLES

  1. #1
    Join Date
    Jan 2005
    Posts
    221

    dbms_stats AND external TABLES

    Hi all,

    If I have dbms_stats collect stats on schema and on this schema I have EXTERNAL table, obviously, DBMS_STATS doeesn't work for EXTERNAL table. The question is how can we handle in such situation like this????

    Thanks,

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I'd suspect you can set the stats for your external tables, no?
    Jeff Hunter

  3. #3
    Join Date
    Oct 2002
    Posts
    807
    Originally posted by marist89
    I'd suspect you can set the stats for your external tables, no?
    Yes, that's correct.

    PS : Just learned that one from asktom myself..he provides examples as usual.

  4. #4
    Join Date
    Jan 2005
    Posts
    221
    Jeff,

    no, you can't. Oracle won't allow you to do that. I am not sure how to handle this in this sitituation.

  5. #5
    Join Date
    Oct 2002
    Posts
    807
    Originally posted by hannah00
    Jeff,

    no, you can't. Oracle won't allow you to do that. I am not sure how to handle this in this sitituation.
    http://asktom.oracle.com/pls/ask/f?p...A:718620715884

    See post on July 31, 2003 for an example.

  6. #6
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    To analyze external tables using dbms_stats, remove the estimate_percent option.

    Code:
    BEGIN
    
          DBMS_STATS.gather_table_stats (ownname          => 'SCHEMA_OWNER',
                                         tabname          => 'EXTERNAL_TABLENAME',
                                         estimate_percent =>  DBMS_STATS.AUTO_SAMPLE_SIZE,
                                         method_opt       => 'for all columns size auto',
                                         cascade          => true
                                        );
    END;
    /
    That wont work until you remove the estimate_percent clause, then it will!!

  7. #7
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Oh, and you'll obviously need to take the CASCADE clause out too.

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