Re: Oracle - best index?

Jens Meier wrote:

> Table with more then 300.000 (and daily more) rows and round 20 Columns.

Not really a very large table... If all else fails, full table scans run in parallel can plow through this data fairly quickly.

> Sometimes we search with the exact ColumnA
> Sometimes we search with like %ColumnA
> Sometimes we use ColumnC ColumnD
> Sometimes we use ColumnD ColumnE ColumnF
> Sometimes we use %ColumnA ColumnD ColumnE ColumnF
> But sometimes we use ColumnD in a timerange
>
> I don't know which index is the best for this situation.
>
> (Which columns, Bitmap or normal index...)

Columns with a low cardinality, are usually candidates for bitmaps indexes.

> Now we have of course the PK as Index (standard)
> Then we have created many other combinations of columns as index,
>
> but the oracle optimizer doesn't match every time the best index, so we
> have sometimes very high answer times.

Do you have accurate/up-to-date analyser stats for the CBO to chew on?

I do not think Oracle is as much at fault here. It looks to me more like a design problem.

You can index every single column. You can index columns(A, B), index columns(B,A), index columns(A,C,D) and index column(D).. etc. But to cater for over 20 columns this way!?

I sit with a production database with a similar wonderfully designed indexing strategy. Goes without saying, it is bloated, insert & update performance are very poor, and index tablespaces are large than the data tablespaces.

What is the first rule of design? Define the business requirements. Having data stuffed into a single growing table, with over 20 columns, where users can use any combination of the 20 columns can be used as search criteria... that points IMO to a design that does not take cognicance at all of the business and user requirements.

Personally. Well, I will not be pointing the finger at Oracle's lack of performance, but at the people who design and implemented the database and/or the users who are (ab)using the system with their queries-from-hell.

Once had a user who asked me for a fact data download from a warehouse. She would like a specific month's fact data please. 70+ million rows. When I pointed that out to her, she said it is not a problem. She have MS Access installed on her notebook and 20GB's of free diskspace. As if that could outperform a 12 node OPS running on a Unix cluster...

Bottom line. Understand the business requirements. Understand user's expectations and ignorance. Add a few shovels of logic, Oracle expertise, user training, and what not. Mix vigorously. Serve.

--
Billy