Oracle Indexing Technology

Oracle Indexing Technology
Basic Information
Author: (US) Darl Kuhn Sam R. alapati Bill padfield [Translator's profile]
Translator: Lu Tao
Series name: Turing programming Series
Press: People's post and telecommunications Press
ISBN: 9787115296269
Mounting time:
Published on: February 1, November 2012
Start: 16
Page number: 1
Version: 1-1
Category: Computer> database> Oracle

More about Oracle Index Technology
Introduction
Books
Computer books
Oracle indexing technology was jointly created by three experienced top-level Oracle DBAs to introduce various Oracle indexing types. An index is a database object used to improve query performance. However, improper use may also lead to a reduction in query performance. The author shares many years of practical experience and personal wisdom to help readers avoid misuse of indexes. In addition, this book also provides a lot of advice on index management and maintenance.
Both Oracle database developers and DBAs can benefit from this book.
Directory
Oracle Indexing Technology
Chapter 2 Oracle Index 1
1.1 Use indexes to improve performance 2
1.2 determine which type of index to use 4
1.2.1 B-tree index 5
1.2.2 specific index type 7
1.3 determine the column 10 to be indexed
1.3.1 primary key column and unique key column index 11
1.3.2 foreign key column index 11
1.3.3 Other columns suitable for index creation 12
1.4 index guide 12
1.5 Summary 13
Chapter 2 B-tree index 15
2.1 How Oracle uses B-tree indexes 15
2.1.1 scenario 1: All data is stored in index Block 17
2.1.2 Scenario 2: The index does not contain all information 19
2.1.3? Scenario 3: only table blocks are accessed 20
2.2 prepare to create B-tree index 21
2.2.1 estimate the index size before creation 21
2.2.2 create a separate tablespace 22 for the index
2.2.3 inherit storage parameter 23 from tablespace
2.2.4 Naming Standard 24
2.3 implement B-tree index 24
2.3.1 create B-tree index 24
2.3.2 Report Index 25
2.3.3 code 26 showing the index creation
2.3.4 Delete B-tree index 27
2.4 manage B-tree indexes with constraints 28
2.4.1 create a B-tree index on the primary key column 29
2.4.2 create a B-tree index on the unique key column 33
2.4.3 index of foreign key column 36
2.5 Summary 39
Chapter 2 bitmap Index 40
3.1 bitmap index 41
3.2 Create a bitmap index 44
3.3 create a partition bitmap index 45
3.4 create a bitmap index on the index organization Table 45
3.5 bitmap index impact on query performance 46
3.6 bitmap index impact on data loading performance 50
3.7 understand bitmap connection index 53
3.8 create a bitmap connection index 54
3.9 report bitmap index 55
3.10 summary 55
Chapter 1 index organization Table 56
4.1 index the structure of the organizational Table 56
4.2 advantages of Table Indexing 57
4.3 create an index organization Table 58
4.4 Add Overflow Section 60
4.5 compression index organization Table 62
4.6 construct a secondary index 63
4.7 re-indexing organization Table 66
4.8 index organization table report 67
4.9 summary 68
Chapter 1 specialized index 69
5.1 invisible index 69
5.1.1 usage of invisible indexes 69
5.1.2 create an invisible Index 70
5.1.3 search for invisible indexes in the database 71
5.1.4 enable the optimizer to use an invisible index 71
5.1.5 maintenance of invisible indexes 72
5.2 function-based index 72
5.2.1 create a function-based index 73
5.2.2 restrictions on function-based indexes 76
5.2.3 collect function-based index statistics 77
5.3 index of virtual column 78
5.4 key compression index 80
5.4.1 key compression purpose 81
5.4.2 create a compressed Index 82
5.4.3 key compression and storage 84
5.5 compound index 85
5.5.1 understand index skip scan and composite index 85
5.5.2 sort columns in the composite index 86
5.5.3 select the key 87 for the composite index
5.6 create a virtual index 89
5.7 reverse key index 91
5.7.1 disadvantage of reverse Key Index 92
5.7.2 reverse key index usage 94
5.7.3 create reverse Key Index 94
5.8 application domain Index 94
5.9 Conclusion 95
Chapter 6 partition index 96
6.1 partition index 96
6.2 create a local partition index 97
6.2.1 simplest form 97
6.2.2 partition level requirement 98
6.2.3 prefix and non-prefix option 99
6.3 managing primary keys and unique indexes 99
6.4 create a global partition index 101
6.5 select index 105 for the Application
6.6 maintain the index of the Partition Table 106
6.6.1 add partition 106
6.6.2 truncation partition 107
6.6.3 move partition 108
6.6.4 split partition 108
6.6.5 swap partition 110
6.6.6 Delete partition 111
6.6.7 merge partitions 111
6.7 rebuilding global and non-partition indexes 112
6.8 set the index partition to unavailable and re-create 113
6.9 index impact on Interval partitions 115
6.10 read-only 116 of old data
6.11 report partition index 116
6.12 conclusion 118
Chapter 2 Index usage optimization 7th
7.1 optimizer access path 119
7.2 index scan 120
7.2.1 unique index scan 120
7.2.2 index range scan 121
7.2.3 index skip scan 123
7.2.4 full index scan 124
7.2.5 fast and full index scan 125
7.3 check whether the query uses an index 125
7.4 avoid using indexes 127
7.4.1 no index 127 is used under any circumstances
7.4.2 avoid scanning 128 quickly
7.4.3 force table scan 128
7.5 select 128 between index and table Scan
7.6 cause for the optimizer to ignore the index 129
7.6.1 different rows 129
7.6.2 index clustering factor 130
7.7 The index access path is changed by 131 because no new statistics are available.
7.7.1 use unequal conditions 131
7.7.2 use wildcards to query 133
7.7.3 reference null value 134 in the predicate
7.7.4 include function 135 in the query
7.7.5 skip the index's leading part 136
7.8 force optimizer to use index 136
7.8.1 Application Index prompt 137
7.8.2 application-related tips 138
7.8.3 diagnose failed index prompts 139
7.8.4 adjust the optimizer_index_cost_adj parameter 140
7.8.5 collect accurate statistics for indexes 142
7.9 concurrent index access 143
7.10 conclusion 144
Chapter 4 index maintenance 8th
8.1 collect index statistics 145
8.1.1 dbms_stats package 145
8.1.2 parameter method_opt 147
8.2 process unavailable indexes 148
8.2.1 make index unavailable 149
8.2.2 specify skip_unusable_indexes parameter 150
8.3 space for managing indexes 153
8.3.1 re-indexing to reduce fragmentation by 153
8.3.2 rebuilding the reverse key index 154
8.3.3 reclaim unused space 154
8.3.4 rebuilding the partition index 154
8.3.5 frequent index reconstruction 157
8.4 index_stats view's role in re-indexing 157
8.4.1 advantages of the index_stats view: 158
8.4.2 index_stats view 160
8.5 rebuild index debate 162
8.5.1 reason for re-indexing 162
8.5.2 reasons against reconstruction 163
8.5.3 recommendation for index reconstruction 163
8.6 merge indexes to reduce fragmentation by 164
8.7 index shrinkage to reduce fragmentation by 165
8.8 move tables and indexes 166
8.9 improve index creation efficiency by 167
8.9.1 concurrent index creation 167
8.9.2 avoid generating redo 168 during index creation
8.9.3 large block 169
8.9.4 compression index 169
8.9.5 use multiple options at the same time: 170
8.10 generate DDL to create index 170
8.10.1 use the DBMS_METADATA Package 170
8.10.2 use session_transform Stored Procedure 171
8.10.3 use the set_filter stored procedure 172
8.10.4 use a Data Pump 173
8.11 deleting an index 173
8.12 conclusion 174
Chapter 2 SQL tuning consultant 9th
9.1 contact between tools 176
9.2 automatic SQL optimization job 178
9.2.1 verify that the automatic job is running 178
9.2.2 view the recommendation 179 in the automatic SQL optimization job
9.2.3 generate SQL scripts to implement automatic optimization 181
9.2.4 disable and enable automatic SQL optimization 182
9.3 manage SQL tuning set 183
9.3.1 view SQL 184 that occupies a large amount of resources in AWR
9.3.2 view the SQL 186 statements that use a large amount of resources in the memory
9.3.3 fill SQL tuning set 187 with SQL statements that occupy a large amount of resources in AWR
9.3.4 fill SQL tuning set 188 with SQL statements that occupy a large amount of resources in the memory
9.3.5 fill the SQL tuning set 189 with all the SQL statements in the memory
9.3.6 display SQL tuning set content 190
9.3.7 selectively Delete statements in the SQL tuning set 192
9.3.8 Add the statement to the existing SQL tuning set 193
9.3.9 Delete SQL tuning set 193
9.4 run SQL tuning consultant 193
9.4.1 create optimization task 195
9.4.2 run dbms_sqltune and check recommendation 197
9.4.3 view and delete optimization task 197
9.4.4 run the SQL Tuning Advisor from SQL developer 197
9.4.5 run SQL tuning consultant from Enterprise Manager 198
9.5 conclusion 199
Chapter 2 SQL access consultant 10th
10.1 suggestions for generating a single SQL statement 201
10.2 suggestions for obtaining a set of SQL statements 203
10.3 query consultant view 209
10.4 conclusion 210

Source of this book: China Interactive publishing network