Indexes should be created for columns with many unique values or those frequently used in joins. It is recommended to limit the number of indexes to reduce disk space and update costs. Indexes are particularly effective for queries that retrieve a relatively small number of rows (less than 15%). Indexing small tables or columns with few distinct values generally does not lead to performance gains. The name of a simple index must match the name of the column it indexes, while a composite index cannot have the same name as an existing column.
Data Analysis
Type : CREATION_INTERNE
Examples use generated data (CREATE TABLE ... LIKE) from an implicitly available table (COUNTRIES, often from SASHELP) or created tables.
1 Code Block
PROC SQL Data
Explanation :
This example creates a new table named `newcountries` using the structure of the `countries` table. Then, it creates a simple index named `area` on the `area` column of the `newcountries` table. The index name here matches the column name.
This example creates a new table named `newcountries` using the structure of the `countries` table. Then, it creates a simple index named `area` on the `area` column of the `newcountries` table. The index name here matches the column name.
Copied!
| 1 | PROC SQL; |
| 2 | create TABLE newcountries |
| 3 | like countries; |
| 4 | create index area |
| 5 | on newcountries(area); |
| 6 | QUIT; |
2 Code Block
PROC SQL
Explanation :
This example creates a composite index named `places` on the `name` and `continent` columns of the `newcountries` table. A composite index is used to optimize queries involving multiple columns simultaneously.
This example creates a composite index named `places` on the `name` and `continent` columns of the `newcountries` table. A composite index is used to optimize queries involving multiple columns simultaneously.
Copied!
| 1 | PROC SQL; |
| 2 | create index places |
| 3 | on newcountries(name, continent); |
| 4 | QUIT; |
3 Code Block
PROC SQL
Explanation :
This example illustrates the creation of a unique composite index. Adding the `UNIQUE` keyword ensures that each combination of values in the `name` and `continent` columns is unique. Any attempt to insert or update a row that would violate this uniqueness would be rejected by SAS.
This example illustrates the creation of a unique composite index. Adding the `UNIQUE` keyword ensures that each combination of values in the `name` and `continent` columns is unique. Any attempt to insert or update a row that would violate this uniqueness would be rejected by SAS.
Copied!
| 1 | PROC SQL; |
| 2 | create TABLE newcountries |
| 3 | like countries; |
| 4 | create unique index places |
| 5 | on newcountries(name, continent); |
| 6 | QUIT; |
4 Code Block
PROC SQL
Explanation :
This example shows how to drop an existing index from a table. The `DROP INDEX` command followed by the index name and the target table is used to remove the `places` index from the `newcountries` table.
This example shows how to drop an existing index from a table. The `DROP INDEX` command followed by the index name and the target table is used to remove the `places` index from the `newcountries` table.
Copied!
| 1 | |
| 2 | PROC SQL; |
| 3 | drop index places from newcountries; |
| 4 | QUIT; |
| 5 |
This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.
Copyright Info : Copyright © SAS Institute Inc. All Rights Reserved
