Hi,
Your question of a table having 20 columns and a CONSTRUCT statement opening up all those columns for the user query is a tricky and it is crucial to understand the different problems involved in it, for a Developer and Database Administrator as well.
Not all the information stored in all the columns are very important in a table. Queries are fired or queried upon only on major information. Normally a column which head a primary, secondary or foreign key should be opened for query purpose. Once the Row is identified by this manner, a subsequent SQL can gather REST of the information from the source table and would display it. This logic will prove very productive and effective in terms of time, effort and money.
If it a case where all the columns are opened up for query, the query path depends upon how the base indexes are made-up of columns and their orders. Obviously if the queried data matches with index pattern the response time will be less (efficient) and vice versa. Can one take a liberty and luxury of creating index on each of those 20 columns? depends upon how populated the table is and how important the query is. Normally this is not suggested, and it not a proper solution also. If it is a huge table in terms of rows and columns it is going to take a lot of disk space to store the index information. Further, any modification of information in such table will call upon the B+tree index page to be modified internally for each columns resulting in performance degradation.
The better solution would be according to me is:
1. Identify the crucial and important columns in the table which can be opened up for the user query. Create Unique or Duplicate indexes on these columns according to the domain requirement. If a column is not a contender for Unique index convert it composite key by adding other crucial columns thus, making it less duplicate.
2. If opening up of all the columns for query is important in an application, a DBA should think of updating the data distribution statistics in a different manner. Default mode of LOW is insufficient in this case. Ideally
UPDATE STATISTICS HIGH FOR TABLE <tabname> or
UPDATE STATISTICS HIGH(col3,col5,col11,col19) FOR TABLE <tabname>
can be issued, so that the columns which are NOT part of indexes should be covered under statistical indexes (distributions). Optimizer can take a better and factual decisions for data paths based on these statistical values.
3. In the query screen segregate fields according to their availability for base indexes. Columns having single or composite indexes should be put in the first segment of the screen, so that the user can have a rough idea of the situation.
4. If an user queries with a combinations of indexed and no-indexed column simultaneously, the Optimizer will gather the information first by using the index path and then filter the result with the non-indexed pattern sequentially. This method will work fine without causing much stain on the database server.
5. Other performance optimizing measures like table partitioning (also called fragmentation) combined with parallel database query (PDQ) would enhance the data retrieval job using mechanisms like fragment elimination and allocating multiple threads to work in parallel on a single query etc.
Regards,
Shriyan
