SQL Operators: Access Paths
A row source is a set of rows returned by a step in the execution plan. A SQL operator acts on a row source.
A unary operator acts on one input, as with access paths. A binary operator acts on two outputs, as with joins.
An access path is a technique used by a query to retrieve rows from a row source.
We will be discussing on Major Access path i.e Table Access Path and Index Access Path in this article.
1=> Table Access Paths
Below are popular access path for Table . => Full Table Scan -(No Index Exists) A full table scan reads all rows from a table, and then filters out those rows that do not meet the selection criteria. Because there is no Index on table ,Access is being done thriugh Full table scan. This is the most common access path for optimizer when there is No INDEX on source table. Optimizer tries to fetch all records from table and do filters based on condition. As below example doesn’t contain any index, it’s got accessed through FULL TABLE Scan. SQL> set lines 400 SQL> set pages 400 SQL> select * from table(dbms_xplan.display_cursor('2nhvn3vpjadzv',0)); PLAN_TABLE_OUTPUT ------------------------- SQL_ID 2nhvn3vpjadzv, child number 0 ------------------------------------- select /* compare */ * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID =0 Plan hash value: 76495054 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 35 (100)| | |* 1 | TABLE ACCESS FULL| DEMO_TAB | 2 | 28 | 35 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("CONS_ID"=0 AND "SHOP_ID"=0 AND "PRODUCT"='WHEELS') 19 rows selected. => Full Table Scan -(Index Exists But Table Column Contains NULL ) The optimizer cannot use the index to count the number of table rows because the index cannot contain null entries. Whenever there is NULL in INDEX Columns values Optimizer will go for FULL TABLE scan if there is NO specific predicate supplied to Query. When we use predicate INDEX data got filtered and access path may got changed and to select only NON NULL data from INDEX as in below example. SQL> select count(*) from emp_null where EMP_NO IS NULL; COUNT(*) ---------- 7 SQL> col INDEX_NAME for a20 SQL> col TABLE_NAME for a20 SQL> col COLUMN_NAME for a20 SQL> select index_name,table_name,column_name from dba_ind_columns where table_name='EMP_NULL'; INDEX_NAME TABLE_NAME COLUMN_NAME -------------------- -------------------- -------------------- IDX_NULL EMP_NULL EMP_NO SQL> desc emp_null Name Null? Type ----------------------------- EMP_NO NUMBER EMP_AGE NUMBER SQL> explain plan for select EMP_NO from EMP_NULL; Explained. SQL> set lines 300 SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ----------------------- Plan hash value: 2944648810 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 3621K| 17M| 2029 (1)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMP_NULL | 3621K| 17M| 2029 (1)| 00:00:01 | ------------------------------------------------------------------------------ 8 rows selected. SQL> explain plan for select EMP_NO from EMP_NULL where EMP_NO< 100; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------- Plan hash value: 338935661 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3774 | 18870 | 16 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IDX_NULL | 3774 | 18870 | 16 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ------------------- 1 - access("EMP_NO"<100) 13 rows selected. SQL> => Full table Scan –(High Degree of Parallelism). SQL> select count(*) from EMP_TEST; COUNT(*) ---------- 2000 SQL> desc EMP_TEST Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER GENDER VARCHAR2(1) SQL> SELECT DISTINCT GENDER FROM emp_test; Gender ------- M F N S V T 6 rows selected. SQL> CREATE INDEX GENDER_IDX ON EMP_TEST(GENDER); Index created. SQL> EXPLAIN PLAN FOR SELECT * FROM EMP_TEST WHERE GENDER='S'; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT ------------------- Plan hash value: 889310681 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 199 | 1194 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP_TEST | 199 | 1194 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | GENDER_IDX | 199 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ----------- 2 - access("GENDER"='S') 14 rows selected. SQL> ALTER TABLE EMP_TEST PARALLEL (DEGREE 15); Table altered. SQL> EXPLAIN PLAN FOR SELECT * FROM EMP_TEST WHERE GENDER='S'; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT ------------------ Plan hash value: 1161765406 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 199 | 1194 | 2 (0)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM)| :TQ10000 | 199 | 1194 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 199 | 1194 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | |* 4 | TABLE ACCESS FULL| EMP_TEST | 199 | 1194 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ----------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("GENDER"='S') Note ----- - Degree of Parallelism is 15 because of table property 20 rows selected. SQL> => Full table Scan –(Query is Unselective). If the optimizer determines that the query requires most of the blocks in the table, then it uses a full table scan, even though indexes are available. Full table scans can use larger I/O calls. Making fewer large I/O calls is cheaper than making many smaller calls. SQL> SELECT COUNT(*) FROM EMP_NAMES; COUNT(*) ---------- 6500 SQL> SELECT EMP_LNAME,COUNT(*) FROM EMP_NAMES GROUP BY EMP_LNAME; EMP_LNAME COUNT(*) ---------- ---------- REDDY 300 SINGH 4200 DIWAN 1500 TIWARI 500 SQL> SELECT INDEX_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='EMP_NAMES'; INDEX_NAME COLUMN_NAME ----------- IDX_SELECTIVE EMP_LNAME SQL> EXPLAIN PLAN FOR SELECT * FROM EMP_NAMES WHERE EMP_LNAME='REDDY'; Explained. SQL> SET LINES 300 SQL> SET PAGES 300 SQL> EXPLAIN PLAN FOR SELECT * FROM EMP_NAMES WHERE EMP_LNAME='REDDY'; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT ---------------- Plan hash value: 4062229589 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 284 | 2840 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP_NAMES | 284 | 2840 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_SELECTIVE | 286 | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMP_LNAME"='REDDY') 14 rows selected. SQL> EXPLAIN PLAN FOR SELECT * FROM EMP_NAMES WHERE EMP_LNAME='SINGH'; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT ----------------------- Plan hash value: 2618431616 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4167 | 41670 | 7 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP_NAMES | 4167 | 41670 | 7 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("EMP_LNAME"='SINGH') 13 rows selected. => Table accessed by ROWID A rowid is an internal representation of the storage location of data. The rowid of a row specifies the data file and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row because it specifies the exact location of the row in the database. First Rowid got fetched from INDEX and then data fetched from TABLE using Rowid. Batched search is used to reduce I/O traversing. i.e Index got scanned in bulk and then trimmed to access ROWID SQL> EXPLAIN PLAN FOR SELECT * FROM EMP_NAMES WHERE EMP_LNAME='REDDY'; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT ----------------------- Plan hash value: 4062229589 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 284 | 2840 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP_NAMES | 284 | 2840 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_SELECTIVE | 286 | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMP_LNAME"='REDDY') 14 rows selected.
2=> Index Access Paths
B-Tree Structure B-Tree contains branch blocks & Leaf Blocks Brach Block is used for Searching/Traversing where as Leaf Block stores actual data. The Leaf block are interconnected to each other and have doubly-linked relation with each other. Leaf block store actual rows with row-id to locate in table. Below mentioned are B-Tree INDEX Access path through which optimizer build row-set. => INDEX UNIQUE SCAN A unique index access path returns maximum 1 row. A unique index access path need “=” as predicate operator. All columns of UNIQUE INDEX have to be part of EQUALITY operator. To satisfy this access path there has to be PRIMARY KEY INDEX or UNIQUE KEY INDEX where UNIQUENESS has to be “UNIQUE” in USER_INDEX table. No other INDEX should exist on the same table column with NONUNIQUE nature. Let we create Index having UNIQUENESS and UNIQ in USER_INDEX column as below. SQL> create table UNIQ_TEST (emp_id number ,emp_name varchar2(20)); Table created. SQL> insert into UNIQ_TEST values (1,'JEFF'); 1 row created. SQL> insert into UNIQ_TEST values (2,'RAU'); 1 row created. SQL> insert into UNIQ_TEST values (3,'DIPTI'); 1 row created. SQL> COMMIT; Commit complete. SQL> CREATE UNIQUE INDEX UNQ_IDX_NON_P ON UNIQ_TEST(EMP_ID); Index created. SQL> explain plan for select * from UNIQ_TEST where EMP_ID=2; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT ---------------------- Plan hash value: 2732305634 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| UNIQ_TEST | 1 | 25 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | UNQ_IDX_NON_P | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMP_ID"=2) 14 rows selected. Let we Check Uniqueness for this INDEX as below. SQL> select INDEX_NAME,TABLE_NAME,UNIQUENESS from user_indexes where table_name='UNIQ_TEST'; INDEX_NAME TABLE_NAME UNIQUENES ------------------------------------------------------------------------------------------------------- UNQ_IDX_NON_P UNIQ_TEST UNIQUE SQL> Let we have another example with having Primary Key as below, SQL> create table UNIQ_TEST (emp_id number primary key,emp_name varchar2(20)); Table created. SQL> insert into UNIQ_TEST values (1,'JEFF'); 1 row created. SQL> insert into UNIQ_TEST values (2,'RAU'); 1 row created. SQL> insert into UNIQ_TEST values (3,'DIPTI'); 1 row created. SQL> COMMIT; Commit complete. SQL> SET LINES 400 SQL> SET PAGES 400 SQL> COL TABLE_NAME FOR A20 SQL> COL COLUMN_NAME FOR A20 SQL> SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = '&TABLE_NAME' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner ORDER BY cols.table_name, cols.position; Enter value for table_name: UNIQ_TEST old 3: WHERE cols.table_name = '&TABLE_NAME' new 3: WHERE cols.table_name = 'UNIQ_TEST' TABLE_NAME COLUMN_NAME POSITION STATUS OWNER -------------------- -------------------- ---------- -------- ----- UNIQ_TEST EMP_ID 1 ENABLED ABHI_TEST SQL> select INDEX_NAME,TABLE_NAME,UNIQUENESS from user_indexes where table_name='UNIQ_TEST'; INDEX_NAME TABLE_NAME UNIQUENES --------------------------------------------------------------------------------------- SYS_C007723 UNIQ_TEST UNIQUE SQL> explain plan for select * from UNIQ_TEST where EMP_ID=2; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT -------------------- Plan hash value: 4089452479 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| UNIQ_TEST | 1 | 25 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | SYS_C007723 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMP_ID"=2) 14 rows selected. Let we have another example with having NON-UNIQUE INDEX as below,INDEX RANGE SCAN is used due to NON-UNIQUE Nature. SQL> ALTER TABLE UNIQ_TEST DROP CONSTRAINTS SYS_C007723; Table altered. SQL> CREATE INDEX NON_UNQ_IDX ON UNIQ_TEST(EMP_ID); Index created. SQL> explain plan for select * from UNIQ_TEST where EMP_ID=2; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT ------------------------ Plan hash value: 289486799 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| UNIQ_TEST | 1 | 25 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | NON_UNQ_IDX | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMP_ID"=2) Note ----- - dynamic statistics used: dynamic sampling (level=2) 18 rows selected. => INDEX RANGE SCAN Index range scan is an Ordered Scan of Index values.(DEFAULT) Optimizer choose index for Query when it’s have high selectivity. Leading columns of Concatenated index must be part of conditions Predicate operator could be >,>=,<,<= Wild-card searches of the form col1 LIKE '%ASD' must not be in a leading position. Selectivity of INDEX must be less that FTS or any other index to be eligible to INDEX RANGE SCAN. If “=” operator is used then INDEX Uniqueness has to be NONUNIQUE. SQL> CREATE INDEX NON_UNQ_IDX ON UNIQ_TEST(EMP_ID); Index created. SQL> explain plan for select * from UNIQ_TEST where EMP_ID=2; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT ----------------- Plan hash value: 289486799 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| UNIQ_TEST | 1 | 25 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | NON_UNQ_IDX | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMP_ID"=2) 18 rows selected. SQL> select INDEX_NAME,TABLE_NAME,UNIQUENESS from user_indexes where table_name='UNIQ_TEST'; INDEX_NAME TABLE_NAME UNIQUENES -------------------------------------------------- NON_UNQ_IDX UNIQ_TEST NONUNIQUE SQL> => INDEX FULL SCAN INDEX Full Scan read entire INDEX in ORDERED way. It eliminates extra work of sorting INDEX by ORDER. When a Predicate references a COLUMN from Index. A query includes an ORDER BY on indexed columns, which does NOT CONTAINS ANY NULL value If NO PREDICATE is specified, then ALL of the BELOW condition has to meet. a.All columns in the table and in the query are in the index. b.At least one indexed column is not null. How It reads actually. The database reads the root block, and then navigates down the left hand side of the index (or right if doing a descending full scan) until it reaches a leaf block. Then the database reaches a leaf block, the scan proceeds across the bottom of the index, one block at a time, in sorted order. The database uses single-block I/O rather than multiblock I/O. SQL> EXPLAIN PLAN FOR SELECT SAT_NAME,SAT_ID FROM SATTELITE ORDER BY SAT_ID; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT ---------------------- Plan hash value: 3333187628 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6499 | 77988 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| SATTELITE | 6499 | 77988 | 3 (0)| 00:00:01 | | 2 | INDEX FULL SCAN | FULL_IDX_SCAN | 6499 | | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- 9 rows selected. SQL> => INDEX FAST FULL SCAN An index fast full scan reads the index blocks in unsorted order, as they exist on disk. This scan does not use the index to probe the table, but reads the index instead of the table, essentially using the index itself as a table. The INDEX_FFS(table_name index_name) hint forces a fast full index scan. The database uses multiblock I/O to read the root block and all of the leaf and branch blocks. The databases ignores the branch and root blocks and reads the index entries on the leaf blocks. Let we continue with above example and changing Columns in Select . SQL> set lines 400 SQL> set pages 400 SQL> EXPLAIN PLAN FOR SELECT SAT_ID FROM SATTELITE ORDER BY SAT_ID; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT ----------------- Plan hash value: 3834974934 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6499 | 25996 | 7 (15)| 00:00:01 | | 1 | SORT ORDER BY | | 6499 | 25996 | 7 (15)| 00:00:01 | | 2 | INDEX FAST FULL SCAN| FULL_IDX_SCAN | 6499 | 25996 | 6 (0)| 00:00:01 | --------------------------------------------------------------------------------------- 9 rows selected. SQL> => INDEX SKIP SCAN An index skip scan occurs when the initial column of a composite index is "skipped" or not specified in the query. This type of scan happens on Multi-Column INDEX. The leading column of a composite index is not specified in the query predicate. For example, the query predicate does not reference the cust_gender column, and the composite index key is (cust_gender,cust_email). Many distinct values exist in the nonleading key of the index and relatively few distinct values exist in the leading key. For example, if the composite index key is (cust_gender,cust_email), then the cust_gender column has only two distinct values, but cust_email has thousands. SQL> SELECT COUNT(*) FROM SATTELITE; COUNT(*) ---------- 6499 SQL> SELECT DISTINCT SAT_CAT FROM SATTELITE; S - Y X SQL> SELECT SAT_LOCATION FROM SATTELITE WHERE ROWNUM<10; SAT_LOCATI ---------- X4 X5 X8 X10 X12 X14 X16 X18 X20 9 rows selected. SQL> SELECT SAT_LOCATION FROM SATTELITE WHERE SAT_LOCATION IS NULL; no rows selected SQL> SELECT SAT_CAT FROM SATTELITE WHERE SAT_LOCATION IS NULL; no rows selected SQL> CREATE INDEX IDX_SKP_SCN ON SATTELITE(SAT_CAT,SAT_LOCATION); Index created. SQL> @gather_stats.sql Enter value for owner: ABHI_TEST Enter value for name: SATTELITE old 2: DBMS_STATS.GATHER_TABLE_STATS(ownname => '&OWNER', tabname =>'&name', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE); new 2: DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'SATTELITE', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE); PL/SQL procedure successfully completed. SQL> EXPLAIN PLAN FOR SELECT * FROM SATTELITE WHERE SAT_LOCATION='X190'; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT ----------------------------------- Plan hash value: 2657963293 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 24 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SATTELITE | 1 | 24 | 4 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | IDX_SKP_SCN | 1 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SAT_LOCATION"='X190') filter("SAT_LOCATION"='X190') 15 rows selected. SQL> => INDEX JOIN SCAN An index join scan is a hash join of multiple indexes that together return all columns requested by a query. The database does not need to access the table because all data is retrieved from the indexes. The cost of retrieving rows from the table is higher than reading the indexes without retrieving rows from the table. An index join is often expensive. For example, when scanning two indexes and joining them, it is often less costly to choose the most selective index, and then probe the table. You can specify an index join with the INDEX_JOIN(table_name) hint. SQL> EXPLAIN PLAN FOR SELECT /*+ INDEX_JOIN(SATTELITE) */ SAT_ID,SAT_LOCATION FROM SATTELITE WHERE SAT_LOCATION='X18' ORDER BY SAT_ID; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT ------- Plan hash value: 2745389282 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 35 (3)| 00:00:01 | | 1 | SORT ORDER BY | | 1 | 10 | 35 (3)| 00:00:01 | | 2 | VIEW | index$_join$_001 | 1 | 10 | 34 (0)| 00:00:01 | |* 3 | HASH JOIN | | | | | | | 4 | INDEX FAST FULL SCAN| FULL_IDX_SCAN | 1 | 10 | 19 (0)| 00:00:01 | |* 5 | INDEX FAST FULL SCAN| IDX_SKP_SCN | 1 | 10 | 24 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access(ROWID=ROWID) 5 - filter("SAT_LOCATION"='X18') 18 rows selected.
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444