Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help me read my 10053 trace file

Re: Help me read my 10053 trace file

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 24 Nov 2002 10:03:12 -0000
Message-ID: <arq9gn$qc5$1$8300dec7@news.demon.co.uk>

Thanks for the reply. The comment about the spatial indexes was particularly helpful.

I've just realised that when I read your first post, I managed to read the DK
(distinct keys) entries as the DB/K
(data blocks per key) which is why I
couldn't figure out what was wrong
with the index which you've now identified as the PK.

I note from your other post that the condition on the bitmap index seems to be:

            l.status_desc = 'ACTIVE' AND
            l.ramp = 'Y' AND

rather that my original suggestion 'first column of two-column index'. Perfect hindsight now tells me that this was clearly likely to have been the case - given that the number of distinct keys (DK) is recorded as 2, and the index cost is telling you that you are going to visit half the index.

Apologies for delayed reply - I've been in the US and out of touch of the newsgroup

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





charlie cs wrote in message ...

>Thanks for your help,Jonathan.
>
>The table is like this, and two indexes are spatial indexes, on
>user_indexes, it is listed as LOB indexes.
>SQL> desc navtech.life
> Name Null? Type
> ----------------------------------------- -------- -----------------
-------
>----
> DASET_ID NOT NULL NUMBER(10)
> SECT_ID NOT NULL NUMBER(10)
> LIFE_ID NOT NULL NUMBER(10)
> VERSION_ID NOT NULL VARCHAR2(10)
> METRO_ID NOT NULL NUMBER
> RAMP NOT NULL VARCHAR2(1)
> GEOLOC NOT NULL
MDSYS.SDO_GEOMETRY
> SPEED NUMBER
> STATUS_DESC NOT NULL VARCHAR2(25)
> EDGE_ID NUMBER(10)
>
>SQL> select index_name,index_type from dba_indexes where
owner='NAVTECH'
> 2 and table_name='LIFE';
>
>INDEX_NAME INDEX_TYPE
>------------------------------ ---------------------------
>LIFE_RAMP_STATUS_DESC BITMAP
>PK_LIFE NORMAL
>SYS_IL0003905260C00013$$ LOB
>SYS_IL0003905260C00014$$ LOB
>
>and index 3905350 is built like this:
>
>CREATE UNIQUE INDEX PK_LIFE ON LIFE (DASET_ID , SECT_ID , LIFE_ID ,
>VERSION_ID ,METRO_ID ) TABLESPACE INDX LOGGING LOCAL;
>
>ALTER TABLE LIFE ADD CONSTRAINT PK_LIFE PRIMARY KEY (DASET_ID,
SECT_ID,
>LIFE_ID, VERSION_ID, METRO_ID) USING INDEX.....
>
>The query was like this
>select ....
>from life l, life_direction eld
>where
>......
> l.daset_id = eld.daset_id (+) AND
> l.sect_id = eld.sect_id (+) AND
> l.life_id = eld.life_id (+) AND
> l.version_id = eld.version_id (+)
>and .....
>
>Hope to hear from you soon.
>
Received on Sun Nov 24 2002 - 04:03:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US