This discussion is archived
6 Replies Latest reply: Dec 12, 2011 4:13 AM by 637538 RSS

iot columns that should be in top segment  are in overflow segment

637538 Newbie
Currently Being Moderated
Hi ,
i created an iot with about 45 columns
i used the including syntax to keep only 15 columns in the top segments while all others in the overfolow segment.

this reduced the size of the top segment dramatically so i thought if i query the table and use only the 15 columns
it will greatly improve the Elapsed time since I/O was my bottleneck.

running the query had the same results as all columns are in the top segment.

checking which files oracle is accessing with 10046 trace i discovered that it's going to the overflow segment
( which is reside in another tablespace and another datafile)

the primary key consist of 5 columns
trying another query the query with only the 5 primary key columns discovered that it's going only to the top segment.

Am i missing something ?

here is my test case

block size is 32k
oracle 11.2.0.2
--create IOT

CREATE
  TABLE "ZVIKAG"."Z1_FAC_OPERATIONS_OF"
  (
    "ACCOUNT_FK" NUMBER,
    "FROM_DATE_TRUNC" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  "ROW_TYPE_FK"            NUMBER,
  "AGENT_FK"               NUMBER,
  "VS_HOT_LEAD_IND"        NUMBER,
  "CHANNEL_FK"             NUMBER,
  "ENG_INTERACTIVE_IND"    NUMBER,
  "IMD_CHANNEL_FK"         NUMBER,
  "ENG_SERVICE_QUEUE_FK"   NUMBER,
  "EVENT_ROOM_FK"          NUMBER,
  "ENG_SKILL_FK"           NUMBER,
  "STATUS_DURATION_MILLIS" NUMBER,
  "AGENT_STATUS_FK"        NUMBER,
  "FROM_DATE" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  "FIRST_INTERVAL_IND" NUMBER,
  "ENG_AGENT_SURVEY_SUBMISSION_DT" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  "ENG_COMMUNICATION_END_DT" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  "ENG_COMMUNICATION_START_DT" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  "LP_AGENT_SESSION_ID"   VARCHAR2(400 BYTE),
  "TOTAL_DURATION_MILLIS" NUMBER,
  "EVENT_SEQ"             NUMBER,
  "ROW_GK"                NUMBER,
  "INSERT_DATE" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  "UPDATE_DATE" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  "DI_RU_ID"          VARCHAR2(4000 BYTE),
  "DI_ETL_VERSION_FK" NUMBER,
  "DI_APP_VERSION_FK" NUMBER,
  "DI_VS_CLOSE_DT" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  "DI_VS_CLOSE_REASON_FK" NUMBER,
  "DI_VS_OPEN_DT" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  "ENG_QUEUE_ENTER_DT" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  "ENG_QUEUE_EXIT_DT" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  "ENG_GK" NUMBER,
  "TO_DATE_TRUNC" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  "ENG_INCLUDE_INTERACTION_IND" NUMBER,
  "TO_DATE" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  "LAST_INTERVAL_IND"        NUMBER,
  "INSERT_REASON_FK"         NUMBER,
  "VS_ROOM_IND"              NUMBER,
  "IMD_APPROACH_TYPE_FK"     NUMBER,
  "VS_VISITOR_SESSION_GK"    NUMBER,
  "VS_LP_VISITOR_ID"         VARCHAR2(400 BYTE),
  "VS_LP_VISITOR_SESSION_ID" VARCHAR2(400 BYTE),
  "VS_START_DT" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  "VS_START_DT_TRUNC" TIMESTAMP (3)
WITH
  LOCAL TIME ZONE,
  CONSTRAINT "FAC_OPERATIONS_IOT_OF1" PRIMARY KEY ("ACCOUNT_FK",
  "FROM_DATE_TRUNC", "ROW_TYPE_FK", "AGENT_FK", "ROW_GK") ENABLE
  )
  ORGANIZATION INDEX COMPRESS 3 PCTFREE 1 INITRANS 2 MAXTRANS 255 TABLESPACE
  "TS_OPERATIONS_OTHER" STORAGE
  (
    BUFFER_POOL DEFAULT
  )
  PCTTHRESHOLD 50 INCLUDING "ROW_GK" OVERFLOW PCTFREE 10 INITRANS 1 MAXTRANS
  255 TABLESPACE "TS_Z_ACCOUNT_100_OLD" STORAGE
  (
    BUFFER_POOL DEFAULT
  )
  PARTITION BY RANGE
  (
    "FROM_DATE_TRUNC"
  )
  (
  PARTITION "P111017" VALUES LESS THAN (TIMESTAMP    ' 2011-10-18 00:00:00.000000000 GMT GMT') PCTFREE 1 
  ,PARTITION "P111018" VALUES LESS THAN (TIMESTAMP    ' 2011-10-19 00:00:00.000000000 GMT GMT') PCTFREE 1 
  ,PARTITION "P111019" VALUES LESS THAN (TIMESTAMP    ' 2011-10-20 00:00:00.000000000 GMT GMT') PCTFREE 1 
  ,PARTITION "P111020" VALUES LESS THAN (TIMESTAMP    ' 2011-10-21 00:00:00.000000000 GMT GMT') PCTFREE 1 
  ,PARTITION "P111021" VALUES LESS THAN (TIMESTAMP    ' 2011-10-22 00:00:00.000000000 GMT GMT') PCTFREE 1 
  ,PARTITION "P111022" VALUES LESS THAN (TIMESTAMP    ' 2011-10-23 00:00:00.000000000 GMT GMT') PCTFREE 1 
  ,PARTITION "P111023" VALUES LESS THAN (TIMESTAMP    ' 2011-10-24 00:00:00.000000000 GMT GMT') PCTFREE 1 
  ,PARTITION "P111024" VALUES LESS THAN (TIMESTAMP    ' 2011-10-25 00:00:00.000000000 GMT GMT') PCTFREE 1 
  ,PARTITION "P111025" VALUES LESS THAN (TIMESTAMP    ' 2011-10-26 00:00:00.000000000 GMT GMT') PCTFREE 1 
  ,PARTITION "P111026" VALUES LESS THAN (TIMESTAMP    ' 2011-10-27 00:00:00.000000000 GMT GMT') PCTFREE 1 
  ,PARTITION "P111027" VALUES LESS THAN (TIMESTAMP    ' 2011-10-28 00:00:00.000000000 GMT GMT') PCTFREE 1 
  );




create table zvikag.z1_t ( id number);





--NEEDS TO LOAD SOME DATA
...




-- start trace  check  file dwh_ora_731_zvika_check_iot_overflow11.trc


-- run as sysdba
 alter system flush shared_pool ; 
 alter system flush buffer_cache ; 

set echo on timing on


--set autot traceonly


alter session set tracefile_identifier = 'zvika_check_iot_overflow11';


alter session set events = '10046 trace name context forever, level 12';

insert into zvikag.z1_t
select 1
                FROM    zvikag.Z1_FAC_OPERATIONS_OF   fac_operations 
  WHERE  fac_operations.account_fk = 382 and  fac_operations.row_type_fk IN (1,3)
                        AND
                        (
                         fac_operations.from_date_trunc BETWEEN CAST(to_timestamp('17/10/2011 00:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP   WITH LOCAL TIME ZONE) 
                AND CAST(to_timestamp('26/10/2011 23:59:59','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP        WITH LOCAL TIME ZONE))
AND fac_operations.agent_fk IN(395729,235639,243679,290076,396272,240522,232646,396279,240717,232675,240681,235505,673653,396278,236890,396274,396275,239291,290088,232685,396271,396273,242625,396270,396268,243827,249498,396282,396269,232814,229163,396283,235541,251284,277992,250162,237528,248920,237525,277660,396253,274388,400255,400251,244950,666500,400246,282202,231101,544323,249492,242897,237118,409067,246810,240879,237513,242663,238928,246878,242423,396641,396644,396643,396642,242376,396632,240308,243035,395718,238884,277982,290495,395715,395717,238889,239375,395716,235600,277219,240917,237050,237049,237521,234451,235604,240507,240505,235605,235606,235607,228497,235608,234449,229159,235609,232644,240503,235610,235611,235612,235613,240500,229256,235614,228504,231404,237487,284384,237486,228714,284385,231075,228715,235616,235617,239332,235618,232508,235619,284386,235620,235621,234507,235622,231395,240501,240508,240499,240502,284387,231398,
                                        231397,235623,237187,231399,240498,231761,240504,231838,235624,235625,231400,284389,235626,239353,284388,235627,284391,235628,235629)
 AND
                        (
                                (
                                        1                              = 1
                                        AND fac_operations.channel_fk IN(1,4)
                                )
                                OR fac_operations.row_type_fk = 3
                        )
;


--set autot off


rollback;


alter session set events '10046 trace name context off';





-- run without filter the channel_fk which not in the PK index see trace dwh_ora_11319_zvika_check_iot_overflow12.trc



-- run as sysdba
 alter system flush shared_pool ; 
 alter system flush buffer_cache ; 

set echo on timing on


--set autot traceonly


alter session set tracefile_identifier = 'zvika_check_iot_overflow12';


alter session set events = '10046 trace name context forever, level 12';

insert into zvikag.z1_t
select 1
                FROM    zvikag.Z1_FAC_OPERATIONS_OF   fac_operations 
  WHERE  fac_operations.account_fk = 382 and  fac_operations.row_type_fk IN (1,3)
                        AND
                        (
                         fac_operations.from_date_trunc BETWEEN CAST(to_timestamp('17/10/2011 00:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP   WITH LOCAL TIME ZONE) 
                AND CAST(to_timestamp('26/10/2011 23:59:59','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP        WITH LOCAL TIME ZONE))
AND fac_operations.agent_fk IN(395729,235639,243679,290076,396272,240522,232646,396279,240717,232675,240681,235505,673653,396278,236890,396274,396275,239291,290088,232685,396271,396273,242625,396270,396268,243827,249498,396282,396269,232814,229163,396283,235541,251284,277992,250162,237528,248920,237525,277660,396253,274388,400255,400251,244950,666500,400246,282202,231101,544323,249492,242897,237118,409067,246810,240879,237513,242663,238928,246878,242423,396641,396644,396643,396642,242376,396632,240308,243035,395718,238884,277982,290495,395715,395717,238889,239375,395716,235600,277219,240917,237050,237049,237521,234451,235604,240507,240505,235605,235606,235607,228497,235608,234449,229159,235609,232644,240503,235610,235611,235612,235613,240500,229256,235614,228504,231404,237487,284384,237486,228714,284385,231075,228715,235616,235617,239332,235618,232508,235619,284386,235620,235621,234507,235622,231395,240501,240508,240499,240502,284387,231398,
                                        231397,235623,237187,231399,240498,231761,240504,231838,235624,235625,231400,284389,235626,239353,284388,235627,284391,235628,235629)
 AND
                        (
                                (
                                        1                              = 1
--                                        AND fac_operations.channel_fk IN(1,4)
                                )
                                OR fac_operations.row_type_fk = 3
                        )
;


--set autot off


rollback;


alter session set events '10046 trace name context off';
if you need i can upload the 10046 trace file to somewhere .


Thanks in advanced .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points