Simple life with named query blocks

Hi. I’m just gonna show you how to use query block for manipulate oracle hints for subqueries.

create table mczim_test_t as select * from dba_objects;

create index mczim_test_i on mczim_test_t(object_id);

begin dbms_stats.gather_table_stats('system','mczim_test_t',cascade=>true); end;

Plain sql text without any tricks.

SQL> explain plan for select * from (select count(object_id) from system.mczim_test_t);

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display(format=>'ALIAS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3520717601

---------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |    13 |   401   (1)| 00:00:01 |
|   1 |  VIEW                  |              |     1 |    13 |   401   (1)| 00:00:01 |
|   2 |   SORT AGGREGATE       |              |     1 |     6 |            |          |
|   3 |    INDEX FAST FULL SCAN| MCZIM_TEST_I |   629K|  3691K|   401   (1)| 00:00:01 |
---------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$2 / from$_subquery$_001@SEL$1
   2 - SEL$2
   3 - SEL$2 / MCZIM_TEST_T@SEL$2

17 rows selected.

And here we add qb_name for manipulate oracle hints.

SQL> explain plan for select * from (select /*+ qb_name("innerQuery") no_index_ffs(mczim_test_t mczim_test_i) */ count(object_id) from system.mczim_test_t) innerQuery;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display(format=>'ALIAS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2737980156

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |    13 |  1468   (1)| 00:00:01 |
|   1 |  VIEW             |              |     1 |    13 |  1468   (1)| 00:00:01 |
|   2 |   SORT AGGREGATE  |              |     1 |     6 |            |          |
|   3 |    INDEX FULL SCAN| MCZIM_TEST_I |   629K|  3691K|  1468   (1)| 00:00:01 |
----------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - innerQuery / INNERQUERY@SEL$1
   2 - innerQuery
   3 - innerQuery / MCZIM_TEST_T@innerQuery

17 rows selected.
SQL> explain plan for select * from (select /*+ qb_name("innerQuery") no_index(mczim_test_t mczim_test_i) */ count(object_id) from system.mczim_test_t) innerQuery;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display(format=>'ALIAS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4156749158

------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |     1 |    13 |  2557   (1)| 00:00:01 |
|   1 |  VIEW               |              |     1 |    13 |  2557   (1)| 00:00:01 |
|   2 |   SORT AGGREGATE    |              |     1 |     6 |            |          |
|   3 |    TABLE ACCESS FULL| MCZIM_TEST_T |   629K|  3691K|  2557   (1)| 00:00:01 |
------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - innerQuery / INNERQUERY@SEL$1
   2 - innerQuery
   3 - innerQuery / MCZIM_TEST_T@innerQuery

17 rows selected.

Simple way to make our life easier.

Advertisements

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