Total two sheets t1,t2
Table Structure of the T1
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/56/C3/wKiom1SM7aDh9FgMAAFNXvJU8Ms226.jpg "title=" 1.png " alt= "Wkiom1sm7adh9fgmaafnxvju8ms226.jpg" style= "padding:0px;margin:0px;vertical-align:top;border:none;"/>
Table Structure of the T2
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/56/C3/wKiom1SM7dSCS6nFAAE5Jn1wIfw943.jpg "title=" 2.png " alt= "Wkiom1sm7dscs6nfaae5jn1wifw943.jpg" style= "padding:0px;margin:0px;vertical-align:top;border:none;"/>
T1 and T2 through
INSERT into T1 values (' www ', ' aaa ', ' BBB ');
INSERT into T1 values (' www1 ', ' aaa1 ', ' bbb1 ');
INSERT into T1 values (' www2 ', ' aaa2 ', ' bbb2 ');
INSERT into T1 values (' www3 ', ' aaa3 ', ' bbb3 ');
T2 construction of the same empathy
INSERT INTO T1 select * from T1;
INSERT INTO T2 select * from T2;
Build Data total:
T1 Total 262144 rows
T2 Total 524288 rows
To ensure accurate query testing (not affected by cached results)
The following statements are executed before the next query is executed
ALTER SYSTEM FLUSH shared_pool (empty statement, execution plan cache)
ALTER SYSTEM FLUSH buffer_cache (empty data cache)
Select t1.a1,t2.a1 from T1,t2 where t1.a3=t2.a3 and t2.a1= ' www ';
Select t1.a1,t2.a1 from t1 join T2 using (A3) where t2.a1= ' www ';
Let's start with
Select t1.a1,t2.a1 from T1,t2 where t1.a3=t2.a3 and t2.a1= ' www ';
Test
Explain plan for select t1.a1,t2.a1 from T1,t2 where t1.a3=t2.a3 and t2.a1= ' www ';
SELECT * from TABLE (Dbms_xplan. DISPLAY);
1, do not build any index
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/56/C0/wKioL1SM7rPiO8jSAAN0dADWmzI552.jpg "title=" 3.png " alt= "Wkiol1sm7rpio8jsaan0dadwmzi552.jpg" width= "650" style= "Padding:0px;margin:0px;vertical-align:top;border: none; "/>
T1,t2 are all full-table scans.
2, in the T2.A1 construction cable
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/56/C3/wKiom1SM7mKSS2wIAACc1VVeDwI540.jpg "title=" 4.png " alt= "Wkiom1sm7mkss2wiaacc1vvedwi540.jpg" width= "650" style= "Padding:0px;margin:0px;vertical-align:top;border: none; "/>
See query results
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/56/C0/wKioL1SM7zTD3BkzAAOw_trWR6o878.jpg "title=" 5.png " alt= "Wkiol1sm7ztd3bkzaaow_trwr6o878.jpg" width= "650" style= "Padding:0px;margin:0px;vertical-align:top;border: none; "/>
T1 full table scan, T2 range Index Scan
3. Non-unique index in T2.A1 and t1.a3
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/56/C3/wKiom1SM73myy97bAAPeTdOSTNU819.jpg "title=" 6.png " alt= "Wkiom1sm73myy97baapetdostnu819.jpg" width= "650" style= "Padding:0px;margin:0px;vertical-align:top;border: none; "/>
4. Non-unique index in T2.A1,T2.A3 and t1.a3
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/56/C1/wKioL1SM8FPyW4-5AAPRLGpzcfU661.jpg "title=" 7.png " alt= "Wkiol1sm8fpyw4-5aaprlgpzcfu661.jpg" width= "650" style= "Padding:0px;margin:0px;vertical-align:top;border: none; "/>
5, build a composite index in t1.a1,t1.a3, build a non-unique index in T2.A3
Select t1.a1,t2.a1 from T1,t2 where t1.a3=t2.a3 and t2.a1= ' www ';
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/56/C3/wKiom1SM7_XytUXfAAMh97QNIMo660.jpg "title=" 8.png " alt= "Wkiom1sm7_xytuxfaamh97qnimo660.jpg" width= "650" style= "Padding:0px;margin:0px;vertical-align:top;border: none; "/>
6. Set up the combined index in T1,T2 A1 and T3 columns
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/56/C1/wKioL1SM8LySdB6EAAMlbMJNn0w656.jpg "title=" 9.png " alt= "Wkiol1sm8lysdb6eaamlbmjnn0w656.jpg" width= "650" style= "Padding:0px;margin:0px;vertical-align:top;border: none; "/>
There are 4 types of index scans, depending on the type of index and where constraints:
Index unique scanning (index unique scan)
Index range scanning (index ranges scan)
Index full scan
Index fast Scan (index fast full scan)
Follow the steps to refine the test results!
This article is from the "is Wayward" blog, please be sure to keep this source http://9756063.blog.51cto.com/9746063/1604099
Oracle Single-column index and composite index performance testing