Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

Generate table stats on External tables

Can we generate table statistics on Oracle external table, please suggest.
0
d27m11y
Asked:
d27m11y
  • 2
2 Solutions
 
JJSmithCommented:
Following statement should work on external table SCOTT.EXT_TABLE :

DBMS_STATS.GATHER_TABLE_STATS (
Ownname           => 'SCOTT',
Tabname           => ‘EXT_TABLE’,
estimate_percent  => NULL,
degree      => 4);
0
 
d27m11yAuthor Commented:
Looking above, I understand that the syntax looks same as regular tables. May I know what exactly degree above means and why is it set to 4 ?

Thank you !
0
 
slightwv (䄆 Netminder) Commented:
To confirm the above generic syntax, the docs seem to imply it can be performed on an external table:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN12897

From the docs:
The ANALYZE statement is not supported for gathering statistics for external tables. Use the DBMS_STATS package instead.

>>May I know what exactly degree above means and why is it set to 4 ?
Again the online docs:
docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68583

degree  Degree of parallelism
0
 
JJSmithCommented:
Simply put degree (of Parallelism) - will break down the stats queries into multiple processes which can be run concurrently depending, of course, on the fact that you have multiple CPUs/cores. You get your results quicker!

The import bit in the procedure call that I put up is that the estimate_percent is set to NULL if you have version 10.2; it's OK to use values if you have 9 or 11.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now