locator index size

Just dealing a situation with 10.2.0.4 locator index. Size of a index had grown to 1.3G. After rebuilding it had size less than 50M.
Maybe there is going on something similar than Richard Foote is talking about bitmap indexes behaving in 9.2 and 10.2.
Trying a similar test with a locator index.

CREATE TABLE foo AS 
 SELECT mod(ROWNUM,1000)+1 ID
     , SYSDATE+mod(ROWNUM,1000)+1 fro
     , SYSDATE+mod(ROWNUM,1000)+2 til
     , 'FOO' NAME 
  FROM (SELECT NULL A FROM
(SELECT NULL A FROM dual CONNECT BY LEVEL <= 1000),
(SELECT NULL A FROM dual CONNECT BY LEVEL <= 1000));

CREATE OR REPLACE FUNCTION TF(FRO DATE, TIL DATE) RETURN SDO_GEOMETRY deterministic as 
BEGIN
RETURN MDSYS.SDO_GEOMETRY(2002,NULL, NULL, SDO_ELEM_INFO_ARRAY (1,2,1),
    SDO_ORDINATE_ARRAY(to_number(to_char(FRO,'j')),0,to_number(to_char(TIL,'j')),0));
END;
/

select min(to_number(to_char(FRO,'j'))),max(to_number(to_char(TIL,'j'))) from foo;

2455373 2456373

INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
  VALUES (
  'FOO',
  'RAFU.TF(FRO,TIL)',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 2455373, 2456373, 0.5),
    SDO_DIM_ELEMENT('Y', 0, 0, 0.5)
     )
  )
;

commit;

CREATE INDEX FOO_LOCATOR_IDX ON FOO(TF(FRO,TIL))
  INDEXTYPE IS mdsys.spatial_index
;

analyze index FOO_LOCATOR_IDX compute statistics;

SELECT index_name, blevel, leaf_blocks, num_rows FROM user_indexes WHERE index_name = 'FOO_LOCATOR_IDX';

"INDEX_NAME" "BLEVEL" "LEAF_BLOCKS" "NUM_ROWS"
"FOO_LOCATOR_IDX" "" "" ""

Nothing there. It is a domain index and storing its structures in a table.

select segment_name,bytes,blocks from user_segments where segment_name like 'MDRT_%';

MDRT_11976$ 92274688 11264

SELECT count(*) FROM MDRT_11976$;

33342

And now populating in a loop as Richard did with bitmap index.

CREATE TABLE bar AS 
 SELECT * FROM foo 
  WHERE 0=1
;

INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
  VALUES (
  'BAR',
  'RAFU.TF(FRO,TIL)',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 2455373, 2456373, 0.5),
    SDO_DIM_ELEMENT('Y', 0, 0, 0.5)
     )
  )
;

commit;

CREATE INDEX BAR_LOCATOR_IDX ON BAR(TF(FRO,TIL))
  INDEXTYPE IS mdsys.spatial_index
;

begin
 FOR i IN 1..1000 loop
  FOR j IN 1..1000 loop
   INSERT INTO bar VALUES (j, SYSDATE+j, SYSDATE+j, 'FOO');
   COMMIT;
  END loop;
 end loop;
end;
/



select segment_name,bytes,blocks from user_segments where segment_name like 'MDRT_%';

MDRT_11976$  92274688      11264
MDRT_119DF$ 125829120      15360


SELECT count(*) FROM MDRT_11976$;

33342

select count(*) from MDRT_119DF$;

58084

Well that does not explain the 1.3G size. Similar result with 11.2.0.1, 11.1.0.7 and 10.2.0.4.

Visual tuning

It was a nice opportunity to attend beta testing a tool that does something that I do with pen and paper quite often when trying to figure out query relations.

Could not attend a webinar last Thursday. Just waiting for the opportunity to see the replay.