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
|
|
|
|
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?
|
|
|
|
|
|
|
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
|
|
|