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,
Printable View
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,
I'd suspect you can set the stats for your external tables, no?
Yes, that's correct.Quote:
Originally posted by marist89
I'd suspect you can set the stats for your external tables, no?
PS : Just learned that one from asktom myself..he provides examples as usual.
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:718620715884Quote:
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.
See post on July 31, 2003 for an example.
To analyze external tables using dbms_stats, remove the estimate_percent option.
That wont work until you remove the estimate_percent clause, then it will!!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;
/
Oh, and you'll obviously need to take the CASCADE clause out too.