Oracle Database Discussions

Hello

I have a 112mil row table that I have copied to a new tablespace, which worked fine
I'm then adding indexes to the copied table, but gets huge costs in the plans when trying to do a query using the new index, compared to quirying the original table/indexes

To begin with I tired changing my main index to another tablespace and bigger initial extend, but even if I remove all this, and put it in the same tablespace as the original index and created the same way, I get the same huge difference.

In most om my system queries this will cause that the CBO doesn't use the index, and the queries get very slow. If I do a simple select and the index is actually chosen, even though the cost is huge, the actual query time is the same as the origibal table/query. I cant get the index cost to change by trying different dbms_stats.gather_index_stats settings

Can anybody tell me why this new index comes up with such a huge cost, and what I can do to bring the cost down, so that the index actually gets chosen?

Here are the basic information:

It's on Oracle 10gR2 (10.2.0.4) EE

The simplyfied create statement for both the original and the new index:

CREATE INDEX IDX_POS_DATE ON FMC_POS
("UTC_DATE" DESC, FMC_LOGVES_ID)

CREATE INDEX IDX_POS_DATE_P2 ON FMC_POS_p2
("UTC_DATE" DESC, FMC_LOGVES_ID)

The two indexes in dba_indexes:

Original:
OWNER: VTRACK
INDEX_NAME: IDX_POS_DATE
INDEX_TYPE: FUNCTION-BASED NORMAL
TABLE_OWNER: VTRACK
TABLE_NAME: FMC_POS
TABLE_TYPE: TABLE
UNIQUENESS: NONUNIQUE
COMPRESSION: DISABLED
PREFIX_LENGTH:
TABLESPACE_NAME: USERS
INI_TRANS: 2
MAX_TRANS: 255
INITIAL_EXTENT: 65536
NEXT_EXTENT:
MIN_EXTENTS: 1
MAX_EXTENTS: 2147483645
PCT_INCREASE:
PCT_THRESHOLD:
INCLUDE_COLUMN:
FREELISTS:
FREELIST_GROUPS:
PCT_FREE: 10
LOGGING: YES
BLEVEL: 3
LEAF_BLOCKS: 439239
DISTINCT_KEYS: 108849202
AVG_LEAF_BLOCKS_PER_KEY: 1
AVG_DATA_BLOCKS_PER_KEY: 1
CLUSTERING_FACTOR: 79021005
STATUS: VALID
NUM_ROWS: 110950137
SAMPLE_SIZE: 2177632
LAST_ANALYZED: 09-05-2011 23:38:15
DEGREE: 1
INSTANCES: 1
PARTITIONED: NO
TEMPORARY: N
GENERATED: N
SECONDARY: N
BUFFER_POOL: DEFAULT
USER_STATS: NO
DURATION:
PCT_DIRECT_ACCESS:
ITYP_OWNER:
ITYP_NAME:
PARAMETERS:
GLOBAL_STATS: YES
DOMIDX_STATUS:
DOMIDX_OPSTATUS:
FUNCIDX_STATUS: ENABLED
JOIN_INDEX: NO
IOT_REDUNDANT_PKEY_ELIM: NO
DROPPED: NO

New:
OWNER: VTRACK
INDEX_NAME: IDX_POS_DATE_P2
INDEX_TYPE: FUNCTION-BASED NORMAL
TABLE_OWNER: VTRACK
TABLE_NAME: FMC_POS_P2
TABLE_TYPE: TABLE
UNIQUENESS: NONUNIQUE
COMPRESSION: DISABLED
PREFIX_LENGTH:
TABLESPACE_NAME: USERS
INI_TRANS: 2
MAX_TRANS: 255
INITIAL_EXTENT: 65536
NEXT_EXTENT:
MIN_EXTENTS: 1
MAX_EXTENTS: 2147483645
PCT_INCREASE:
PCT_THRESHOLD:
INCLUDE_COLUMN:
FREELISTS:
FREELIST_GROUPS:
PCT_FREE: 10
LOGGING: YES
BLEVEL: 3
LEAF_BLOCKS: 408128
DISTINCT_KEYS: 111888565
AVG_LEAF_BLOCKS_PER_KEY: 1
AVG_DATA_BLOCKS_PER_KEY: 1
CLUSTERING_FACTOR: 88607794
STATUS: VALID
NUM_ROWS: 112757727
SAMPLE_SIZE: 112757727
LAST_ANALYZED: 23-06-2011 07:57:14
DEGREE: 1
INSTANCES: 1
PARTITIONED: NO
TEMPORARY: N
GENERATED: N
SECONDARY: N
BUFFER_POOL: DEFAULT
USER_STATS: NO
DURATION:
PCT_DIRECT_ACCESS:
ITYP_OWNER:
ITYP_NAME:
PARAMETERS:
GLOBAL_STATS: NO
DOMIDX_STATUS:
DOMIDX_OPSTATUS:
FUNCIDX_STATUS: ENABLED
JOIN_INDEX: NO
IOT_REDUNDANT_PKEY_ELIM: NO
DROPPED: NO

The simple selects and costs:

Original table/index:

select * from fmc_pos where utc_date>sysdate-10
Plan:
SELECT STATEMENT ALL_ROWS
Cost: 5 Bytes: 5.350 Cardinality: 50
2 TABLE ACCESS BY INDEX ROWID TABLE VTRACK.FMC_POS
Cost: 5 Bytes: 5.350 Cardinality: 50
1 INDEX RANGE SCAN INDEX VTRACK.IDX_POS_DATE
Cost: 4 Cardinality: 1

Original table/index:

select * from fmc_pos_p2 where utc_date>sysdate-10
Plan:
Plan
SELECT STATEMENT ALL_ROWS
Cost: 3.067 Bytes: 2.708.856 Cardinality: 25.082
2 TABLE ACCESS BY INDEX ROWID TABLE VTRACK.FMC_POS_P2
Cost: 3.067 Bytes: 2.708.856 Cardinality: 25.082
1 INDEX RANGE SCAN INDEX VTRACK.IDX_POS_DATE_P2
Cost: 2.927 Cardinality: 177