Re: 11g. Incorrect results returned from query.
Date: Fri, 30 May 2008 08:19:04 -0700 (PDT)
Message-ID: <5cf10992-fd08-4777-9cee-2cf6fff793fd@b1g2000hsg.googlegroups.com>
On 30 May, 15:51, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
wrote:
> <Ralph.in..._at_googlemail.com> wrote in message
>
> news:4690406e-f86e-45a5-a7b9-4aaf67d22e16_at_26g2000hsk.googlegroups.com...
>
>
>
>
>
> > Hi All,
>
> > 10 rows selected.
>
> > MIDASTST>8
> > 8* -- WHERE transaction_subtype_type is null -- UNCOMMENT THIS LINE
> > FIRST
> > MIDASTST>c/--//
> > 8* WHERE transaction_subtype_type is null -- UNCOMMENT THIS LINE FIRST
> > MIDASTST>/
>
> > no rows selected
>
> > Cheers
>
> > Ralph
>
> Do you get different execution paths for the two queries ?
> Is the table partitioned in any way ?
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide quoted text -
>
> - Show quoted text -
Hi Jonathan,
The explain plans look as follows.
MIDASTST>explain plan for
2 select * from (
3 SELECT
4 transaction_group,
transaction_type,
5 6 transaction_subtype,
7 nvl(transaction_subtype_type, 'AAA transaction_subtype_type field
is NULL')
FROM warehouse.s4_claim_fact
8 9 -- WHERE transaction_subtype_type is null -- UNCOMMENT THIS
LINE FIRST
10 order by 4
)
where rownum <= 10; 11 12
Explained.
MIDASTST>@explain
Wrote file /home/oracle/scripts/explain.tmp
PLAN_TABLE_OUTPUT
Plan hash value: 660358270
| Id | Operation | Name | Rows | Bytes | TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 1130 | | 74448 (1)| 00:14:54 | | | | |* 1 | COUNT STOPKEY | | | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | | 3 | PX SEND QC (ORDER) | :TQ10001 | 18M| 1943M| | 74448 (1)| 00:14:54 | Q1,01 | P->S | QC (ORDER) | | 4 | VIEW | | 18M| 1943M| | 74448 (1)| 00:14:54 | Q1,01 | PCWP | | |* 5 | SORT ORDER BY STOPKEY | | 18M| 1943M| 4471M| 74448 (1)| 00:14:54 | Q1,01 | PCWP | | | 6 | PX RECEIVE | | 10 | 1130 | | | | Q1,01 | PCWP | | | 7 | PX SEND RANGE | :TQ10000 | 10 | 1130 | | | | Q1,00 | P->P | RANGE | |* 8 | SORT ORDER BY STOPKEY| | 10 | 1130 | | | | Q1,00 | PCWP | | | 9 | PX BLOCK ITERATOR | | 18M| 1943M| | 10292 (2)| 00:02:04 | Q1,00 | PCWC | | | 10 | TABLE ACCESS FULL | S4_CLAIM_FACT | 18M| 1943M| | 10292 (2)| 00:02:04 | Q1,00 | PCWP | | -----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(ROWNUM<=10) 5 - filter(ROWNUM<=10) 8 - filter(ROWNUM<=10)
Note
- dynamic sampling used for this statement
28 rows selected.
1 explain plan for
2 select * from (
3 SELECT
4 transaction_group, 5 transaction_type, 6 transaction_subtype,
7 nvl(transaction_subtype_type, 'AAA transaction_subtype_type field is NULL')
8 FROM warehouse.s4_claim_fact
9 -- WHERE transaction_subtype_type is null -- UNCOMMENT THIS LINE FIRST
10 order by 4
11 )
12* where rownum <= 10
MIDASTST>9
9* -- WHERE transaction_subtype_type is null -- UNCOMMENT THIS LINE FIRST
MIDASTST>c/--//
9* WHERE transaction_subtype_type is null -- UNCOMMENT THIS LINE FIRST
MIDASTST>/ Explained.
MIDASTST>@explain
Wrote file /home/oracle/scripts/explain.tmp
PLAN_TABLE_OUTPUT
Plan hash value: 1726186173
| Id | Operation | Name | Rows | Bytes | TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 113 | | 5 (100)| 00:00:01 | | | | |* 1 | COUNT STOPKEY | | | | | | | | | | |* 2 | PX COORDINATOR | | | | | | | | | | | 3 | PX SEND QC (ORDER) | :TQ10001 | 1 | 113 | | 5 (100)| 00:00:01 | Q1,01 | P->S | QC (ORDER) | | 4 | VIEW | | 1 | 113 | | 5 (100)| 00:00:01 | Q1,01 | PCWP | | |* 5 | SORT ORDER BY STOPKEY | | 1 | 113 | 4471M| | | Q1,01 | PCWP | | | 6 | PX RECEIVE | | 1 | 113 | | | | Q1,01 | PCWP | | | 7 | PX SEND RANGE | :TQ10000 | 1 | 113 | | | | Q1,00 | P->P | RANGE | |* 8 | FILTER | | | | | | | Q1,00 | PCWC | | |* 9 | SORT ORDER BY STOPKEY| | 1 | 113 | | | | Q1,00 | PCWP | | | 10 | PX BLOCK ITERATOR | | 18M| 1943M| | 10292 (2)| 00:02:04 | Q1,00 | PCWC | | | 11 | TABLE ACCESS FULL | S4_CLAIM_FACT | 18M| 1943M| | 10292 (2)| 00:02:04 | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(ROWNUM<=10) 2 - filter(NULL IS NOT NULL) 5 - filter(ROWNUM<=10) 8 - filter(NULL IS NOT NULL) 9 - filter(ROWNUM<=10)
The table is not partitioned.
Unfortunately we have attempted to re run our bulk load process (this is a DW app) from the beginning and the queries are now showing the correct results.
The first issue we encontered of this type (which I alluded to in my
first post)
is still repeatable...)
Here is the example of that one. I think that the two must be related.
MIDASTST>set echo on MIDASTST>@/tmp/w2 MIDASTST>SELECT policyholder_key, name_id,2 phone_evening,
3 valid_from_date, valid_to_date
4 FROM test_ph p
5 WHERE name_id IN (2074451, 5173620) 6 order by policyholder_key
7 /
POLICYHOLDER_KEY NAME_ID PHONE_EVENING VALID_FROM_DATE VALID_TO_DATE
---------------- ---------- ------------------------------
-------------------- --------------------
212827197 5173620 01773 785 185 18-JUN-2001
20:37:10
212827198 5173620 01773 785 185 18-JUN-2001
20:37:10
212827199 5173620 08-JUL-2004
12:23:41 01-JAN-4000 00:00:00
214956388 2074451 01746714284 13-MAR-2001
20:39:01
214956389 2074451 01746714284 13-MAR-2001
20:39:01
MIDASTST>SELECT policyholder_key, name_id,
2 --phone_evening,
3 valid_from_date, valid_to_date
4 FROM test_ph p
5 WHERE name_id IN (2074451, 5173620)
6 order by policyholder_key
7 /
POLICYHOLDER_KEY NAME_ID VALID_FROM_DATE VALID_TO_DATE
---------------- ---------- -------------------- --------------------
212827197 5173620 18-JUN-2001 20:37:10 18-JUN-2001 20:37:09
212827198 5173620 18-JUN-2001 20:37:10 08-JUL-2004 12:23:40
212827199 5173620 08-JUL-2004 12:23:41 01-JAN-4000 00:00:00
214956388 2074451 13-MAR-2001 20:39:01 13-MAR-2001 20:39:00
214956389 2074451 13-MAR-2001 20:39:01 01-JAN-4000 00:00:00
MIDASTST>SELECT policyholder_key, name_id,
2 phone_evening,
3 valid_from_date, valid_to_date
4 FROM test_ph p
5 WHERE name_id IN (2074451, 5173620)
6 /
POLICYHOLDER_KEY NAME_ID PHONE_EVENING VALID_FROM_DATE VALID_TO_DATE
---------------- ---------- ------------------------------
-------------------- --------------------
214956388 2074451 01746714284 13-MAR-2001
20:39:01
214956389 2074451 13-MAR-2001
20:39:01 01-JAN-4000 00:00:00
212827197 5173620 01773 785 185 18-JUN-2001
20:37:10
212827198 5173620 01773 785 185 18-JUN-2001
20:37:10
212827199 5173620 08-JUL-2004
12:23:41 01-JAN-4000 00:00:00
MIDASTST>@/tmp/w3
MIDASTST>explain plan for
2 SELECT policyholder_key, name_id,
3 phone_evening,
4 valid_from_date, valid_to_date
5 FROM test_ph p
6 WHERE name_id IN (2074451, 5173620) 7 order by policyholder_key
8 /
Explained.
MIDASTST>@explain
MIDASTST>sav explain.tmp REPLACE
Wrote file /home/oracle/scripts/explain.tmp
MIDASTST>select * from table(dbms_xplan.display())
2 /
PLAN_TABLE_OUTPUT
Plan hash value: 1594186399
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 19 | 741 | 54324 (1)| 00:10:52 | | 1 | SORT ORDER BY | | 19 | 741 | 54324 (1)|00:10:52 |
|* 2 | TABLE ACCESS FULL| TEST_PH | 19 | 741 | 54323 (1)| 00:10:52 |
Predicate Information (identified by operation id):
2 - filter("NAME_ID"=2074451 OR "NAME_ID"=5173620)
14 rows selected.
MIDASTST>explain plan for
2 SELECT policyholder_key, name_id,
3 --phone_evening,
4 valid_from_date, valid_to_date
5 FROM test_ph p
6 WHERE name_id IN (2074451, 5173620)
7 order by policyholder_key
8 /
Explained.
MIDASTST>@explain
MIDASTST>sav explain.tmp REPLACE
Wrote file /home/oracle/scripts/explain.tmp
MIDASTST>select * from table(dbms_xplan.display())
2 /
PLAN_TABLE_OUTPUT
Plan hash value: 1594186399
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 19 | 570 | 54324 (1)| 00:10:52 | | 1 | SORT ORDER BY | | 19 | 570 | 54324 (1)|00:10:52 |
|* 2 | TABLE ACCESS FULL| TEST_PH | 19 | 570 | 54323 (1)| 00:10:52 |
Predicate Information (identified by operation id):
2 - filter("NAME_ID"=2074451 OR "NAME_ID"=5173620)
14 rows selected.
MIDASTST>explain plan for
2 SELECT policyholder_key, name_id,
3 phone_evening,
4 valid_from_date, valid_to_date
5 FROM test_ph p
6 WHERE name_id IN (2074451, 5173620)
7 /
Explained.
MIDASTST>@explain
MIDASTST>sav explain.tmp REPLACE
Wrote file /home/oracle/scripts/explain.tmp
MIDASTST>select * from table(dbms_xplan.display())
2 /
PLAN_TABLE_OUTPUT
Plan hash value: 1355899646
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19 | 741 | 54323 (1)|00:10:52 |
|* 1 | TABLE ACCESS FULL| TEST_PH | 19 | 741 | 54323 (1)| 00:10:52 |
Predicate Information (identified by operation id):
1 - filter("NAME_ID"=2074451 OR "NAME_ID"=5173620)
13 rows selected.
Cheers
Ralph Received on Fri May 30 2008 - 10:19:04 CDT