Also, with a bit of forethought, planning multi column indexes with
(approprite) use of compredsion, listing least selective columns first, or
even just compressing non-unique single column indexes, can be quite
beneficial in terms of index size reduction and cache efficiency.
I find it hard to imagine a scenario where having one single column index
per column on 122 columns, would have a defensible position.
-Mark
On Fri, Feb 19, 2016, 08:33 l.flatz@xxxxxxxxxx <l.flatz@xxxxxxxxxx> wrote:
Hi Stefan,
You have all 122 columns in different combinations searched? Hardly.
It is conceivable, but my experience tells me that most of the time it is
pure incompetence.
I have seen such a situation that you describe with the early
"pre-google" web searcher apps. Much more often people don't know that an
index can have more than one column.
Normally if you want index combine you would go for a bitmap index unless
there could be locking issues.
Regards
Lothar
----Ursprüngliche Nachricht----
Von : contact@xxxxxxxx
Datum : 19/02/2016 - 12:53 (GMT)
An : laimutis.nedzinskas@xxxxxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Betreff : Re: LOL: 122 one column indexes on 122 column table
Hi Laimis,
That’s…rude.
… or needed by design. Just think about dynamic queries with all possible
predicate combinations - impossible to create proper (composite) indexes for
all these cases. It may be a valid approach to index each column and let
the optimizer work out the combinations (+ "B-tree to Bitmap Conversions").
They also can be used in joins.
Not everything is as crazy as it looks at first view ;-)
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Laimutis Nedzinskas <laimutis.nedzinskas@xxxxxxxxxxxxxxxxxxxxx> hat am19. Februar 2016 um 10:58 geschrieben:
Hi..
I thought I’d seen all.
Not even close:
122 column table.
122 one-column indexes on EACH column.
(BLOBs I don’t count, let them be)
That’s…rude.
/Laimis N
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
