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...

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

Can you post the plan for the Query, Folks here could be of more help