{"id":918,"date":"2026-03-12T12:52:37","date_gmt":"2026-03-12T12:52:37","guid":{"rendered":"https:\/\/forgefuse.wasmer.app\/?p=918"},"modified":"2026-03-12T12:52:37","modified_gmt":"2026-03-12T12:52:37","slug":"oracle-sql-certification-1z0-071-course-section-15-managing-indexes","status":"publish","type":"post","link":"https:\/\/forgefuse.wasmer.app\/index.php\/oracle-sql-certification-1z0-071-course-section-15-managing-indexes\/","title":{"rendered":"Oracle SQL Certification 1Z0-071 Course | Section 15: Managing Indexes"},"content":{"rendered":"\n<p>Indexes are one of the most important tools for improving database performance. They help the Oracle database engine locate rows much faster when executing queries.<\/p>\n\n\n\n<p>Without indexes, the database often has to perform a <strong>full table scan<\/strong>, reading every row to find the data it needs. With indexes, Oracle can quickly navigate to the correct rows, similar to how an index page in a book helps you find topics quickly.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Lesson 1: Indexes \u2013 Overview<\/h4>\n\n\n\n<p>An <strong>index<\/strong> is a separate database object that stores references to rows in a table, allowing Oracle to retrieve data more efficiently.<\/p>\n\n\n\n<p>Indexes are linked to specific columns in a table and act as a fast lookup mechanism for queries.<\/p>\n\n\n\n<h6 class=\"wp-block-heading\">Why Indexes Are Important<\/h6>\n\n\n\n<p>Indexes help the database:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Retrieve rows faster<\/li>\n\n\n\n<li>Reduce the need for full table scans<\/li>\n\n\n\n<li>Improve performance of SELECT queries<\/li>\n<\/ul>\n\n\n\n<p>However, indexes come with a trade-off.<\/p>\n\n\n\n<p>Because indexes must be updated whenever data changes, they can slow down:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>INSERT operations<\/li>\n\n\n\n<li>UPDATE operations<\/li>\n\n\n\n<li>DELETE operations<\/li>\n<\/ul>\n\n\n\n<p>This means indexes improve <strong>query performance<\/strong>, but they add overhead to <strong>data modification operations<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Indexes as Separate Objects<\/h6>\n\n\n\n<p>Indexes are stored separately from the table they reference. They contain:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Indexed column values<\/li>\n\n\n\n<li>Pointers to the corresponding table rows<\/li>\n<\/ul>\n\n\n\n<p>This structure allows Oracle to jump directly to the relevant rows instead of scanning the entire table.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Implicit vs Explicit Indexes<\/h6>\n\n\n\n<p>There are two main ways indexes are created.<\/p>\n\n\n\n<p><strong>Implicit indexes<\/strong><\/p>\n\n\n\n<p>Created automatically by Oracle when certain constraints are defined.<\/p>\n\n\n\n<p><strong>Explicit indexes<\/strong><\/p>\n\n\n\n<p>Created manually by developers or database administrators using SQL statements.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">When the Optimizer Uses Indexes<\/h6>\n\n\n\n<p>Oracle has a <strong>query optimizer<\/strong> that decides whether using an index will improve performance.<\/p>\n\n\n\n<p>Even if an index exists, Oracle may still choose a full table scan if it believes it will be faster.<\/p>\n\n\n\n<p>Understanding this behavior is important when designing efficient queries.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Indexes - Overview - Oracle SQL Certification (1Z0-071) | Section 15, Video 1\" width=\"1290\" height=\"726\" src=\"https:\/\/www.youtube.com\/embed\/-iMMeVoaa1Y?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Lesson 2: Implicit Index Creation<\/h4>\n\n\n\n<p>Oracle automatically creates indexes when certain constraints are defined on a table.<\/p>\n\n\n\n<p>This process is known as <strong>implicit index creation<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Primary Key Indexes<\/h6>\n\n\n\n<p>When you create a <strong>PRIMARY KEY constraint<\/strong>, Oracle automatically generates a unique index to enforce it.<\/p>\n\n\n\n<p>Example:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">ALTER TABLE employees<br>ADD CONSTRAINT emp_pk PRIMARY KEY (employee_id);<\/pre>\n\n\n\n<p>Oracle automatically creates an index for <code>employee_id<\/code>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Unique Constraint Indexes<\/h6>\n\n\n\n<p>The same happens when you define a <strong>UNIQUE constraint<\/strong>.<\/p>\n\n\n\n<p>Example:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">ALTER TABLE employees<br>ADD CONSTRAINT emp_email_uk UNIQUE (email);<\/pre>\n\n\n\n<p>Oracle creates an index to guarantee that the column contains only unique values.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Checking Created Indexes<\/h6>\n\n\n\n<p>Oracle provides several data dictionary views to inspect indexes.<\/p>\n\n\n\n<p>Useful views include:<\/p>\n\n\n\n<p><strong>USER_INDEXES<\/strong><\/p>\n\n\n\n<p>Shows information about indexes owned by the user.<\/p>\n\n\n\n<p><strong>USER_IND_COLUMNS<\/strong><\/p>\n\n\n\n<p>Displays which columns belong to each index.<\/p>\n\n\n\n<p><strong>USER_CONSTRAINTS<\/strong><\/p>\n\n\n\n<p>Shows table constraints and their relationship to indexes.<\/p>\n\n\n\n<p>These views allow you to see what the database has created automatically.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Implicit Index Creation - Oracle SQL Certification (1Z0-071) | Section 15, Video 2\" width=\"1290\" height=\"726\" src=\"https:\/\/www.youtube.com\/embed\/yOaia1YyIa4?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Lesson 3: Single Column Index<\/h4>\n\n\n\n<p>A <strong>single column index<\/strong> is the simplest type of manually created index.<\/p>\n\n\n\n<p>It indexes only one column in a table and helps speed up queries that filter or search using that column.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Creating an Index<\/h6>\n\n\n\n<p>Example:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE INDEX emp_salary_idx<br>ON employees(salary);<\/pre>\n\n\n\n<p>Naming convention typically includes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>table name<\/li>\n\n\n\n<li>column name<\/li>\n\n\n\n<li><code>_idx<\/code> suffix<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Dropping an Index<\/h6>\n\n\n\n<p>If an index is no longer needed, it can be removed.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">DROP INDEX emp_salary_idx;<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">When the Optimizer Uses an Index<\/h6>\n\n\n\n<p>Oracle decides whether to use an index based on the query conditions.<\/p>\n\n\n\n<p>Indexes are commonly used with:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>equality comparisons (<code>=<\/code>)<\/li>\n\n\n\n<li>range conditions (<code>><\/code>, <code>&lt;<\/code>, <code>BETWEEN<\/code>)<\/li>\n\n\n\n<li>prefix searches with <code>LIKE<\/code><\/li>\n<\/ul>\n\n\n\n<p>Indexes are often <strong>not used<\/strong> when:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>functions are applied to indexed columns<\/li>\n\n\n\n<li><code>NOT EQUAL<\/code> conditions are used<\/li>\n\n\n\n<li>a large percentage of rows must be retrieved<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Rule of Five<\/h6>\n\n\n\n<p>A common best practice suggests limiting indexes to <strong>about five per table<\/strong>.<\/p>\n\n\n\n<p>Too many indexes can slow down data modification operations significantly.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Single Column Index - Oracle SQL Certification (1Z0-071) | Section 15, Video 3\" width=\"1290\" height=\"726\" src=\"https:\/\/www.youtube.com\/embed\/IftHoHcGI0c?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Lesson 4: Composite and Unique Indexes<\/h4>\n\n\n\n<p>More advanced indexing techniques involve <strong>unique indexes<\/strong> and <strong>composite indexes<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Unique Index<\/h6>\n\n\n\n<p>A <strong>unique index<\/strong> ensures that all values in a column are distinct.<\/p>\n\n\n\n<p>Example:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE UNIQUE INDEX emp_email_idx<br>ON employees(email);<\/pre>\n\n\n\n<p>If a duplicate value is inserted, Oracle raises an error.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Composite Index<\/h6>\n\n\n\n<p>A <strong>composite index<\/strong> indexes multiple columns together.<\/p>\n\n\n\n<p>Example:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE INDEX emp_name_dept_idx<br>ON employees(last_name, department_id);<\/pre>\n\n\n\n<p>This type of index is useful when queries frequently filter using multiple columns.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Column Order Matters<\/h6>\n\n\n\n<p>In composite indexes, the <strong>order of columns is critical<\/strong>.<\/p>\n\n\n\n<p>Example:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">(last_name, department_id)<\/pre>\n\n\n\n<p>Oracle can efficiently use the index when queries include:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>last_name<\/code><\/li>\n\n\n\n<li><code>last_name<\/code> and <code>department_id<\/code><\/li>\n<\/ul>\n\n\n\n<p>But the index may not be used if only <code>department_id<\/code> is referenced.<\/p>\n\n\n\n<p>The first column in the index has the highest priority.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Composite and Unique Index - Oracle SQL Certification (1Z0-071) | Section 15, Video 4\" width=\"1290\" height=\"726\" src=\"https:\/\/www.youtube.com\/embed\/lP5Q6oOMlRo?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Lesson 5: Visible and Invisible Indexes<\/h4>\n\n\n\n<p>Oracle allows indexes to be marked as <strong>visible<\/strong> or <strong>invisible<\/strong>.<\/p>\n\n\n\n<p>An invisible index still exists in the database but is ignored by the query optimizer.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Why Invisible Indexes Are Useful<\/h6>\n\n\n\n<p>Invisible indexes are commonly used for:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>performance testing<\/li>\n\n\n\n<li>troubleshooting query plans<\/li>\n\n\n\n<li>safely evaluating whether an index is needed<\/li>\n<\/ul>\n\n\n\n<p>Instead of dropping an index, you can temporarily hide it from the optimizer.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Creating an Invisible Index<\/h6>\n\n\n\n<p>Example:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE INDEX emp_salary_idx<br>ON employees(salary)<br>INVISIBLE;<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Changing Visibility<\/h6>\n\n\n\n<p>You can toggle visibility using:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">ALTER INDEX emp_salary_idx VISIBLE;<\/pre>\n\n\n\n<p>or<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">ALTER INDEX emp_salary_idx INVISIBLE;<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h6 class=\"wp-block-heading\">Checking Index Visibility<\/h6>\n\n\n\n<p>Index visibility can be verified using Oracle data dictionary views such as <code>USER_INDEXES<\/code>.<\/p>\n\n\n\n<p>This allows administrators to monitor how indexes are configured and used.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Visible And Invisible Indexes - Oracle SQL Certification (1Z0-071) | Section 15, Video 5\" width=\"1290\" height=\"726\" src=\"https:\/\/www.youtube.com\/embed\/VJiVb7APNlE?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Final Thoughts<\/h4>\n\n\n\n<p>Indexes are one of the most powerful tools for improving database performance. When used correctly, they can dramatically speed up queries and reduce database workload.<\/p>\n\n\n\n<p>However, they must be designed carefully. Too many indexes can slow down data modifications and increase maintenance overhead.<\/p>\n\n\n\n<p>Understanding how Oracle creates, manages, and uses indexes will help you build faster queries and more efficient database systems, which is essential both for the <strong>Oracle 1Z0-071 certification exam<\/strong> and for real-world database development.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this section, you will learn how indexes work, how they are created automatically and manually, and how they influence query performance.<\/p>\n","protected":false},"author":1,"featured_media":919,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":13,"footnotes":""},"categories":[2,11,4],"tags":[7,13,12],"class_list":["post-918","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-article","category-oracle-1z0-071-sql-course","category-videoandarticle","tag-1z0-071","tag-certification","tag-sql"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/forgefuse.wasmer.app\/index.php\/wp-json\/wp\/v2\/posts\/918","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/forgefuse.wasmer.app\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/forgefuse.wasmer.app\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/forgefuse.wasmer.app\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/forgefuse.wasmer.app\/index.php\/wp-json\/wp\/v2\/comments?post=918"}],"version-history":[{"count":0,"href":"https:\/\/forgefuse.wasmer.app\/index.php\/wp-json\/wp\/v2\/posts\/918\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/forgefuse.wasmer.app\/index.php\/wp-json\/wp\/v2\/media\/919"}],"wp:attachment":[{"href":"https:\/\/forgefuse.wasmer.app\/index.php\/wp-json\/wp\/v2\/media?parent=918"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/forgefuse.wasmer.app\/index.php\/wp-json\/wp\/v2\/categories?post=918"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/forgefuse.wasmer.app\/index.php\/wp-json\/wp\/v2\/tags?post=918"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}