Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> CREATE INDEX: PARALLEL flag influences index usage

CREATE INDEX: PARALLEL flag influences index usage

From: Robert Klemme <bob.news_at_gmx.net>
Date: Fri, 11 Mar 2005 16:17:41 +0100
Message-ID: <39dr6gF5u61i9U1@individual.net>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US