About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文(172)
分类: Oracle
2022-04-12 10:26:22
DROP TABLE t; CREATE TABLE t AS SELECT * FROM dba_objects; CREATE INDEX idx1_t ON t(owner,object_id); CREATE INDEX idx2_t ON t(object_id,owner); BEGIN dbms_stats.gather_table_stats(ownname => USER,tabname => 'T',estimate_percent => 100,cascade => TRUE); END; / dingjun123@ORADB> SELECT COUNT(DISTINCT owner),COUNT(DISTINCT object_id),COUNT(*) FROM t; COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_ID) COUNT(*) -------------------- ------------------------ ---------- 33 75250 75251 1 row selected. |
dingjun123@ORADB> SELECT/*+index(t idx1_t)*/ * FROM t 2 WHERE owner='DINGJUN123' AND object_id=75677; 1 row selected. Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 2071967826 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 97 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX1_T | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='DINGJUN123' AND "OBJECT_ID"=75677) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1403 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed dingjun123@ORADB> SELECT/*+index(t idx2_t)*/ * FROM t 2 WHERE owner='DINGJUN123' AND object_id=75677; 1 row selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 3787301248 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 97 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX2_T | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=75677 AND "OWNER"='DINGJUN123') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1403 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
dingjun123@ORADB> SELECT * FROM t 2 WHERE owner='DINGJUN123' 3 AND object_id>=107889; 1 row selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 2071967826 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 96 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 96 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX1_T | 1 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='DINGJUN123' AND "OBJECT_ID">=107889 AND "OBJECT_ID" IS NOT NULL) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 1399 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
dingjun123@ORADB> SELECT/*+index(t idx2_t)*/ * FROM t 2 WHERE owner='DINGJUN123' 3 AND object_id>=107889; 1 row selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 3787301248 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 96 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 96 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX2_T | 1 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID">=107889 AND "OWNER"='DINGJUN123' AND "OBJECT_ID" IS NOT NULL) filter("OWNER"='DINGJUN123') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 1399 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
dingjun123@ORADB> SELECT * FROM t 2 WHERE owner='DINGJUN123' 3 AND object_id>=100; 2540 rows selected. Elapsed: 00:00:00.15 Execution Plan ---------------------------------------------------------- Plan hash value: 2071967826 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2539 | 238K| 499 (0)| 00:00:06 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 2539 | 238K| 499 (0)| 00:00:06 | |* 2 | INDEX RANGE SCAN | IDX1_T | 2539 | | 12 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='DINGJUN123' AND "OBJECT_ID">=100 AND "OBJECT_ID" IS NOT NULL) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 527 consistent gets 21 physical reads 0 redo size 268134 bytes sent via SQL*Net to client 2275 bytes received via SQL*Net from client 171 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2540 rows processed |
dingjun123@ORADB> SELECT/*+index(t idx2_t)*/ * FROM t 2 WHERE owner='DINGJUN123' 3 AND object_id>=100; 2540 rows selected. Elapsed: 00:00:00.33 Execution Plan ---------------------------------------------------------- Plan hash value: 3787301248 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2539 | 238K| 3762 (1)| 00:00:46 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 2539 | 238K| 3762 (1)| 00:00:46 | |* 2 | INDEX RANGE SCAN | IDX2_T | 2539 | | 3274 (1)| 00:00:40 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID">=100 AND "OWNER"='DINGJUN123' AND "OBJECT_ID" IS NOT NULL) filter("OWNER"='DINGJUN123') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3763 consistent gets 0 physical reads 0 redo size 268134 bytes sent via SQL*Net to client 2275 bytes received via SQL*Net from client 171 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2540 rows processed dingjun123@ORADB> SELECT COUNT(*) FROM t WHERE object_id >= 100; COUNT(*) ---------- 1032649 |
dingjun123@ORADB> SELECT * FROM t 2 WHERE owner>='DINGJUN123' 3 AND object_id>=107872; 37 rows selected. Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 3787301248 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 205 | 19680 | 43 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 205 | 19680 | 43 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX2_T | 205 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID">=107872 AND "OWNER">='DINGJUN123' AND "OBJECT_ID" IS NOT NULL) filter("OWNER">='DINGJUN123') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 14 consistent gets 0 physical reads 0 redo size 6468 bytes sent via SQL*Net to client 438 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 37 rows processed dingjun123@ORADB> SELECT COUNT(*) FROM t WHERE object_id>=107872; COUNT(*) ---------- 37 1 row selected. |
dingjun123@ORADB> SELECT/*+index(t idx1_t)*/ * FROM t 2 WHERE owner>='DINGJUN123' 3 AND object_id>=107872; 37 rows selected. Elapsed: 00:00:00.23 Execution Plan ---------------------------------------------------------- Plan hash value: 2071967826 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 205 | 19680 | 3740 (1)| 00:00:45 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 205 | 19680 | 3740 (1)| 00:00:45 | |* 2 | INDEX RANGE SCAN | IDX1_T | 205 | | 3700 (1)| 00:00:45 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER">='DINGJUN123' AND "OBJECT_ID">=107872 AND "OWNER" IS NOT NULL) filter("OBJECT_ID">=107872) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3688 consistent gets 0 physical reads 0 redo size 6468 bytes sent via SQL*Net to client 438 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 37 rows processed |