Re: Indexes and the rule_based optimizer
Date: 1996/06/22
Message-ID: <4qfoiq$kn1_at_sjx-ixn3.ix.netcom.com>#1/1
You are not using the entire key in your queries, only the leading column of each key, col1 of the primary key and col3 of your alternate key. Thus, the order is not significant. You still get a rowid access and range scan access in both cases, so performance is equally good. If you had used both keys in their entirety, you may see different results in your scenarios.
Steve
804-262-6332
In <4qe2ds$ee0_at_intp1.columbus.co.za> adams.werner_at_columbus.co.za
(Werner Adams) writes:
>
>Please help.....
>
>I have problems on a ORACLE 7.1.6.2.5 database. I created a table
with a
>primary key and a non-unique index as shown below.
>
>Create table test (
>col1 number(5),
>col2 number(5),
>col3 number(5),
>col4 number(5));
>
>alter table test
>add constraint pk_test primary key (col1,col2);
>
>create index test_ind on test (col3,col4);
>
>I then issued the following query:
>
>explain plan for
>select *
>from test
>where col1 = 1 and col3 = 1;
>
>explain plan output when index test_ind was created last:
>
>ID P_ID OPERATION OPTIONS OBJECT_NAME
>--- ---- -------------------- ---------------
> 0 SELECT STATEMENT
> 1 0 TABLE ACCESS BY ROWID TEST
> 2 1 INDEX RANGE SCAN TEST_IND
>
>I then dropped the primary key, recreated it and issued the same sql
code.
>
>explain plan output when primary key was recreated:
>
>ID P_ID OPERATION OPTIONS OBJECT_NAME
>--- ---- -------------------- ---------------
> 0 SELECT STATEMENT
> 1 0 TABLE ACCESS BY ROWID TEST
> 2 1 INDEX RANGE SCAN PK_TEST
>
>
>
>The database use the rule-based optimizer. The world according to
>ORACLE7 Server Concepts Manual p13-33, the primary key must always be
>used in scenario explained above.
>
>Does anybody have an idea why this is happening?
>
>Werner Adams Tel : +27 (0)13-2472717
>Database Administrator Fax: +27 (0)13-2472622
>Columbus Stainless E-mail:
adams.werner_at_columbus.co.za
>
>Always question! Never simply accept and follow!
>----------------------------------------------------------------------
>
Received on Sat Jun 22 1996 - 00:00:00 CEST