Slightly off topic for this list, but in any case, I don't
see a problem with it. You don't mention version and platform.
I'm guessing you need to apply a patch as the two queries
produce the same execution plan on 9.2.0.4 on RH 8.0
Jared
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
113 | 1 |
| 1 | SORT GROUP BY | | 1 |
113 | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID | LINEITEM | 1 |
52 | |
| 3 | NESTED LOOPS | | 1 |
113 | 1 |
| 4 | NESTED LOOPS | | 1 |
61 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| MARKET_X_COMPANY | 1 |
26 | 1 |
| 6 | INDEX FULL SCAN | IDX_MXC_2 | 1
| | |
|* 7 | TABLE ACCESS BY INDEX ROWID| INVOICE | 1 |
35 | |
|* 8 | INDEX RANGE SCAN | IDX_INVOICE_2 | 1
| | |
|* 9 | INDEX RANGE SCAN | IDX_LINEITEM_1 | 1
| | |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("LINEITEM"."HOME_DELIVERY"=1)
7 - filter("MARKET_X_COMPANY"."COMPANY"="INVOICE"."COMPANY")
8 - access("INVOICE"."INVOICE_DATE">=TO_DATE('2003-01-01 00:00:00',
'yyyy-mm-dd
hh24:mi:ss') AND
"INVOICE"."INVOICE_DATE"<=TO_DATE('2003-03-18 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
9 - access("LINEITEM"."INVOICE"="INVOICE"."ID")
Note: cpu costing is off
27 rows selected.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
113 | 1 |
| 1 | SORT GROUP BY | | 1 |
113 | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID | LINEITEM | 1 |
52 | |
| 3 | NESTED LOOPS | | 1 |
113 | 1 |
| 4 | NESTED LOOPS | | 1 |
61 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| MARKET_X_COMPANY | 1 |
26 | 1 |
| 6 | INDEX FULL SCAN | IDX_MXC_2 | 1
| | |
|* 7 | TABLE ACCESS BY INDEX ROWID| INVOICE | 1 |
35 | |
|* 8 | INDEX RANGE SCAN | IDX_INVOICE_2 | 1
| | |
|* 9 | INDEX RANGE SCAN | IDX_LINEITEM_1 | 1
| | |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("LINEITEM"."HOME_DELIVERY"=1)
7 - filter("MARKET_X_COMPANY"."COMPANY"="INVOICE"."COMPANY" AND
"MARKET_X_COMPANY"."COMPANY"="INVOICE"."COMPANY")
8 - access("INVOICE"."INVOICE_DATE">=TO_DATE('2003-01-01 00:00:00',
'yyyy-mm-dd
hh24:mi:ss') AND
"INVOICE"."INVOICE_DATE"<=TO_DATE('2003-03-18 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
9 - access("LINEITEM"."INVOICE"="INVOICE"."ID")
Note: cpu costing is off
On Fri, 2004-03-19 at 09:48, Kinyon, Rob wrote:
> Tim â
>
>
>
> FYI â I have found a reproducible CBO bug in Oracle 9i,
> latest patchset. I was wondering if you could look at it and tell me
> if you have any insight into it. Iâve attached a tar.gz containing
> five files â four setup scripts and a shell script that runs them in
> the correct order. It requires a connection to a database with
> scott/tiger set up.
>
>
>
> The bug is related to using the same where condition
> twice. (SELECT * FROM foo WHERE a = b AND a = b) I found it when doing
> some SQL generation.
>
>
>
> Iâm not looking for you to do anything with it re:
> DBD::Oracle â this is just a FYI.
>
>
>
> Thanks,
>
> Rob
>
>
