CBO bug with global index ?

 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!
!

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

Other related posts: