Home » RDBMS Server » Performance Tuning » Query performance decreased between order by ASC/DESC
Query performance decreased between order by ASC/DESC [message #532962] Fri, 25 November 2011 11:33 Go to next message
euphORIA
Messages: 3
Registered: November 2011
Junior Member
Hello all,

I am having difficulties trying to fix the performance issue I am having running one query, which runs fine (3 seconds) if no order or an ASC order is applied to the PK field of the main table, but it is very slow when the order by is DESC.

The query would look like:

SELECT A.FIELD_A,
        A.FIELD_A,
        A.FIELD_A,
        A.FIELD_A,
        A.FIELD_A,
        A.FIELD_A,
        B.FIELD_B,
        C.FIELD_B,
        D.FIELD_B,
        E.FIELD_B,
        ...
        L.FIELD_B,
        L.FIELD_C
    FROM HAT_TABLE_A A
    LEFT JOIN TABLE_B B
    ON A.FIELD_A=B.FIELD_A
    LEFT JOIN TABLE_C C
    ON A.FIELD_A=C.FIELD_A
    LEFT JOIN TABLE_D D
    ON A.FIELD_A=D.FIELD_A
    LEFT JOIN TABLE_D D
    ON A.FIELD_A=D.FIELD_A
    LEFT JOIN TABLE_E E
    ON A.FIELD_A=E.FIELD_A
    ...
    LEFT JOIN TABLE_L L
    ON A.FIELD_A=L.FIELD_A
    order by A.FIELD_A DESC


In total, there are 12 tables, the main one (TABLE_A) and all the others that are linked to main one, and which in my test are a one to one relationship to TABLE_A (In the future, this will change), except table L that contains 20 times more data.

Basically, tables A to K have 50K rows, and table L has 1 million.

FIELD_A is the primary key for TABLE_A, and is used as foreign key for all other tables. I have tried creating an index on [TABLE_A.FIELD_A DESC] but it does not change anything. All stats are up to date.

Do you know what could be the reason why changing the order by from ASC to DESC may decrease the performance that much?

Report message to a moderator

Re: Query performance decreased between order by ASC/DESC [message #532963 is a reply to message #532962] Fri, 25 November 2011 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
For any peformances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

Re: Query performance decreased between order by ASC/DESC [message #532977 is a reply to message #532963] Fri, 25 November 2011 18:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I am having difficulties trying to fix the performance issue I am having running one query,
>which runs fine (3 seconds) if no order or an ASC order is applied to the PK field of the main table,
>but it is very slow when the order by is DESC.

maybe so or maybe not so.

Do result vary based upon order of testing or when doing same case multiple times in a row?
Post EXPLAIN PLAN for both cases.

Report message to a moderator

Re: Query performance decreased between order by ASC/DESC [message #533089 is a reply to message #532977] Mon, 28 November 2011 04:11 Go to previous messageGo to next message
euphORIA
Messages: 3
Registered: November 2011
Junior Member
The explain plan is different, depending on the order (I am only including the last lines, all the rest is the same in both plans):

ASC order - Cost: 10,408 for the sorting:
SELECT STATEMENT  ALL_ROWSCost: 10,408  Bytes: 23,007,600  Cardinality: 63,910
	57 SORT UNIQUE  Cost: 10,408  Bytes: 23,007,600  Cardinality: 63,910
		56 SORT GROUP BY  Cost: 10,408  Bytes: 23,007,600  Cardinality: 63,910
			55 HASH JOIN RIGHT OUTER  Cost: 5,499  Bytes: 23,007,600  Cardinality: 63,910 
			...


DESC order - Cost: 15,316 for the sorting:
SELECT STATEMENT  ALL_ROWSCost: [color=red]15,316[/color]  Bytes: 23,007,600  Cardinality: 63,910
	57 SORT UNIQUE  Cost: [color=red]15,316[/color]  Bytes: 23,007,600  Cardinality: 63,910
		56 SORT GROUP BY  Cost: [color=red]15,316[/color]  Bytes: 23,007,600  Cardinality: 63,910
			55 HASH JOIN RIGHT OUTER  Cost: 5,499  Bytes: 23,007,600  Cardinality: 63,910 
			...


Does it help? Unfortunately I have no access to trace files.

In general, is there any recommendation to follow when sorting a query in DESC order? this is the first time that I am facing a similar problem with the performance just for changing the order by Sad

[Updated on: Mon, 28 November 2011 04:13]

Report message to a moderator

Re: Query performance decreased between order by ASC/DESC [message #533138 is a reply to message #533089] Mon, 28 November 2011 07:39 Go to previous messageGo to next message
jrnayak
Messages: 35
Registered: November 2011
Location: London
Member
create a unique index instead of PK on TABLE_A.FIELD_A.

create unique index IDX_(Name)_DSC on TABLE_A(FIELD_A DESC).

and don't use order by clause. the query would fetch the result in desc order.

Report message to a moderator

Re: Query performance decreased between order by ASC/DESC [message #533143 is a reply to message #533138] Mon, 28 November 2011 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
and don't use order by clause. the query would fetch the result in desc order.


This is wrong.
The ONLY way to get a specific order is to use the ORDER BY clause.

Regards
Michel

Report message to a moderator

Re: Query performance decreased between order by ASC/DESC [message #533144 is a reply to message #533143] Mon, 28 November 2011 07:51 Go to previous messageGo to next message
jrnayak
Messages: 35
Registered: November 2011
Location: London
Member
Oracle does a nosort opereration if it is using an index and the order by clause is on the indexed column

Report message to a moderator

Re: Query performance decreased between order by ASC/DESC [message #533145 is a reply to message #533138] Mon, 28 November 2011 08:08 Go to previous messageGo to next message
euphORIA
Messages: 3
Registered: November 2011
Junior Member
jrnayak wrote on Mon, 28 November 2011 07:39
create a unique index instead of PK on TABLE_A.FIELD_A.

create unique index IDX_(Name)_DSC on TABLE_A(FIELD_A DESC)


This has been created but Oracle is not picking it up.

Report message to a moderator

Re: Query performance decreased between order by ASC/DESC [message #533162 is a reply to message #533145] Mon, 28 November 2011 09:27 Go to previous messageGo to next message
jrnayak
Messages: 35
Registered: November 2011
Location: London
Member
Use an index hint to force the optimizer to use this particular index

Report message to a moderator

Re: Query performance decreased between order by ASC/DESC [message #533168 is a reply to message #533144] Mon, 28 November 2011 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
jrnayak wrote on Mon, 28 November 2011 14:51
Oracle does a nosort opereration if it is using an index and the order by clause is on the indexed column


And? This does NOT invalidate my remark that if you want an order you MUST use ORDER BY clause.

Regards
Michel

Report message to a moderator

Re: Query performance decreased between order by ASC/DESC [message #533170 is a reply to message #533162] Mon, 28 November 2011 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
jrnayak wrote on Mon, 28 November 2011 16:27
Use an index hint to force the optimizer to use this particular index


Do NOT use a hint unless you have a proof there is a bug in Oracle that prevent it from finding/using the most optimized plan.

Regards
Michel

[Updated on: Mon, 28 November 2011 10:24]

Report message to a moderator

Re: Query performance decreased between order by ASC/DESC [message #533182 is a reply to message #533170] Mon, 28 November 2011 11:59 Go to previous message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Descending indexes are function-based indexes and require special attention.
See Advantages of Function-Based Indexes (midway page) and Function based restrictions at the bottom of the page

Report message to a moderator

Previous Topic: SGA AND PGA PARAMETER SETTINGS
Next Topic: Data type conversion impact on performance
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Nov 24 13:04:48 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.