2010-06-24

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.

2010-06-16

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.

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.