Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Oracle FIRST_ROWS bug? No rows (Read 3421 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Oracle FIRST_ROWS bug? No rows
13.09.08 at 11:40:10
 
Oracle FIRST_ROWS bug? No rows
 
We are using Oracle 10g and found this worrying behavior. On including a particular column in the select list the execution path completely changed and no rows were retrieved at all whereas leaving the column out does give us records.
 
SELECT /* FIRST_ROWS */  
    ctr.mtr_code ,
    ctr.SERVERS_AS_AREA,  
    cve.TIMESTAMP,  
    cve.VALUE    
FROM   ctr,        acy,       aty,       cve,       acn
WHERE  ctr.mtr_code = cve.ctr_mtr_code
AND    ctr.mtr_code = acy.ctr_mtr_code
AND    acn.ean_id = ctr.ean_id
AND    acy.aty_seq = aty.seq
AND    aty.act_code = 'DEF'
AND    acn.mde_code = 'ABC'
AND cve.timestamp >=TO_DATE ('20051231', 'YYYYMMDD')
AND    acy.start_date <= cve.TIMESTAMP
AND    acy.end_date >= cve.TIMESTAMP;
 
The above query returns no rows when the last column cve.VALUE is omitted. So including the column does return rows, excluding the column returns nothing.  
When the FIRST_ROWS hint is replaced by ALL_ROWS data is returned even without including the cve.VALUE column in the select list.
 
Note that no error is displayed when no rows are returned. This worries me!
Back to top
 
« Last Edit: 13.09.08 at 11:40:27 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle FIRST_ROWS bug? No rows
Reply #1 - 01.10.08 at 09:50:09
 
The problem exhibits itself only on a table with partitioning that has a corrupt index. After fixing the index the problem is resolved.
 
So, be aware of corrupted indexes on partitioned tables in Oracle 10g!
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1