Oracle Apps DBA

Oracle Concatenated Indexes - Practical Deep Dive Oracle concatenated index deep dive Concatenated Indexes How composite indexes really work, why column order matters, and when skip scan changes the story Concatenated indexes, also called composite indexes, are easy to explain badly and surprisingly rich to explain well. The usual summary is “Oracle can use the index only when the leading column is present,” but that is only the starting point. To design them properly, you need to think about leading portions, equality versus range predicates, ordering requirements, skip scan eligibility, covering behavior, and whether one composite index can replace several single-column indexes in a given workload. Contents 01 What concatenated indexes are 02 Leading edge and leading portion 03 Why column order matters 04 Skip scan and when it helps 05 Access patterns and plan reading 06 Covering and sort elimination 07 Design rules that actually hold 08 Common mistakes 09 End-to-end demo 1...

Oracle Single-Column Index Access - Practical Deep Dive Oracle Performance Series Oracle Single-Column Index Access How Oracle decides between unique scan, range scan, full table scan, full index scan, and fast full index scan Single-column indexes look simple, but the execution plans they trigger are shaped by selectivity, row ordering, rowid lookup cost, requested columns, and whether the optimizer can get the answer from the index alone. This guide turns that apparently small topic into a practical mental model you can use when reading plans and designing indexes. Contents 01 Foundations 02 Unique vs range scan 03 Why full scans win 04 Clustering factor 05 Full vs fast full 06 Covering and ordering 07 Reading real plans 08 End-to-end lab 09 Quiz Section 01 The real question is not “is there an index?” but “what is the cheapest way to get the rows?” Oracle does not reward a query just because an index exists. The optimizer compares access paths and chooses the one with th...

Oracle Different Access Paths - Practical Deep Dive Oracle Performance Series Oracle Different Access Paths How the optimizer chooses between full scans, rowid access, range scans, fast full scans, skip scans, and related paths One of the most important Oracle performance skills is recognizing that two queries with similar predicates can take very different routes to the data. This guide turns ?access paths? into something operational: what each path means, why Oracle chooses it, how to validate the choice, and how to teach your eye to read plans without falling for simplistic index folklore. Contents 01 Access-path mindset 02 Table access paths 03 Index access paths 04 Less obvious index paths 05 Plan-reading workflow 06 Practical lab 07 Monitoring and comparison 08 Quiz Section 01 Access paths are the optimizer?s route choices, not moral judgments An access path is simply the method Oracle uses to find rows in a table or index. The optimizer compares many possible plans, ...