0

Is there a way to instruct dmbs_stats.gather_table_stats() to ignore a particular column?

Context: I have a table that has a virtual column that performs a standard_hash() of the concatenation of several columns. When this table is analyzed, that virtual column gets calculated for every row in the table. The presence of this virtual column, therefore, causes the time required to analyze the table to increase.

Most of the time I would like the virtual column to be analyzed, but sometimes I don't need the virtual column analyzed.

Generally, when the table is analyzed, I would want that virtual column to be analyzed. However, in our Data Warehouse batch load process we analyze the table several times during the load process for the benefit of subsequent downstream batch load processes. I do not always need the virtual column analyzed. Hence, I am inquiring if there is a way for me to control whether or not the virtual column is analyzed when its table is analyzed.

Improve this question
20
  • 2
    Simply override the method_opt parameter in your call to dbms_stats.gather_table_stats to list the columns you want to include. Btw, in my opinion, these hash methods for comparing rows are not optimal, as they require either expensive CPU (if virtual or in code) or space (if materialized). Simply comparing each column individually between staging and target table is the best method for performance, IMO. Also, gathering stats multiple times on a table in a single load window sounds like overkill. Commented Jan 20 at 2:28
  • 2
    @KenWhite In a data warehouse environment, it is pretty common for programmers to gather statistics, since their programs will often significantly alter the size of data. (Whereas in an OLTP environment that only gradually changes statistics, you may only need a DBA to ensure there is a scheduled job to periodically gather statistics.) Commented Jan 20 at 4:08
  • 1
    @KenWhite The answer involves writing a PL/SQL statement that calls a standard PL/SQL package and procedure, and sets an argument in a kind of weird format that is easy to get wrong. When I was an Oracle DBA, I frequently reminded developers to gather statistics in their code because it is not something that should only be done by DBAs. (Although the situation may be very different in an OLTP system, or with a non-Oracle database.) Paul W's comment is pretty close to an answer. Commented Jan 20 at 6:10
  • 2
    As it's been indicated that migration to DBA was blocked, I've removed my comments from above and voted to reopen this question. Commented Jan 20 at 14:54
  • 2
    Looks like this is a known issue: jonathanlewis.wordpress.com/2018/09/12/column-stats-2 Commented Jan 24 at 2:51
Related questions
683
Fetch the rows which have the Max value for a column for each distinct value of another column
1394
How do I limit the number of rows returned by an Oracle query after ordering?
8
Concatenating numbers in virtual column expression throws ORA-12899: value too large for column
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.