RE: Question on Stats for Indexes Created in Parallel

From: Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>
Date: Tue, 1 Dec 2015 02:34:32 +0000
Message-ID: <2FE2AA1C5F8DEC478F58DF8DD32BA63714D192_at_HKWPIPXMB03C.zone1.scb.net>

Yes, DISTINCT_KEYS seems to be wrong for CREATE BITMAP INDEX ... PARALLEL. (It is the combination of CREATE BITMAP and PARALLEL).

Tested with 2 and 4 distinct values and Parallel 4 and 8 versus non-parallel on 11.2.0.4

With 2 distinct VARCHAR2(1) values, Parallel 4 and 8 show DISTINCT_KEYS as 5 and 8 With 4 distinct VARHCAR2(1) values, Parallel 4 and 8 show DISTINCT_KEYS as 7 and 11

Hemant K Chitale

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Deas, Scott Sent: Tuesday, December 01, 2015 12:26 AM To: oracle-l_at_freelists.org
Subject: Question on Stats for Indexes Created in Parallel

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.**

This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html

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

Received on Tue Dec 01 2015 - 03:34:32 CET