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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Deas, Scott
Sent: Tuesday, December 01, 2015 12:26 AM
To: oracle-l@xxxxxxxxxxxxx
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
