Performance Test of oracle single-column Index and combined Index

There are two tables, T1 and T2

Table structure of T1

The table structure of T2

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 synonymous construction

Insert into T1 select * from T1

Insert into T2 select * from T2

Build the data altogether:

T1 has 262144 lines

T2 has 524288 lines

To ensure that the query test is accurate (not affected by the cached results)

The following statement is executed before the next query is executed

ALTER SYSTEM FLUSH SHARED_POOL (clear statement, execute plan cache)

ALTER SYSTEM FLUSH BUFFER_CACHE (clear data cache)

Select t1.a1 from T1 from T2 where t1.a3=t2.a3 and t2.a1recording www'

Select t1.a1 from T1 join T2 using (A3) where t2.a1 recording www'

Let's use it first.

Select t1.a1 from T1 from T2 where t1.a3=t2.a3 and t2.a1recording www'

test

Explain plan for select t1.a1 from T1 from T2 where t1.a3=t2.a3 and t2.a1recording www'

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY)

1. Do not build any index

T1 and T2 are all table scans.

2. Build a cable in t2.a1

Look at the query results

T1 full table scan, T2 range index scan

3. Build non-unique indexes in t2.a1 and t1.a3

4. Build a non-unique index on t2.a1, dint t2.a3 and t1.a3

5, build a composite index on t1.a1, and a non-unique index on t2.a3

Select t1.a1 from T1 from T2 where t1.a3=t2.a3 and t2.a1recording www'

6. Set up a combined index in the A1 and T3 columns of T1 and T2

Depending on the type of index and the where constraint, there are four types of index scans:

Index unique scan (index unique scan)

Index range scan (index range scan)

Index full scan (index full scan)

Index Quick scan (index fast full scan)

Follow up and gradually improve the test results!