CREATE INDEX: PARALLEL flag influences index usage

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 Received on Fri Mar 11 2005 - 09:17:41 CST