全表扫描提示
SYS@ prod> grant plustrace to hr ;
Grant succeeded.
SYS@ prod> conn hr/hr
Connected.
HR@ prod> set autotrace on
HR@ prod> select /*+ full(employees) */ first_name from employees where employee_id = 100 ;
FIRST_NAME
--------------------
Steven
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 11 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
Statistics
----------------------------------------------------------
736 recursive calls
0 db block gets
193 consistent gets
6 physical reads
0 redo size
532 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
HR@ prod> select first_name from employees where employee_id = 100 ;
FIRST_NAME
--------------------
Steven
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 11 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
532 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1rows processed
按顺序联结提示,表联结会按照FROM后面的顺序进行。
SH@ prod> set autotrace on
SH@ prod> select /*+ ordered */ promo_name , cust_first_name , prod_name , amount_sold from sales , promotions , customers , products
2 where sales.prod_id = 100 ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2619432180
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 713G| 43T| 13G (1)|999:59:59 | | |
| 1 | MERGE JOIN CARTESIAN | | 713G| 43T| 13G (1)|999:59:59 | | |
| 2 | MERGE JOIN CARTESIAN | | 9910M| 378G| 71M (1)|239:56:32 | | |
| 3 | MERGE JOIN CARTESIAN | | 178K| 5928K| 5582 (1)| 00:01:07 | | |
| 4 | PARTITION RANGE ALL | | 355 | 3195 | 94 (0)| 00:00:02 | 1 | 28 |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 355 | 3195 | 94 (0)| 00:00:02 | 1 | 28 |
| 6 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 7 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | 1 | 28 |
| 8 | BUFFER SORT | | 503 | 12575 | 5487 (1)| 00:01:06 | | |
| 9 | TABLE ACCESS FULL | PROMOTIONS | 503 | 12575 | 15 (0)| 00:00:01 | | |
| 10 | BUFFER SORT | | 55500 | 379K| 71M (1)|239:56:32 | | |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 379K| 403 (1)| 00:00:05 | | |
| 12 | BUFFER SORT | | 72 | 1872 | 13G (1)|999:59:59 | | |
| 13 | TABLE ACCESS FULL | PRODUCTS | 72 | 1872 | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("SALES"."PROD_ID"=100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
559 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0rows processed
去掉提示后,PRODUCTS与CUSTOMERS的顺序发生了颠倒。
SH@ prod> select promo_name , cust_first_name , prod_name , amount_sold from sales , promotions , customers , products
2 where sales.prod_id = 100 ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3204799813
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 713G| 43T| 5182M (1)|999:59:59 | | |
| 1 | MERGE JOIN CARTESIAN | | 713G| 43T| 5182M (1)|999:59:59 | | |
| 2 | MERGE JOIN CARTESIAN | | 12M| 735M| 247K (1)| 00:49:32 | | |
| 3 | MERGE JOIN CARTESIAN | | 178K| 5928K| 5582 (1)| 00:01:07 | | |
| 4 | PARTITION RANGE ALL | | 355 | 3195 | 94 (0)| 00:00:02 | 1 | 28 |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 355 | 3195 | 94 (0)| 00:00:02 | 1 | 28 |
| 6 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 7 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | 1 | 28 |
| 8 | BUFFER SORT | | 503 | 12575 | 5487 (1)| 00:01:06 | | |
| 9 | TABLE ACCESS FULL | PROMOTIONS | 503 | 12575 | 15 (0)| 00:00:01 | | |
| 10 | BUFFER SORT | | 72 | 1872 | 247K (1)| 00:49:32 | | |
| 11 | TABLE ACCESS FULL | PRODUCTS | 72 | 1872 | 1 (0)| 00:00:01 | | |
| 12 | BUFFER SORT | | 55500 | 379K| 5182M (1)|999:59:59 | | |
| 13 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 379K| 403 (1)| 00:00:05 | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("SALES"."PROD_ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
559 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0rows processed
指定索引的指示INDEX(table_name [index_name]),如果不指定索引名,表示访问这个表要使用索引。
HR@ prod> create table employees1 as select * from employees ;
create table employees1 as select * from employees
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
HR@ prod> create index test_idx1 on employees1 ( employee_id ) ;
Index created.
HR@ prod> create index test_idx2 on employees1 ( employee_id , first_name ) ;
Index created.
Oracle在默认的情况下当然会使用单列索引,而不用复合索引。
HR@ prod> select last_name from employees1 where employee_id = 100 ;
LAST_NAME
-------------------------
King
Execution Plan
----------------------------------------------------------
Plan hash value: 2613790398
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES1 | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
下面这种只指定索引的写法是错的,会被优化器忽略。
HR@ prod> select /*+ index(test_idx2) */ last_name from employees1 where employee_id = 100 ;
LAST_NAME
-------------------------
King
Execution Plan
----------------------------------------------------------
Plan hash value: 2613790398
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES1 | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
正确的写法
HR@ prod> select /*+ index(employees1 test_idx2) */ last_name from employees1 where employee_id = 100 ;
LAST_NAME
-------------------------
King
Execution Plan
----------------------------------------------------------
Plan hash value: 2093088777
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES1 | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX2 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1rows processed
常见的联结提示:
Use_merge()
Use_ln()
Use_hash()
Leading()
使用提示时要注意,如果表有别名,一定要用别名,否则提示无效。而且,表名一定不能有用户名来限制。
Oracle 用hint来影响执行计划
最新推荐文章于 2021-04-07 02:33:12 发布