How can i increase the performance for the following query where the EXPLAIN commend give the following result:
Explanation 1) First, we lock GPW_DATA.B01_BKP1 for access. 2) Next, we do an all-AMPs RETRIEVE step from GPW_DATA.B01_BKP1 by way of an all-rows scan with a condition of ( "((GPW_DATA.B01_BKP1.GPWB01_ORDLN_STS_C = 'AR') OR ((GPW_DATA.B01_BKP1.GPWB01_ORDLN_STS_C = 'ND') OR ((GPW_DATA.B01_BKP1.GPWB01_ORDLN_STS_C = 'LD') OR ((GPW_DATA.B01_BKP1.GPWB01_ORDLN_STS_C = 'SD') OR ((GPW_DATA.B01_BKP1.GPWB01_ORDLN_STS_C = 'MD') OR (GPW_DATA.B01_BKP1.GPWB01_ORDLN_STS_C = 'DD')))))) AND ((GPW_DATA.B01_BKP1.GPWB01_FILL_RPRT_Y <= DATE '2007-11-30') AND (GPW_DATA.B01_BKP1.GPWB01_FILL_RPRT_Y >= DATE '2007-11-01'))") into Spool 1 (group_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1. The size of Spool 1 is estimated with no confidence to be 697,168 rows. The estimated time for this step is 0.93 seconds. 3) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.93 seconds.
Yes.. I understand your point that when we are using NON-EQUALITY in the where clause, the SI is not used effectively, and Full table scan will take place. But in this case we are not able to get rid of it.
But any way the table is already using SI along with UPI. UNIQUE PRIMARY INDEX ( GPWB01_ORDER_ID_R ) INDEX ( GPWB01_FILL_RPRT_Y ,GPWB01_ORDLN_STS_C ) INDEX ( GPWB01_1STCK_DEP_C ) INDEX ( GPWB01_STCK_FLOC_C ) INDEX ( GPWB01_ORIG_DEP_C );
The following options can be tried out. But activities like creating index, collecting stats etc are resource intensive and comes with a cost. So use them judiciously(i.e if they give you the result you want :) )
- Create Partitions on Date Column.(PPIs and NUSIs typically help Range queries) - Try using IN clause instead of OR clauses and replace = with Between clause and evaluate the explain. - Collect statistics on the two SI columns used in the where clause(Your Explain Plan says No confidence even while retrieving data directly from a single table). - Can create a single table join index with those two columns as the primary index. - Check if you are using an Order By clause to sort data and remove it if not needed. - Remove OR clause and write it as Union of 6 statements and see the explain.(Also see if BMSMS happens) - Try using a derived query to first retrieve based on one Index column and from that retrieve the necessary rows based on the second index column(Ideally this should not effective as this consumes spool space, but donno how effective is your query in a relative sense)
The actual select clause was happening against a view. and the base table is really very big and is used for various other purposes also, like joins etc.
And the actual query used IN clause as well as Between clause only, But only the EXPLAIN command retrived the answer like that. This is the reason in which i am not able to decide for defining PPI or Join index.