Re: My View performance is very slow

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Fri, 02 Sep 2005 18:57:58 +0200
Message-ID: <43188479$0$2116$9b4e6d93@newsread2.arcor-online.net>

DA Morgan schrieb:
> Robert Klemme wrote:
>

>> DA Morgan wrote:
>>
>>> siddu_htc_at_yahoo.com wrote:
>>>
>>>> I found one thing the select is having an order by whcih is the
>>>> culprit for the performace.
>>>> Ex: select .... from msc_item_attributes_v where ..... order by
>>>> item_name ,organization_code
>>>>
>>>> We get the order by columns values based on functions.
>>>> Can anyone tell me how can i order the results without hanpering the
>>>> performance.
>>>
>>>
>>> You can't. And the function likely adds to the misery. There have been
>>> a number of articles by Tom Kyte (check http://asktom.oracle.com) on
>>> this issue.
>>
>>
>>
>> Sorry for jumping right in the middle of the thread.  What about a
>> function based index?  Wouldn't that help for ordering also?  Or did I
>> miss something?
>>
>> Kind regards
>>
>>     robert

>
>
> Indexes, of any kind, have nothing to do with ordering.
>
> In 10g one might consider a sorted hash cluster as a solution.

I disagree.

scott_at_ORA92> create table t(id number primary key,text varchar2(20));

Table created.

scott_at_ORA92> insert into t select rownum, 'Hello' from all_objects where rownum <101;

100 rows created.

scott_at_ORA92> commit;

Commit complete.

scott_at_ORA92> exec dbms_stats.gather_table_stats(user,'T',cascade=>TRUE)

PL/SQL procedure successfully completed.

scott_at_ORA92>
scott_at_ORA92> set autotrace traceonly explain
scott_at_ORA92> select id from t where id<5 order by id;

Execution Plan

---

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=12)     1 0 INDEX (RANGE SCAN) OF 'SYS_C002739' (UNIQUE) (Cost=1 Card=4 Bytes=12)

scott_at_ORA92> select text from t where id<5 order by id;

Execution Plan

---

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=36)     1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=4 Bytes=36)     2 1 INDEX (RANGE SCAN) OF 'SYS_C002739' (UNIQUE) (Cost=1 Card=4)

scott_at_ORA92> select text from t where id < 5 order by text;

Execution Plan

---

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=4 Bytes=36)     1 0 SORT (ORDER BY) (Cost=4 Card=4 Bytes=36)

    2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=4 Bytes=36)
    3    2       INDEX (RANGE SCAN) OF 'SYS_C002739' (UNIQUE) (Cost=1 

Card=4)

Best regards

Maxim Received on Fri Sep 02 2005 - 11:57:58 CDT