CBO bug with global index ?
- From: David Feng <dbanotes@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 27 May 2005 17:41:23 +0800
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!
!
--
//www.freelists.org/webpage/oracle-l
Other related posts:
