Home » RDBMS Server » Performance Tuning » Query is getting stuck after adding one filter condition is where clause (9I, Windows)
Query is getting stuck after adding one filter condition is where clause [message #397344] |
Fri, 10 April 2009 03:10 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Sep 10 14:54:23 CDT 2024
|