Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    Pasay City, Philippines
    Posts
    8

    Unanswered: speed up the process

    Just want to ask this, how can I adjust the speed in retrieving data from oracle database . I only have 132,323 records in the database but it seems it is very slow in retrieving. I have also index keys but still the same. Our server is Prentium III 500 MHZ, does it affects the retrieving ? Oracle Version is 7.3


    SELECT "IN_CONTAINER"."CNTR_NUM",
    "IN_CONTAINER"."BL_NUMBER",
    "IN_CONTAINER"."PR_NUMBER",
    "IN_CONTAINER"."REGISTRY",
    "IN_CONTAINER"."CSIZE",
    "IN_CONTAINER"."CTYP",
    "IN_GENINFO"."VSL_ATA",
    "IN_GENINFO"."ETA_NORTH",
    "IN_CONTAINER"."CONTAINER_DEPOSIT",
    "IN_CONTAINER"."DEPOSIT_STATUS",
    "IN_CONTAINER"."DEPOSIT_PLACE",
    "IN_CONTAINER"."PR_NUMBER",
    "IN_CONTAINER"."IS_DEPOSIT",
    "IN_CONTAINER"."CD_EXCHANGE_RATE",
    "IN_CONTAINER"."CD_CURRENCY",
    "IN_CONTAINER"."CD_CASH_AMT",
    "IN_CONTAINER"."CD_CHEQUE_AMOUNT",
    "IN_CONTAINER"."CD_BANK_INFO",
    "IN_CONTAINER"."CD_CHEQUE_NO",
    "IN_CONTAINER"."CD_CURRENCY_AMOUNT",
    "IN_CONTAINER"."CD_PAYMENT_STATUS",
    "IN_CONTAINER"."CD_CURRENCY_PAYMENT",
    "IN_CONTAINER"."CD_SAFE_KEEPING",
    "IN_CONTAINER"."CD_IN_USER",
    "IN_CONTAINER"."CD_DATA_PROCESS"
    FROM "IN_CONTAINER",
    "IN_GENINFO"
    WHERE (( "IN_CONTAINER"."REGISTRY" = "IN_GENINFO"."REFNUM" ) OR ( "IN_CONTAINER"."REGISTRY" = "IN_GENINFO"."REGISTRY_NORTH" )) and
    ( ( SUBSTR("IN_CONTAINER"."REGISTRY",12,4) = 'P02A' ) OR
    ( SUBSTR("IN_CONTAINER"."REGISTRY",12,4) = 'P02B' ) OR
    ( SUBSTR("IN_CONTAINER"."REGISTRY",12,4) = 'P03' ))


    Anybody, can help me with this one ?????? Thanks...

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You need to reduce the OR clauses as they are inefficient.

    1) Try this ( SUBSTR("IN_CONTAINER"."REGISTRY",12,4) IN ( 'P02A' ,'P02B','P03' ))

    2) Try replacing the remaining OR clause using a union so
    select ... from ...."IN_CONTAINER"."REGISTRY" = "IN_GENINFO"."REFNUM" ...
    union
    select ... from ...."IN_CONTAINER"."REGISTRY" = "IN_GENINFO"."REGISTRY_NORTH" ...

    3) check it is being driven off the right table which looks like IN_CONTAINER looking at the criteria. If it is doing it the wrong way try the ORDERED hint.

    Alan

  3. #3
    Join Date
    Oct 2003
    Posts
    26
    Can you post the plan for the Query, Folks here could be of more help

Posting Permissions

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