| >>>Wired SQL Tuning issue, maybe Oracle's bug<<<< [message #303059] |
Wed, 27 February 2008 18:53  |
elawcn
Messages: 1
Registered: February 2008
|
Junior Member |
|
|
We have two enviornments, Oracle 10g Xe and Oracle 10g.
When running the below query, Index can be picked up by SQL properly in XE but 10g standard version. all the SQL and index are identical.
Does any expert know what happened?
select
/* +index(d_user_key_idx1) */u.*
from
cca_dw.d_user u inner join cca_dw.a_userstats a on a.user_key = u.user_key
where
u.user_key in(-1) OR (-1 in (-1))
XE's EP
Plan
SELECT STATEMENT ALL_ROWSCost: 1 Bytes: 208 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID TABLE CCA_DW.D_USER Cost: 1 Bytes: 195 Cardinality: 1
3 NESTED LOOPS Cost: 1 Bytes: 208 Cardinality: 1
1 INDEX FULL SCAN INDEX CCA_DW.A_USERSTSATS_IDX_USERKEY1 Cost: 0 Bytes: 13 Cardinality: 1
2 INDEX RANGE SCAN INDEX CCA_DW.D_USER_KEY_IDX1 Cost: 0 Cardinality: 1
Oracle Standard version
----------------------------------
Plan
SELECT STATEMENT ALL_ROWSCost: 1,792 Bytes: 195,514,410 Cardinality: 1,156,890
3 HASH JOIN Cost: 1,792 Bytes: 195,514,410 Cardinality: 1,156,890
1 TABLE ACCESS FULL TABLE CCA_DW.D_USER Cost: 57 Bytes: 1,360,370 Cardinality: 8,195
2 INDEX FAST FULL SCAN INDEX CCA_DW.A_USERSTSATS_IDX_USERKEY1 Cost: 800 Bytes: 3,470,670 Cardinality: 1,156,890
|
|
|
|
| Re: >>>Wired SQL Tuning issue, maybe Oracle's bug<<<< [message #303066 is a reply to message #303059] |
Wed, 27 February 2008 20:55  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Look at the cardinality estimates. The standard edition is much higher. If you have the same data in both environments, then you should gather statistics with DBMS_STATS.GATHER_TABLE_STATS().
Your post was very hard to read. Please enclose code in [code]...[/code] tags next time to preserve formatting.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
