0

I need to execute the following UNION ALL Select statements, I am using bind variables for the WHERE conditions. I am using PyODBC for Oracle. While the code executes and returns results, it takes a lot of time if compared to running it from TOAD directly. Any Ideas what is the problem?

query = """
SELECT concat('Abstract ',display_order), abstract_text as field1, 
abstract_source_code, display_order 
From abstract
WHERE control_number = ? and control_level = ?
UNION ALL
SELECT 'cTitle', chapter_title, null as col2, null as col3 
From chapter
WHERE control_number = ? and control_level = ?
"""

result = cursor.execute(query, ['201728871', '005', '201728871', '005']).fetchall()
print(result)
Martin
  • 1
  • 1
  • 1
    Compare the two execution plans. Perhaps the values of control_number or control_level are skewed, and the optimiser is able to make a different choice when it knows what specific value to look for. – William Robertson Jan 31 '18 at 09:15
  • How many rows are returned? the code here has `fetchall()` , and will get completed only when all rows are fetched. TOAD on the other hand, will fetch the rows in batch or lazy loaded. So, it might appear as if in TOAD is faster. – Maheswaran Ravisankar Jan 31 '18 at 09:16
  • Thanks, for these specific conditions the statement only returns two rows and so for the TOAD. It there a way to specify one condition for all the UNION ALL Select statements without affecting the performance? I am having more that 13 SELECT statements and I need to UNION ALL of them, they all share the same condition. – Martin Jan 31 '18 at 09:23

0 Answers0