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 - 12:51:49 CDT
