CBO bug with global index ?

From: David Feng <dbanotes_at_gmail.com>
Date: Thu, 26 May 2005 15:31:15 +0800
Message-ID: <6622a16f05052600314b8fdc1c@mail.gmail.com>

Hi,All,
 TRADE_BASE is a partitioned table .
SELECT COUNT (*) AS totalcount
FROM trade_base
WHERE (buyer_account = 'www.cnoug.org <http://www.cnoug.org>' OR seller_account = 'www.cnoug.org <http://www.cnoug.org>') AND trade_status IN (
SELECT *
FROM THE (SELECT CAST (str2varlist ('www,cnoug,org') AS vartabletype) FROM DUAL
));

---

| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |

---

| 0 | SELECT STATEMENT | | 1 | 1056 | 14040 | | |
| 1 | SORT AGGREGATE | | 1 | 1056 | | | |

|* 2 | HASH JOIN SEMI | | 10 | 10560 | 14040 | | |


| 3 | PARTITION RANGE ALL | | | | | 1 | 21 |

|* 4 | TABLE ACCESS FULL | TRADE_BASE | 62 | 3348 | 14028 | 1 | 21 |


| 5 | VIEW | VW_NSO_1 | 8168 | 7992K| 11 | | |

| 6 | COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | | | | | |
| 7 | TABLE ACCESS FULL | DUAL | 82 | | 2 | | |

---

 I've created 2 global indexes on TRADE_BASE's 'buyer_account' and 'seller_account' columns,but ,unfortunate, CBO choose FULL TABLE scan on TRADE_BASE. And ,I have already analyzed the table TRADE_BASE .You can see the plan
show that TABLE_BASE'S 62 rows ,but indeed:  SQL>select count(*) from trade_base;  

COUNT(*)

---

1960960
 However ,when I change index to local ,everything is OK .   

---

| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |

---

| 0 | SELECT STATEMENT | | 1 | 1056 | 223 | | |
| 1 | SORT AGGREGATE | | 1 | 1056 | | | |

|* 2 | HASH JOIN SEMI | | 63 | 66528 | 223 | | |

| 3 | PARTITION RANGE ALL | | | | | 1 | 21 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| TRADE_BASE | 376 | 20304 | 208 | 1
| 21 |
| 5 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 6 | BITMAP OR | | | | | | |
| 7 | BITMAP CONVERSION FROM ROWIDS | | | | | | |

|* 8 | INDEX RANGE SCAN | IND_TRADE_BASE_BACCOUNT | | | 43 | 1 | 21 |

| 9 | BITMAP CONVERSION FROM ROWIDS | | | | | | |

|* 10 | INDEX RANGE SCAN | IND_TRADE_BASE_SACCOUNT | | | 44 | 1 | 21 |


| 11 | VIEW | VW_NSO_1 | 8168 | 7992K| 11 | | |

| 12 | COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | | | | | |
| 13 | TABLE ACCESS FULL | DUAL | 8168 | | 11 | | |

---

   

SQL> select * from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production

 Thanks for any help!!

-- 

Meet me at Alibaba.com <http://Alibaba.com>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 26 2005 - 03:36:07 CDT