Esri ArcGIS Enterprise EGMP2201 Question # 15 Topic 2 Discussion

 Understanding the Scenario:

The query filters rows based on inspector_name and sorts the results by inspection_date.

Creating an index optimizes data retrieval by reducing the number of rows scanned during the query execution.

 Key Considerations for Indexing:

Primary Filter Column:The inspector_name column is the primary filter in the WHERE clause. Indexing this column ensures quick identification of rows matching the filter condition.

Sort Optimization:The ORDER BY inspection_date clause benefits from indexing inspection_date as the second column in a composite index, which accelerates sorting for the filtered results.

 Index Selection:

A composite index on inspector_name and inspection_date enables efficient query execution:

inspector_name ensures fast filtering.

inspection_date optimizes sorting.

Indexing unrelated columns like facility_type is unnecessary, as it is not part of the query.

 Steps to Create the Index:

In the enterprise geodatabase, use the database management tools or SQL commands to create a composite index:

CREATE INDEX idx_facility_inspections

ON facility_inspections (inspector_name, inspection_date);

 References:

Esri Documentation: Creating and Managing Indexes.

SQL Indexing Best Practices: Guidelines for composite index creation to optimize queries.

 Why the Correct Answer is D:A composite index on inspector_name and inspection_date directly addresses the query structure, optimizing both the WHERE filter and the ORDER BY clause.