Maxim Demenko wrote:
> 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
In this you are correct and I to general. Thank you for pointing out
the specific case.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Sat Sep 03 2005 - 11:07:34 CDT
