Hi,
From below sql I got 1.5 million records in the output.
select count(1) from (below sql). It took 10 minutes of time in toad to get count. Can you please help me to understand this will be the total time for fetching all records.
I came to understood that when I have normal run of below sql in toad it extracts only first 500 records but not all. Appreciate if you could share how oracle engine works on this. Thank You.
SELECT xoh.oms_country_code country_code,
xoh.oms_ordered_date order_date,
ooh.order_number,
ooh.attribute6 order_type,
hca.account_number distributor_number,
oe_oe_totals_summary.prt_order_total (ooh.header_id) order_amount,
DECODE (
ooh.cancelled_flag,
'Y', 0,
(SELECT NVL (SUM (oola.tax_value), 0)
FROM apps.oe_order_lines_all oola
WHERE oola.header_id = ooh.header_id
AND oola.flow_status_code <> 'CANCELLED'))
tax_amount,
CASE
WHEN (SELECT COUNT (1)
FROM apps.oe_order_lines_all ool
WHERE 1 = 1
AND ool.flow_status_code <> 'CANCELLED'
AND ool.header_id = ooh.header_id) = 0
THEN 'CANCELLED'
WHEN ooh.attribute2 IS NULL THEN 'BOOKED'
WHEN ooh.attribute2 IS NOT NULL THEN 'SHIPPED'
END
order_status,
(SELECT COUNT (1)
FROM apps.oe_order_lines_all ool
WHERE 1 = 1 AND ool.header_id = ooh.header_id)
line_count,
DECODE (
ooh.cancelled_flag,
'Y', 0,
NVL (ar_rcpt_data.receipt_amount, 0)
+ (SELECT NVL (SUM (oms_payment_amount), 0)
FROM apps.fnd_lookup_values_vl flv1,
apps.fnd_lookup_values_vl flv,
apps.xx_om_order_payments_stg xop
WHERE 1 = 1
AND TRUNC (SYSDATE) BETWEEN NVL (
flv1.start_date_active,
TRUNC (SYSDATE) - 1)
AND NVL (
flv1.end_date_active,
TRUNC (SYSDATE) + 1)
AND flv1.enabled_flag = 'Y'
AND flv1.attribute13 IN
('Cash Coupon', 'Voucher', 'AR')
AND flv1.tag = xop.oms_transaction_type
AND flv1.lookup_type =
'XX_AR_PAYMENT_TYPE_MAPPING_' || flv.meaning
AND TRUNC (SYSDATE) BETWEEN NVL (
flv.start_date_active,
TRUNC (SYSDATE) - 1)
AND NVL (flv.end_date_active,
TRUNC (SYSDATE) + 1)
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = xop.oms_country_code
AND flv.lookup_type = 'XX_OM_COUNTRY_OU_MAPPING'
AND xop.oms_order_number = xoh.oms_order_number
AND xop.oms_country_code = xoh.oms_country_code
AND xop.oms_source_category = xoh.oms_source_category))
payment_amount,
xoh.oms_warehouse warehouse
FROM apps.hz_cust_accounts_all hca,
apps.oe_order_headers_all ooh,
apps.hr_all_organization_units_tl hou,
apps.xx_om_order_headers_stg xoh,
( SELECT SUM (receipt_amount) receipt_amount, header_id, org_id
FROM (SELECT DISTINCT acra.cash_receipt_id,
archa.status,
NVL (acra.amount, 0) receipt_amount,
op.header_id,
acra.org_id
FROM apps.oe_payments op,
apps.ar_receivable_applications_all araa,
apps.ar_cash_receipts_all acra,
apps.ar_cash_receipt_history_all archa
WHERE 1 = 1
AND araa.payment_set_id = op.payment_set_id
AND acra.cash_receipt_id = araa.cash_receipt_id
AND archa.cash_receipt_id = acra.cash_receipt_id
AND archa.current_record_flag = 'Y'
AND araa.org_id = acra.org_id)
GROUP BY header_id, org_id) ar_rcpt_data
WHERE 1 = 1
AND hou.language = 'US'
AND hou.name IN ('CN_OU_119', 'HK_OU_104', 'TW_OU_116','CN_OU_174')
AND xoh.oms_source_category = 'ORDER'
AND xoh.oms_ordered_date >= (TRUNC (SYSDATE) - 40)
AND xoh.org_id = hou.organization_id
AND ooh.order_number = xoh.oms_order_number
AND ooh.order_type_id = xoh.order_type_id
AND ooh.version_number = 0
AND ooh.org_id = xoh.org_id
AND hca.cust_account_id = ooh.sold_to_org_id
AND ar_rcpt_data.header_id(+) = ooh.header_id
AND ar_rcpt_data.org_id(+) = ooh.org_id
AND xoh.oms_ordered_date BETWEEN TO_DATE (('2017-10-01' || ' 00:00:00'),'YYYY-MM-DD HH24:MI:SS')
AND TO_DATE (('2017-10-31' || ' 23:59:59'),'YYYY-MM-DD HH24:MI:SS')
AND xoh.oms_country_code = '360'