OraFAQ Forum: Performance Tuning » Query is getting stuck after adding one filter condition is where clause

Home » RDBMS Server » Performance Tuning » Query is getting stuck after adding one filter condition is where clause (9I, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query is getting stuck after adding one filter condition is where clause [message #397344]

Fri, 10 April 2009 03:10

gurinder_mann2004
Messages: 4
Registered: April 2009

Junior Member

Dear All,
I am facing strange problem when running a below query with comment as RED COLOUR its coming fine

Below is the number of records in table

bed_history -- 399063
Visit -- 6153120
patient_service_event -- 7715396
patient_service -- 2658574
patient_service_order -- 2658284
item -- 1732
item_group_item -- 1725

Indexes (Bed_History)
CREATE UNIQUE INDEX BTHS_BED_HISTORY_PK ON BED_HISTORY
(BTHS_MOVEMENT_NUMBER)

CREATE INDEX BTHS_EXTERNAL_MOVEMENT_N ON BED_HISTORY
(BTHS_EXTERNAL_MOVEMENT_NUMBER)

CREATE INDEX BTHS_OUT_DATE_N ON BED_HISTORY
(NVL("BTHS_OUT_DATE_TIME",TO_DATE('2100-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')), BTHS_BUSINESS_CENTER, BTHS_WARD_ROOM)

CREATE INDEX BTHS_PERSON_KEY ON BED_HISTORY
(BTHS_PERSON_ID)

CREATE INDEX BTHS_SWAP_PERSON_N ON BED_HISTORY
(BTHS_SWAP_PERSON_ID)

CREATE INDEX BTHS_VISIT_N ON BED_HISTORY
(BTHS_VISIT_NO, BTHS_IN_DATE_TIME)

CREATE INDEX BTHS_WARD_BED_N ON BED_HISTORY
(BTHS_BUSINESS_CENTER, BTHS_WARD_ROOM, BTHS_BED, BTHS_IN_DATE_TIME)

CREATE INDEX GURINDER_BED_HISTORY ON BED_HISTORY
(BTHS_BUSINESS_CENTER, BTHS_IN_DATE_TIME, BTHS_OUT_DATE_TIME)

ALTER TABLE BED_HISTORY ADD (
CONSTRAINT BTHS_BED_HISTORY_PK PRIMARY KEY (BTHS_MOVEMENT_NUMBER))

VISIT

CREATE INDEX VIS_ALTERNATE_VISIT_N ON VISIT
(VIS_ALTERNATE_VISIT_NUMBER)

CREATE INDEX VIS_BUSINESS_CENTER_N ON VISIT
(VIS_BUSINESS_CENTER, VIS_VISIT_DATE)

CREATE INDEX VIS_DATE_N ON VISIT
(VIS_VISIT_DATE, VIS_VISIT_TIME)

CREATE INDEX VIS_EPISODE_N ON VISIT
(VIS_PRINCIPAL_EPISODE_NO)

CREATE INDEX VIS_HCP_N ON VISIT
(VIS_PRINCIPAL_HCP)

CREATE UNIQUE INDEX VIS_PATIENT_VISIT_NO_PK ON VISIT
(VIS_VISIT_NO)

CREATE INDEX VIS_PERSON_N ON VISIT
(VIS_PERSON_ID)

CREATE INDEX VIS_PRN_N ON VISIT
(VIS_PRN)

CREATE INDEX VIS_REFERRAL_HCO_N ON VISIT
(VIS_REFERRAL_MAIN_HCO, VIS_REFERRAL_HCO)

CREATE INDEX VIS_REFERRAL_HCPN ON VISIT
(VIS_REFERRAL_DOCTOR)

ALTER TABLE VISIT ADD (
CONSTRAINT VIS_PATIENT_VISIT_NO_PK PRIMARY KEY (VIS_VISIT_NO))

PATIENT_SERVICE_EVENT

CREATE INDEX PSRE_ENCOUNTER_N ON PATIENT_SERVICE_EVENT
(PSRE_ENCOUNTER_NO)

CREATE INDEX PSRE_EXTERNAL_FILLER_NO_N ON PATIENT_SERVICE_EVENT
(PSRE_EXTERNAL_FILLER_NO)

CREATE UNIQUE INDEX PSRE_PATIENT_SERVICE_EVENT ON PATIENT_SERVICE_EVENT
(PSRE_OBSERVATION_NO, PSRE_EVENT, PSRE_STEP)

CREATE INDEX PSRE_PERFORMED_HCO_N ON PATIENT_SERVICE_EVENT
(PSRE_PERFORM_BUSINESS_CENTER, PSRE_PERFORM_HCO)

CREATE INDEX PSRE_PERFORMED_HCP_N ON PATIENT_SERVICE_EVENT
(PSRE_PERFORM_HCP, PSRE_EVENT, PSRE_PERFORM_START)

CREATE INDEX PSRE_PERFORM_START_N ON PATIENT_SERVICE_EVENT
(PSRE_EVENT, PSRE_PERFORM_START)

CREATE INDEX PSRE_PERSON_N ON PATIENT_SERVICE_EVENT
(PSRE_PERSON_ID)

CREATE INDEX PSRE_PRNN ON PATIENT_SERVICE_EVENT
(PSRE_PRN)

CREATE INDEX PSRE_SCHEDULE_START_N ON PATIENT_SERVICE_EVENT
(PSRE_EVENT, PSRE_SCHEDULE_START, PSRE_SCHEDULE_HCP)

ALTER TABLE PATIENT_SERVICE_EVENT ADD (
CONSTRAINT PSRE_PATIENT_SERVICE_EVENT PRIMARY KEY (PSRE_OBSERVATION_NO, PSRE_EVENT, PSRE_STEP)

PATIENT_SERVICE

CREATE INDEX PTSR_DISP_DUE_DATE_N ON PATIENT_SERVICE
(PTSR_DISPENSE_DUE_DATE)

CREATE INDEX PTSR_EXTERNAL_ORDER_N ON PATIENT_SERVICE
(PTSR_EXTERNAL_ORDER_NO)

CREATE INDEX PTSR_LAST_EVENT_N ON PATIENT_SERVICE
(PTSR_LAST_EVENT, PTSR_ITEM_TYPE, PTSR_ORDER_STATUS)

CREATE INDEX PTSR_LINK_OBSERVATION_NO_N ON PATIENT_SERVICE
(PTSR_LINK_OBSERVATION_NO)

CREATE INDEX PTSR_ORDER_N ON PATIENT_SERVICE
(PTSR_ORDER_NO)

CREATE UNIQUE INDEX PTSR_ORG_OBSERVATION_U ON PATIENT_SERVICE
(PTSR_ORIGINAL_OBSERVATION_NO)

CREATE UNIQUE INDEX PTSR_PATIENT_SERVICE_PK ON PATIENT_SERVICE
(PTSR_OBSERVATION_NO)

CREATE INDEX PTSR_PERSON_N ON PATIENT_SERVICE
(PTSR_PERSON_ID)

CREATE INDEX PTSR_PRN_N ON PATIENT_SERVICE
(PTSR_PRN)

CREATE INDEX PTSR_SERVICE_N ON PATIENT_SERVICE
(PTSR_SERVICE)

ALTER TABLE PATIENT_SERVICE ADD (
CONSTRAINT PTSR_PATIENT_SERVICE_PK PRIMARY KEY (PTSR_OBSERVATION_NO)

PATIENT_SERVICE_ORDER

CREATE INDEX TROD_CARE_PLAN_N ON PATIENT_SERVICE_ORDER
(TROD_CARE_PLAN_ID)

CREATE UNIQUE INDEX TROD_PATIENT_SERVICE_ORDER ON PATIENT_SERVICE_ORDER
(TROD_OBSERVATION_NO, TROD_STEP)

CREATE INDEX TROD_PERSON_N ON PATIENT_SERVICE_ORDER
(TROD_PERSON_ID, TROD_GROUP_NUMBER)

CREATE INDEX TROD_PRNN ON PATIENT_SERVICE_ORDER
(TROD_PRN)

CREATE INDEX TROD_SERVICE_N ON PATIENT_SERVICE_ORDER
(TROD_SERVICE)

CREATE INDEX TROD_VALID_FROM_TO_N ON PATIENT_SERVICE_ORDER
(TROD_VALID_FROM, TROD_VALID_TO)

CREATE INDEX TROD_WORKSHEET_N ON PATIENT_SERVICE_ORDER
(TROD_WORKSHEET_ID)

ALTER TABLE PATIENT_SERVICE_ORDER ADD (
CONSTRAINT TROD_PATIENT_SERVICE_ORDER PRIMARY KEY (TROD_OBSERVATION_NO, TROD_STEP)

SELECT /*+INDEX(B PSRE_PERSON_N) INDEX(VISIT VIS_PATIENT_VISIT_NO_PK) INDEX(A PTSR_PATIENT_SERVICE_PK) INDEX(C
TROD_PATIENT_SERVICE_ORDER)*/
a.ptsr_prn, a.ptsr_order_no, a.ptsr_order_status,
a.ptsr_dispense_due_date, c.trod_duration DURATION,
c.trod_duration_unit duration_unit, a.ptsr_order_status,
a.ptsr_observation_no, c.trod_route_of_admin route_of_admin,
a.ptsr_visit_discharge_status, c.trod_valid_from valid_from,
c.trod_valid_to valid_to, c.trod_service service,
c.trod_service_type service_type, c.trod_as_required trod_as_required,
c.trod_once_now trod_once_now,
c.trod_instruction_template instruction_template,
c.trod_instructions instructions, c.trod_repeat_quantity quantity,
c.trod_uom uom, c.trod_store bin, c.trod_repeats repeats,
b.psre_perform_hcp, b.psre_perform_start, b.psre_observation_no,
b.psre_event, b.psre_step, vis_financial_class, vis_act_status,
vis_visit_discharge_status, vis_visit_no, vis_visit_date,
vis_principal_department, vis_principal_specialty, vis_visit_type,
bths_ward_room, bths_bed,
(SELECT DECODE (og_organiz_type,
'WR', og_parent_organization,
og_organization
)
FROM ORGANIZATION
WHERE og_main_organization = 'WH'
AND og_organization = bths_ward_room) AS patient_ward,
trod_remarks, trod_store_business_center, trod_group_number,
trod_time_critical, trod_drug_code, trod_drug_id, trod_alias,
ptsr_is_intervened, trod_virtual_order, NULL AS subst_no
FROM bed_history,
visit,
patient_service_event b,
patient_service a,
patient_service_order c,
item,
item_group_item
WHERE bths_business_center = 'WH'
AND bths_in_date_time <=
TO_DATE ('09/04/2009 12:00:00', 'dd/mm/yyyy hh24:mi:ss')
AND (NVL (bths_out_date_time, TO_DATE ('31-dec-5000', 'dd-mon-yyyy')) >
TO_DATE ('09/04/2009 12:00:00', 'dd/mm/yyyy hh24:mi:ss')
)
AND b.psre_person_id = bths_person_id
AND vis_visit_no = bths_visit_no
AND bths_inpatient_movement <> 'OL'
AND vis_business_center = 'WH'
AND vis_visit_date <=
TO_DATE ('09/04/2009 12:00:00', 'dd/mm/yyyy hh24:mi:ss')
AND (NVL (vis_discharge_date, TO_DATE ('31-dec-5000', 'dd-mon-yyyy')) >=
TO_DATE ('09/04/2009 12:00:00', 'dd/mm/yyyy hh24:mi:ss')
)
AND b.psre_event = 'ORD'
AND b.psre_observation_no = a.ptsr_observation_no
AND b.psre_observation_no = c.trod_observation_no
AND c.trod_step = 0
AND c.trod_service = item_item(+)
AND vis_visit_type = 'I'
AND a.ptsr_item_type = 'MED'
AND a.ptsr_act_status = 'C'
AND a.ptsr_visit_discharge_status = 'A'
AND c.trod_valid_from <=
TO_DATE ('09/04/2009 12:00:00', 'dd/mm/yyyy hh24:mi:ss')
/*AND (C.TROD_VALID_TO IS NULL OR C.TROD_VALID_TO >= To_date('06/04/2009 12:00:00','dd/mm/yyyy hh24:mi:ss'))*/
AND c.trod_store = 'WHPHM02'
AND c.trod_store_business_center = 'WH'
AND a.ptsr_act_status = 'C'
AND a.ptsr_visit_discharge_status = 'A'
AND b.psre_step = c.trod_step
AND item_item = igpi_item(+)
AND a.ptsr_person_id = c.trod_person_id
AND b.psre_person_id = c.trod_person_id
AND ptsr_prn = trod_prn
AND ptsr_prn = psre_prn
AND a.ptsr_last_event IN ('ORD', 'APR', 'DSP', 'ADM')
AND a.ptsr_suspended <> 'T'
AND ( a.ptsr_order_status IN ('P', 'R', 'O')
OR (a.ptsr_order_status = 'N' AND a.ptsr_last_event IN ('ADM'))
)
AND EXISTS (
SELECT 1
FROM encounter
WHERE enco_encounter_no = psre_encounter_no
AND enco_visit_no = vis_visit_no)
AND ( (NVL
((SELECT dros_dispense_required
FROM drug_order_status
WHERE ( dros_visit_type IS NULL
OR dros_visit_type = vis_visit_type
)
AND ( dros_visit_discharge_status IS NULL
OR dros_visit_discharge_status =
a.ptsr_visit_discharge_status
)
AND ( dros_specialty IS NULL
OR vis_principal_specialty IN (
SELECT spxl_specialty
FROM specialty
START WITH spxl_specialty = dros_specialty
CONNECT BY PRIOR spxl_specialty =
spxl_parent_specialty)
)
AND ( dros_drug IS NULL
OR ( item_drug_code IS NOT NULL
AND dros_drug = item_drug_code
)
OR ( trod_drug_code IS NOT NULL
AND dros_drug = trod_drug_code
)
)
AND ( dros_item_group IS NULL
OR ( igpi_item_group IS NOT NULL
AND dros_item_group = igpi_item_group
AND igpi_item_group_type = 'T'
)
)
AND dros_weightage =
(SELECT MAX (dros_weightage)
FROM drug_order_status
WHERE ( dros_visit_type IS NULL
OR dros_visit_type = vis_visit_type
)
AND ( dros_visit_discharge_status IS NULL
OR dros_visit_discharge_status =
ptsr_visit_discharge_status
)
AND ( dros_specialty IS NULL
OR vis_principal_specialty IN (
SELECT spxl_specialty
FROM specialty
START WITH spxl_specialty =
dros_specialty
CONNECT BY PRIOR spxl_specialty =
spxl_parent_specialty)
)
AND ( dros_drug IS NULL
OR ( item_drug_code IS NOT NULL
AND dros_drug = item_drug_code
)
OR ( trod_drug_code IS NOT NULL
AND dros_drug = trod_drug_code
)
)
AND ( dros_item_group IS NULL
OR ( igpi_item_group IS NOT NULL
AND dros_item_group = igpi_item_group
AND igpi_item_group_type = 'T'
)
))),
'F'
) = 'T'
)
OR NOT EXISTS (
SELECT 1
FROM item_store
WHERE itst_business_center = 'WH'
AND (itst_store =
(SELECT ogcv_organization
FROM organiz_coverage
WHERE ogcv_coverage_type = 'ST'
AND ogcv_main_organization_covered = 'WH'
AND ogcv_organization_covered =
(SELECT DECODE (og_organiz_type,
'WR', og_parent_organization,
og_organization
)
FROM ORGANIZATION
WHERE og_main_organization = 'WH'
AND og_organization = bths_ward_room))
)
AND (itst_effective_from <=
TO_DATE ('09/04/2009 12:00:00',
'dd/mm/yyyy hh24:mi:ss'
)
)
AND ( (itst_effective_to IS NULL)
OR (itst_effective_to >=
TO_DATE ('09/04/2009 12:00:00',
'dd/mm/yyyy hh24:mi:ss'
)
)
)
AND ( (trod_service_type = 'I' AND itst_item = trod_service
)
OR ( trod_service_type = 'D'
AND itst_item =
(SELECT item_item
FROM item
WHERE item_drug_code = trod_drug_code
AND (SELECT COUNT (*)
FROM item a
WHERE a.item_drug_code =
trod_drug_code) =
1)
)
OR ( trod_service_type = 'G'
AND itst_item =
(SELECT item_item
FROM item a, drug b
WHERE a.item_drug_code = b.trk_drug_code
AND a.item_drug_code = trod_drug_code
AND (SELECT COUNT (*)
FROM item c
WHERE c.item_drug_code =
b.trk_drug_code) =
1)
)
))
)

Below is the Execution plan for the query

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 841735
TABLE ACCESS BY INDEX ROWID MAXCARE.ORGANIZATION 1 19 2
INDEX UNIQUE SCAN MAXCARE.OG_ORGANIZATION_PK 1 K 1
FILTER
NESTED LOOPS OUTER 1 448 841735
NESTED LOOPS SEMI 1 424 841733
NESTED LOOPS 1 398 841731
NESTED LOOPS OUTER 1 360 841729
NESTED LOOPS 1 343 841728
NESTED LOOPS 1 201 841726
NESTED LOOPS 4 K 510 K 833616
TABLE ACCESS BY INDEX ROWID MAXCARE.BED_HISTORY 4 K 251 K 24
INDEX RANGE SCAN MAXCARE.GURINDER_BED_HISTORY 6 K 10
TABLE ACCESS BY INDEX ROWID MAXCARE.PATIENT_SERVICE_EVENT 1 71 188
INDEX RANGE SCAN MAXCARE.PSRE_PERSON_N 44 2
TABLE ACCESS BY INDEX ROWID MAXCARE.PATIENT_SERVICE 1 72 2
INDEX UNIQUE SCAN MAXCARE.PTSR_PATIENT_SERVICE_PK 183 1
TABLE ACCESS BY INDEX ROWID MAXCARE.PATIENT_SERVICE_ORDER 1 142 2
INDEX UNIQUE SCAN MAXCARE.TROD_PATIENT_SERVICE_ORDER 1 K 1
TABLE ACCESS BY INDEX ROWID MAXCARE.ITEM 1 17 1
INDEX UNIQUE SCAN MAXCARE.ITEM_ITEM_PK 1
TABLE ACCESS BY INDEX ROWID MAXCARE.VISIT 1 38 2
INDEX UNIQUE SCAN MAXCARE.VIS_PATIENT_VISIT_NO_PK 480 1
TABLE ACCESS BY INDEX ROWID MAXCARE.ENCOUNTER 13 M 335 M 2
INDEX UNIQUE SCAN MAXCARE.ENCO_ENCOUNTER_PK 1 1
TABLE ACCESS BY INDEX ROWID MAXCARE.ITEM_GROUP_ITEM 1 24 2
INDEX RANGE SCAN MAXCARE.IGPI_ITEM_N 1 1
FILTER
TABLE ACCESS FULL MAXCARE.DRUG_ORDER_STATUS 1 28 7
FILTER
CONNECT BY WITH FILTERING
NESTED LOOPS
INDEX UNIQUE SCAN MAXCARE.SPXL_SPECIALTY_PK 1 5
TABLE ACCESS BY USER ROWID MAXCARE.SPECIALTY
HASH JOIN
CONNECT BY PUMP
TABLE ACCESS FULL MAXCARE.SPECIALTY 145 1 K 7
SORT AGGREGATE 1 26
FILTER
TABLE ACCESS FULL MAXCARE.DRUG_ORDER_STATUS 1 26 7
FILTER
CONNECT BY WITH FILTERING
NESTED LOOPS
INDEX UNIQUE SCAN MAXCARE.SPXL_SPECIALTY_PK 1 5
TABLE ACCESS BY USER ROWID MAXCARE.SPECIALTY
HASH JOIN
CONNECT BY PUMP
TABLE ACCESS FULL MAXCARE.SPECIALTY 145 1 K 7
FILTER
TABLE ACCESS BY INDEX ROWID MAXCARE.ITEM_STORE 1 33 3
INDEX RANGE SCAN MAXCARE.ITST_LOCSTORE_N 11 1
TABLE ACCESS BY INDEX ROWID MAXCARE.ORGANIZ_COVERAGE 1 23 1
INDEX UNIQUE SCAN MAXCARE.OGCV_COVERAGE_PK 575
TABLE ACCESS BY INDEX ROWID MAXCARE.ORGANIZATION 1 19 2
INDEX UNIQUE SCAN MAXCARE.OG_ORGANIZATION_PK 1 K 1
FILTER
TABLE ACCESS BY INDEX ROWID MAXCARE.ITEM 1 17 2
INDEX RANGE SCAN MAXCARE.ITEM_DRUG_CODE_N 1 1
SORT AGGREGATE 1 6
INDEX RANGE SCAN MAXCARE.ITEM_DRUG_CODE_N 1 6 1
NESTED LOOPS 1 23 3
INDEX UNIQUE SCAN MAXCARE.TRK_DRUG_P 1 6 1
SORT AGGREGATE 1 6
INDEX RANGE SCAN MAXCARE.ITEM_DRUG_CODE_N 1 6 1
TABLE ACCESS BY INDEX ROWID MAXCARE.ITEM 1 17 2
INDEX RANGE SCAN MAXCARE.ITEM_DRUG_CODE_N 1 1

Below is the query with uncommenting where condition

SELECT /*+INDEX(B PSRE_PERSON_N) INDEX(VISIT VIS_PATIENT_VISIT_NO_PK) INDEX(A PTSR_PATIENT_SERVICE_PK) INDEX(C
TROD_PATIENT_SERVICE_ORDER)*/
a.ptsr_prn, a.ptsr_order_no, a.ptsr_order_status,
a.ptsr_dispense_due_date, c.trod_duration DURATION,
c.trod_duration_unit duration_unit, a.ptsr_order_status,
a.ptsr_observation_no, c.trod_route_of_admin route_of_admin,
a.ptsr_visit_discharge_status, c.trod_valid_from valid_from,
c.trod_valid_to valid_to, c.trod_service service,
c.trod_service_type service_type, c.trod_as_required trod_as_required,
c.trod_once_now trod_once_now,
c.trod_instruction_template instruction_template,
c.trod_instructions instructions, c.trod_repeat_quantity quantity,
c.trod_uom uom, c.trod_store bin, c.trod_repeats repeats,
b.psre_perform_hcp, b.psre_perform_start, b.psre_observation_no,
b.psre_event, b.psre_step, vis_financial_class, vis_act_status,
vis_visit_discharge_status, vis_visit_no, vis_visit_date,
vis_principal_department, vis_principal_specialty, vis_visit_type,
bths_ward_room, bths_bed,
(SELECT DECODE (og_organiz_type,
'WR', og_parent_organization,
og_organization
)
FROM ORGANIZATION
WHERE og_main_organization = 'WH'
AND og_organization = bths_ward_room) AS patient_ward,
trod_remarks, trod_store_business_center, trod_group_number,
trod_time_critical, trod_drug_code, trod_drug_id, trod_alias,
ptsr_is_intervened, trod_virtual_order, NULL AS subst_no
FROM bed_history,
visit,
patient_service_event b,
patient_service a,
patient_service_order c,
item,
item_group_item
WHERE bths_business_center = 'WH'
AND bths_in_date_time <=
TO_DATE ('09/04/2009 12:00:00', 'dd/mm/yyyy hh24:mi:ss')
AND (NVL (bths_out_date_time, TO_DATE ('31-dec-5000', 'dd-mon-yyyy')) >
TO_DATE ('09/04/2009 12:00:00', 'dd/mm/yyyy hh24:mi:ss')
)
AND b.psre_person_id = bths_person_id
AND vis_visit_no = bths_visit_no
AND bths_inpatient_movement <> 'OL'
AND vis_business_center = 'WH'
AND vis_visit_date <=
TO_DATE ('09/04/2009 12:00:00', 'dd/mm/yyyy hh24:mi:ss')
AND (NVL (vis_discharge_date, TO_DATE ('31-dec-5000', 'dd-mon-yyyy')) >=
TO_DATE ('09/04/2009 12:00:00', 'dd/mm/yyyy hh24:mi:ss')
)
AND b.psre_event = 'ORD'
AND b.psre_observation_no = a.ptsr_observation_no
AND b.psre_observation_no = c.trod_observation_no
AND c.trod_step = 0
AND c.trod_service = item_item(+)
AND vis_visit_type = 'I'
AND a.ptsr_item_type = 'MED'
AND a.ptsr_act_status = 'C'
AND a.ptsr_visit_discharge_status = 'A'
AND c.trod_valid_from <=
TO_DATE ('09/04/2009 12:00:00', 'dd/mm/yyyy hh24:mi:ss')
AND (C.TROD_VALID_TO IS NULL OR C.TROD_VALID_TO >= To_date('06/04/2009 12:00:00','dd/mm/yyyy hh24:mi:ss'))
AND c.trod_store = 'WHPHM02'
AND c.trod_store_business_center = 'WH'
AND a.ptsr_act_status = 'C'
AND a.ptsr_visit_discharge_status = 'A'
AND b.psre_step = c.trod_step
AND item_item = igpi_item(+)
AND a.ptsr_person_id = c.trod_person_id
AND b.psre_person_id = c.trod_person_id
AND ptsr_prn = trod_prn
AND ptsr_prn = psre_prn
AND a.ptsr_last_event IN ('ORD', 'APR', 'DSP', 'ADM')
AND a.ptsr_suspended <> 'T'
AND ( a.ptsr_order_status IN ('P', 'R', 'O')
OR (a.ptsr_order_status = 'N' AND a.ptsr_last_event IN ('ADM'))
)
AND EXISTS (
SELECT 1
FROM encounter
WHERE enco_encounter_no = psre_encounter_no
AND enco_visit_no = vis_visit_no)
AND ( (NVL
((SELECT dros_dispense_required
FROM drug_order_status
WHERE ( dros_visit_type IS NULL
OR dros_visit_type = vis_visit_type
)
AND ( dros_visit_discharge_status IS NULL
OR dros_visit_discharge_status =
a.ptsr_visit_discharge_status
)
AND ( dros_specialty IS NULL
OR vis_principal_specialty IN (
SELECT spxl_specialty
FROM specialty
START WITH spxl_specialty = dros_specialty
CONNECT BY PRIOR spxl_specialty =
spxl_parent_specialty)
)
AND ( dros_drug IS NULL
OR ( item_drug_code IS NOT NULL
AND dros_drug = item_drug_code
)
OR ( trod_drug_code IS NOT NULL
AND dros_drug = trod_drug_code
)
)
AND ( dros_item_group IS NULL
OR ( igpi_item_group IS NOT NULL
AND dros_item_group = igpi_item_group
AND igpi_item_group_type = 'T'
)
)
AND dros_weightage =
(SELECT MAX (dros_weightage)
FROM drug_order_status
WHERE ( dros_visit_type IS NULL
OR dros_visit_type = vis_visit_type
)
AND ( dros_visit_discharge_status IS NULL
OR dros_visit_discharge_status =
ptsr_visit_discharge_status
)
AND ( dros_specialty IS NULL
OR vis_principal_specialty IN (
SELECT spxl_specialty
FROM specialty
START WITH spxl_specialty =
dros_specialty
CONNECT BY PRIOR spxl_specialty =
spxl_parent_specialty)
)
AND ( dros_drug IS NULL
OR ( item_drug_code IS NOT NULL
AND dros_drug = item_drug_code
)
OR ( trod_drug_code IS NOT NULL
AND dros_drug = trod_drug_code
)
)
AND ( dros_item_group IS NULL
OR ( igpi_item_group IS NOT NULL
AND dros_item_group = igpi_item_group
AND igpi_item_group_type = 'T'
)
))),
'F'
) = 'T'
)
OR NOT EXISTS (
SELECT 1
FROM item_store
WHERE itst_business_center = 'WH'
AND (itst_store =
(SELECT ogcv_organization
FROM organiz_coverage
WHERE ogcv_coverage_type = 'ST'
AND ogcv_main_organization_covered = 'WH'
AND ogcv_organization_covered =
(SELECT DECODE (og_organiz_type,
'WR', og_parent_organization,
og_organization
)
FROM ORGANIZATION
WHERE og_main_organization = 'WH'
AND og_organization = bths_ward_room))
)
AND (itst_effective_from <=
TO_DATE ('09/04/2009 12:00:00',
'dd/mm/yyyy hh24:mi:ss'
)
)
AND ( (itst_effective_to IS NULL)
OR (itst_effective_to >=
TO_DATE ('09/04/2009 12:00:00',
'dd/mm/yyyy hh24:mi:ss'
)
)
)
AND ( (trod_service_type = 'I' AND itst_item = trod_service
)
OR ( trod_service_type = 'D'
AND itst_item =
(SELECT item_item
FROM item
WHERE item_drug_code = trod_drug_code
AND (SELECT COUNT (*)
FROM item a
WHERE a.item_drug_code =
trod_drug_code) =
1)
)
OR ( trod_service_type = 'G'
AND itst_item =
(SELECT item_item
FROM item a, drug b
WHERE a.item_drug_code = b.trk_drug_code
AND a.item_drug_code = trod_drug_code
AND (SELECT COUNT (*)
FROM item c
WHERE c.item_drug_code =
b.trk_drug_code) =
1)
)
))
)

and below is the execution plan but my query is getting hanged and not getting output.

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 841735
TABLE ACCESS BY INDEX ROWID MAXCARE.ORGANIZATION 1 19 2
INDEX UNIQUE SCAN MAXCARE.OG_ORGANIZATION_PK 1 K 1
FILTER
NESTED LOOPS OUTER 1 448 841735
NESTED LOOPS SEMI 1 424 841733
NESTED LOOPS 1 398 841731
NESTED LOOPS OUTER 1 360 841729
NESTED LOOPS 1 343 841728
NESTED LOOPS 1 201 841726
NESTED LOOPS 4 K 510 K 833616
TABLE ACCESS BY INDEX ROWID MAXCARE.BED_HISTORY 4 K 251 K 24
INDEX RANGE SCAN MAXCARE.GURINDER_BED_HISTORY 6 K 10
TABLE ACCESS BY INDEX ROWID MAXCARE.PATIENT_SERVICE_EVENT 1 71 188
INDEX RANGE SCAN MAXCARE.PSRE_PERSON_N 44 2
TABLE ACCESS BY INDEX ROWID MAXCARE.PATIENT_SERVICE 1 72 2
INDEX UNIQUE SCAN MAXCARE.PTSR_PATIENT_SERVICE_PK 183 1
TABLE ACCESS BY INDEX ROWID MAXCARE.PATIENT_SERVICE_ORDER 1 142 2
INDEX UNIQUE SCAN MAXCARE.TROD_PATIENT_SERVICE_ORDER 2 K 1
TABLE ACCESS BY INDEX ROWID MAXCARE.ITEM 1 17 1
INDEX UNIQUE SCAN MAXCARE.ITEM_ITEM_PK 1
TABLE ACCESS BY INDEX ROWID MAXCARE.VISIT 1 38 2
INDEX UNIQUE SCAN MAXCARE.VIS_PATIENT_VISIT_NO_PK 480 1
TABLE ACCESS BY INDEX ROWID MAXCARE.ENCOUNTER 13 M 335 M 2
INDEX UNIQUE SCAN MAXCARE.ENCO_ENCOUNTER_PK 1 1
TABLE ACCESS BY INDEX ROWID MAXCARE.ITEM_GROUP_ITEM 1 24 2
INDEX RANGE SCAN MAXCARE.IGPI_ITEM_N 1 1
FILTER
TABLE ACCESS FULL MAXCARE.DRUG_ORDER_STATUS 1 28 7
FILTER
CONNECT BY WITH FILTERING
NESTED LOOPS
INDEX UNIQUE SCAN MAXCARE.SPXL_SPECIALTY_PK 1 5
TABLE ACCESS BY USER ROWID MAXCARE.SPECIALTY
HASH JOIN
CONNECT BY PUMP
TABLE ACCESS FULL MAXCARE.SPECIALTY 145 1 K 7
SORT AGGREGATE 1 26
FILTER
TABLE ACCESS FULL MAXCARE.DRUG_ORDER_STATUS 1 26 7
FILTER
CONNECT BY WITH FILTERING
NESTED LOOPS
INDEX UNIQUE SCAN MAXCARE.SPXL_SPECIALTY_PK 1 5
TABLE ACCESS BY USER ROWID MAXCARE.SPECIALTY
HASH JOIN
CONNECT BY PUMP
TABLE ACCESS FULL MAXCARE.SPECIALTY 145 1 K 7
FILTER
TABLE ACCESS BY INDEX ROWID MAXCARE.ITEM_STORE 1 33 3
INDEX RANGE SCAN MAXCARE.ITST_LOCSTORE_N 11 1
TABLE ACCESS BY INDEX ROWID MAXCARE.ORGANIZ_COVERAGE 1 23 1
INDEX UNIQUE SCAN MAXCARE.OGCV_COVERAGE_PK 575
TABLE ACCESS BY INDEX ROWID MAXCARE.ORGANIZATION 1 19 2
INDEX UNIQUE SCAN MAXCARE.OG_ORGANIZATION_PK 1 K 1
FILTER
TABLE ACCESS BY INDEX ROWID MAXCARE.ITEM 1 17 2
INDEX RANGE SCAN MAXCARE.ITEM_DRUG_CODE_N 1 1
SORT AGGREGATE 1 6
INDEX RANGE SCAN MAXCARE.ITEM_DRUG_CODE_N 1 6 1
NESTED LOOPS 1 23 3
INDEX UNIQUE SCAN MAXCARE.TRK_DRUG_P 1 6 1
SORT AGGREGATE 1 6
INDEX RANGE SCAN MAXCARE.ITEM_DRUG_CODE_N 1 6 1
TABLE ACCESS BY INDEX ROWID MAXCARE.ITEM 1 17 2
INDEX RANGE SCAN MAXCARE.ITEM_DRUG_CODE_N 1 1

Please suggest what could be the reason

Report message to a moderator

Re: Query is getting stuck after adding one filter condition is where clause [message #397425 is a reply to message #397344]

Fri, 10 April 2009 11:04

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Quote:

I am facing strange problem when running a below query with comment as RED COLOUR its coming fine

Can you come again on this?

Try nvl instead of is null
/*AND (C.TROD_VALID_TO IS NULL OR C.TROD_VALID_TO >= To_date('06/04/2009 12:00:00','dd/mm/yyyy hh24:mi:ss'))*/
to
/*AND nvl(C.TROD_VALID_TO,To_date('06/04/2009 12:00:00','dd/mm/yyyy hh24:mi:ss')) >= To_date('06/04/2009 12:00:00','dd/mm/yyyy hh24:mi:ss')*/

As this is your first post, Please read the Forum Guide before posting.

By
Vamsi

Report message to a moderator

Re: Query is getting stuck after adding one filter condition is where clause [message #397583 is a reply to message #397425]

Sun, 12 April 2009 22:43

gurinder_mann2004
Messages: 4
Registered: April 2009

Junior Member

Hi Vamsi,
Thanks for the reply, i tried all options NVL and even with equality condition also, just after adding that particular column query get stuck.

Thanks,
Gurinder

Report message to a moderator

Re: Query is getting stuck after adding one filter condition is where clause [message #397601 is a reply to message #397583]

Mon, 13 April 2009 00:15

dasu1232001
Messages: 4
Registered: April 2009
Location: India

Junior Member

Please try with these clause:
---------------------------------
Approach1:[Logic of from and to changed]
--------------
AND nvl(C.TROD_VALID_TO,To_date('09/04/2009 12:00:00','dd/mm/yyyy hh24:mi:ss')) <= To_date('09/04/2009 12:00:00','dd/mm/yyyy hh24:mi:ss')

AND c.trod_valid_from >=
TO_DATE ('06/04/2009 12:00:00', 'dd/mm/yyyy hh24:mi:ss')
---------------------------------------------
If the above does not work:
Approach2:
----------
1.create a view with the commented query.
2.Then introduce the commented clause to select from the resultset of that view.
I think once we see the outcome;we will get a hint for hanging.

Report message to a moderator

Re: Query is getting stuck after adding one filter condition is where clause [message #397613 is a reply to message #397601]

Mon, 13 April 2009 00:51

dasu1232001
Messages: 4
Registered: April 2009
Location: India

Junior Member

Another query : what is MAXCARE in the plan?

I am not able to understand the plan:It is not showing any index names etc. although it is claiming index scan?

Report message to a moderator

Re: Query is getting stuck after adding one filter condition is where clause [message #397662 is a reply to message #397613]

Mon, 13 April 2009 03:41

gurinder_mann2004
Messages: 4
Registered: April 2009

Junior Member

MAXCARE is schema name....

Report message to a moderator

Re: Query is getting stuck after adding one filter condition is where clause [message #397792 is a reply to message #397662]

Mon, 13 April 2009 23:26

dasu1232001
Messages: 4
Registered: April 2009
Location: India

Junior Member

Thanks Gurinder.
One more query:
-------------------
The execution plan which you have enclosed,is it from explain plan or is it the actual execution plan?(i.e. from sql trace or from v$sql_plan). some part of your predicate seems to be missing there.
Please provide the other plan also.Stating which one is what for farther analysis.

Report message to a moderator

Re: Query is getting stuck after adding one filter condition is where clause [message #397799 is a reply to message #397792]

Mon, 13 April 2009 23:49

dasu1232001
Messages: 4
Registered: April 2009
Location: India

Junior Member

Gurinder another approach to make things simpler and analyze the problem:
Can you comment out the query from AND EXISTS (
SELECT 1
FROM encounter

till end. Then test it to see whether the query is still hanging.

Report message to a moderator

Re: Query is getting stuck after adding one filter condition is where clause [message #398631 is a reply to message #397799]

Fri, 17 April 2009 00:15

gurinder_mann2004
Messages: 4
Registered: April 2009

Junior Member

Hi Many thanks for response,

i tried all combinations even i tried on column TROD_VALID_TO(With Equality condition also) the moment i add TROD_VALID_TO column into the Query my qiery gets hanged.

Thanks,
Gurinder

Report message to a moderator

Previous Topic:	problem while generating AWR report
Next Topic:	SQL too slow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 06:58:01 CST 2024