Toad World® Forums

Toad Data Filter versus Where clause, They return different results initially

I run a simple join query in TOAD (see below), right click on the receipt_number field, select ‘Filter Data’ and add an ‘AND’ Condition, e.g. receipt_number = ‘XYZ’. Then the appropriate number of records, e.g., 60 records, are returned but the value in a number field is wrong for 4 out of the 60 records. If I take this same query, add the condition directly under the where clause (see below), run the query in TOAD, the appropriate number of records are returned, e.g., 60 records, and all data returned is correct. Then if I run the query again without the condition under the where clause, use Toad’s filter capability again, the data returned is now correct. We believe the problem is not with TOAD but with how Oracle optimizes the query, the straight SQL is using a different optimization technique then TOAD’s filter. We are going to rebuild the indexes for both tables to see if this solves the problem but this is such a strange problem, I wonder if anyone has seen this before or could explain in detail what is happening.

Simple two table join that used TOAD’s ‘Filter Data’

SELECT a.*
FROM gift_tender_type a, zz_consolidate_log_sl l
WHERE l.log_table_name = ‘gift_tender_type’
AND (a.receipt_number = l.log_id
OR a.mg_number = l.log_id);

Simple two table join that used ‘AND’ statement added

SELECT a.*
FROM gift_tender_type a, zz_consolidate_log_sl l
WHERE l.log_table_name = ‘gift_tender_type’
AND (a.receipt_number = l.log_id
OR a.mg_number = l.log_id) AND a.receipt_number =‘XYZ’;

Oracle Version: 11.2.0.1.0, Toad Version: 12.8.0.49

FYI. We rebuilt the table indexes and the issue continues to happen. All suggestions/help for this confusing issue will be greatly appreciated.

Can you give us a ‘create table’ statement and some insert statements so we can reproduce the problem? I don’t think the fact that you are joining tables is relevant. I think you’ll be able to reproduce it with a single table.

We now believe there is a problem with the internal table storage. We copied the main table, moved the data and the issue no longer happens. We are going to use TOAD’s Rebuild Table capability to rebuild all tables associated with this strange issue. If anyone had issues or know of any caveats with the Rebuild please let me know. Thanks

Rebuild Table should work fine, but if you just want to put the table data into a different place on the disk, the easiest thing to do is "alter table

move tablespace ". You can even keep it in the same tablespace if you want.

Oh, and if I remember correctly, you’ll want to rebuild any indexes on the table afterwards.

Thanks for the suggestion, Thanks John