背景：
一张2000万的数据表和一张50万的数据表关联查询，关联的条件是=号和>号，还有一些其他等值条件，查询时间要15秒，关键这条语句要查询好多次，在SQL ordered by Reads、SQL ordered by User I/O Wait Time、SQL ordered by Elapsed Time中都能看到这条语句的身影，所以必须要优化。
在家里模拟了相关过程
第一步创建模拟表
第二步分析数据分布状况
第三步不加索引看执行计划
第四步保持原有索引看执行计划
第五步改变为组合索引看执行计划
最后结论是走组合索引方式。
代码示例
--要执行的SQL语句
-
SELECT count(*) from ( -
SELECT a.tid,max(b.sysncdate) -
FROM maintable a,othertable b -
WHERE a.tid=b.logid -
AND a.sysncdate>b.sysncdate -
AND b.randomi=1 -
AND a.owner='SYS' -
GROUP BY a.tid);
--创建模拟表
CREATE TABLE maintable as
SELECT a.*,
DBMS_RANDOM.STRING('l',4) tid,
SYSDATE-TRUNC(DBMS_RANDOM.VALUE(1,10001))/24/60 sysncdate
FROM dba_tables a,
(
SELECT level,ROWNUM rn
FROM DUAL
CONNECT BY ROWNUM<=3000
);
CREATE TABLE othertable as
SELECT TRUNC(DBMS_RANDOM.VALUE(1,101)) as randomi,
DBMS_RANDOM.string('~',15) as random_,
DBMS_RANDOM.string('l',15) as randoml,
DBMS_RANDOM.string('a',15) as randoma,
DBMS_RANDOM.string('A',15) as randomuppera,
DBMS_RANDOM.string('u',15) as randomu,
DBMS_RANDOM.string('U',15) as randomupperu,
DBMS_RANDOM.string('x',15) as randomx,
DBMS_RANDOM.string('X',15) as randomupperx,
DBMS_RANDOM.string('p',15) as randomp,
DBMS_RANDOM.string('P',15) as randomupperp,
a.tid as logid,
SYSDATE-TRUNC(DBMS_RANDOM.VALUE(1,10001))/24/60 sysncdate
FROM (SELECT DISTINCT tid FROM maintable ) a
--分析表和列的分布
SELECT A.owner,a.table_name,a.num_rows/1024/1024,a.blocks,a.avg_row_len,a.last_analyzed
FROM DBA_TABLES A
WHERE A.TABLE_NAME IN ('MAINTABLE','OTHERTABLE');
SELECT A.owner,a.segment_name,a.segment_type,a. bytes/1024/1024,a.blocks
FROM DBA_SEGMENTS A
WHERE A.SEGMENT_NAME IN ('MAINTABLE','OTHERTABLE');
SELECT A.owner,a.table_name,a.column_name,a.data_type,a.num_distinct,a.density，a.num_nulls
FROM DBA_TAB_COLUMNS A
WHERE A.TABLE_NAME IN ('MAINTABLE','OTHERTABLE');
SELECT a.owner,a.index_name,a.index_type,a.table_name,a.clustering_factor,a.num_rows,a.degree,a.last_analyzed
FROM DBA_INDEXES A
WHERE A.TABLE_NAME IN ('MAINTABLE','OTHERTABLE');
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as usera@ORCL
SQL>
SQL> SELECT A.owner,a.table_name,a.num_rows/1024/1024,a.blocks,a.avg_row_len,a.last_analyzed
2 FROM DBA_TABLES A
3 WHERE A.TABLE_NAME IN ('MAINTABLE','OTHERTABLE');
OWNER TABLE_NAME A.NUM_ROWS/1024/1024 BLOCKS AVG_ROW_LEN LAST_ANALYZED
------------------------------ ------------------------------ -------------------- ---------- ----------- -------------
USERA MAINTABLE 8.11150646209717 311289 259 2020-11-22 0:
USERA OTHERTABLE 0.441422462463379 11869 179 2020-11-22 0:
SQL> SELECT A.owner,a.segment_name,a.segment_type,a. bytes/1024/1024,a.blocks
2 FROM DBA_SEGMENTS A
3 WHERE A.SEGMENT_NAME IN ('MAINTABLE','OTHERTABLE');
OWNER SEGMENT_NAME SEGMENT_TYPE A.BYTES/1024/1024 BLOCKS
------------------------------ -------------------------------------------------------------------------------- ------------------ ----------------- ----------
USERA OTHERTABLE TABLE 96 12288
USERA MAINTABLE TABLE 2432 311296
SQL> SELECT A.owner,a.table_name,a.column_name,a.data_type,a.num_distinct,a.density，a.num_nulls
2 FROM DBA_TAB_COLUMNS A
3 WHERE A.TABLE_NAME IN ('MAINTABLE','OTHERTABLE');
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE NUM_DISTINCT DENSITY NUM_NULLS
------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------ ---------- ----------
USERA OTHERTABLE SYSNCDATE DATE 10016 9.98402555 0
USERA OTHERTABLE LOGID VARCHAR2 462865 2.16045715 0
USERA OTHERTABLE RANDOMUPPERP VARCHAR2 462865 2.16045715 0
USERA OTHERTABLE RANDOMP VARCHAR2 462865 2.16045715 0
USERA OTHERTABLE RANDOMUPPERX VARCHAR2 462865 2.16045715 0
USERA OTHERTABLE RANDOMX VARCHAR2 462865 2.16045715 0
USERA OTHERTABLE RANDOMUPPERU VARCHAR2 462865 2.16045715 0
USERA OTHERTABLE RANDOMU VARCHAR2 462865 2.16045715 0
USERA OTHERTABLE RANDOMUPPERA VARCHAR2 462865 2.16045715 0
USERA OTHERTABLE RANDOMA VARCHAR2 462865 2.16045715 0
USERA OTHERTABLE RANDOML VARCHAR2 462865 2.16045715 0
USERA OTHERTABLE RANDOM_ VARCHAR2 462865 2.16045715 0
USERA OTHERTABLE RANDOMI NUMBER 100 0.01 0
USERA MAINTABLE SYSNCDATE DATE 10000 0.0001 0
USERA MAINTABLE TID VARCHAR2 439709 2.27423136 0
USERA MAINTABLE RESULT_CACHE VARCHAR2 1 1 0
USERA MAINTABLE SEGMENT_CREATED VARCHAR2 3 0.33333333 0
USERA MAINTABLE READ_ONLY VARCHAR2 1 1 0
USERA MAINTABLE DROPPED VARCHAR2 1 1 0
USERA MAINTABLE COMPRESS_FOR VARCHAR2 2 0.5 7673823
USERA MAINTABLE COMPRESSION VARCHAR2 2 0.5 192533
USERA MAINTABLE DEPENDENCIES VARCHAR2 1 1 0
USERA MAINTABLE CLUSTER_OWNER VARCHAR2 1 1 8393696
USERA MAINTABLE MONITORING VARCHAR2 2 0.5 0
USERA MAINTABLE SKIP_CORRUPT VARCHAR2 1 1 0
USERA MAINTABLE DURATION VARCHAR2 2 0.5 8171771
USERA MAINTABLE USER_STATS VARCHAR2 1 1 0
USERA MAINTABLE GLOBAL_STATS VARCHAR2 2 0.5 0
USERA MAINTABLE ROW_MOVEMENT VARCHAR2 2 0.5 0
USERA MAINTABLE CELL_FLASH_CACHE VARCHAR2 1 1 0
USERA MAINTABLE FLASH_CACHE VARCHAR2 1 1 0
USERA MAINTABLE BUFFER_POOL VARCHAR2 1 1 0
USERA MAINTABLE NESTED VARCHAR2 2 0.5 0
USERA MAINTABLE SECONDARY VARCHAR2 2 0.5 0
USERA MAINTABLE TEMPORARY VARCHAR2 2 0.5 0
USERA MAINTABLE IOT_TYPE VARCHAR2 2 0.5 7870239
USERA MAINTABLE PARTITIONED VARCHAR2 2 0.5 0
USERA MAINTABLE LAST_ANALYZED DATE 539 0.00185528 417582
USERA MAINTABLE SAMPLE_SIZE NUMBER 369 0.00271002 417582
USERA MAINTABLE TABLE_LOCK VARCHAR2 1 1 0
USERA MAINTABLE CACHE VARCHAR2 1 1 0
USERA MAINTABLE INSTANCES VARCHAR2 2 0.5 0
USERA MAINTABLE DEGREE VARCHAR2 1 1 0
USERA MAINTABLE NUM_FREELIST_BLOCKS NUMBER 1 1 896846
USERA MAINTABLE AVG_SPACE_FREELIST_BLOCKS NUMBER 1 1 417582
USERA MAINTABLE AVG_ROW_LEN NUMBER 236 0.00423728 417582
USERA MAINTABLE CHAIN_CNT NUMBER 1 1 417582
USERA MAINTABLE AVG_SPACE NUMBER 6 0.16666666 417582
USERA MAINTABLE EMPTY_BLOCKS NUMBER 6 0.16666666 896846
USERA MAINTABLE BLOCKS NUMBER 94 0.01063829 896846
USERA MAINTABLE NUM_ROWS NUMBER 378 0.00264550 417582
USERA MAINTABLE BACKED_UP VARCHAR2 1 1 0
USERA MAINTABLE LOGGING VARCHAR2 2 0.5 678189
USERA MAINTABLE FREELIST_GROUPS NUMBER 1 1 6237550
USERA MAINTABLE FREELISTS NUMBER 1 1 6237550
USERA MAINTABLE PCT_INCREASE NUMBER 0 1.17570554 8505531
USERA MAINTABLE MAX_EXTENTS NUMBER 1 1 3531023
USERA MAINTABLE MIN_EXTENTS NUMBER 1 1 3531023
USERA MAINTABLE NEXT_EXTENT NUMBER 6 0.16666666 3515751
USERA MAINTABLE INITIAL_EXTENT NUMBER 18 0.05555555 3515751
USERA MAINTABLE MAX_TRANS NUMBER 2 0.5 192533
USERA MAINTABLE INI_TRANS NUMBER 6 0.16666666 192533
USERA MAINTABLE PCT_USED NUMBER 3 0.33333333 5748851
USERA MAINTABLE PCT_FREE NUMBER 6 0.16666666 192533
USERA MAINTABLE STATUS VARCHAR2 1 1 0
USERA MAINTABLE IOT_NAME VARCHAR2 50 0.02 8355896
USERA MAINTABLE CLUSTER_NAME VARCHAR2 10 0.1 8393696
USERA MAINTABLE TABLESPACE_NAME VARCHAR2 9 0.11111111 1014992
USERA MAINTABLE TABLE_NAME VARCHAR2 2812 0.00035561 0
USERA MAINTABLE OWNER VARCHAR2 30 0.03333333 0
70 rows selected
SQL> SELECT a.owner,a.index_name,a.index_type,a.table_name,a.clustering_factor,a.num_rows,a.degree,a.last_analyzed
2 FROM DBA_INDEXES A
3 WHERE A.TABLE_NAME IN ('MAINTABLE','OTHERTABLE');
OWNER INDEX_NAME INDEX_TYPE TABLE_NAME CLUSTERING_FACTOR NUM_ROWS DEGREE LAST_ANALYZED
------------------------------ ------------------------------ --------------------------- ------------------------------ ----------------- ---------- ---------------------------------------- -------------
SQL> EXPLAIN PLAN FOR
2 SELECT count(*) from (
3 SELECT a.tid,max(b.sysncdate)
4 FROM maintable a,othertable b
5 WHERE a.tid=b.logid
6 AND a.sysncdate>b.sysncdate
7 AND b.randomi=1
8 AND a.owner='SYS'
9 GROUP BY a.tid);
Explained
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2942004947
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 87839 (1)| 00:17:
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | VIEW | | 3085 | | 87839 (1)| 00:17:
| 3 | HASH GROUP BY | | 3085 | 92550 | 87839 (1)| 00:17:
|* 4 | HASH JOIN | | 3085 | 92550 | 87838 (1)| 00:17:
|* 5 | TABLE ACCESS FULL| OTHERTABLE | 4629 | 60177 | 3229 (1)| 00:00:
|* 6 | TABLE ACCESS FULL| MAINTABLE | 283K| 4706K| 84607 (1)| 00:16:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."TID"="B"."LOGID")
filter("A"."SYSNCDATE">"B"."SYSNCDATE")
5 - filter("B"."RANDOMI"=1)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
6 - filter("A"."OWNER"='SYS')
21 rows selected
SQL> SQL>
SQL> create index maintable_index_tid on maintable (tid);
Index created
SQL> create index maintable_index_sysncdate on maintable (sysncdate);
Index created
SQL> create index othertable_index_logid on othertable (logid);
Index created
SQL> create index othertable_index_sysncdate on othertable (sysncdate);
Index created
SQL> analyze table maintable estimate statistics sample 5 percent;
Table analyzed
SQL> analyze table othertable estimate statistics sample 5 percent;
Table analyzed
SQL> EXPLAIN PLAN FOR
2 SELECT count(*) from (
3 SELECT a.tid,max(b.sysncdate)
4 FROM maintable a,othertable b
5 WHERE a.tid=b.logid
6 AND a.sysncdate>b.sysncdate
7 AND b.randomi=1
8 AND a.owner='SYS'
9 GROUP BY a.tid);
Explained
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2942004947
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 88495 (2)| 00:17:
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | VIEW | | 3085 | | 88495 (2)| 00:17:
| 3 | HASH GROUP BY | | 3085 | 92550 | 88495 (2)| 00:17:
|* 4 | HASH JOIN | | 3085 | 92550 | 88494 (2)| 00:17:
|* 5 | TABLE ACCESS FULL| OTHERTABLE | 4629 | 60177 | 3236 (1)| 00:00:
|* 6 | TABLE ACCESS FULL| MAINTABLE | 283K| 4706K| 85255 (2)| 00:17:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."TID"="B"."LOGID")
filter("A"."SYSNCDATE">"B"."SYSNCDATE")
5 - filter("B"."RANDOMI"=1)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
6 - filter("A"."OWNER"='SYS')
21 rows selected
SQL> EXPLAIN PLAN FOR
2 select count(*) from (
3 select /*+INDEX(a maintable_index_tid) INDEX(b othertable_index_logid)*/ a.tid,max(b.sysncdate)
4 from maintable a,othertable b
5 where a.tid=b.logid
6 and a.sysncdate>b.sysncdate
7 and b.randomi=1
8 and a.owner='SYS'
9 group by a.tid);
Explained
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1035171181
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Byte
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | VIEW | | 3085 |
| 3 | SORT GROUP BY NOSORT | | 3085 | 9255
| 4 | NESTED LOOPS | | |
| 5 | NESTED LOOPS | | 3085 | 9255
|* 6 | TABLE ACCESS BY INDEX ROWID| OTHERTABLE | 4629 | 6017
| 7 | INDEX FULL SCAN | OTHERTABLE_INDEX_LOGID | 469K|
|* 8 | INDEX RANGE SCAN | MAINTABLE_INDEX_TID | 19 |
|* 9 | TABLE ACCESS BY INDEX ROWID | MAINTABLE | 1 | 1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
6 - filter("B"."RANDOMI"=1)
8 - access("A"."TID"="B"."LOGID")
9 - filter("A"."OWNER"='SYS' AND "A"."SYSNCDATE">"B"."SYSNCDATE")
23 rows selected
SQL> drop index maintable_index_tid;
Index dropped
SQL> drop index maintable_index_sysncdate;
Index dropped
SQL> drop index othertable_index_sysncdate;
Index dropped
SQL> drop index othertable_index_logid;
Index dropped
SQL> create index maintable_index on maintable (tid,sysncdate);
Index created
SQL> create index othertable_index on othertable (logid,sysncdate);
Index created
SQL> analyze table maintable estimate statistics sample 5 percent;
Table analyzed
SQL> analyze table othertable estimate statistics sample 5 percent;
Table analyzed
SQL>
SQL> explain plan for
2 select count(*) from (
3 select a.tid,max(b.sysncdate)
4 from maintable a,othertable b
5 where a.tid=b.logid
6 and a.sysncdate>b.sysncdate
7 and b.randomi=1
8 and a.owner='SYS'
9 group by a.tid);
Explained
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2209219092
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5650
| 1 | SORT AGGREGATE | | 1 | |
| 2 | VIEW | | 3085 | | 5650
| 3 | HASH GROUP BY | | 3085 | 92550 | 5650
| 4 | NESTED LOOPS | | | |
| 5 | NESTED LOOPS | | 3085 | 92550 | 5650
|* 6 | TABLE ACCESS FULL | OTHERTABLE | 4629 | 60177 | 323
|* 7 | INDEX RANGE SCAN | MAINTABLE_INDEX | 10 | |
|* 8 | TABLE ACCESS BY INDEX ROWID| MAINTABLE | 1 | 17 | 1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("B"."RANDOMI"=1)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
7 - access("A"."TID"="B"."LOGID" AND "A"."SYSNCDATE">"B"."SYSNCDATE" AND
"A"."SYSNCDATE" IS NOT NULL)
8 - filter("A"."OWNER"='SYS')
23 rows selected
SQL> explain plan for
2 select count(*) from (
3 select /*+INDEX(a maintable_index) INDEX(b othertable_index)*/ a.tid,max(b.sysncdate)
4 from maintable a,othertable b
5 where a.tid=b.logid
6 and a.sysncdate>b.sysncdate
7 and b.randomi=1
8 and a.owner='SYS'
9 group by a.tid);
Explained
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3283341491
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Co
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | |
| 1 | SORT AGGREGATE | | 1 | |
| 2 | VIEW | | 3085 | |
| 3 | SORT GROUP BY NOSORT | | 3085 | 92550 |
| 4 | NESTED LOOPS | | | |
| 5 | NESTED LOOPS | | 3085 | 92550 |
|* 6 | TABLE ACCESS BY INDEX ROWID| OTHERTABLE | 4629 | 60177 |
| 7 | INDEX FULL SCAN | OTHERTABLE_INDEX | 464K| | 1
|* 8 | INDEX RANGE SCAN | MAINTABLE_INDEX | 10 | |
|* 9 | TABLE ACCESS BY INDEX ROWID | MAINTABLE | 1 | 17 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
6 - filter("B"."RANDOMI"=1)
8 - access("A"."TID"="B"."LOGID" AND "A"."SYSNCDATE">"B"."SYSNCDATE" AND "A".
IS NOT NULL)
9 - filter("A"."OWNER"='SYS')
24 rows selected
SQL>
