How to add Histogram constraints to Yes when altering adding columns
Multi tool use
Clash Royale CLAN TAG#URR8PPPHow to add Histogram constraints to Yes when altering adding columns
I'm working on Oracle database and have to alter the table 'RETURNS' and add the columns RENTAL_SALES and INBOUND_SALES.
ALTER TABLE
RETURNS
ADD(
RENTAL_SALES NUMBER (14,2) NULL,
INBOUND_SALES NUMBER (14,2) NULL
);
How do I set the Histogram to "Yes"
2 Answers
2
Run the gather status using method_opt='FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 {colum name on which you want to enable histogram}' .
Check whether it is enabled or not
Select column_name, histogram from
User_tab_column_statics where table_name='tableName';
BEGIN dbms_stats.Gather_table_stats('SH', 'table_name', - method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 Column_name'); END;
– Shivam
yesterday
I revived this error. Encountered the symbol "end-of-file" when expecting one of the following: ; <an identifier> <a double-quoted delimited-identifier> The symbol ";" was substituted for "end-of-file" to continue.
– Jeffrey Johnston
yesterday
Try this BEGIN dbms_stats.Gather_table_stats('SH', 'TABLE_NAME', method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 COLUMN_NAME'); END;
– Shivam
yesterday
Why you need to use histograms? are you facing wrong query planes?
there are type of histograms, depending on Number of distinct values the type is assigned.
frequency(top) histograms, high balanced histograms and hybrid histograms.
The database will assign a histogram by gathering the statistics auto, then query on the tables (when quering on the table data will be update on SYS.COL_USAGE$) then update statistic again.
BEGIN
dbms_stats.Gather_table_stats('SCHEMA_NAME', 'TABLE',
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
END;
/
select * from TABLE where ....
BEGIN
dbms_stats.Gather_table_stats('SCHEMA_NAME', 'TABLE',
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
END;
/
Note: ( If you already created an index before or already updated statistics and you were quering on the table, updating the statistics again will create the histogram)
Another Note: this method_opt='FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 column name will assign the column to high balanced , maybe this columns needs frequency type, so if you dont know the NDV and how much data there its better let the database choose, else you might have bad query plan, and the rest columns will not have histograms created because all columns size 1 collects base column statistics.
method_opt='FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 column name
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
How do I run this?
– Jeffrey Johnston
yesterday