Oracle Database Discussions

hi everybody,

I have a small problem about partitioning and i couldn't understand why. here is my scenario:

I created a partitioned table, partition is a range partition ona date column, then I created 2 index one is prefixed, one is non-prefixed and both of them is local. when i execute a query based on partition column and one other (that i indexed) execution plans become really different. as an example:

  CREATE TABLE PART_HAREKET_TABLE (
    ISLEM_TAR DATE, -- MY PARTITION COLUMN
    ISLEM_REF VARCHAR2(10), -- INDEX COLUMN
    ... -- OTHER COLUMNS HERE
  );
  
-- load data to the table from one of my prod table...

  CREATE INDEX I_PART_HAREKET_1 ON PART_HAREKET_TABLE(ISLEM_TAR, ISLEM_REF) LOCAL;
  
  CREATE INDEX I_PART_HAREKET_2 ON PART_HAREKET_TABLE(ISLEM_REF) LOCAL;
  
  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'PART_HAREKET_TABLE', ESTIMATE_PERCENT => 100, CASCADE => TRUE);

after that i run these queries:

  EXPLAIN PLAN FOR
  select /*+ INDEX(PART_HAREKET_TABLE, I_PART_HAREKET_1 ) */ * 
  from   part_hareket_table 
  where islem_tar = to_Date('22/01/2012','dd/mm/yyyy') and islem_ref like 'KN%';

execution plan:

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                    |  1243 |   195K|    19   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                    |  1243 |   195K|    19   (0)| 00:00:01 |    62 |    62 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PART_HAREKET_TABLE |  1243 |   195K|    19   (0)| 00:00:01 |    62 |    62 |
|*  3 |    INDEX RANGE SCAN                | I_PART_HAREKET_1   |  1243 |       |     5   (0)| 00:00:01 |    62 |    62 |
-------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("ISLEM_TAR"=TO_DATE(' 2012-01-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "ISLEM_REF" LIKE 'KN%')
       filter("ISLEM_REF" LIKE 'KN%')

that is a good cost i think, also as predicate info i can see that ISLEM_TAR and ISLEM_REF.

when I use this:

  EXPLAIN PLAN FOR
  select /*+ INDEX(PART_HAREKET_TABLE, I_PART_HAREKET_2 ) */ * 
  from   part_hareket_table 
  where islem_tar = to_Date('22/01/2012','dd/mm/yyyy') and islem_ref like 'KN%';

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                    |  1243 |   195K|  8209   (1)| 00:01:55 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                    |  1243 |   195K|  8209   (1)| 00:01:55 |    62 |    62 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PART_HAREKET_TABLE |  1243 |   195K|  8209   (1)| 00:01:55 |    62 |    62 |
|*  3 |    INDEX RANGE SCAN                | I_PART_HAREKET_2   |   141K|       |   218   (1)| 00:00:04 |    62 |    62 |
-------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("ISLEM_TAR"=TO_DATE(' 2012-01-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   3 - access("ISLEM_REF" LIKE 'KN%')
       filter("ISLEM_REF" LIKE 'KN%')

as you see here there is a huge cost difference and ISLEM_TAR (partitioned column) is also use as filter not access. both of these indexes are LOCAL

so I expected that second index (non-prefixed) would be more efficient. because oracle already know which partition that has to be read and this index is smaller (just one column has it) so i thought, Oracle will find appropriate index partition part and will read just that partition (for index and table) and find rows.

even the query times are different, first one (prefixed) brings data 0.031 ms, second (non-prefixed) 0.375ms.

but it is not? what am i missing ?

you may say that "LIKE" operator cause to that on ISLEM_REF column. I also use it as equality "=",
than for first query cost is 4, for second one, cost become 8. still 2 times...

partition size is approximately 440MB

also a similer example is exist in here: 2152455

Jonathan Lewis made an example and that one works fine...

my db:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE	11.2.0.2.0	Production"
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production