OraFAQ Forum: Performance Tuning » How to make use of indexes?

Home » RDBMS Server » Performance Tuning » How to make use of indexes? (Oracle 10g,10.2.0.1.0,Windows 2003)

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

How to make use of indexes? [message #360628]

Fri, 21 November 2008 12:47

newToPlsql
Messages: 4
Registered: October 2008

Junior Member

Hi All

I am using a query with a group by clause without any WHERE clause.There are indexes on columns in the select clause,but the explain plan shows a full table scan.Can this be avoided and make use of indexes?

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=347 Card=27007 Byt
          es=1053273)
 
   1    0   HASH (GROUP BY) (Cost=347 Card=27007 Bytes=1053273)
   2    1     TABLE ACCESS (FULL) OF 'TABLEXYZ' (TABLE) (Cost=73 Ca
          rd=27007 Bytes=1053273)
 
 
 
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        317  consistent gets
          0  physical reads
        116  redo size
     373725  bytes sent via SQL*Net to client
      12351  bytes received via SQL*Net from client
       1086  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      16268  rows processed

Report message to a moderator

Re: How to make use of indexes? [message #360629 is a reply to message #360628]

Fri, 21 November 2008 12:50

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

Senior Member
Account Moderator

What about reading the sticky on top of this forum?
If you read it you have not to post the question, the answer is already there.

Regards
Michel

Report message to a moderator

Re: How to make use of indexes? [message #360631 is a reply to message #360628]

Fri, 21 November 2008 13:09

newToPlsql
Messages: 4
Registered: October 2008

Junior Member

Thanks
I have gone through one of them.Still i have the same question,pardon my ignorance

1. I am not using WHERE caluses so most of the points listed in the article are not applicable to my situation.

2.I have not created any Bitmap index.

so is it that if i use a group by clause it will never use an INDEX?

Report message to a moderator

Re: How to make use of indexes? [message #360636 is a reply to message #360631]

Fri, 21 November 2008 13:36

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

Senior Member
Account Moderator

How can we answer without the query?
What is the (first) purpose of index? Quickly find the result set (the rows) not the result fields (the select expressions).

Regards
Michel

Report message to a moderator

Re: How to make use of indexes? [message #360661 is a reply to message #360629]

Fri, 21 November 2008 22:57

raja.or.king
Messages: 24
Registered: November 2008
Location: GJ 5

Junior Member

what is this 'sticky'?
Please tell me

Report message to a moderator

Re: How to make use of indexes? [message #360667 is a reply to message #360661]

Sat, 22 November 2008 00:26

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

Senior Member
Account Moderator

There on top of Forum:

/forum/fa/5326/0/

Regards
Michel

Attachment: sticky.jpg
(Size: 648.05KB, Downloaded 1446 times)

Report message to a moderator

Re: How to make use of indexes? [message #360671 is a reply to message #360667]

Sat, 22 November 2008 00:59

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Create a single index containing all the GROUP BY columns AND all other SELECT columns - the GROUP BY columns must come first.

Ross Leishman

Report message to a moderator

Re: How to make use of indexes? [message #360672 is a reply to message #360671]

Sat, 22 November 2008 01:06

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

You may find that it's not faster though. Index scans read one block at a time, whereas full table scans gobble disk many blocks at a time. Of course the idea is to make up the loss by avoiding a sort, but 10gR2 avoids a sort anyway because it uses HASH GROUP BY.

Ross Leishman

Report message to a moderator

Previous Topic:	performance problems when using ...like string\|\|'%';
Next Topic:	INIT.ORA parameters to SCALE UP INSTANCE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 01:36:58 CDT 2024