Re: My View performance is very slow

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 02 Sep 2005 16:20:55 -0700
Message-ID: <1125703207.757434@yasure>

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)

Received on Fri Sep 02 2005 - 18:20:55 CDT