How to reduce Querying time

Hi,

I am using below query to display reports:

select a.abc, a.title, a.language, a.status, a.stock_level, (nvl(b.hard_copy_orders, 0) + nvl(g.hard_copy_orders, 0)) as hard_copy_orders, nvl((nvl(b.stock_used, 0) - nvl(b.email_orders, 0)) + nvl(g.stock_used, 0), 0) as hard_copy_volume, nvl(b.email_orders, 0) as email_orders, a.max_order_qty, a.document_list_urn, a.fulfillment_details, d.description, e.print_name from test.tbl_phmes a, (select abc, nvl(sum(b.quantity), 0) as stock_used, count(a.customer_email) as email_orders, count(a.customer_address) as hard_copy_orders from test.tbl_orders a, test.tbl_order_phmes b where a.urn=b.order_urn and a.entered_date between '1-Jan-2006' and '27-Oct-2006' group by abc)b, (select c.abc, nvl(sum(b.quantity), 0) as stock_used, count(a.urn) as hard_copy_orders from test.tbl_css_orders a, test.tbl_css_order_phmes b, test.tbl_phmes c where a.urn=b.order_urn and b.abc=c.css_product_code and a.entered_date between '1-Jan-2006' and '27-Oct-2006' group by c.abc)g, test.tbl_product_lines d, test1.desk_tops e where a.abc=b.abc(+) and a.abc=g.abc(+) and a.product_id=d.urn(+) and a.document_owner=e.user_id(+) and A.abc in ('45000') ORDER BY abc DESC

I dont know why its taking lots of time to retrieve values from database.
Of course each table contains more than 100 thousands of rows.
I created indexes for all columns which are in where clause.

Is there any better way to rewrite this query or any other solutions to run faster.

waiting for your reply
Thanks in advance,
Start Free Trial