Re: My View performance is very slow

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 03 Sep 2005 09:47:54 +0200
Message-ID: <dfbkmk$cl5$04$1@news.t-online.com>

DA Morgan schrieb:
> Maxim Demenko wrote:
>

>> 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

>
>
> Perhaps I am brain dead but your example neither proves nor disproves
> anything. Why not this example?
>
> CREATE TABLE test AS
> SELECT DISTINCT table_name
> FROM all_tables;
> -- I have 1901 rows in the table test
>
> exec dbms_stats.gather_schema_stats(OWNNAME=>'UWCLASS',CASCADE=>TRUE);
>
> -- get all blocks in memory
> SELECT * FROM test;
>
> SET AUTOTRACE TRACEONLY
>
> SELECT table_name
> FROM test
> ORDER BY table_name;
>
> SET AUTOTRACE OFF
>
> CREATE INDEX ix_tname
> ON test(table_name);
>
> exec dbms_stats.gather_schema_stats(OWNNAME=>'UWCLASS',CASCADE=>TRUE);
>
> SET AUTOTRACE TRACEONLY
>
> SELECT table_name
> FROM test
> ORDER BY table_name;
>
> Without index
> -------------
> Statistics
> ---------------------------------------------------
> 1 recursive calls
> 0 db block gets
> 9 consistent gets
> 0 physical reads
> 0 redo size
> 52467 bytes sent via SQL*Net to client
> 1894 bytes received via SQL*Net from client
> 128 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 1901 rows processed
>
>
> With index
> -------------
> Statistics
> ---------------------------------------------------
> 1 recursive calls
> 0 db block gets
> 136 consistent gets
> 0 physical reads
> 0 redo size
> 52467 bytes sent via SQL*Net to client
> 1894 bytes received via SQL*Net from client
> 128 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1901 rows processed
>
>
> I wouldn't count on an index for an ORDER BY.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)

Daniel,
 >>> Indexes, of any kind, have nothing to do with ordering. and
 > I wouldn't count on an index for an ORDER BY.

sounds for me absolutely differently.
I was disagreed with your first statement, because in situations where an additional sort can be avoided , Oracle is smart enough to avoid it (knowing, that indexed values are just ordered as desired ). And in my examples it is obvious , all 3 queries have an ORDER BY, only first two ORDER BY can be avoided ( and it *has* to do with an index ) and Oracle indeed don't sorts in this cases.
Whether one can or will rely on this, it depends.

Best regards

Maxim Received on Sat Sep 03 2005 - 02:47:54 CDT