Re: Why the monstrous SORT?

Brandon,

I just remembered something that you might find useful. This is the link to a doc. on cost/selectivity association for functions. Beware, it is for Oracle 9i and requires Data Cartridge to be installed. You might want to go this route if available in 8i.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#47091

Secondly, the view that you have sent looks overall fine to me. Except that it contains very dynamic data. I hardly work with such tables. The solution seems to me very likely be same as it would be for using temporary tables. My suggestion is to make sure statistics is updated often on the underlying tables.

Jonathan probably could give you more valuable suggestion on this matter.

Regards,

• Vladimir

On 7/12/05, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
> Yes, you're correct Jonathan (like there was any doubt :-)
>
> SQL_test500>select count(*) from oe_line;
>
> COUNT(*)
> ----------
> 252548
>
> SQL_test500>set autot trace exp
>
> SQL_test500>select count(*) from oe_line where not upper(item_no) LIKE 'AF-%';
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=141 Card=1 Bytes=15)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FAST FULL SCAN) OF 'OE_LINE_2' (NON-UNIQUE) (Cost=141 Card=12628 Bytes=189420)
> ^^^^^
>
> 12628/252548=.05
>
> SQL_test500>select count(*) from oe_line where not upper(item_no) LIKE 'AF-%' and not upper(item_no) LIKE 'IFF%';
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=141 Card=1 Bytes=15)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FAST FULL SCAN) OF 'OE_LINE_2' (NON-UNIQUE) (Cost=141 Card=632 Bytes=9480)
> ^^^
> 632/12628=.05
>
> Thanks!
>

>

--
http://www.freelists.org/webpage/oracle-l