| 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: Oracle Won't use INLIST ITERATOR in simple case
That's odd,
I've just run a test with:
SQL> desc t1
Name Null? Type ----------------------------------------- -------- ------------- ID NOT NULL NUMBER SMALL_VC VARCHAR2(10) PADDING VARCHAR2(215) R1 RAW(3)
t1_r1 is a non-unique index on t1(r1)
select
/*+ index(t1 t1_r1) */
id, small_vc, r1
hextoraw('1'),
...
hextoraw('3E8')
In the extreme case of 1,000 values, this was the plan.
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2962 Card=3000 Bytes=54000)
1 0 INLIST ITERATOR
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2962 Card=3000
Bytes=54000)
3 2 INDEX (RANGE SCAN) OF 'T1_R1' (NON-UNIQUE) (Cost=14 Card=3000)
I got the same plan for any number of values I tested between 2 and 1000. For the lower counts, the hint was not necessary.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html <jim.thompson_at_lagosantafe.com> wrote in message news:1162507213.224528.169680_at_m73g2000cwd.googlegroups.com...Received on Fri Nov 03 2006 - 01:22:47 CST
> In Oracle 9.2.x queries of this form will only use the INList iterator
> when the number of items
> in the list is around 250 or so. The limit of an IN clause is 1000.
>
> The table in question has 275,000 rows. If it does tablespace scan it
> takes 1 to 3 minutes.
> If INLIST is used 0.150 seconds.
>
> If I remove statistics from the index but leave them on the table --
> Inlist is used.
>
> uuid is unique key of table.
>
> I have tried many hints, which seem to be ingnored. What a poor
> feature that is!
>
> SELECT TradePilot.Libra_Collection01.uuid,
> TradePilot.Libra_Collection01.enywareid,
> TradePilot.Libra_Collection01.dbState,
> TradePilot.Libra_Collection01.label,
> TradePilot.Libra_Collection01.isEmptyFlag,
> TradePilot.Libra_Collection01.objectVersion
>
> FROM TradePilot.Libra_Collection01
> WHERE ((TradePilot.Libra_Collection01.uuid IN
> ((HEXTORAW('75B17AD392C90A4CB02260D1D4D2FAA8')),
> (HEXTORAW('F5F99289CD1A194B8DB804EB82642F66')),
> (HEXTORAW('0F8C2091D042B04882C341404C067612')),
> (HEXTORAW('ED27DFF2D4DE8547B42BA398149B7D94')),
> (HEXTORAW('E1DD983DCE25E441876D29AE1304DD37')),
> (HEXTORAW('24FA0FF719070648B221A053C0F5130A')),
> (HEXTORAW('E9C0FE99CC950E4C9743111A4A01CBCF')),
> (HEXTORAW('3608FFBA1C26D04C88BB7EFB257E9282')),
> (HEXTORAW('40F6086C1E59E141A8C026037E8BA6B4')),
>
> Soapbox on
>
> The entire idea that the database is always smarter needs to die.
> The amount of time I've spend trying hints, etc is ridiculous.
> I've had this problem with every RDB.
>
> I'd really like to see some way to list tables that tablespace scans
> are not allowed. It would be invaluable for application development
> to determine performance problems immediately.
>
> Soapbox off
>
> If you have any suggestions -- well thanks in advance.
>
 |
 |