can't explain query behavior

hi all: using online 7.23 on solaris 2.6. here is the table def: CREATE TABLE M4557PRM_REC ( POL_STATE CHAR (02) NOT NULL, POL_TYPE CHAR (03) NOT NULL, LIA_PHYS_CODE CHAR (01) NOT NULL, COVERAGE_CODE CHAR (03) NOT NULL, ZIP_CODE CHAR (05) NOT NULL, ACCT_DATE CHAR (06) NOT NULL, COMPANY CHAR (02) NOT NULL, COVERAGE_DESC CHAR (04) NOT NULL, MC_DB_IND CHAR (01) NOT NULL, WRIT_PREM DEC (12,2) NOT NULL, EARN_PREM DEC (12,2) NOT NULL, WRIT_EXPOS_DAYS DEC (10,0) NOT NULL, EARN_EXPOS_DAYS DEC (10,0) NOT NULL} here are the index: index0 root dupls No pol_state acct_date company index1 root dupls No acct_date company here are the sql: set explain on; unload to "./result" select p.POL_STATE, p.POL_TYPE, p.LIA_PHYS_CODE, p.COVERAGE_CODE, p.ZIP_CODE, p.COMPANY, sum (p.WRIT_PREM), sum (p.EARN_PREM), sum (p.WRIT_EXPOS_DAYS), sum (p.EARN_EXPOS_DAYS) from M4557PRM_REC p where p.COMPANY = '05' and (p.ACCT_DATE >= '199701' and p.ACCT_DATE <= '199801') group by p.POL_STATE, p.POL_TYPE, p.LIA_PHYS_CODE, p.COVERAGE_CODE, p.ZIP_CODE, p.COMPANY order by p.POL_STATE, p.POL_TYPE, p.LIA_PHYS_CODE, p.COVERAGE_CODE, p.ZIP_CODE, p.COMPANY ; here is the output of sqexplain.out Estimated Cost: 8224434 Estimated # of Rows Returned: 1342507 Maximum Threads: 2 Temporary Files Required For: Order By Group By 1) root.p: SEQUENTIAL SCAN Filters: (root.p.company = '05' AND (root.p.acct_date >= '199701' AND root.p.acct_date <= '199801' ) ) an update satistics high was done on the whole table, my question is, why in the world did it use sequential scan while there is a composite key covers those two fields? thanks a lot. yan

Yan Zhu wrote: > > hi all: > using online 7.23 on solaris 2.6. > here is the table def: [SNIP] > an update satistics high was done on the whole table, my question is, > why in the world did it use sequential scan while there is a composite > key covers those two fields? Because the filter value of the range on acct_date is too low. What % of the rows match that pair of criteria? The rows are small so there are over 90 rows on a data page. If the engine predicts that more than ~12% of the rows will match the criteria it will decide that it will most likely have to read every page anyway so why waste the I/Os to also read index pages? It is even possible that company is not a good filter either. Perhaps if you had an index on company first then acct_date the optimizer might use it. Art S. Kagel