Home > Optimizer, Oracle Database, Performance > Hint queries containing views with block and aliases

Hint queries containing views with block and aliases

Create the view.


create view v1 as select t1.* from o2 t1 join (select * from o1 t2 where object_id > 10000) t3 on t1.object_id=t3.object_id;

Explain query with blocks and aliases


select /*+ GATHER_PLAN_STATISTICS */ * from v1 where last_ddl_time > sysdate -1.5 ; 

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'IOSTATS ALIAS'));

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      2 |        |     80 |00:00:01.35 |     428 |
|*  1 |  HASH JOIN                   |          |      2 |     40 |     80 |00:00:01.35 |     428 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| O2       |      2 |     40 |     80 |00:00:00.01 |      66 |
|*  3 |    INDEX RANGE SCAN          | IX2_LDT  |      2 |     40 |     80 |00:00:00.01 |       6 |
|*  4 |   INDEX FAST FULL SCAN       | IX1_OID  |      2 |  68784 |    137K|00:00:00.36 |     362 |
---------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$5CB8D2B8
   2 - SEL$5CB8D2B8 / T1@SEL$2
   3 - SEL$5CB8D2B8 / T1@SEL$2
   4 - SEL$5CB8D2B8 / T2@SEL$3

Hint the query with blocks and aliases


select /*+ USE_NL(@SEL$5CB8D2B8 T1@SEL$2 T2@SEL$3) GATHER_PLAN_STATISTICS */ * from v1 where last_ddl_time > sysdate -1.5 ; 

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'IOSTATS ALIAS'));

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |     40 |00:00:00.01 |      75 |
|   1 |  NESTED LOOPS                |          |      1 |     82 |     40 |00:00:00.01 |      75 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| O2       |      1 |     82 |     40 |00:00:00.01 |      33 |
|*  3 |    INDEX RANGE SCAN          | IX2_LDT  |      1 |     94 |     40 |00:00:00.01 |       3 |
|*  4 |   INDEX RANGE SCAN           | IX1_OID  |     40 |      1 |     40 |00:00:00.01 |      42 |
---------------------------------------------------------------------------------------------------

References :

http://jonathanlewis.wordpress.com/2007/06/25/qb_name/

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: