Summary

Text Summary

Understanding Indexes

An index is an auxiliary file that is defined on one or more of a table's columns, which are called key columns. The index stores the unique column values and a system of directions that enable access to rows in that table by index value. When an index is used to process a PROC SQL query, PROC SQL accesses directly (without having to read all the prior rows) instead of sequentially.
You can create two types of indexes:
  • simple index (an index on one column)
  • composite index (an index on two or more columns).
You can define either type of index as a unique index, which requires that values for the key column(s) be unique for each row.

Deciding Whether to Create an Index

When deciding whether to create an index, you must weigh any benefits in performance improvement against the costs of increased resource usage. Certain classes of PROC SQL queries can be optimized by using an index. To optimize the performance of your PROC SQL queries, you can follow some basic guidelines for creating indexes.

Creating an Index

To create an index on one or more columns of a table, use the CREATE INDEX statement. To specify a unique index, you add the keyword UNIQUE.

Displaying Index Specifications

To display a CREATE INDEX statement in the SAS log for each index that is defined for one or more specified tables, use the DESCRIBE TABLE statement.

Managing Index Usage

To manage indexes effectively, it is important to know how SAS decides whether to use an index and which index to use.
To find out whether an index is being used, specify the SAS option MSGLEVEL=I in an OPTIONS statement before the PROC SQL statement. This option enables SAS to write informational messages about index usage (and other additional information) to the SAS log. The default setting MSGLEVEL=N displays notes, warnings, and error messages only.
To force SAS to use the best available index, to use a specific index, or not to use an index at all, include either the SAS data set option IDXWHERE= or IDXNAME= in your PROC SQL query.

Dropping Indexes

To drop (delete) one or more indexes, use the DROP INDEX statement.

Sample Programs

Creating a Simple, Unique Index, and a Composite Index

proc sql;
   create unique index EmpID
      on work.payrollmaster(empid);
   create index daily
      on work.marchflights(flightnumber,date);
quit;

Displaying Index Specifications

proc sql;
   describe table marchflights;
quit;

Determining Whether SAS Is Using an Index

options msglevel=i;
proc sql;
   select *
      from marchflights
      where flightnumber='182';
quit;

Directing SAS to Ignore All Indexes

proc sql;
   select *
      from marchflights (idxwhere=no)
      where flightnumber='182';
quit;

Directing SAS to Use a Specified Index

proc sql;
   select *
      from marchflights (idxname=daily)
      where flightnumber='182';
quit;

Dropping an Index

proc sql;
   drop index daily
      from work.marchflights;
quit;

Points to Remember

  • An index cannot be created on a view.
  • Keep the number of indexes to a minimum to reduce disk storage and update costs.
  • Do not create an index for small tables; sequential access is faster on small tables.
  • Do not create an index based on columns that have a very small number of distinct values, low cardinality (for example, a Gender column that contains only the two values Male and Female).
  • Use indexes for queries that retrieve a relatively small subset of rows — that is, less than 15%.
  • Do not create more than one index that is based on the same column as the primary key.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.118.253.55