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 -> Re: Optimzer use for an index

Re: Optimzer use for an index

From: Syltrem <syltrem_at_videotron.ca>
Date: Fri, 20 Apr 2001 14:17:59 -0400
Message-ID: <%Z_D6.3299$vT5.498710@weber.videotron.net>

I must add:
select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT from dba_TAB_COL_STATISTICS where TABLE_NAME='PRODUCT_WAREHOUSE';
Reported count = 1 for field COMPANY_CODE and count = 15 for field WAREHOUSE

So statistically only 1 row out of 15 may be returned by the query (6.66%).

--

Syltrem
http://pages.infinit.net/syltrem (OpenVMS related web site)


"Syltrem" <syltrem_at_videotron.ca> a écrit dans le message news:
uB_D6.3249$vT5.494259_at_weber.videotron.net...

> Answers embedded below
> Thanks for bringing some light into this!
> --
>
> Syltrem
> http://pages.infinit.net/syltrem (OpenVMS related web site)
>
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> a écrit dans le message news:
> umo0etgnijb2s3t8nesgpr1mc4l4cq3aua_at_4ax.com...
> > On Fri, 20 Apr 2001 11:34:42 -0400, "Syltrem" <syltrem_at_videotron.ca>
> > wrote:
> >
> > Please post more background
> > - the optimizer you are using
COST
> > - the *full* index definition (it is important to know whether it's
> > unique or not)
> Original index:
> CREATE INDEX "FINANCE"."XXXX"
> ON "FINANCE"."PRODUCT_WAREHOUSE"("COMPANY_CODE", "WAREHOUSE",
> "PART_CODE")
> TABLESPACE "FIN_IDX_SMALL" PCTFREE 10 INITRANS 2 MAXTRANS 255
> STORAGE ( INITIAL 7200K NEXT 160K MINEXTENTS 1 MAXEXTENTS 1024
> PCTINCREASE 0 FREELISTS 1)
> LOGGING;
>
> New index:
> CREATE INDEX "FINANCE"."ZZZZ"
> ON "FINANCE"."PRODUCT_WAREHOUSE"("COMPANY_CODE", "WAREHOUSE")
> TABLESPACE "FIN_IDX_SMALL" PCTFREE 10 INITRANS 2 MAXTRANS 255
> STORAGE ( INITIAL 7200K NEXT 160K MINEXTENTS 1 MAXEXTENTS 1024
> PCTINCREASE 0 FREELISTS 1)
> LOGGING
>
> > - execution plans.
> SQL:
> SELECT A1.ROWID
> FROM FINANCE.PRODUCT_WAREHOUSE A1
> WHERE A1.COMPANY_CODE=:1 AND A1.WAREHOUSE=:8
> ORDER BY A1.WAREHOUSE ASC,A1.PART_CODE ASC
>
>
> With the XXXX index only:
>
> OPERATION OPTIONS OBJECT_NAME
ORDER
> OPT RECS
> ------------------------- ------------- ------------------------------ ---
--
> ---
> -------- ----------
> SELECT STATEMENT COST = 3774
> 0-0-3774
> CHOOSE 3456
> SORT ORDER BY
1-0-1
> 3456
> TABLE ACCESS FULL PRODUCT_WAREHOUSE
2-1-1
> ANALYZ 3456
>
>
> With the newly added ZZZZ index:
>
> OPERATION OPTIONS OBJECT_NAME
ORDER
> OPT RECS
> ------------------------- ------------- ------------------------------ ---
--
> ---
> -------- ----------
> SELECT STATEMENT COST = 90
> 0-0-90
> CHOOSE 3456
> SORT ORDER BY
1-0-1
> 3456
> TABLE ACCESS BY INDEX ROWI PRODUCT_WAREHOUSE
2-1-1
> ANALYZ 3456
> D
>
> INDEX RANGE SCAN ZZZZ
3-2-1
> 3456
>
> >
> > If you are using CBO the answer would probably be:
> > - right now, according to CBO it would take less I/O to perform a full
> > table scan
> > - as the second index is smaller the situation is probably reversed
> > - the first index is not unique
> > and finally
> >
> > - try creating histograms on affected columns
>
> I tried:
> analyze index finance.ifa_product_warehouse compute statistics for all
> indexed columns;
> Does that do the job?
>
> >
> > BTW : if you have three columns on an index and all three columns in
> > the where clause, Oracle will always use *all* three columns. The
> > scenario you describe above will never apply.
>
> I was taliking of two (2) fields from the index in the WHERE clause, plus
> possibly but not necessarily, 1 other field.
>
> >
> > Hth,
> >
> >
> > Sybrand Bakker, Oracle DBA
>
>
Received on Fri Apr 20 2001 - 13:17:59 CDT

Original text of this message

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