CODEWITHSUNDEEP
oracle-databaseoracle11g
Sony Antony
Sony Antony

Reputation: 346

How does Oracle implement an SQL query over a VIEW

We have a VIEW defined over a base table using an SQL Queries ( SQL query1 )

Im trying to understand how Querying ( SQL query2 ) over a view work

  1. When I run an SQL over a VIEW, does Oracle first execute query1 to create a temp table and then run query2 over the temp table ?

  2. Or does it create a single composite query by combining query1 and query2 in order to give the result

( my query2 has high selectivity if run directly over the base table and a composite query should run much faster than executing query1 first )

Upvotes: 1

Views: 28

Answers (1)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

  1. Or does it create a single composite query by combining query1 and query2 in order to give the result

Yes, CBO (oracle cost-based optimizer) expands final query and transforms it and build an execution plan and you can check final query after transformation in trace 10053(optimizer trace) or using DBMS_UTILITY.EXPAND_SQL_TEXT

NB. DBMS_UTILITY.EXPAND_SQL_TEXT has appeared in 12.1, but you tagged Oracle 11g, so you need to use dbms_sql2.expand_sql_text, an example: https://github.com/xtender/xt_scripts/blob/master/expand_11.sql

Upvotes: 1

Related Questions