Re: CREATE INDEX: PARALLEL flag influences index usage

There are various reasons why the
path could change when you
parallel-enable an object in a query.

Trying to guess the reason without
seeing either plan is a little hard.
Can you post the execution plans
you get - using the dbms_xplan package.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005






"Robert Klemme" <bob.news_at_gmx.net> wrote in message 
news:39dr6gF5u61i9U1_at_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

>