| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Need help with cost based optimizer "logic"
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> a écrit dans le message de
news:d3m0dd$ktq$1_at_hercules.btinternet.com...
>
>
>
>
>
>
>
> "Syltrem" <syltremzulu_at_videotron.ca> wrote in message
> news:cbv7e.1106$g4.23378_at_tor-nn1.netcom.ca...
> > Merci - this is what I was looking for
> >
> > BTW after getting new stats with FOR ALL INDEXED COLUMNS I discovered
the
> > query runs much faster but now uses an index I would never thought would
> > be
> > a good one:
> >
> > Query has COMPANY + PART in the WHERE clause
> > Query does NOT mention the column PART_EAN_NUMBER anywhere (not in
select
> > nor in where)
> > The table has an index on COMPANY + PART_NUMBER so I would expect Oracle
> > to
> > use this or else an FTS
> > Oracle prefers using another index that has COMPANY + PART_EAN_NUMBER +
> > PART_CODE, and indeed it is much faster. I don't get it. This index is
> > larger in size (it has one more column) so how can an INDEX RANGE SCAN
be
> > faster on this index than on the one that only has the 2 columns we have
> > in
> > the WHERE clause?
> >
> > Query:
> > SELECT A1.ROWID
> > FROM PRODUCT_MASTER A1
> > WHERE (((((A1.COMPANY_CODE=:1
> > AND UPPER(A1.PART_CODE) LIKE :2 ESCAPE '\' )
> > AND A1.ACTIVATION_STATUS=:3)
> > AND A1.SALES_ORDERS_FLAG=:4)
> > AND A1.IFA_FLAG_41=:5)
> > AND UPPER(A1.PART_DESC_2) LIKE :7 ESCAPE '\' )
> > ORDER BY A1.COMPANY_CODE ASC,A1.PART_CODE ASC
> >
> > The other columns in the where clause are not part of an index
> >
> > I really don't understand why Oracle would use and index on A+B+C when
the
> > SELECT clause only has A+C and an index on A+C exists.
> >
> > --
> > Syltrem
> >
>
> I'd have to take a closer look at the details to give you a
> definite answer, but the first guess would be related to
> the clustering factor - the A+B clustering factor may be
> very much larger than the A+B+C clustering factor.
> However, even though the COST of the query could easily
> be lower for the 'wrong' index in cases like that, I wouldn't
> normally expect the choice of the wrong index to make the
> query run faster unless we were looking at a big table and
> a busy system.
>
> Regards
>
> Jonathan Lewis
>
INDEX_NAME CLUSTERING_FACTOR ------------------------------ ----------------- IFA_PART_UPC 50237 (the A+B+C index) PRODUCT_MASTER 65541 (the A+C index)
The query runs much faster with the IFA_PART_UPC index. Please note: This is not A+B but A+C; this index does not even have my 2 columns as the 1st 2 of the index, but as the 1st and last.
I also tried to create an index on UPPER(PART_CODE) and that causes a lot more IO (I found this very odd)
This is with Oracle 8174 (this info used to be always in my signature but apparently it went away)
Here are the stats from the various access paths:
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2325 Card=14 Bytes=1
036)
1 0 SORT (ORDER BY) (Cost=2325 Card=14 Bytes=1036)
2 1 TABLE ACCESS (FULL) OF 'PRODUCT_MASTER' (Cost=2323 Card=
14 Bytes=1036)
Statistics
0 recursive calls
7 db block gets
24144 consistent gets
19132 physical reads
0 redo size
2780 bytes sent via SQL*Net to client
769 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
89 rows processed
2) index IFA_PART_UPC (quickest way)
Elapsed: 00:00:10.95
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3308 Card=14 Bytes=1
036)
1 0 SORT (ORDER BY) (Cost=3308 Card=14 Bytes=1036)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT_MASTER' (Cost=
3306 Card=14 Bytes=1036)
3 2 INDEX (RANGE SCAN) OF 'IFA_PART_UPC' (NON-UNIQUE) (Cos
t=794 Card=14)
Statistics
8 recursive calls
0 db block gets
4317 consistent gets
4 physical reads
0 redo size
2780 bytes sent via SQL*Net to client
769 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
89 rows processed
3) index PRODUCT_MASTER
Elapsed: 00:00:16.41
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3836 Card=14 Bytes=1
036)
1 0 SORT (ORDER BY) (Cost=3836 Card=14 Bytes=1036)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT_MASTER' (Cost=
3834 Card=14 Bytes=1036)
3 2 INDEX (RANGE SCAN) OF 'PRODUCT_MASTER' (UNIQUE) (Cost=
556 Card=14)
Statistics
8 recursive calls
0 db block gets
5005 consistent gets
467 physical reads
0 redo size
2780 bytes sent via SQL*Net to client
769 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
89 rows processed
4) testing with a new index on COMPANY_CODE + UPPER(PART_CODE) Elapsed: 00:00:32.50
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=828 Card=14 Bytes=10
36)
1 0 SORT (ORDER BY) (Cost=828 Card=14 Bytes=1036)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT_MASTER' (Cost=
826 Card=14 Bytes=1036)
3 2 INDEX (RANGE SCAN) OF 'TESTUPPER' (NON-UNIQUE) (Cost=2
6 Card=14)
Statistics
8 recursive calls
0 db block gets
66073 consistent gets
16271 physical reads
60 redo size
2780 bytes sent via SQL*Net to client
769 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
89 rows processed
And why is REDO used for this one, I don't know.
I hope you can shed some light on all of this !
Thanks
-- Syltrem http://pages.infinit.net/syltrem (OpenVMS related web site, en français)Received on Thu Apr 14 2005 - 10:57:43 CDT
 |
 |