What are Query Block Names and how to find them

I got a lot of follow-up questions on what Query Block names are and how to find them, after my recent post about using SQL Patches to influence execution plans. Rather than burying my responses in the comment section under that post, I thought it would be more useful to do a quick post on it.

What are query blocks?

A query block is a basic unit of SQL. For example, any inline view or subquery of a SQL statement are considered separate query blocks to the outer query.

The simple query below has just one sub-query, but it has two Query Blocks—one for the outer SELECT and one for the subquery SELECT.

Oracle automatically names each query block in a SQL statement based on the keyword using the following sort of name; sel$1, ins$2, upd$3, del$4, cri$5, mrg$6, set$7, misc$8, etc.

Given there are two SELECT statements in our query, the query block names will begin with SEL. The outer query will be SEL$1 and the inner query SEL$2.

How do I find the name of a query block?

To find the Query Block name, you can set the FORMAT parameter to ‘+alias’ in the DBMS_XPLAN.DISPLAY_CURSOR command. This will display the contents of the OBJECT_ALIAS column in the PLAN_TABLE, as a new section under the execution plan.

The new section will list the Query Block name for each of the lines in the plan.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=\>'+alias'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID 4c8bfsduxhyht, child NUMBER 0
-------------------------------------
SELECT e.ename, e.deptno FROM emp e WHERE e.deptno IN (SELECT d.deptno 
FROM dept d WHERE d.loc='DALLAS')
Plan hash VALUE: 2484013818
---------------------------------------------------------------------------
| Id  | Operation	   | Name | ROWS  | Bytes | Cost (%CPU)| TIME	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	  |	  |	5 (100)|	  |
|*  1 |  HASH JOIN SEMI    |	  |	5 |   205 |	5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   280 |	2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |	1 |    21 |	2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / E@SEL$1
3 - SEL$5DA710D3 / D@SEL$2
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
3 - FILTER("D"."LOC"='DALLAS')

As you can see, @SEL1 is the Query Block name for the outer query, where the EMP table is used, and @SEL2 is the Query Block name for the sub-query, where the DEPT tables is used.

So, if I wanted to use a SQL Patch to change the access method for the DEPT table, I would specify it as follows.

DECLARE
   patch_name varchar2(100);
BEGIN
   patch_name := sys.dbms_sqldiag.create_sql_patch(
                 sql_id=>'4c8bfsduxhyht',
                 hint_text=>'INDEX(@"SEL$2" "D" "DEPT_LOC_IDX")',
                 name=>'TEST_QB_PATCH');
END;
/

Let’s check the plan to be sure the SQL Patch worked.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'+alias'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID	4c8bfsduxhyht, child NUMBER 0
-------------------------------------
SELECT e.ename, e.deptno FROM	emp e WHERE e.deptno IN (SELECTd.deptno
FROM   dept d	WHEREd.loc='DALLAS')
Plan hash VALUE: 3547841569
-----------------------------------------------------------------------------------------------------
| Id  | Operation			     | Name	    | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |		    |	    |	    |	  5 (100)|	    |
|*  1 |  HASH JOIN SEMI 		     |		    |	  5 |	205 |	  5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL		     | EMP	    |	 14 |	280 |	  2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEPT	    |	  1 |	 21 |	  2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN		     | DEPT_LOC_IDX |	  1 |	    |	  1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / E@SEL$1
   3 - SEL$5DA710D3 / D@SEL$2
   4 - SEL$5DA710D3 / D@SEL$2
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   4 - access("D"."LOC"='DALLAS')
 
Note
-----
   - SQL patch "TEST_QB_PATCH" used FOR this statement

Remember, even if it’s a long-running query, once it starts executing the execution plan will be available in the cursor cache and therefore can be viewed by supplying the SQL_ID and CHILD NUMBER to the DBMS_XPLAIN.DISPLAY function.

If you have a more complex SQL statement, you may want to use the QB_NAME hint to explicitly name each query block. This is an excellent way to document your code, and it will make it a lot easier to use a SQL PATCH to add additional hints, to a particular query block.

Let’s add a QB_NAME hint to the subquery in our simple SQL statement above and then use that Query Block name in our SQL Patch.

SELECT e.ename, e.deptno 
FROM   emp e 
WHERE  e.deptno IN (SELECT /*+QB_NAME(MY_DEPT_SQ) */ d.deptno 
                    FROM   dept d 
                    WHERE  d.loc='DALLAS');
 
DECLARE
   patch_name varchar2(100);
BEGIN
   patch_name := sys.dbms_sqldiag.create_sql_patch(
                 sql_id=>'bah0gcbrvm1gk',
                 hint_text=>'INDEX(@"MY_DEPT_SQ" "D" "DEPT_LOC_IDX")',
                 name=>'TEST_MYQB_PATCH2');
END;
/
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'bah0gcbrvm1gk',- 
                                              format=>'+alias'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID bah0gcbrvm1gk, child NUMBER 0
-------------------------------------
SELECT e.ename, e.deptno  FROM   emp e  
WHERE  e.deptno IN (SELECT /*+QB_NAME(MY_DEPT_SQ) */ d.deptno 
FROM   dept d  WHERE  d.loc='DALLAS')
 
Plan hash VALUE: 2006641677
-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |       |       |     4 (100)|          |
|*  1 |  HASH JOIN SEMI                      |              |    10 |   200 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                  | EMP          |    30 |   270 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEPT         |     1 |    11 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN                  | DEPT_LOC_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
   1 - SEL$CA735286
   2 - SEL$CA735286 / E@SEL$1
   3 - SEL$CA735286 / D@MY_DEPT_SQ
   4 - SEL$CA735286 / D@MY_DEPT_SQ
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   4 - access("D"."LOC"='DALLAS')
 
Note
-----
   - SQL patch "TEST_MYQB_PATCH2" used FOR this statement

For more complex queries, it might be easier to use the Explain Plan or Auto Trace functionality in SQL Developer to view the Query Block names. Jeff Smith, the product manager for SQL Developer, recently showed me how to do this, as the OBJECT_ALIAS column isn’t displayed there by default.

In SQL Developer, go to the main menu under the Oracle SQL Developer tab in the top left-hand corner of the screen. From there, select preferences. This will open a new window that will allow you to change the default preferences. Under Database, select the Autotrace/Explain Plan option, which will enable you to select which columns you want to have displayed in an execution plan. You want to choose OBJECT_ALIAS from the list and click OK.

Once you have set the preference, simply select the EXPLAIN or AUTO TRACE button for the statement you are interested in and the Query Block Name will appear in the plan.

Unfortunately, SQL Monitor doesn’t provide a mechanism to display the OBJECT_ALIAS column right now. But I have filed an Enhancement Request to get it added.

8 thoughts on “What are Query Block Names and how to find them”

Jonathan Lewis says:

March 10, 2020 at 10:48 am

I find it interesting, and a little irritating, that

index(@sel$2 d@sel$2 (loc))

and

index(@sel$5da710d3 d@sel$2 (loc))

are both valid ways of achieving the indexed access path.

I can’t help feeling that the former shouldn’t have worked – particularly since the hint that gets into the outline is the latter, and “sel$2” doesn’t exist by the time the optimizer has transformed the query to it’s final form.

It would be nice to have a formal statement of the correct strategy for hinting rather than having a few very simple examples that just happen to work but won’t work in similar but slightly difference circumstances.

Regards
Jonathan Lewis

Reply
Rajeshwaran, Jeyabal says:

April 13, 2020 at 7:26 am

Thanks for the details, is that the SQL Patch is tied at sql_id level? when the sql_id changes, the sql patch doesn’t work as expected. kindly confirm.

demo@PDB1> create table emp as select * from scott.emp;

Table created.

demo@PDB1> create table dept as select * from scott.dept;

Table created.

demo@PDB1> create index dept_loc_idx on dept(loc);

Index created.

demo@PDB1> create index dept_dname_idx on dept(loc,dname);

Index created.

demo@PDB1>
demo@PDB1> set serveroutput off
demo@PDB1> select empno,ename,sal
2 from emp
3 where deptno in (select deptno
4 from dept
5 where loc =’DALLAS’ );

EMPNO ENAME SAL
———- ———- ———-
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000

demo@PDB1>
demo@PDB1> set serveroutput off
demo@PDB1> select empno,ename,sal
2 from emp
3 where deptno in (select deptno
4 from dept
5 where loc =’DALLAS’ );

EMPNO ENAME SAL
———- ———- ———-
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000

demo@PDB1>
demo@PDB1> select * from table( dbms_xplan.display_cursor(format=>’+alias’));

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————
SQL_ID 8u42d326m6413, child number 0
————————————-
select empno,ename,sal from emp where deptno in (select deptno from
dept where loc =’DALLAS’ )

Plan hash value: 3597657443

——————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————-
| 0 | SELECT STATEMENT | | | | 5 (100)| |
|* 1 | HASH JOIN SEMI | | 5 | 140 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 1 | 11 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | DEPT_DNAME_IDX | 1 | | 1 (0)| 00:00:01 |
——————————————————————————————————-

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

1 – SEL$5DA710D3
2 – SEL$5DA710D3 / EMP@SEL$1
3 – SEL$5DA710D3 / DEPT@SEL$2
4 – SEL$5DA710D3 / DEPT@SEL$2

Predicate Information (identified by operation id):
—————————————————

1 – access(“DEPTNO”=”DEPTNO”)
4 – access(“LOC”=’DALLAS’)

Note
—–
– this is an adaptive plan

35 rows selected.

demo@PDB1> declare
2 l_name varchar2(40);
3 begin
4 l_name := dbms_sqldiag.create_sql_patch(
5 sql_id=>’8u42d326m6413′,
6 hint_text=>’index(@”SEL$2” “DEPT” “DEPT_LOC_IDX”)’,
7 name=>’MY_DEMO_PATCH’);
8 end;
9 /

PL/SQL procedure successfully completed.

demo@PDB1> select empno,ename,sal
2 from emp
3 where deptno in (select deptno
4 from dept
5 where loc =’DALLAS’ );

EMPNO ENAME SAL
———- ———- ———-
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000

demo@PDB1>
demo@PDB1> select * from table( dbms_xplan.display_cursor(format=>’+alias’));

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————
SQL_ID 8u42d326m6413, child number 0
————————————-
select empno,ename,sal from emp where deptno in (select deptno from
dept where loc =’DALLAS’ )

Plan hash value: 2006641677

—————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————–
| 0 | SELECT STATEMENT | | | | 5 (100)| |
|* 1 | HASH JOIN SEMI | | 5 | 140 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 1 | 11 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | DEPT_LOC_IDX | 1 | | 1 (0)| 00:00:01 |
—————————————————————————————————–

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

1 – SEL$5DA710D3
2 – SEL$5DA710D3 / EMP@SEL$1
3 – SEL$5DA710D3 / DEPT@SEL$2
4 – SEL$5DA710D3 / DEPT@SEL$2

Predicate Information (identified by operation id):
—————————————————

1 – access(“DEPTNO”=”DEPTNO”)
4 – access(“LOC”=’DALLAS’)

Note
—–
– SQL patch “MY_DEMO_PATCH” used for this statement
– this is an adaptive plan

36 rows selected.

demo@PDB1> select t1.empno,t1.ename,t1.sal
2 from emp t1
3 where t1.deptno in (select deptno
4 from dept
5 where loc =’DALLAS’ );

EMPNO ENAME SAL
———- ———- ———-
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000

demo@PDB1>
demo@PDB1> select * from table( dbms_xplan.display_cursor(format=>’+alias’));

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————
SQL_ID 1nr8vry5gc2c8, child number 0
————————————-
select t1.empno,t1.ename,t1.sal from emp t1 where t1.deptno in (select
deptno from dept where loc =’DALLAS’ )

Plan hash value: 3597657443

——————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————-
| 0 | SELECT STATEMENT | | | | 5 (100)| |
|* 1 | HASH JOIN SEMI | | 5 | 140 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 1 | 11 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | DEPT_DNAME_IDX | 1 | | 1 (0)| 00:00:01 |
——————————————————————————————————-

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

1 – SEL$5DA710D3
2 – SEL$5DA710D3 / T1@SEL$1
3 – SEL$5DA710D3 / DEPT@SEL$2
4 – SEL$5DA710D3 / DEPT@SEL$2

Predicate Information (identified by operation id):
—————————————————

1 – access(“T1″.”DEPTNO”=”DEPTNO”)
4 – access(“LOC”=’DALLAS’)

Note
—–
– this is an adaptive plan

35 rows selected.

demo@PDB1>

Reply
1. Maria Colgan says:
  
  April 13, 2020 at 8:15 am
  
  Hi Rajeshwaran,
  
  A SQL Patch can be applied based on either the SQL_ID or the SQL text. If either change the patch won’t be applied.
  
  There is no force matching with SQL Patches or SQL Plan Baselines like there is with a SQL Profile.
  
  Thanks,
  Maria
  
  Reply
Yossi Cohen says:

August 13, 2020 at 3:08 am

Thanks for your blog – you are awesome!!!
Please fix a tiny typo:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>’+alias’));

change the > to >

Reply
Alex says:

December 10, 2020 at 2:34 pm

Using SYS.DBMS_SQLDIAG.create_sql_patch function, how do I apply full table scan hint on this statement:
DELETE FROM DATA_TBL WHERE SETID = ‘SHARE’ AND PROCESS_GROUP = ‘ACCT’

Thanks.

Reply
1. Maria Colgan says:
  
  December 21, 2020 at 7:09 pm
  
  Hi Alex,
  
  Added a SQL Patch for a DELETE statement is not different than doing it for a SELECT statement other than the fact the Query Block name begins with DEL instead of SEL.
  
  Take this example:
  
  SQL> DELETE FROM foo f WHERE cust_city=’Killarney’ AND cust_income_level=’K: 250,000 – 299,999′;
  
  30 rows deleted.
  
  SQL> select * from table(dbms_xplan.display_cursor( format=>’+alias’));
  
  PLAN_TABLE_OUTPUT
  _______________________________________________________________________________
  SQL_ID 6rdcru767wprh, child number 0
  ————————————-
  Delete from foo f where cust_city=’Killarney’ and cust_income_level=’K:
  250,000 – 299,999′
  
  Plan hash value: 3280074074
  —————————————————————————-
  | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  —————————————————————————-
  | 0 | DELETE STATEMENT | | | | 1 (100)| |
  | 1 | DELETE | FOO | | | | |
  |* 2 | INDEX RANGE SCAN| MY_IND | 2 | 58 | 1 (0)| 00:00:01 |
  —————————————————————————-
  
  Query Block Name / Object Alias (identified by operation id):
  ————————————————————-
  1 – DEL$1
  2 – DEL$1 / F@DEL$1
  
  — Now we have the Query Block name and the SQL_ID, let’s create the patch
  
  SQL> DECLARE
  2 patch_name varchar2(100);
  3 BEGIN
  4 patch_name := sys.dbms_sqldiag.create_sql_patch( sql_id=>’6rdcru767wprh’, hint_text=>’FULL(@”DEL$1″ “F”)’, name=>’TEST_QB_PATCH2′);
  5 END;
  6 /
  
  PL/SQL procedure successfully completed.
  
  SQL> explain plan for Delete from foo f where cust_city=’Killarney’ and cust_income_level=’K: 250,000 – 299,999′;
  
  Explained.
  
  SQL> select * from table(dbms_xplan.display());
  
  PLAN_TABLE_OUTPUT
  ______________________________________________________________________________________
  Plan hash value: 1926921011
  ———————————————————————————–
  | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  ———————————————————————————–
  | 0 | DELETE STATEMENT | | 2 | 58 | 9 (0)| 00:00:01 |
  | 1 | DELETE | FOO | | | | |
  |* 2 | TABLE ACCESS STORAGE FULL| FOO | 2 | 58 | 9 (0)| 00:00:01 |
  ———————————————————————————–
  
  Predicate Information (identified by operation id):
  —————————————————
  filter(“CUST_CITY”=’Killarney’ AND “CUST_INCOME_LEVEL”=’K: 250,000
  – 299,999′)
  
  Note
  —–
  – SQL patch “TEST_QB_PATCH2” used for this statement
  
  Reply
Chuck O'Neal says:

January 28, 2022 at 4:24 pm

Maria,

Maria,

This article has been a huge help! I’ll not do a full confession on how may times I had to reread this to actually get it to work for me. In my case the Query Block Name produced the full table name and not the alias, e.g., 3 – SEL$5DA710D3 / DEPT@SEL$2. Is there a reason behind this difference? The alias will not work in the patch. The full table name does.

I’m with Jonathan on the desire to see better official coverage of this insanely useful tool. Your examples here are the only ones I have found that really demonstrate query block notation with SQL Patches. I also appreciate the visual aids with the color highlighting. Circles and lines between the XPLAN and procedure call might have helped me see it sooner, but I finally made it.

Regards,
Chuck

Reply
Sanjay says:

January 22, 2023 at 7:57 pm

Can you explain double quotes around query block ? Is it required ?
Based on my testing, I have found double quotes makes object name or query block case sensitive.

hint_text=>’INDEX(@”MY_DEPT_SQ” “D” “DEPT_LOC_IDX”)’,

is not same as since lower case index will not be found. This will not work since index name dept_LOC_IDX does not exists ( assuming index was created by default using upper case )

hint_text=>’INDEX(@”MY_DEPT_SQ” “D” “dept_LOC_IDX”)’,

Removed double quotes, it still works
hint_text=>’INDEX(@my_dept_SQ D dept_LOC_idx )’,
—–
This is my test case, removed double quotes

—-
declare
patch_name dba_sql_patches.name%TYPE := ‘SANJAY_TEST_PATCH’;
begin
patch_name := dbms_sqldiag.create_sql_patch( sql_id=>’a1j005c8tf2mc’, hint_text=>’INDEX(@sel$2 DEPARTMENTS i_DEPT_loc )’, name => patch_name);
end ;

——–

SELECT * FROM employees where DEPARTMENT_ID in ( Select
DEPARTMENT_ID from DEPARTMENTS where LOCATION_ID=1700)
SQL_ID a1j005c8tf2mc, child number 0
Plan hash value: 2477167509

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | | | 4 (100)| |

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
|* 1 | HASH JOIN | | 106 | 8056 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS | 21 | 147 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_DEPT_LOC | 21 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7383 | 2 (0)| 00:00:01 |
—————————————————————————————————-

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

1 – SEL$5DA710D3
2 – SEL$5DA710D3 / DEPARTMENTS@SEL$2

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
3 – SEL$5DA710D3 / DEPARTMENTS@SEL$2
4 – SEL$5DA710D3 / EMPLOYEES@SEL$1

Predicate Information (identified by operation id):
—————————————————

1 – access(“DEPARTMENT_ID”=”DEPARTMENT_ID”)
3 – access(“LOCATION_ID”=1700)

Column Projection Information (identified by operation id):
———————————————————–

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————

1 – (#keys=1; rowset=256) “DEPARTMENT_ID”[NUMBER,22],
“EMPLOYEES”.”EMPLOYEE_ID”[NUMBER,22], “EMPLOYEES”.”FIRST_NAME”[VARCHAR2,20],
“EMPLOYEES”.”LAST_NAME”[VARCHAR2,25], “EMPLOYEES”.”EMAIL”[VARCHAR2,25],
“EMPLOYEES”.”PHONE_NUMBER”[VARCHAR2,20], “EMPLOYEES”.”HIRE_DATE”[DATE,7],
“EMPLOYEES”.”JOB_ID”[VARCHAR2,10], “EMPLOYEES”.”SALARY”[NUMBER,22],
“EMPLOYEES”.”COMMISSION_PCT”[NUMBER,22], “EMPLOYEES”.”MANAGER_ID”[NUMBER,22]
2 – “DEPARTMENT_ID”[NUMBER,22]
3 – “DEPARTMENTS”.ROWID[ROWID,10]
4 – (rowset=256) “EMPLOYEES”.”EMPLOYEE_ID”[NUMBER,22],
“EMPLOYEES”.”FIRST_NAME”[VARCHAR2,20], “EMPLOYEES”.”LAST_NAME”[VARCHAR2,25],

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
“EMPLOYEES”.”EMAIL”[VARCHAR2,25], “EMPLOYEES”.”PHONE_NUMBER”[VARCHAR2,20],
“EMPLOYEES”.”HIRE_DATE”[DATE,7], “EMPLOYEES”.”JOB_ID”[VARCHAR2,10],
“EMPLOYEES”.”SALARY”[NUMBER,22], “EMPLOYEES”.”COMMISSION_PCT”[NUMBER,22],
“EMPLOYEES”.”MANAGER_ID”[NUMBER,22], “DEPARTMENT_ID”[NUMBER,22]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
—————————————————————————

2 – SEL$5DA710D3 / DEPARTMENTS@SEL$2
– INDEX(@sel$2 DEPARTMENTS i_DEPT_loc )

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————

Note
—–
– SQL patch “SANJAY_TEST_PATCH” used for this statement

Reply

What are Query Block Names and how to find them

What are query blocks?

How do I find the name of a query block?

Like this:

Related

8 thoughts on “What are Query Block Names and how to find them”

Leave a Reply Cancel reply

What are query blocks?

How do I find the name of a query block?

Share this:

Like this:

Related

8 thoughts on “What are Query Block Names and how to find them”

Leave a Reply Cancel reply