OraFAQ Forum: Performance Tuning » Index on order by column

Home » RDBMS Server » Performance Tuning » Index on order by column (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)

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

Index on order by column [message #671716]

Sat, 15 September 2018 09:44

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi All,

I had created index on column , but in our query we are not using indexed column in the where condition , but we are using it in the order by clause,

Here my question is will that index will help full when the indexed column is in order by clause ?
If I see the explain plan and cost before and after creating the index , i didn't find much difference

Please help me to understand this

Thanks
Sai Pradyumn

Report message to a moderator

Re: Index on order by column [message #671717 is a reply to message #671716]

Sat, 15 September 2018 09:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

saipradyumn wrote on Sat, 15 September 2018 07:44

Hi All,

I had created index on column , but in our query we are not using indexed column in the where condition , but we are using it in the order by clause,

Here my question is will that index will help full when the indexed column is in order by clause ?
If I see the explain plan and cost before and after creating the index , i didn't find much difference

Please help me to understand this

Thanks
Sai Pradyumn

my car won't go
Please help me to understand this

We can't say what is wrong since you did not show exactly what you have & do.
post DDL for both the table & all involved indexes.
post actual SQL that does not behave they way you expect it perform.
Realize that ORDER BY clause require a sort operation.
INDEX exist to speed up row filtering (WHERE clause) & not ORDER BY clause.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: Index on order by column [message #671718 is a reply to message #671717]

Sat, 15 September 2018 10:39

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Thanks for your interest to solve ths issue .

Following are DDL and DML & Query


DROP TABLE "INDEX_ORDERBY";
CREATE TABLE "INDEX_ORDERBY"
  (
    "BRANCH"           VARCHAR2(3 CHAR) NOT NULL ENABLE,
    "INTERNAL_REF_NUM" VARCHAR2(20 CHAR) NOT NULL ENABLE ,
    DATE_RECEIVED      DATE
  )
  PARTITION BY LIST
  (
    "BRANCH"
  )
  (
    PARTITION "ABC" VALUES
    (
      'ABC'
    )
  );
 
 INSERT INTO INDEX_ORDERBY 
 SELECT  'ABC', LEVEL  , SYSDATE FROM DUAL  CONNECT BY LEVEL  <= 400000;
 
 CREATE INDEX BDX_INDEX_ORDERBY ON INDEX_ORDERBY(BRANCH,INTERNAL_REF_NUM);
 SELECT *
FROM
  (SELECT PAGINATED_TBL.*,
    ROWNUM RNUM
  FROM
    (SELECT
      /*+ parallel (MV 2) */
      BRANCH,
      INTERNAL_REF_NUM
    FROM INDEX_ORDERBY MV  
    ORDER BY INTERNAL_REF_NUM
    ) PAGINATED_TBL
  WHERE ROWNUM <=200
  )
WHERE RNUM >=1;

Its behaving differently in different scenarios.

Report message to a moderator

Re: Index on order by column [message #671719 is a reply to message #671718]

Sat, 15 September 2018 11:21

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Blackswan,

For the above query to get the fast results, we need to apply the index on that partitioned table.

What kind of index is use full for that kind of pagination query .

Do I need to go for the composite index (combination of ref number and branch)
or
Partitioned index ?.If it partitioned index is it global or local ?

But ultimately need to get the fast results by creating the index

Thanks
SaiPradyumn

Report message to a moderator

Re: Index on order by column [message #671720 is a reply to message #671718]

Sat, 15 September 2018 11:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Its behaving differently in different scenarios.

I bet SQL above behaves the same every time it is executed.
Can you prove me wrong?

What problem are you trying to solve?

Report message to a moderator

Re: Index on order by column [message #671721 is a reply to message #671720]

Sat, 15 September 2018 11:39

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

In uat environment we are getting fast results where as in production its taking long time to execute.

Right now prod have normal index on only the branch column.Even though its taking more time.

My task is reduce the time with the help index. It may be the non partitioned or composite or global or local
what ever it may be with the help of indexes time has to be reduced .

Report message to a moderator

Re: Index on order by column [message #671722 is a reply to message #671721]

Sat, 15 September 2018 11:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

saipradyumn wrote on Sat, 15 September 2018 09:39

In uat environment we are getting fast results where as in production its taking long time to execute.

Right now prod have normal index on only the branch column.Even though its taking more time.

My task is reduce the time with the help index. It may be the non partitioned or composite or global or local
what ever it may be with the help of indexes time has to be reduced .

post EXPLAIN PLAN from both databases.
how many rows in each table?

BTW, so far you have posted ZERO actionable details; only general descriptions

Report message to a moderator

Re: Index on order by column [message #671726 is a reply to message #671718]

Sun, 16 September 2018 04:40

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

You ORDER BY clause is on INTERNAL_REF_NUM but your index is on BRANCH,INTERNAL_REF_NUM. Unless you include a filter on BRANCH, I do no see how your index can be used other than as an index full scan (or fast full scan) followed by a sort.

[Updated on: Sun, 16 September 2018 04:42]

Report message to a moderator

Re: Index on order by column [message #671737 is a reply to message #671726]

Mon, 17 September 2018 02:27

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Try removing the parallel hint, although based on what has been posted I don't expect major gains to be honest.

from the data supplied your options are:

FTS + sort
Index FFS + sort
Index full scan

Breaking these down you're looking at two approaches using multiblock access to read the object and one using single block access. Multiblock is going to win all day when it comes to that. An index FFS+sort might be marginally quicker than a table FTS, but I wouldn't expect miracles given the table described and it'll not do that in parallel (hence my suggesting the hint removal)

Perhaps if you added an access predicate or something else to help the database limit the rows?

All that being said, you seem to have skipped ahead to this approach being the solution to the problem, but is it really the correct solution? Again based on what has been supplied at best this is a band aid which likely won't work long term (if at all).

Report message to a moderator

Re: Index on order by column [message #671754 is a reply to message #671737]

Tue, 18 September 2018 02:53

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi All,

Thanks for your suggestions.Finally we able to get the improvement by adding wherecondition on branch column and
stats gather on the table before firing this query.

Thank you very much for your valuable suggestions

Thanks
Sai Pradyumn

Report message to a moderator

Previous Topic:	Range Scan Index
Next Topic:	Pleaese clear my doubt about View and synonym having same name

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 18:01:44 CST 2025