Cannot help myself to post this message

From: hsheng <harrysheng00_at_hotmail.com>
Date: 26 Nov 2004 09:51:51 -0800
Message-ID: <380261e0.0411260951.6c8e2b0e@posting.google.com>

I have 2 queries, one used binding variables, the other did not. The only difference of these 2 queries is the binding variable part.

SELECT dataset.ds_ed_key, 
       dataset.dataset_name,
       dataset.revision, 
       dataset.stream, dataset.issue,
       dataset.cust_ruid,
       dataset.dataset_type,
       getSystemCode('Dataset Type', dataset.dataset_type),
       dataset.sub_type,
       getSystemCode('Dataset SubType', dataset.sub_type),
       dataset.parent_ruid, dataset.dataset_status,
       getSystemCode('Dataset Status', dataset.dataset_status),
       dataset.reason, dataset.description, dataset.source,"
       dataset.creation_time, dataset.cre_owner,
       dataset.chg_time, dataset.chg_owner

FROM dataset WHERE (1 = 1)
 AND dataset.cust_ruid IN

     (SELECT ruid FROM customer2 WHERE company_ruid=1180000000)  AND dataset.dataset_name LIKE '%CXNTNQ6513%' ORDER BY dataset_name, revision DESC

Oracle 8.0.5 on WindowsNT4 sp6.
57533 records in dataset, 3891 records in customer2 (Migrated from Oracle 7.3.4 on HP-UX 10.2)

The query using binding-variables took 681.900 seconds to run, with 1,463,928 logical buffer gets and 15,000 physical disk reads.

The query not using binding-variables took 2.414 seconds to run, with 18,000 logical buffer gets and 15,000 physical disk reads.

Also run these same queries against Oracle 7.3.4 on HP-UX 10.2

The query using binding-variables took 1.542 seconds to run, with 7,000 logical buffer gets and 6,000 physical disk reads.

The query not using binding-variables took 1.352 seconds to run, with 7,000 logical buffer gets and 6,000 physical disk reads. Received on Fri Nov 26 2004 - 11:51:51 CST