I am puzzled that that is the only statement you have a problem with.
That call to dbms_stats ought to produce an insert that lists every single
column in the table, plus the virtual columns supporting function-based indexes.
As it is your table appears to consist of one column with a function-based
index (or some variant thereof).
Since you're on 11g you should be using for all columns size 1 with
auto_sample_size (not 2 percent) to take advantage of the approximate_ndv to
get the most of the table stats.
If you then think these two columns need a histogram you either live with an
expensive insert and histogram generation, or you write some SQL that creates
the histograms from scratch (or, if you're amazingly lucky) use method_opt in a
separate call to specify just the columns that you want to have histograms and
live with the tiny sample that Oracle will use for generating a histogram with
auto_sample_size.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Juan Carlos Reyes Pacheco [jcdrpllist@xxxxxxxxx]
Sent: 03 October 2016 20:10
To: ORACLE-L
Subject: Is there a way to skip the creation of a temporal table when gathering
statistics?
when gather statistics
DBMS_STATS.GATHER_TABLE_STATS('SOA','HICARTERA_RW',ESTIMATE_PERCENT=>2,method_opt=>'FOR
ALL COLUMNS SIZE SKEWONLY FOR ALL COLUMNS');
I found an insert that is taking a lot of time.
Is there a way to skip it, like increasing pga memory, or something like that.
Thank you :)
INSERT /*+ append */INTO SYS.ora_temp_1_ds_1342719
SELECT /*+ no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
no_substrb_pad */
"CAR_QUIEN_CUST", "SYS_NC00097$" sys_ds_alias_2,
"SYS_NC00094$" sys_ds_alias_3, "CAR_ESVIRF" (cut)
FROM "SOA"."HICARTERA_RW" SAMPLE (2.0000000000) t
UNION ALL
SELECT "CAR_QUIEN_CUST", sys_ds_alias_2, sys_ds_alias_3, "CAR_ESVIRF" (cut)
FROM SYS.ora_temp_1_ds_1342719
WHERE 1 = 0