Results 1 to 3 of 3

Thread: Magic Number

  1. #1
    Join Date
    Dec 2014
    Posts
    2

    Unanswered: Magic Number

    Dear Members,

    retrieving spatial data we often use querys as:

    SELECT DISTINCT MSLINK,ENTITY
    FROM GRAPH_HITTEST
    WHERE LTB_STATUS<>1
    AND JOBID IN (0,:JOBID)
    AND GRAPHROW=0
    AND ENTITY =1201
    AND SDO_RELATE(SHAPE,MDSYS.SDO_GEOMETRY(2003,NULL,NULL ,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
    MDSYS.SDO_ORDINATE_ARRAY(3484806.055,5371604.294,3 484402.076,5371511.894,3484388.884,5371570.426,348 4750.365,5371653.106,3484806.055,5371604.294)),'MA SK=ANYINTERACT QUERYTYPE=WINDOW')='TRUE';

    Above query lasts ca. 260 seconds. if we change the line 'AND ENTITY =1201 ' with 'AND ENTITY IN(0,1201)' it lasts only 0,02 seconds.An ENTITY with 0 doesn't exists. We have no explanation for this and call it therefor 'Magic Number'.

    Perhaps anyone here has an idea?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What happens if you
    - exit SQL*Plus (or the tool you are using now)
    - start it again
    - run the query which contains "AND ENTITY IN (0, 1201)" first (before running "AND ENTITY = 1201")
    How much time does it take?

    What I'm trying to say: if Oracle cached previous results, it didn't have much to do when retrieving the "super fast" result as it was already "prepared".

    Did you try to explain both statements? How does the explain plan look like?

  3. #3
    Join Date
    Dec 2014
    Posts
    2
    Hi Littlefoot.

    thanks for your comments and questions. If we query first with "AND ENTITY IN (0, 1201)" it takes 2.5 seconds so we thought caching is not the problem.

    Explain plan for ENTITY IN (0, 1201):
    OPERATION OBJECT_NAME CARDINALITY COST

    SELECT STATEMENT
    1 1

    HASH
    1 1

    TABLE ACCESS
    GRAPH_HITTEST 1 0

    Filter Predicates

    AND

    GRAPHROW=0

    LTB_STATUS<>1

    OR

    ENTITY=0

    ENTITY=1201

    OR

    JOBID=0

    JOBID=TO_NUMBER(:JOBID)

    DOMAIN INDEX
    GRAPH_HITTEST_IDX 0

    Access Predicates

    and for ENTITY = 1201:
    OPERATION OBJECT_NAME CARDINALITY COST

    SELECT STATEMENT
    1 1

    HASH
    1 1

    TABLE ACCESS
    GRAPH_HITTEST 1 0

    Filter Predicates

    AND

    GRAPHROW=0

    ENTITY=1201

    LTB_STATUS<>1

    OR

    JOBID=0

    JOBID=TO_NUMBER(:JOBID)

    DOMAIN INDEX
    GRAPH_HITTEST_IDX 0

    Access Predicates

    We check and rebuild the indizes but with no effect.



    Quote Originally Posted by Littlefoot View Post
    What happens if you
    - exit SQL*Plus (or the tool you are using now)
    - start it again
    - run the query which contains "AND ENTITY IN (0, 1201)" first (before running "AND ENTITY = 1201")
    How much time does it take?

    What I'm trying to say: if Oracle cached previous results, it didn't have much to do when retrieving the "super fast" result as it was already "prepared".

    Did you try to explain both statements? How does the explain plan look like?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •