Use the use_concat prompt
- ISE USE_CONCAT HINTS in Oracle
Last Updated:
THURSDAY, 2004-11-18 21:48
Eygle
Use_concat Tips Forced Each OR predicate in the optimizer extension inquiry is a separate query block. Finally, merge all the results of the query block, return the result collection to the user.
When using multiple in-lists queries, Oracle may choose to extend a single query into multiple query blocks.
Use the USE_CONCAT Tip Example:
1. Test using Scott users and standard tables
$ SQLPLUS Scott / Tiger
SQL * Plus: Release 9.2.0.4.0 - Production on Wed Nov 17 15:17:51 2004
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the partitioning, OLAP AND ORACLE DATA MINING OPTIONS
JServer Release 9.2.0.4.0 - Production
SQL> Set autotrace on
SQL> SELECT * FROM EMP WHERE EMPNO IN (7788, 7900);
Empno ename Job Mgr Hiredate Sal Comm Deptno
---------- ------------------------------------- -------- --------------------
7788 Scott Analyst 7566 19-APR-87 3000 20
7900 James Clerk 7698 03-DEC-81 950 30
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 2 Card = 2 Bytes = 74)
1 0 Table Access (Full) of 'EMP' (COST = 2 Card = 2 bytes = 74)
- Note that Oracle here is a full table scan because there is a low cost.
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
0 DB Block Get
4 consistent gets
0 Physical READS
0 redo size
1032 BYTES SENT VIA SQL * NET to Client
655 BYtes Received Via SQL * Net from Client
2 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
2 rows proped
2. Add prompt
SQL> SELECT / * USE_CONCAT * / * FROM EMP WHERE EMPNO IN (7788, 7900);
Empno ename Job Mgr Hiredate Sal Comm Deptno ------------------------------------ --- ------ ----------------------------
7900 James Clerk 7698 03-DEC-81 950 30
7788 Scott Analyst 7566 19-APR-87 3000 20
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 4 Card = 2 Bytes = 74)
1 0 ConcateNation
2 1 Table Access (by index rowid) of 'EMP' (COST = 2 card = 1 Bytes = 37)
3 2 INDEX (Unique Scan) of 'PK_EMP' (COST = 1 Card = 14)
4 1 Table Access (By Index Rowid) of 'EMP' (COST = 2 Card = 1 Bytes = 37)
5 4 Index (Unique Scan) of 'PK_EMP' (COST = 1 Card = 14)
- After using the USE_CONCAT prompt, Oracle expands the In-Lists condition to two query blocks, separately using the index, and finally ConcateNation gets the last output.
- Note that in force here the index is rising to 4.
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
0 DB Block Get
4 consistent gets
0 Physical READS
0 redo size
1032 BYTES SENT VIA SQL * NET to Client
655 BYtes Received Via SQL * Net from Client
2 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
2 rows proped
SQL>
3. Oracle's rewriting for the implementation plan
For Inlist queries, Oracle usually rewrites,
Select ..... from ....... WHERE ....IN (........)
SQL statements, rewrite to union all, this rewrite is usually potential.
However, this change may have problems. If the value in the inList is more, the time and cost of CBO spending on the analysis execution path will be quite large. At this time, we usually need to block this unfolded operation of Oracle. We can prompt via No_expand To prevent Oracle from making such a rewrite.
So actually, here, using_concat and no_expand become "reverse function". After using the NO_EXPAND prompt, from ORACLE8, Oracle uses the "Inlist Iterator" mode to perform SQL, which can be used in INDEX. Author: eygle, Oracle technology followers, Oracle technical forum itpub.www.eygle.com from China is the biggest author's personal site you may contact the author by Guoqiang.Gai@gmail.com welcome to explore technical exchanges and links. exchange.
Original source:
http://www.eygle.com/sql/how.to.use.use_concat.hints.in.Orcle.htm
