Oracle Single-column index and composite index performance testing

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"/>

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"/>

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"/>

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"/>

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"/>

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"/>

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"/>

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"/>

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"/>

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 Oracle blog, so be sure to keep this source http://axlewire.blog.51cto.com/1418858/1589683

Oracle Single-column index and composite index performance testing