Question on Stats for Indexes Created in Parallel

From: Deas, Scott <Scott.Deas_at_lfg.com>
Date: Mon, 30 Nov 2015 16:26:20 +0000
Message-ID: <C1FB7BA65B13C542B2CB1CE5DB8F74AF1EBFE0EF_at_NC2PWEX501.us.ad.lfg.com>

All,

I've noticed that when creating an index in parallel, the NUM_DISTINCT value is way off. I'm wondering if there is a way to create an index in parallel without having to then go re-gather statistics after.

The column I'm indexing has two distinct values (Y,N). When creating the index serially, it works fine:

SQL> create bitmap index dim_part_currow on dim_participant_test (current_row_ind);

Index created.

SQL>
SQL>
SQL> SELECT

  2 index_name,
  3 distinct_keys,
  4 sample_size,
  5 num_rows,
  6 last_analyzed
  7 FROM
  8 dba_indexes i
  9 WHERE
10 i.table_name = 'DIM_PARTICIPANT_TEST';

INDEX_NAME                     DISTINCT_KEYS SAMPLE_SIZE   NUM_ROWS LAST_ANALYZED


------------------------------ ------------- ----------- ---------- -------------------

DIM_PART_CURROW                            2        3463       3463 11/30/2015 11:06:58

SQL> Now if I drop the index and re-create it in parallel, the DISTINCT_KEYS value jumps up from 2 (which is correct), to 16:

SQL> drop index dim_part_currow;

Index dropped.

SQL>
SQL> create bitmap index dim_part_currow on dim_participant_test (current_row_ind) parallel 32;

Index created.

SQL>
SQL>
SQL> SELECT

  2 index_name,
  3 distinct_keys,
  4 sample_size,
  5 num_rows,
  6 last_analyzed
  7 FROM
  8 dba_indexes i
  9 WHERE
10 i.table_name = 'DIM_PARTICIPANT_TEST';

INDEX_NAME                     DISTINCT_KEYS SAMPLE_SIZE   NUM_ROWS LAST_ANALYZED


------------------------------ ------------- ----------- ---------- -------------------

DIM_PART_CURROW                           16        3462       3462 11/30/2015 11:07:13

SQL> When querying dba_ind_statistics, the stale_stats column is NULL. Shouldn't this value be either NO or YES, since statistics were collected at the time of the index creation? If we based any of our stats gathering on stale stats, this new index would be skipped because the value is NULL.

So, two questions:

1. Is there any way to avoid having to re-gather stats when creating an index in parallel?
2. Why is the stale stats value NULL in dba_ind_statistics - especially since it says the stats have been collected at the time the index was built (last_analyzed is populated in dba_indexes and dba_ind_statistics)

Thanks,
Scott
Notice of Confidentiality: **This E-mail and any of its attachments may contain Lincoln National Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Lincoln National Corporation family of companies. This E-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this E-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this E-mail is strictly prohibited and may be unlawful. If you have received this E-mail in error, please notify the sender immediately and permanently delete the original and any copy of this E-mail and any printout. Thank You.**

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Nov 30 2015 - 17:26:20 CET