OraFAQ Forum: Performance Tuning » Query performance decreased between order by ASC/DESC

Home » RDBMS Server » Performance Tuning » Query performance decreased between order by ASC/DESC

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query performance decreased between order by ASC/DESC [message #532962]

Fri, 25 November 2011 11:33

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

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

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

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

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

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

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

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

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

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

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

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

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

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

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

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 23:38:59 CDT 2024