Querying a view is taking too long.
931268Apr 17 2012 — edited Apr 17 2012Hi,
When I query the table below using the condition "where DATE_ASSIGNED > '28-AUG-11'" it is working.
But when I query the table using the condition where "DATE_ASSIGNED > '29-AUG-11'" it's taking a very long time.
I created a normal index on the column last_changed_date of the base table TLKG_KEY_REQUEST but still doesn't work.
I will appreciate any help.
12:22:57 SQL> select count(DATE_ASSIGNED) from ASLMDB.VLKG_ORDER_QTY_ASSIGNED
where DATE_ASSIGNED > '28-AUG-11'
/12:23:13 2 12:23:13 3
COUNT(DATE_ASSIGNED)
--------------------
41351
12:23:16 SQL> select count(DATE_ASSIGNED) from ASLMDB.VLKG_ORDER_QTY_ASSIGNED
where DATE_ASSIGNED > '29-AUG-11'
/12:23:25 2 12:23:25 3
VIEW STRUCTURE:
CREATE OR REPLACE FORCE VIEW "ASLMDB"."VLKG_ORDER_QTY_ASSIGNED" ("ORDER_LINE_ID", "REQUEST_ID", "SYSTEM_ID", "PARAMETER_ID", "DATE_ASSIGNED", "TOT_QTY_ASSIGNED") AS
SELECT assgn.order_line_id, kr.id, kr.customer_system_id, lr.parameter_id, kr.
last_changed_date, assgn.assigned_quantity FROM
TLKG_ORDLINE_LICRIGHT_ASSIGN assgn,
TLKG_LICENSE_RIGHT lr,
TLKG_LICENSE_RIGHT_SET ls,
TLKG_KEY_REQUEST kr
WHERE assgn.license_right_id = lr.id
AND lr.license_right_set_id = ls.id
AND ls.key_request_id = kr.id
AND kr.key_request_status_id = 'COMPLETED'