Home » RDBMS Server » Performance Tuning » How to make use of indexes? (Oracle 10g,10.2.0.1.0,Windows 2003)
How to make use of indexes? [message #360628] Fri, 21 November 2008 12:47 Go to next message
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

Re: How to make use of indexes? [message #360629 is a reply to message #360628] Fri, 21 November 2008 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
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
Re: How to make use of indexes? [message #360631 is a reply to message #360628] Fri, 21 November 2008 13:09 Go to previous messageGo to next message
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?

Re: How to make use of indexes? [message #360636 is a reply to message #360631] Fri, 21 November 2008 13:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
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
Re: How to make use of indexes? [message #360661 is a reply to message #360629] Fri, 21 November 2008 22:57 Go to previous messageGo to next message
raja.or.king
Messages: 24
Registered: November 2008
Location: GJ 5
Junior Member
Confused
what is this 'sticky'?
Please tell me
Re: How to make use of indexes? [message #360667 is a reply to message #360661] Sat, 22 November 2008 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There on top of Forum:

/forum/fa/5326/0/

Regards
Michel
  • Attachment: sticky.jpg
    (Size: 648.05KB, Downloaded 1692 times)
Re: How to make use of indexes? [message #360671 is a reply to message #360667] Sat, 22 November 2008 00:59 Go to previous messageGo to next message
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
Re: How to make use of indexes? [message #360672 is a reply to message #360671] Sat, 22 November 2008 01:06 Go to previous message
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
Previous Topic: performance problems when using ...like string||'%';
Next Topic: INIT.ORA parameters to SCALE UP INSTANCE
Goto Forum:
  


Current Time: Tue Nov 26 09:00:53 CST 2024