Re: Dual index question

From: Gus Teschke <gus.teschke_at_med.umich.edu>
Date: 5 Apr 1994 18:39:40 GMT
Message-ID: <gus.teschke-050494134525_at_no-name-medadmin.med.umich.edu>

Hi Woody,

    Which index? It depends. We use the rule-based query builder because the cost-based has many bugs. The rule-based builder uses an ordering of access paths that enables you to predict how a query plan will be built. In your case, the rules say that a composite index will take precedence over a single-column index, but I'd use Explain Plan to verify this. In the following example (Sun, V7.0.15.4.0), the composite index is not used until it is rebuilt as unique.

I haven't found the access path rankings in the V7 manuals, but the V6 rankings are in the V6 DBA Guide on page 19-17.

                                                                    Good
luck,
                                                                           

   Gus

---

SQL> create table taba (

  2          foobee number constraint pk_foobee primary key,
  3          d date
  4          );

SQL> create table foo (
  2          foobar  number constraint pk_foobar primary key using index,
  3          foobee  number constraint fk_foobee references taba (foobee),
  4          foobat  number,
  5          fooboo  number
  6          );

SQL> create index ffcat_i on foo (foobar, foobee);

SQL> explain plan
  2 set statement_id = 'ind1'
  3 for

  4          select f.foobar, f.foobee
  5          from foo f, taba t
  6          where f.foobee = t.foobee
  7            and f.foobar = 123
  8            and f.foobee = 456;

SQL> _at_iplan ind1
SQL> SELECT

  2          substr(LPAD(' ',2*LEVEL)||OPERATION,1,30) "operation",
  3          substr(OPTIONS,1,15)            "options",
  4          substr(OBJECT_NAME,1,30)        "object_name"
  5  FROM
  6          PLAN_TABLE
  7  WHERE
  8          STATEMENT_ID='&&1'
  9  CONNECT BY
 10          PRIOR ID = PARENT_ID
 11      and STATEMENT_ID = '&&1'
 12  START WITH
 13          ID = 1
 14      and STATEMENT_ID = '&&1'
 15  ORDER BY
 16          ID;

operation                      options         object_name
------------------------------ ---------------
------------------------------
  NESTED LOOPS
    TABLE ACCESS               BY ROWID        FOO
      INDEX                    UNIQUE SCAN     PK_FOOBAR
    INDEX                      UNIQUE SCAN     PK_FOOBEE

SQL> drop index ffcat_i;
SQL> create unique index ffcat_i on foo (foobar, foobee);

SQL> explain plan
  2 set statement_id = 'ind3'
  3 for

  4          select f.foobar, f.foobee
  5          from foo f, taba t
  6          where f.foobee = t.foobee
  7            and f.foobar = 123
  8            and f.foobee = 456;

operation                      options         object_name
------------------------------ ---------------
------------------------------
  NESTED LOOPS
    INDEX                      UNIQUE SCAN     FFCAT_I
    INDEX                      UNIQUE SCAN     PK_FOOBEE
______________________________________________

In article <2nkbabINNin1_at_srvr1.engin.umich.edu>, lwk_at_engin.umich.edu (Lewis W Kellum) wrote:
>
> Can anyone answer the following question regarding
> dual indices on a table?
>
> Given a table FOO:
>
> Column Type
> foobar primary key
> foobee foriegn key
> foobat data
> fooboo data
>
> I want to create a unique index on column foobar to
> insure data integrity, but I also want a concatenated
> index on foobar & foobee in order to speed table joins.
> In many cases, I will not return any other data from FOO.
>
> If I do a select joining this table with the one
> where foobee is a primary key, and refer to both
> foobar and foobee in the where clause, which index
> will I be using?
>
> Thanks.
> -Woody
Received on Tue Apr 05 1994 - 20:39:40 CEST