Today - University of South Carolina

Today - University of South Carolina

Chapter 6 Creating and Managing Indexes Using Proc SQL 1 What is An Index? An index is an auxiliary file that stores the physical location of values for one or more specified

columns in a table. An index stores unique values for a specified column(s) in ascending order. An index includes value/identifier pairs that allow you to access a row 2 Why Use An Index? When a query is submitted on a table, PROC SQL accesses rows in a

table in the order in which they are stored in the table, beginning with the first row and reading all rows in the table. For large tables, it can be time consuming for PROC SQL to read all the rows in a table. 3 Why Use An Index? An index on a table may allow a PROC SQL query to execute more

efficiently. We can refer to the LOG to view real time and CPU time (we will learn about STIMER in Chapter 8) for a partial measure of efficiency gains 4 Simple and Composite Indexes Two types of indexes can be created

Simple based on one column. The name of the index must be the name of the indexed column. Composite based on two or more columns. The name of the composite index cannot be the name of any existing column or index in the table. 5 Example - Creating A Simple Index

Suppose we had a table (meddb) containing all doctors claims for 100,000 patients. If we wanted to create an index on the patients Social Security Number (ssn), we would use the following code. proc sql; create index ssn on meddb(ssn); quit; 6 Example - Creating A Composite Index

If we wanted to create a composite index on the patients Social Security Number (ssn) and the type of service the patient received (tos), we would use the following code proc sql; create index ssntos on meddb(ssn,tos); quit; 7 Unique Indexes

Simple and composite indexes can be defined as unique indexes. Add the word unique before the word index when creating a unique index If a table contains multiple occurrences of the same value in a column, a unique index cannot be defined on that column. 8 Costs of Using an Index

Additional CPU time Additional input/output requests may be required Additional memory Additional disk space to store the index file 9 Guidelines for Creating Indexes

Keep the number of indexes to a minimum Do not create an index for small tables Do not create an index on a column that has a small number of distinct values (low cardinality) Use indexes for queries that return a small subset of rows (< 15%) 10 Guidelines for Creating Indexes

Create an index when the key column occurs in A WHERE clause that contains a comparison operator, CONTAINS operator, or LIKE operator A WHERE clause that contains a SUBSTR or TRIM function 11 Guidelines for Creating Indexes Create an index when the key column occurs in A subquery that returns values to the IN operator

A correlated subquery that compares the correlated reference with the key column 12 Guidelines for Creating Indexes Create an index when the key column Occurs in a join in which all columns are indexed for one of the tables in the join Occurs in a join in which the JOIN expression contains an equality (=) operator Has been pre-sorted

13 Describe Table Statement Use to display a CREATE INDEX statement in the SAS log for each index that is defined If no indexes are defined for the specified table, a CREATE INDEX statement will not appear in the SAS log 14

MSGLEVEL = Option To determine if an index is used, specify the SAS system option MSGLEVEL=I. The default for the MSGLEVEL= option is MSGLEVEL=N. This will display notes, warnings, and error messages only. OPTIONS MSGLEVEL=I; 15 IDXWHERE = Option IDXWHERE=YES

Tells SAS to choose the best index to process the WHERE expression IDXWHERE=NO Tells SAS to ignore all indexes

IDXNAME= index name Tells SAS to use the specified index 16 Dropping Indexes Use the drop index statement to drop(delete) one or more indexes proc sql;

drop index ssn from claims; quit; 17

Recently Viewed Presentations

  • Team Colorado

    Team Colorado

    Jonathan Padia, CO Dept of Education. Jessica Bonilla, Little Giants Learning Center. Emily Wengrovius, CO Dept of Human Services. Sherry Valdez, San Luis Valley Early Childhood Council. Wendy Peters-Moschetti, Livewell CO. Liza Marron, Exec. Director San Luis Valley Local Foods...
  • Carbon Based Polymers - PC&#92;|MAC

    Carbon Based Polymers - PC\|MAC

    Nylon carpet, polyester and acrylic seats, polyester curtains, nylon screen, polyester film strip, waxy polyethylene popcorn tub, starch in popcorn, polystyrene cups, plastic M&M bag, protein in hotdogs, gelatin in gummy bears, paraffin in Junior Mints, sticky stuff on the...
  • AUTOMATIC TRANSFER SWITCHES - Russelectric

    AUTOMATIC TRANSFER SWITCHES - Russelectric

    ATS. If an acceptable window of voltage and frequency is not achieved, desired transfer will not happen. Example: ATS needs to return to utility after being in the engine generator (emergency) position… engine speed running erratically, no window achieved… switch...
  • Unit 5 - NWCG

    Unit 5 - NWCG

    Do not run pump dry for more than a few seconds. ... Maintain trailer brakes, wheel bearings, electrical system, engine oil, air filter, spark arrestor, and general integrity of the unit. Batch Mixer Maintenance. Keep 2" male plugs in place...
  • JuliCha - Massachusetts Institute of Technology

    JuliCha - Massachusetts Institute of Technology

    JuliCha. AbstractRNG. LCG is the old standard. Imperfect, but good enough for many practical purposes. The ChaCha CS-PRNG. State consists of 16 32-bit words. From initial configuration, apply transformation via a sequence ofadditions, bit shifts, and xors.
  • La Historia y tradiciones, de Dia de los muertos

    La Historia y tradiciones, de Dia de los muertos

    Ofrendas and altares are decorated with sugar skulls, candles, marigolds, and paper mache skeletons. Plates of food, beverages and other favorite items are placed on the altars as well as salt and bread which have significant meanings. Gravesites are sometimes...
  • Matthew O. Ward Professor Computer Science WPI data

    Matthew O. Ward Professor Computer Science WPI data

    XmdvTool. 20 Years Since 1994 "It . is always exciting to work with . others … to see their . data . in new . ways … and help them . understand their data in a way that is much
  • Digestion

    Digestion

    Digestion Grade 9