my question is why there is the full access in the option however I create two index for LIGPOR and DATRAI tables ?? and if there is any one can help me to clearly read the long execution plan in sql oracle
I have this query :
select *
FROM DATRAI D,
LIGPOR L
WHERE D.COMAR= L.COMAR
and TO_CHAR(l.daprm,'yyyy') ='2017';
and this is the execution plan
SQL_ID 0v5mmx6sg8scu, child number 0
-------------------------------------
select * FROM LIGPOR L,DATRAI D WHERE D.COMAR= L.COMAR and
TO_CHAR(l.daprm,:"SYS_B_0") =:"SYS_B_1"
Plan hash value: 3308708566
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | NESTED LOOPS | | 1 | 2177 | 9 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 2177 | 9 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | LIGPOR | 1 | 2138 | 8 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DATRAI1 | 1 | | 0 (0)| |
| 5 | TABLE ACCESS BY INDEX ROWID| DATRAI | 1 | 39 | 1 (0)| 00:00:01
the tables creations
CREATE TABLE "user"."LIGPOR"
(
"COINT" VARCHAR2(11 BYTE) NOT NULL ENABLE,
"COMAR" VARCHAR2(5 BYTE) NOT NULL ENABLE,
"DAPRM" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
CREATE TABLE "user"."DATRAI"
( "COMAR" VARCHAR2(5 BYTE) NOT NULL ENABLE,
"DATRA" DATE NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
the indexes creation
CREATE INDEX "user"."LIGPOR5" ON "user"."LIGPOR" ("COMAR")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
CREATE UNIQUE INDEX "user"."DATRAI1" ON "user"."DATRAI" ("COMAR")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "UBIX_TABLES" ;
thix :)
CodePudding user response：
The SQL you're using is very simple and the plan is very simple also. What the DB is doing is starting with one table, here it chose LIGPO, reading the data using a full scan, as there are no predicates that allows it to use the index. Then, for each row, it uses the index on the second table to see if there are any matching rows (as the second table has an index on the COMAR column). If it finds one it uses the rowid given by the index to read the remaining data from the table. Depeding on the size of your table it may choose to do it the other way around, i.e. start with DATRAI using a full scan, the use the index to check for the matching rows in LIGPO.
What the optimizer does depends on the statistics on the objects, and with a query like yours, it may well choose to do a hash join with a full scan of both tables.
Last remark: do NOT write predicates like TO_CHAR(l.daprm,'yyyy') ='2017', because even if you had an index on DAPRM, the database would not use it. Better do something like ldaprm >= date '2017-01-01' and ldaprm < date '2018-01-01'.
CodePudding user response：
First you must realize that the general rule Index quick - Full table scan slow has few exceptions.
The most obvious are
the table is small it consist only of few blocks (a simgle mutiblock read is better that repeated jumping between the index and the table)
you want to access a large part of the table data (again you die in switching between the index and the table)
I guess the first point is your case.
Let's try to simulate it. Basically you have a parent DATRAI- child LIGPOR tables, connected by the PK/FK column COMAR.
Sample Data
create table DATRAI as
select rownum COMAR,
date'2017-01-01' rownum DATRA
from dual connect by level <= 2;
CREATE UNIQUE INDEX "DATRAI1" ON "DATRAI" ("COMAR");
create table LIGPOR as
select rownum COINT,
mod(rownum,1000) 1 COMAR,
date'2017-01-01' rownum DAPRM
from dual connect by level <= 20;
CREATE INDEX "LIGPOR5" ON "LIGPOR" ("COMAR");
Note that I'm on 18g, where the object statistics are online gathered in create table and index, in former versions you'd need add a call of dbms_stats.gather_table_stats
OLTP Example
Let's start with the typical example (you probably have in mind) - you constraint the parent with the primary key (COMAR = 10) and access all coresponding child rows with the foreign key.
EXPLAIN PLAN SET STATEMENT_ID = 'EXPL_1' into plan_table FOR
SELECT *
FROM DATRAI D
JOIN LIGPOR L ON D.COMAR= L.COMAR
WHERE
D.COMAR = 10 AND
TO_CHAR(l.daprm,'yyyy') ='2017';
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'EXPL_1','ALL'));
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 13 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 28 | 13 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DATRAI | 1 | 12 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | DATRAI1 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| LIGPOR | 1 | 16 | 11 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | LIGPOR5 | 10 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."COMAR"=10)
4 - filter(TO_CHAR(INTERNAL_FUNCTION("L"."DAPRM"),'yyyy')='2017')
5 - access("L"."COMAR"=10)
Short note how to read the execution plan
You start with the row with the first row with highest intend, i.e. the row with Id = 3.
The Id is marked with an asterix, so you check the Predicate Information for the access / filter condition - here access("D"."COMAR"=10)
Ths means you use the index (index name is in the column Name) to select as Oracle informs you in the colum Rows one row.
Next (Id = 2) you take the rowid from the index to access the table
Next (Id = 1) you are in NESTED LOOPS, i.e. for each row you perform the line with Id 5 and 4, i.e. again index acccess with access("L"."COMAR"=10) followed with the rowid table access.
Note the BATCHED is an optimization in recent version - that I can't much comment.
So I guess - this is the execution plan you will enjoy.
Other Example
Let go back to your example, which is basically the same query - only without the PK predicate.
EXPLAIN PLAN SET STATEMENT_ID = 'EXPL_2' into plan_table FOR
SELECT *
FROM DATRAI D
JOIN LIGPOR L ON D.COMAR= L.COMAR
WHERE
TO_CHAR(l.daprm,'yyyy') ='2017';
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'EXPL_2','ALL'));
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 25 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 25 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | LIGPOR | 1 | 14 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DATRAI1 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DATRAI | 1 | 11 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TO_CHAR(INTERNAL_FUNCTION("L"."DAPRM"),'yyyy')='2017')
4 - access("D"."COMAR"="L"."COMAR")
Again the starting line is Id = 3, but here Oracle choose to start with the child table LIGPOR. Why? There is at least a filter predicate for Id = 3 which makes it cheaper for Oracle Cost Based Optimizer.
The alternative would be a full scan of the table DATRAI where you have no filter at all.
*Note the critical difference between an access predicate - active index selection - and a filter predicate - you must process all rows and some of them discard.
The rest is basically the same nested loops as before, only technically performed twice first index access than the table access.
To not make the answer extreme long, I make the Summary
Do not fear
table access fullprematurelyDon't use
select *if you can limit the result column - this could enable Oracle a better optimization