dbms_stats AND external TABLES

[hannah00]

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,

[marist89]

I'd suspect you can set the stats for your external tables, no?

[Axr2]

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.

[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.

[Axr2]

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.

[Horace]

To analyze external tables using dbms_stats, remove the estimate_percent option.

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

[Horace]

Oh, and you'll obviously need to take the CASCADE clause out too.