Re: CREATE INDEX: PARALLEL flag influences index usage

Robert Klemme wrote:
> Hi folks,
>
> I'm seeing a phenomen I have no explanation for. I tried to figure
out
> via Oracle's documentation and also resources on the web but to no
avail.
>
> Basically we notice that it makes a huge difference whether we do
"CREATE
> INDEX" with or without "PARELLEL" when querying the table. The
execution
> plan shows that the index is not used when created without "PARALLEL"
and
> consequently the query takes a lot longer. Data is the same (~
17,000
> records) and statistics were recomputed after each index creation.
>
> Here's the DDL:
>
> CREATE TABLE ag_user
> (
> userid NUMBER(9) CONSTRAINT pk_user PRIMARY KEY,
> userip VARCHAR2(20) NOT NULL,
> username VARCHAR2(100) NOT NULL,
> dnsname VARCHAR2(255) NULL
> );
>
> CREATE INDEX idx_user
> ON ag_user (username, userip);
>
> Variant:
>
> CREATE INDEX idx_user
> ON ag_user (username, userip) PARALLEL;
>
> The difference also shows up if the index is changed via
> ALTER INDEX idx_user NOPARALLEL;
> and
> ALTER INDEX idx_user PARALLEL;
>
> All in default tablespace etc.
>
> Here's the query
>
> SELECT userid
> FROM ag_user
> WHERE username = 'foo';
>
> i.e. the leading portion of the index could be used.
>
> OS is Win2K Serverm, Oracle 9i, 1 CPU with hyperthreading enabled.
>
> Now my question is, what's happening here? How does the option
influence
> the execution plan and why does the optimizer choose the slow variant
if
> PARALLEL is switched off? Thanks a lot for any hints!
>
> Kind regards
>
> robert

You don't mention any anomalies in data distribution in the indexed columns, but have you tried creating histograms?

David Fitzjarrell Received on Fri Mar 11 2005 - 10:12:55 CST