segment field and index field

[jw]

Is there any benefit specifying a date field as "Segment" as well as "Index" field in terms of searching efficiency?

Please correct me if I am wrong.  My understanding is that "Segment" field will tell you which DB2 table(s) will go after.  This will definitely narrow down the number of tables involved in the search (if you have many data in many DB2 tables).

As for the index field, this will speed up the search within those DB2 by using the DB2 index.  If the date field is not an index field, it will perform a table scan.

So, there should not be any conflict specifying a particular date field as "Segment" and "Index" (as oppose to filter)?  Am I right?

Thanks for your comments.

-jw

[dr_te_z]

Yes. You are right. If you do not create the index the system will know which table to scan instead of which table to access more efficient.

[Justin Derrick]

Query performance is very tricky -- and there a lot of things you can do in CMOD to tweak performance.

It really depends on what your data looks like.  Indexes are simply a list of database pages that contain the index value you're searching for.  If you only load one document per day into an Application Group, then indexing the date field might be all you need, since any search for a single date will only require you to load up one database page to find the record you're looking for -- but that's not common.

The advice I normally recommend that you find the 'mostly unique' field, like Customer Number, and index ONLY that field, and require that field be used for searches.  This means that in a 10 million row table, your customer number might show up 10 times -- and by using the index, DB2 knows it only needs to read 10 pages to complete the query.  Then, it uses criteria from the other fields (date, etc.) to discard from the short list, and return exactly what's required.

-JD.

[pankaj.puranik]

You should also be careful while deciding to define a field as an index.
This has effect on the loading performance.
More indexes = more time to load the report.

[dr_te_z]

The advice I normally recommend that you find the 'mostly unique' field, like Customer Number, and index ONLY that field, and require that field be used for searches.

And if the user needs another entrance then it is too bad for them?

You should also be careful while deciding to define a field as an index.
This has effect on the loading performance.
More indexes = more time to load the report.

True. Only once. Is the load-process time critical? Cannot you spare that few extra seconds? But now the report is loaded and users will start to retrieve this data. First they gotto find it by entering their search-parameters and wait for the results..... how long do you let them wait? When you indexes are not properly set-up your users have to wait a long time and your server is using a lot of resources. And this is not "only once", this is repeating itself over and over again.

[Alessandro Perucchi]

To add my .2$ in the discussion :-) It's true that the question of performance is something very tricky to get it right.

One thing is also true, is that too much indexes is not a good idea either, first it takes too much space in the database.
Second, you will slow down the load as pankaj said, but also it will give problems to archive document in parallel for the same application group.
(we have found recently by one of our customer, that with 1 index, he can do parallel archiving in 1 AG, and with lots of index, everything is archive sequentially).

The idea of indexing field is also tricky, because if you have a field that is really unique (like a doc id)... and you index it, then you loose the benefit of using index.
If your date for the segment is also an index, and you are using 1 document per day... then the fact that you have an index is not really useful too.

Best would be to have as few indexes as possible, and to select the best candidat for it (like customerNo, Date (if several dates per day), ...).

Sincerely yours,
Alessandro

[dr_te_z]

if you have a field that is really unique (like a doc id)... and you index it, then you loose the benefit of using index.

No sorry. This is not true. Have lunch with your DBA, he/she will explain.

[Justin Derrick]

The advice I normally recommend is that you find the 'mostly unique' field, like Customer Number, and index ONLY that field, and require that field be used for searches.

And if the user needs another entrance then it is too bad for them?

No, then you need to re-evaluate the use case, and your indexes.  In the overwhelming majority of cases, a single field contains the information needed to narrow down your results to a reasonable, useful number of records.  

There are a number of reasons for requiring specific fields for searches -- availability and security are two that I can think of off the top of my head.  By forcing someone to use an indexed field in their searches, you ensure that they use the most efficient method of searching the database -- rather than gobbling up all the CPU and I/O on the server doing table scans, slowing down other users.  Also, by forcing the usage of, say, a customer number field (and forbidding wildcards), you prevent unscrupulous users from producing information like customer lists to sell to your competition.

Again, there is no one, single, best answer to a general question like this.  Each situation is different, and can use a variety or combination of methods to get the best performance.  Your best bet is to experiment and understand how CMOD and your database engine work at a lower level, so that you can choose the methods that give you the most benefit for the smallest cost.

-JD.

[Alessandro Perucchi]

if you have a field that is really unique (like a doc id)... and you index it, then you loose the benefit of using index.

No sorry. This is not true. Have lunch with your DBA, he/she will explain.

Well thank you for explaining, I don't have a DBA near me, so I've googled and found that information.

Thank you to clarify that point :-)

I have found the following information that might be useful for index ignorant like me :-D (Reference at Oracle and DB2 websites)

- Do not index columns that are modified frequently. UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes as well as data in tables. They also generate additional undo and redo.

- Choose index keys that have high selectivity. The selectivity of an index is the percentage of rows in a table having the same value for the indexed key. An index's selectivity is optimal if few rows have the same value.

- Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values.

- Consider indexing keys that are used frequently in WHERE clauses.

- Define as few indexes as possible on a column that is updated frequently because every change to the column data must be reflected in each index.

- The usefulness of an index depends on its key. Columns and expressions that you use frequently in performing selection, join, grouping, and ordering operations are good key candidates.

Sincerely yours,
Alessandro

[dr_te_z]

Your best bet is to experiment and understand how CMOD and your database engine work at a lower level, so that you can choose the methods that give you the most benefit for the smallest cost.

Good. We do agree.

[jw]

Thanks everyone for the comments and inputs.
Very much appreciated.
-jw